Google Apps Script优化Google Sheets每日行程数据排序的数组/JSON方案咨询

现有程序通过Appsheet收集乘客信息，提交后存入Google Sheets的BUS PASSENGER DETAILS SHEET；通过sortGender()函数按Trip Number每日将数据分类至对应单元格。当前代码仅支持第12行（C12:AF12，对应Day1），需扩展至12-42行（对应每月1-31天，C12:AF42）。原代码可正常运行但仅覆盖首行，需实现高效生成覆盖全月的定位数组，或构建并使用JSON文件实现需求。

原代码片段：

const sheetRanges = {
    "1": {male: "C12", female: "D12"},
    "2": {male: "E12", female: "F12"},
    "3": {male: "G12", female: "H12"},
    "4": {male: "I12", female: "J12"},
    "5": {male: "K12", female: "L12"},
    "6": {male: "M12", female: "N12"},
    "7": {male: "O12", female: "P12"},
    "8": {male: "Q12", female: "R12"},
    "9": {male: "S12", female: "T12"},
    "10": {male: "U12", female: "V12"},
    "11": {male: "W12", female: "X12"},
    "12": {male: "Y12", female: "Z12"},
    "13": {male: "AA12", female: "AB12"},
    "14": {male: "AC12", female: "AD12"},
    "15": {male: "AE12", female: "AF12"}
  };

function sortGender(callback){
  var range = sheetNameDAVAO1.getRange(lastrow_davao1, 1, 1, 5);
  var value = range.getValues();
  Logger.log(value);

  value.forEach(x => {
    var currentTripNumber = values[1];
    var currentGender = values[3];
    if (currentTripNumber in sheetRanges && currentGender == "Male") {
      var maleRange = sheetNameOverall.getRange(sheetRanges[currentTripNumber].male);
      Logger.log(currentTripNumber);
      var addMale = maleRange.getValue() + 1;
      maleRange.setValue(parseInt(addMale));
      sheetNameMonthly.getRange("X10").setValue(parseInt(addMale));
      sheetNameOverall.getRange("J8").setValue(parseInt(addMale));

    } else if(currentTripNumber in sheetRanges && currentGender == "Female") {
      var femaleRange = sheetNameOverall.getRange(sheetRanges[currentTripNumber].female);
      var addFemale = femaleRange.getValue() + 1;
      femaleRange.setValue(parseInt(addFemale));
      sheetNameMonthly.getRange("X11").setValue(parseInt(addFemale))
      sheetNameOverall.getRange("J8").setValue(parseInt(addFemale));
    }
  });
  callback()
}

一、高效生成全月定位数组 #

不需要手动编写31天的单元格映射，可通过单元格规律自动生成：

• 行号规则：Day1对应行12，Day n对应行11+n（Day31对应行42）
• 列号规则：每个Trip占2列，Male列是奇数位（C=3, E=5...），Female列是紧随其后的偶数位（D=4, F=6...）
• 用SpreadsheetApp.getColumnLetter()将列号转换为Google Sheets的字母格式

生成映射的函数 #

// 自动生成1-31天、1-15个Trip的单元格位置映射
function generateSheetRanges() {
  const sheetRanges = {};
  // 遍历1-31天
  for (let day = 1; day <= 31; day++) {
    const row = 11 + day;
    sheetRanges[day] = {};
    // 遍历1-15个Trip
    for (let trip = 1; trip <= 15; trip++) {
      const maleCol = 2 + (trip * 2); // Trip1对应列3（C），Trip2对应列5（E）...
      const femaleCol = maleCol + 1;
      sheetRanges[day][`trip${trip}_male`] = `${SpreadsheetApp.getColumnLetter(maleCol)}${row}`;
      sheetRanges[day][`trip${trip}_female`] = `${SpreadsheetApp.getColumnLetter(femaleCol)}${row}`;
    }
  }
  return sheetRanges;
}

修改后的sortGender函数 #

