Google Sheet脚本问题：从Sheet导入数据并每隔9行设置行间隔

Hey there! Let's tackle your Google Sheets script issue where the write functionality works but importing data from Sheet1 isn't working as expected. Based on your requirement to import rows from Sheet1 and write them to Sheet2 with an increasing 9-row interval, here's a step-by-step fix:

常见导入失败的核心原因 #

From what you described, the write logic is solid, so the problem almost always boils down to one of these data-reading issues:

• You're using a fixed range (like A1:Z100) that doesn't cover all your data in Sheet1, or includes empty rows that break the flow
• The sheet name reference is incorrect (case-sensitive, so "sheet1" vs "Sheet1" matters)
• You aren't filtering out empty rows from Sheet1, leading to "invisible" imports that write blank data

修正后的完整脚本 #

Here's a revised script that fixes the import logic while keeping your working write behavior:

function importAndWriteData() {
  // Get the active spreadsheet and target sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("Sheet1");
  const targetSheet = ss.getSheetByName("Sheet2");
  
  // Guard clause: Check if sheets exist (prevents errors if names are wrong)
  if (!sourceSheet || !targetSheet) {
    SpreadsheetApp.getUi().alert("Sheet1 or Sheet2 not found! Check your sheet names.");
    return;
  }
  
  // 1. Correctly read ALL non-empty data from Sheet1
  const sourceData = sourceSheet.getDataRange().getValues();
  // Filter out completely empty rows to avoid importing junk
  const filteredData = sourceData.filter(row => row.some(cell => cell !== ""));
  
  // Guard clause: Exit if there's no data to import
  if (filteredData.length === 0) {
    SpreadsheetApp.getUi().alert("No valid data found in Sheet1!");
    return;
  }
  
  // 2. Configure target row settings (adjust starting row as needed)
  let targetRow = 2; // Start writing at row 2 in Sheet2
  const rowInterval = 9; // Increase by 9 rows each time
  
  // 3. Write data to Sheet2 with the required interval
  filteredData.forEach(row => {
    // Write the entire row to the target position
    targetSheet.getRange(targetRow, 1, 1, row.length).setValues([row]);
    // Jump 9 rows for the next entry
    targetRow += rowInterval;
  });
  
  // Confirmation alert for the user
  SpreadsheetApp.getUi().alert(`Import complete! ${filteredData.length} rows added to Sheet2.`);
}

Key Fixes & Explanations #

• Dynamic Data Reading: getDataRange() automatically grabs all cells with data in Sheet1, so you never miss rows or read unnecessary empty cells.
• Empty Row Filtering: The filter method removes any completely blank rows from Sheet1, ensuring only valid data gets imported.
• Error Prevention: Added checks for missing sheets and empty data, so you get clear alerts instead of silent failures.
• Flexible Targeting: You can adjust targetRow if you need to start writing from a different row in Sheet2.

How to Test This #

1. Open your Google Sheet, go to Tools > Script Editor
2. Replace your existing script with the code above
3. Save the script (give it a name like ImportWithInterval)
4. Run the function for the first time—you'll need to grant permissions (follow the prompts, it's safe)
5. Check Sheet2 to see if data is imported correctly with the 9-row interval

If It Still Doesn't Work #

Double-check these:

• Are there merged cells in Sheet1? Merged cells can mess up getValues() output—unmerge them or add logic to handle merged data.
• Is your Sheet2 protected? If it has edit restrictions, the script might fail to write (but you said write works, so this is less likely).

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