Google Apps Script批量复制Google Sheets数据至数据库报错求助

Hey there! Let's break down what's going wrong with your script and fix it so you can append all rows from your input sheet to the Database correctly.

What's Causing the Error? #

Your current code pulls entire columns (A1:A, B1:B, C1:C) which gives you 2D arrays for each column (e.g., [[url1], [url2], [url3]] for column A). When you wrap these into newAttData = [[url,follower_count,date]], you end up with a mismatched array structure: a single row where each cell contains an entire column's array. This is why Google Sheets throws the "Incorrect range height" error—your target range expects 3 rows (matching your input data), but you're passing a structure that only looks like 1 row.

The Fixed Script #

Here's a revised version that properly grabs all valid rows from your "Data" sheet and appends them to "Database":

function saveData() {
  // Get the active spreadsheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Reference the input sheet ("Data")
  const inputSheet = ss.getSheetByName("Data");
  
  // Get all valid data rows from columns A to C (skip empty rows at the end)
  const inputData = inputSheet.getRange(1, 1, inputSheet.getLastRow(), 3).getValues();
  
  // If there's no data to append, exit early to avoid errors
  if (inputData.length === 0) return;
  
  // Reference the database sheet
  const dbSheet = ss.getSheetByName("Database");
  
  // Find the next empty row in the database
  const dbLastRow = dbSheet.getRange("A:A").getValues().filter(String).length;
  
  // Append the input data to the database
  dbSheet.getRange(dbLastRow + 1, 1, inputData.length, 3).setValues(inputData);
}

Key Improvements Explained #

• Grab all data at once: Instead of fetching each column separately, we use getRange(1, 1, inputSheet.getLastRow(), 3) to get all rows from columns A-C in one go. This gives us a clean 2D array ([[url1, follower1, date1], [url2, follower2, date2], ...]) that matches what setValues() expects.
• Check for empty data: We added a check to exit if there's no data in the input sheet, preventing unnecessary errors.
• Dynamic range for appending: When setting the range in the database, we use inputData.length to define how many rows we need to write—this ensures the range height matches exactly the number of rows we're adding.

Testing the Script #

1. Make sure your "Data" sheet has valid data in rows 1 to N (no empty rows in between, unless you want to include them—if you do, adjust how you find the last row).
2. Run the script manually first to confirm it appends all rows to "Database" correctly.
3. Once it works, set up your time trigger as planned (remember to grant necessary permissions when prompted).

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