function sortGender(callback) {
  const sheetNameDAVAO1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("BUS PASSENGER DETAILS SHEET");
  const lastrow_davao1 = sheetNameDAVAO1.getLastRow();
  const range = sheetNameDAVAO1.getRange(lastrow_davao1, 1, 1, 5);
  const values = range.getValues()[0]; // 修正原代码变量名错误
  Logger.log(values);

  // 从乘客数据中提取日期（假设第5列是日期，根据实际列调整）
  const tripDate = new Date(values[4]);
  const dayOfMonth = tripDate.getDate(); // 获取当月天数（1-31）

  const sheetRanges = generateSheetRanges();
  const currentTripNumber = values[1];
  const currentGender = values[3];

  // 验证Day和Trip是否在有效范围内
  const targetCellKey = `trip${currentTripNumber}_${currentGender.toLowerCase()}`;
  if (sheetRanges[dayOfMonth] && sheetRanges[dayOfMonth][targetCellKey]) {
    const targetCell = sheetRanges[dayOfMonth][targetCellKey];
    const sheetNameOverall = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Overall Sheet"); // 替换为实际表名
    const sheetNameMonthly = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Monthly Sheet"); // 替换为实际表名

    // 更新对应单元格计数
    const targetRange = sheetNameOverall.getRange(targetCell);
    const currentCount = targetRange.getValue() || 0;
    const newCount = parseInt(currentCount) + 1;
    targetRange.setValue(newCount);

    // 更新汇总单元格（根据实际需求调整）
    if (currentGender === "Male") {
      sheetNameMonthly.getRange("X10").setValue(newCount);
    } else {
      sheetNameMonthly.getRange("X11").setValue(newCount);
    }
    sheetNameOverall.getRange("J8").setValue(newCount);
  }

  callback();
}

这种方法的优势：完全自动生成映射，避免手动输入错误，后续调整天数或Trip数量只需修改循环参数即可。

二、使用JSON文件存储静态映射 #

如果单元格位置固定不需要动态生成，可将映射保存为JSON文件，方便团队共享修改：

步骤1：生成并保存JSON文件 #

运行上述generateSheetRanges()函数，在日志中复制生成的JSON内容，在Google Apps Script项目中创建新文件sheetRanges.json，粘贴内容。

步骤2：加载JSON并修改sortGender函数 #

// 加载项目内的JSON映射文件
function loadSheetRanges() {
  // 从项目文件中读取JSON（需确保sheetRanges.json已创建）
  const jsonFile = DriveApp.getFilesByName("sheetRanges.json").next();
  return JSON.parse(jsonFile.getBlob().getDataAsString());
}

function sortGender(callback) {
  const sheetNameDAVAO1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("BUS PASSENGER DETAILS SHEET");
  const lastrow_davao1 = sheetNameDAVAO1.getLastRow();
  const range = sheetNameDAVAO1.getRange(lastrow_davao1, 1, 1, 5);
  const values = range.getValues()[0];
  Logger.log(values);

  const tripDate = new Date(values[4]);
  const dayOfMonth = tripDate.getDate();
  const sheetRanges = loadSheetRanges();
  const currentTripNumber = values[1];
  const currentGender = values[3];

  const targetCellKey = `trip${currentTripNumber}_${currentGender.toLowerCase()}`;
  if (sheetRanges[dayOfMonth] && sheetRanges[dayOfMonth][targetCellKey]) {
    const targetCell = sheetRanges[dayOfMonth][targetCellKey];
    const sheetNameOverall = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Overall Sheet");
    const sheetNameMonthly = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Monthly Sheet");

    const targetRange = sheetNameOverall.getRange(targetCell);
    const currentCount = targetRange.getValue() || 0;
    const newCount = parseInt(currentCount) + 1;
    targetRange.setValue(newCount);

    if (currentGender === "Male") {
      sheetNameMonthly.getRange("X10").setValue(newCount);
    } else {
      sheetNameMonthly.getRange("X11").setValue(newCount);
    }
    sheetNameOverall.getRange("J8").setValue(newCount);
  }

  callback();
}

这种方法适合单元格位置固定的场景，映射修改无需改动代码，直接编辑JSON文件即可。

内容的提问来源于stack exchange，提问作者Dean