为何Apps Script编辑器执行与触发器执行表现不同？

问题现象 #

编写的脚本用于循环填充表格后复制到新电子表格，存在以下异常：

• 在Apps Script编辑器中运行时，流程正常：填充完成后再复制表格
• 通过自定义菜单项或嵌入图片触发器执行时，填充表格的batchSocialWorker函数尚未执行完毕，就已开始复制表格，导致目标电子表格中出现未完成的中间状态，影响用户使用

用户提供代码 #

核心循环代码 #

//loop thru supervisor, create new page
for (let i = 0; i < swConcise.length; i++){

let swName = swConcise[i];

//draw social worker report function
batchSocialWorker(swName);

//copy to alt ss
const sheet = baseSS.getSheetByName('SocialWorkersReport');
sheet.copyTo(newSS);

//rename sheet
newSS.getSheets()[i+1].setName(swName);

}

完整代码 #

function batchSocialWorkers(){

/*
1. create new ss, set baseSS, newSS vars
2. create directors array
3. loop directors, copy sheet to newSS and rename
4. create link in new sheet
*/

const baseSS = SpreadsheetApp.getActiveSheet();

//create new ss titled date, sw Report
const today = date();

const title = "Social Workers Reports " + today;

const id = createSS (title);
const newSS = SpreadsheetApp.openById(id);

//scrape social workers names
const swAll = baseSS.getSheetByName('Relationships').getRange('F11:F').getValues();

//create concise s0cial workers array
const swConcise = cleanupArray(swAll);

//loop thru supervisor, create new page
for (let i = 0; i < swConcise.length; i++){

let swName = swConcise[i];

//draw social worker report function
batchSocialWorker(swName);

//copy to alt ss
const sheet = baseSS.getSheetByName('SocialWorkersReport');
sheet.copyTo(newSS);

//rename sheet
newSS.getSheets()[i+1].setName(swName);

}  

//delete first 2 sheets
newSS.deleteSheet(newSS.getSheets()[0]);
newSS.deleteSheet(newSS.getSheets()[0]);

//provide link to new sheet
const link = 'https://docs.google.com/spreadsheets/d/' + id;
var ui = SpreadsheetApp.getUi();
ui.alert('Batch Social Workers Reports Created', link, ui.ButtonSet.OK);
}

function createSS (title) {
// This code uses the Sheets Advanced Service, but for most use cases
// the built-in method SpreadsheetApp.create() is more appropriate.
try {
let sheet = Sheets.newSpreadsheet();
sheet.properties = Sheets.newSpreadsheetProperties();
sheet.properties.title = title;
const spreadsheet = Sheets.Spreadsheets.create(sheet);

return spreadsheet.spreadsheetId;
} catch (err) {
// TODO (developer) - Handle exception
console.log('Failed with error %s', err.message);
}
}

function cleanupArray (array){

let newArray = new Array();

for (let i = 0; i < array.length; i++){

if (array[i][0] != '') newArray.push(array[i][0]);

}

return newArray;
}

解决方法 #

1. 强制同步表格操作 #

Apps Script在触发器/菜单上下文下，部分写入操作会延迟执行（性能优化机制），导致batchSocialWorker的填充操作未完成就执行复制。在填充后添加SpreadsheetApp.flush()，强制所有待处理操作立即同步到云端：

修改后的循环代码：

for (let i = 0; i < swConcise.length; i++){
  let swName = swConcise[i];
  // 生成社工报告
  batchSocialWorker(swName);
  // 强制刷新所有未完成的表格写入操作
  SpreadsheetApp.flush();
  // 复制到目标表格
  const sheet = baseSS.getSheetByName('SocialWorkersReport');
  // 复制后直接获取新工作表对象并命名
  const copiedSheet = sheet.copyTo(newSS);
  copiedSheet.setName(swName);
}

2. 修正baseSS对象类型 #

原代码中baseSS = SpreadsheetApp.getActiveSheet()获取的是单个工作表，后续调用getSheetByName会出错（该方法属于Spreadsheet对象），应改为：

const baseSS = SpreadsheetApp.getActiveSpreadsheet();

3. 优化工作表重命名逻辑 #

避免依赖索引定位新复制的工作表，直接使用copyTo返回的对象操作，避免因工作表数量变化导致的索引错误。

4. 检查batchSocialWorker内部同步 #

如果batchSocialWorker函数内部有大量单元格写入操作，建议在函数末尾添加SpreadsheetApp.flush()，确保所有填充操作完成后再返回。

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