通过按钮迁移表单响应表数据至验证表的脚本故障问题

Problem Diagnosis #

Your original script runs into two critical issues when team members try to use it:

• Permission & Trigger Restrictions: Simple script-bound buttons have strict permission limits for shared users, often blocking execution entirely.
• Risky Bulk Data Handling: Clearing and overwriting entire ranges can lead to data loss in collaborative environments, and the row-indexing logic doesn't account for individual row deletions properly.

Revised Member-Friendly Script #

Here's a robust, reliable version that fixes these problems while meeting your core requirements:

function moveVerifiedRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("Form responses 1");
  const targetSheet = ss.getSheetByName("Verified Form Responses");
  
  // Pull all data from source sheet (preserve header row)
  const sourceData = sourceSheet.getDataRange().getValues();
  const headerRow = sourceData[0];
  const rowsToMove = [];
  const rowsToKeep = [];
  
  // Split rows into verified (W column = true) and unverified
  for (let i = 1; i < sourceData.length; i++) {
    const row = sourceData[i];
    // W column uses 0-based index 22
    if (row[22] === true) {
      rowsToMove.push(row);
    } else {
      rowsToKeep.push(row);
    }
  }
  
  // Append verified rows to target sheet (add header if target is empty)
  if (targetSheet.getLastRow() === 0) {
    targetSheet.appendRow(headerRow);
  }
  if (rowsToMove.length > 0) {
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1, rowsToMove.length, rowsToMove[0].length).setValues(rowsToMove);
  }
  
  // Refresh source sheet with only unverified rows (keep header intact)
  sourceSheet.clearContents();
  sourceSheet.appendRow(headerRow);
  if (rowsToKeep.length > 0) {
    sourceSheet.getRange(2, 1, rowsToKeep.length, rowsToKeep[0].length).setValues(rowsToKeep);
  }
}

Key Fixes & Improvements #

• Simplified Member Authorization: Ensure team members have Edit access to the spreadsheet. When they click the Submit button for the first time, they’ll need to go through the "Advanced" > "Go to [Script Name]" flow to grant permissions—this only needs to be done once per user.
• Safer Data Management: Instead of bulk overwriting, we explicitly separate rows, preserve headers, and rewrite only necessary data. This reduces collision risks when multiple users are working in the sheet.
• Empty State Handling: The script automatically adds the header row to the target sheet if it’s empty, preventing formatting mismatches.

Setting Up the Submit Button for Your Team #

1. In your Google Sheet, go to Insert > Drawing to create a custom button (e.g., a labeled rectangle with "Submit" text).
2. Save the drawing, click on it, and open the three-dot menu > Assign script.
3. Enter moveVerifiedRows (the exact name of the revised function) and click OK.

Team members can now click this button to trigger the migration after marking rows in column W as true.

内容的提问来源于stack exchange，提问作者Admin - R K Pareek Co