Google Apps Script循环中遇Range not Found Error及模板赋值问题

问题背景 #

使用Google Apps Script（GAS）实现批量模板生成：将表格每行变量填充到指定单元格，生成对应模板后处理下一行。单模板生成正常，添加循环后先出现“Range not Found”错误；调整代码后能循环创建标签页，但数据赋值未生效，添加Utilities.sleep也无改善。

初始报错代码 #

function CreateTemplates() {
   var templatesNeeded = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test');            
   var rundownSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Exclusive Template Creation');
   var tabname = templatesNeeded.getRange('A2').getValue();
   var pasteTemplate = rundownSheet.getRange('e1:l150').getValues();

    const row = 2;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Test');
  const rows = sheet.getLastRow() - row + 1;
  const range = sheet.getRange(row, 1, rows, 1);
  const values = range.getValues().flat();

 const dateRange = sheet.getRange(row, 2, rows, 1);
  const dateValues = dateRange.getValues().flat();


   const gameRange = sheet.getRange(row, 3, rows, 1);
  const gameValues = gameRange.getValues().flat();


   const typeRange = sheet.getRange(row, 4, rows, 1);
  const typeValues = typeRange.getValues().flat();

     const broadcastRange = sheet.getRange(row, 5, rows, 1);
  const broadcastValues = broadcastRange.getValues().flat();

  const matches = {};
  values.forEach((value, i) => {
    if (value !== '') {

  
 rundownSheet.getRange('E3').setValue(templatesNeeded.getRange(values).getValue());
   rundownSheet.getRange('B1').setValue(templatesNeeded.getRange(dateValues).getValue());
   rundownSheet.getRange('B2').setValue(templatesNeeded.getRange(gameValues).getValue());
   rundownSheet.getRange('B3').setValue(templatesNeeded.getRange(typeValues).getValue());
rundownSheet.getRange('H2').setValue(templatesNeeded.getRange(broadcastValues).getValue());

 
     var tss = SpreadsheetApp.openById('16nn9mfSOpVbPZBsB_nwhRNRv4nYQblrS1GzAD4jgmoQ');

 var sheet = tss.getSheetByName('Single Template');


 sheet.copyTo(tss).setName(tabname);


 
// Logger.log(value)

  sheet.getRange(1,1,pasteTemplate.length,pasteTemplate[0].length).setValues(pasteTemplate);



Utilities.sleep(1000);
    }
  });
}

调整后仍有赋值问题的代码 #

function createNewTemplates() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName('Test');
  const sh2 = ss.getSheetByName('Exclusive Template Creation');
  const sh1A2 = sh1.getRange('A2').getValue();
  const vs2 = sh2.getRange('e1:l150').getValues();
  const sr = 2;
  const vs1 = sh1.getRange(sr, 1, sh1.getLastRow() - sr + 1, 5).getValues();
  const tss = SpreadsheetApp.openById('16nn9mfSOpVbPZBsB_nwhRNRv4nYQblrS1GzAD4jgmoQ');
  vs1.forEach((r, i) => {
    if (r[0] !== '') {
       Utilities.sleep(2000);
      sh2.getRange('E3').setValue(r[0]);
      sh2.getRange('B1').setValue(r[1]);
      sh2.getRange('B2').setValue(r[2]);
      sh2.getRange('B3').setValue(r[3]);
      sh2.getRange('H2').setValue(r[4]);
    
      let sh = tss.getSheetByName('Single Template');
      sh.copyTo(tss).setName(r[0]);
      let shnew = tss.getSheetByName(r[0]);
      Utilities.sleep(200);
      shnew.getRange(1, 1, vs2.length, vs2[0].length).setValues(vs2);
        
    }
  });
}

问题原因分析 #

初始代码报错原因 #

1. Range参数错误：templatesNeeded.getRange(values)中，values是扁平化数组，而getRange()仅接受单元格地址字符串或行列参数，直接传数组会触发“Range not Found”错误。
2. 变量复用冲突：循环内重新定义var sheet = tss.getSheetByName('Single Template')，覆盖了外层的const sheet = ss.getSheetByName('Test')，导致后续操作逻辑混乱。
3. 标签名固定：tabname = templatesNeeded.getRange('A2').getValue()仅获取A2单元格值，循环生成的所有标签页名称重复，引发命名冲突。

调整后代码赋值失效原因 #

1. 模板数据获取时机错误：vs2 = sh2.getRange('e1:l150').getValues()在循环外执行，此时未填充当前行变量，导致所有新标签页都使用初始模板数据，而非填充后的最新内容。
2. 冗余休眠操作：Utilities.sleep无法解决核心逻辑问题，反而降低执行效率。

解决方案 #

修改代码，确保每次循环先填充变量到模板页，再获取填充后的模板数据，最后复制并命名新标签页：

function createNewTemplates() {
  const ss = SpreadsheetApp.getActive();
  const dataSheet = ss.getSheetByName('Test');
  const templateSheet = ss.getSheetByName('Exclusive Template Creation');
  const targetSS = SpreadsheetApp.openById('16nn9mfSOpVbPZBsB_nwhRNRv4nYQblrS1GzAD4jgmoQ');
  const sourceTemplate = targetSS.getSheetByName('Single Template');
  
  // 获取数据行（从第2行开始，共5列）
  const dataRows = dataSheet.getRange(2, 1, dataSheet.getLastRow() - 1, 5).getValues();
  
  dataRows.forEach(row => {
    const tabName = row[0];
    if (!tabName) return; // 跳过空行
    
    // 1. 填充当前行数据到模板页指定单元格
    templateSheet.getRange('E3').setValue(row[0]);
    templateSheet.getRange('B1').setValue(row[1]);
    templateSheet.getRange('B2').setValue(row[2]);
    templateSheet.getRange('B3').setValue(row[3]);
    templateSheet.getRange('H2').setValue(row[4]);
    
    // 2. 获取填充后的模板数据
    const filledTemplateData = templateSheet.getRange('E1:L150').getValues();
    
    // 3. 复制源模板并命名
    const newTab = sourceTemplate.copyTo(targetSS).setName(tabName);
    
    // 4. 将填充后的数据写入新标签页
    newTab.getRange(1, 1, filledTemplateData.length, filledTemplateData[0].length).setValues(filledTemplateData);
  });
}

关键优化点 #

• 循环内获取模板数据：每次循环都读取填充后的最新模板内容，确保新标签页携带当前行变量。
• 清晰命名变量：使用dataSheet、templateSheet等明确名称，避免变量覆盖冲突。
• 跳过空行：判断标签名是否为空，减少无效循环。
• 移除冗余休眠：GAS表格操作具备原子性，无需额外等待，提升执行效率。

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