按表头名称复制Google Sheet筛选后列数据的技术求助

Got it, let's tweak your code to only copy the exact columns you need instead of all data. Here's a straightforward fix with explanations:

Modified Code #

toFilter(); 
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
var sheet1 = spreadsheet.getSheetByName("Worksheet"); 
var sheet2 = spreadsheet.getSheetByName("Update"); 

// Define the exact headers we want to extract
var targetHeaders = ["EmployeeID", "Email", "Date", "Employee name", "Phone"];

// Get the first row (headers) to map column positions
var headerRow = sheet1.getRange(1, 1, 1, sheet1.getLastColumn()).getValues()[0];
var columnPositions = {};

// Create a map: header name → column index (0-based)
targetHeaders.forEach(header => {
  const index = headerRow.indexOf(header);
  if (index !== -1) {
    columnPositions[header] = index;
  } else {
    console.log(`Heads up: Header "${header}" wasn't found in Worksheet`);
  }
});

// Get all data and filter visible rows
var allData = sheet1.getDataRange().getValues();
var filteredSpecificColumns = [];

// Add our target headers as the first row of the output
filteredSpecificColumns.push(targetHeaders);

// Loop through rows (skip header row, start at index 1)
for (let i = 1; i < allData.length; i++) {
  if (!sheet1.isRowHiddenByFilter(i + 1)) { // i+1 converts to 1-based row number
    const originalRow = allData[i];
    const newRow = [];
    // Only pull values from columns we care about
    targetHeaders.forEach(header => {
      const colIndex = columnPositions[header];
      newRow.push(colIndex !== undefined ? originalRow[colIndex] : "");
    });
    filteredSpecificColumns.push(newRow);
  }
}

// Write the filtered data to the Update sheet
if (filteredSpecificColumns.length > 0) {
  sheet2.getRange(sheet2.getLastRow() + 1, 1, filteredSpecificColumns.length, filteredSpecificColumns[0].length).setValues(filteredSpecificColumns);
}

What Changed & Why #

• Header Mapping: We first scan the first row of Worksheet to find where each target column lives. This means your code will still work even if someone rearranges columns later.
• Selective Column Extraction: Instead of copying entire rows, we only pull values from the columns matching your target headers.
• Header Preservation: We add your target headers as the first row of the output to keep the Update sheet's structure consistent.
• Quick Error Checks: A console warning will pop up if any of your target headers are missing from Worksheet, so you can fix it fast.

This will copy only the columns you listed from visible rows in Worksheet straight into Update.

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