如何避免先写入列表值再清除？优化Google Apps Script执行效率

你的脚本运行缓慢的核心原因是频繁与电子表格进行读写交互——尤其是循环中调用setValue()，以及先写入完整数据再清理单元格的冗余流程。我们可以把所有数据处理逻辑移到内存中完成，最后一次性写入表格，彻底砍掉冗余操作。

优化后的代码 #

function PLANNING() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const PLsheet = ss.getSheetByName("PLANNING_DATA");
  const COsheet = ss.getSheetByName("1_REALISATION PROJETS");

  // 1. 获取源数据并生成初始结果集
  const values = COsheet.getRange('A2:AA' + COsheet.getLastRow()).getValues();
  let res = values.flatMap(valueRange => {
    const DUR = valueRange[7] instanceof Date ? new Date(valueRange[7].getTime() - (100 * 86400000)) : valueRange[7];
    const BAT = valueRange[7] instanceof Date ? new Date(valueRange[7].getTime() - (15 * 86400000)) : valueRange[7];

    return [
      [valueRange[5], valueRange[1], valueRange[6], DUR, valueRange[7], valueRange[8], valueRange[2], 'durée projet'],
      [valueRange[5], valueRange[1], valueRange[6], BAT, BAT, 'BK', valueRange[2], 'date BAT'],
      [valueRange[5], valueRange[1], valueRange[6], valueRange[7], valueRange[7], 'BK', valueRange[2], 'date BAP'],
    ];
  });

  // 2. 填充空的PROJECT值（内存中处理）
  if (res.length > 0) {
    let lastProject = res[0][1];
    res = res.map(row => {
      if (row[1] === "") {
        row[1] = lastProject;
      } else {
        lastProject = row[1];
      }
      return row;
    });
  }

  // 3. 填充空的FINISH?值（内存中处理）
  if (res.length > 0) {
    let lastFinish = res[0][6];
    res = res.map(row => {
      if (row[6] === "") {
        row[6] = lastFinish;
      } else {
        lastFinish = row[6];
      }
      return row;
    });
  }

  // 4. 执行清理逻辑（内存中处理，无需写入后再清除）
  res = res.map(row => {
    // 清理空task对应的单元格
    if (row[2] === "") {
      row[3] = "";
      row[4] = "";
    }
    // 清理FINISH?为TRUE的单元格
    if (row[6] === true) {
      row[3] = "";
      row[4] = "";
    }
    return row;
  });

  // 5. 写入表头和处理后的完整数据
  PLsheet.getRange('M1:T1').setValues([['2D TASK','2D PROJECT', '2D RESPONSIBLE', "START DATE", 'END DATE', 'PROGRESS', 'FINISH?', 'PHASE']]);
  if (res.length > 0) {
    PLsheet.getRange(2, 13, res.length, res[0].length).setValues(res);
  } else {
    // 若无数据，清除之前的内容
    PLsheet.getRange(2,13,PLsheet.getLastRow()-1,8).clearContent();
  }
}

关键优化点 #

• 砍掉冗余读写：原代码多次读写单元格，优化后仅保留2次批量写入操作（表头+数据），彻底避免循环中的setValue()调用——这类单次操作的耗时是批量操作的数百倍。
• 内存中完成所有处理：填充空值、清理无效数据的逻辑全部在数组中完成，无需先写入表格再修改，消除冗余流程。
• 简化对象获取：提前获取Spreadsheet对象，避免重复调用getActiveSpreadsheet()，减少不必要的方法执行。

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