如何提取名称含特定文本的所有Sheet数据？脚本故障求助

Sheet名称示例 #

• "5 - Past Send Assets - 2022 Q2"
• "1 - Dates and Assets"
• "5 - Past Send Assets Q4 & Q1"

当前脚本 #

try {
  // Get the URL from cell A1
  var spreadsheetURL = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RunHistory").getRange("D2").getValue();

  // Open the other spreadsheet using its URL
  var otherSpreadsheet = SpreadsheetApp.openByUrl(spreadsheetURL);
  
  // Initialize an empty array to store the results
  var results = [];
  
  // Get all the sheets in the other spreadsheet
  var sheets = otherSpreadsheet.getSheets();

  // Loop through each sheet
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var sheetName = sheet.getName();
    
    // Check if the sheet name contains "5 -" or "1 -"
    if (sheetName.indexOf("5 -") !== -1 || sheetName.indexOf("1 -") !== -1) {
      // Get data from F3:F and G3:G in the current sheet
      var data = sheet.getRange("F3:F").getValues();
      var data2 = sheet.getRange("G3:G").getValues();
  
      // Append the data to the results array
      for (var j = 0; j < data.length; j++) {
        results.push([data[j][0], data2[j][0]]);
      }
    }
  }
  
  // Get the active spreadsheet and sheet
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = activeSpreadsheet.getActiveSheet();
  
  // Clear any existing data in the active sheet (starting from A2)
  activeSheet.getRange("A2:B").clearContent();
  
  // Write the results to the active sheet starting from A2
  activeSheet.getRange(2, 1, results.length, 2).setValues(results);
  
  // Display a success message
  SpreadsheetApp.getUi().alert("Data retrieved and populated successfully.");

} catch (error) {
  // Display an error message if any issues occur
  SpreadsheetApp.getUi().alert("An error occurred: " + error.message);
}

已尝试的操作 #

• 添加各类continue;语句
• 调整名称匹配条件（如"5 "和"5 - "）
• 将结果数组的拼接方式从push改为concat

解决方案 #

问题根源在于你使用getRange("F3:F")和getRange("G3:G")获取了整列数据，包括大量空行。第一个匹配Sheet的空行会被加入results数组，后续Sheet的有效数据会被推到数组的极末尾，导致你误以为没有返回其他Sheet的数据。

修改后的脚本会先获取每个Sheet中F列和G列的有效数据范围，只读取有内容的行：

try {
  var spreadsheetURL = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RunHistory").getRange("D2").getValue();
  var otherSpreadsheet = SpreadsheetApp.openByUrl(spreadsheetURL);
  var results = [];
  var sheets = otherSpreadsheet.getSheets();

  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var sheetName = sheet.getName();
    
    if (sheetName.includes("5 -") || sheetName.includes("1 -")) {
      // 获取F列从第3行开始的最后一个有数据的行号
      var lastRowF = sheet.getRange("F:F").getValues().filter(String).length;
      // 如果F列从第3行开始没有数据，跳过当前Sheet
      if (lastRowF < 3) continue;
      
      // 只读取F3到F[lastRowF]的有效数据
      var data = sheet.getRange(3, 6, lastRowF - 2, 1).getValues();
      var data2 = sheet.getRange(3, 7, lastRowF - 2, 1).getValues();
  
      for (var j = 0; j < data.length; j++) {
        // 跳过空行（可选，避免无效空值）
        if (data[j][0] !== "" || data2[j][0] !== "") {
          results.push([data[j][0], data2[j][0]]);
        }
      }
    }
  }
  
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = activeSpreadsheet.getActiveSheet();
  activeSheet.getRange("A2:B").clearContent();
  
  if (results.length > 0) {
    activeSheet.getRange(2, 1, results.length, 2).setValues(results);
  }
  
  SpreadsheetApp.getUi().alert("Data retrieved and populated successfully.");

} catch (error) {
  SpreadsheetApp.getUi().alert("An error occurred: " + error.message);
}

关键修改点 #

1. 使用getRange("F:F").getValues().filter(String).length获取F列最后一个有数据的行号，避免读取整列空行
2. 计算有效数据范围：从第3行开始，行数为lastRowF - 2（因为第3行是起始行）
3. 可选：添加空行判断，跳过F和G都为空的行，减少无效数据
4. 使用includes替代indexOf，代码更简洁易读

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