Google Sheets Apps Script复制的validate函数跨脚本运行报错问题

问题根因 #

错误由全局变量初始化时机错误导致：
你将active_spreadsheet、sheet、cell、lastRow定义为全局变量，Google Apps Script每次执行任意函数（包括触发器触发的onOpen、onEdit、validate）时，都会先执行全局作用域的代码再运行目标函数逻辑。全局初始化时如果没有有效激活的工作表/单元格，或是初始化的lastRow值已经和运行时的表格实际状态不一致，就会导致后续范围操作的行数小于1，抛出对应错误。你的单函数版本没有这类全局变量，因此可以正常运行。

修复方案 #

1. 移除所有全局变量，改为在各函数内部实时获取运行时的表格、工作表、单元格、行数等状态值，避免值过时或上下文无效。
2. 在validate函数中明确指定操作的工作表，不要依赖getActiveSheet()的结果，避免切换激活工作表后操作错误的对象。
3. 增加外部导入数据的行数校验，避免外部表格为空时抛出错误。

修改后完整代码 #

function onOpen() 
{
  const active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = active_spreadsheet.getActiveSheet();
  sheet.getRange('O2').setValue('Klik tu in izberi akcijo')
     .setFontColor("red")
     .setFontWeight("bold")
     .setBorder(true, true, true, true, true, true);
}

function validate() 
{
  const ss1 = SpreadsheetApp.getActiveSpreadsheet();
  // 明确指定生成下拉的工作表为第一个表，避免激活其他表时出错
  const sht1 = ss1.getSheets()[0];
  const ss4imp = SpreadsheetApp.openById('1wOo-ntaLOIcDrFuB9y3WwAOsRm1GyAFOcIacCewQfUo');
  const sht4imp = ss4imp.getSheets()[0];
  const sht2 = ss1.getSheets()[1];
  const lastRowOfImpItems = sht4imp.getLastRow();
  // 兜底校验，外部表无数据时直接退出
  if(lastRowOfImpItems < 1) {
    Logger.log("外部导入表无有效下拉选项数据");
    return;
  }
  const rng4 = sht4imp.getRange(1,1,lastRowOfImpItems,1).getValues();
  const rng1 = sht1.getRange(2,2,1,1);
  const rng2 = sht2.getRange(1,1,lastRowOfImpItems,1).setValues(rng4);
  const rule = SpreadsheetApp.newDataValidation().requireValueInRange(rng2).build();
  rng1.setDataValidation(rule);
}

function onEdit(e)
{
  const active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = active_spreadsheet.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const cell = sheet.getActiveCell();
  const row = e.range.getRow();
  const column = e.range.getColumn();
  const cellValue = e.range.getValue();
  
  if(column == 15 && cellValue == "vrini vrstico na vrh" )
  {
    sheet.insertRowBefore(2);
    cell.setValue('Klik tu in izberi akcijo');
    sheet.getRange(2,column).setValue('Klik tu in izberi akcijo');
  }
  if(column == 15 && cellValue == "vrini vrstico nad trenutno" )
  {
    sheet.insertRowBefore(row);
    cell.setValue('Klik tu in izberi akcijo');
    sheet.getRange(row+1,column).setValue('Klik tu in izberi akcijo');
  }
  if(column == 15 && cellValue == "vrini vrstico pod trenutno" )
  {
    sheet.insertRowAfter(row);
    cell.setValue('Klik tu in izberi akcijo');
    sheet.getRange(row+1,column).setValue('Klik tu in izberi akcijo');
  }
  if(column == 15 && cellValue == "dodaj vrstico na konec" )
  {
    sheet.insertRowAfter(lastRow);
    cell.setValue('Klik tu in izberi akcijo');
    validate();
    sheet.getRange(lastRow+1,column).setValue('Klik tu in izberi akcijo')
     .setFontColor("red")
     .setFontWeight("bold")
     .setBorder(true, true, true, true, true, true)
     .setWrap(true)
     .setHorizontalAlignment("center");
    sheet.getRange(lastRow+1,16).setValue(lastRow);
  }
  for (var i = 0; i <= lastRow-1; i = i + 1) 
  {
    sheet.getRange(i+2,1).setValue(lastRow-i);
  }
}

function onChange(e){
  const sh = e.source.getActiveSheet();
  const lastRow = sh.getLastRow();
  if(e.changeType == 'INSERT_ROW' || e.changeType == 'REMOVE_ROW')
  { 
    if(sh.getName()=='Main')
    {
      for (var i = 0; i <= lastRow-1; i = i + 1) 
      {
        sh.getRange(i+2,1).setValue(lastRow-i);
      }
    }  
  }
}

注意事项 #

• 如果操作的工作表是固定的，建议直接通过getSheetByName('工作表名称')或getSheets()[索引]的方式指定工作表，不要依赖getActiveSheet()的结果，避免手动切换激活工作表后执行逻辑出错。
• 所有随表格内容变化的状态值（比如行数、激活单元格）都要在函数运行时实时获取，不要存储在全局变量中，全局变量初始化后不会随表格更新自动刷新。

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