Google Apps Script实现Google Sheets指定列非空行数据定向复制的需求及代码修改问询

Got it, let's tweak your script to only copy rows where column G has content! Your current code copies entire columns straight over, which doesn't include the check we need. Here's a revised approach that's both efficient and meets your requirement:

function theme_copy_WIP() {
  const workbook = SpreadsheetApp.getActiveSpreadsheet();
  const auditSheet = workbook.getActiveSheet(); // Source sheet
  const themeSheet = workbook.getSheetByName("Themes"); // Destination sheet

  // Get all data from the source sheet (starting from row 2, since row 1 is headers)
  const sourceData = auditSheet.getDataRange().getValues().slice(1);

  // Filter rows where column G (index 6, since arrays are 0-indexed) is NOT empty
  const filteredRows = sourceData.filter(row => row[6] !== "");

  // Extract the specific columns we need: A(0), B(1), C(2), E(4), G(6), H(7)
  const dataToCopy = filteredRows.map(row => [
    row[0], row[1], row[2], row[4], row[6], row[7]
  ]);

  // Clear existing data in the destination sheet (optional, adjust as needed)
  themeSheet.getRange(2, 1, themeSheet.getLastRow() - 1, 6).clearContent();

  // Paste the filtered data into the destination sheet starting at row 2
  if (dataToCopy.length > 0) {
    themeSheet.getRange(2, 1, dataToCopy.length, 6).setValues(dataToCopy);
  }
}

Key Changes Explained: #

• Batch Data Fetch: Instead of copying columns one by one, we grab all source data at once with getDataRange().getValues(). This is way more efficient than multiple calls to the spreadsheet service.
• Row Filtering: The filter method checks if column G (array index 6) is not empty, keeping only the rows we need.
• Column Extraction: The map method pulls out exactly the columns you specified (A, B, C, E, G, H) and arranges them to match the destination columns (A-F).
• Batch Write: We write all the filtered data in one go with setValues(), which is much faster than copying individual ranges.

Optional Notes: #

• If you don't want to clear existing data in the destination sheet, remove the clearContent() line. In that case, you might want to find the next empty row instead of starting at row 2 every time.
• Make sure your source sheet's headers are in row 1—if not, adjust the slice(1) part to match where your data starts.

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