Google Forms：通过脚本批量删除指定响应以满足GDPR合规需求

Absolutely, you can automate this tedious task using Google Apps Script—no more scrolling and deleting one by one! Here's a practical, step-by-step solution tailored to your GDPR compliance needs:

Step 1: Prepare Your Response Sheet #

First, make sure your Google Forms responses are linked to a Google Sheet (this is the default setup, but if not, go to your Form > Responses > Link to Sheets to create a new one).

Step 2: Open the Script Editor #

1. Open the linked Google Sheet.
2. Go to Extensions > Apps Script to launch the script editor.

Step 3: Paste the Custom Script #

Replace the default myFunction() code with this script. Be sure to update the placeholder values to match your sheet's setup:

function deleteLeftMembers() {
  // 🔧 Update these values to match your sheet
  const spreadsheetId = "YOUR_SPREADSHEET_ID"; // Grab this from your Sheet's URL
  const sheetName = "Form Responses 1"; // Name of your responses sheet
  const statusColumn = "D"; // Column where "LEFT" is marked (e.g., D for column 4)
  const leftStatus = "LEFT"; // Exact status text to match

  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  
  // Loop from bottom to top to avoid index issues when deleting rows
  for (let i = data.length - 1; i >= 0; i--) {
    const statusIndex = statusColumn.charCodeAt(0) - 65; // Convert column letter to index (A=0)
    if (data[i][statusIndex] === leftStatus) {
      sheet.deleteRow(i + 1); // Sheets uses 1-based row numbers
      console.log(`Deleted row ${i + 1}: Member marked as ${leftStatus}`);
    }
  }

  // Show a completion alert
  SpreadsheetApp.getUi().alert("Batch deletion finished! Check the script log for details.");
}

Key Updates You Need to Make: #

• spreadsheetId: Copy this from your Google Sheet's URL (it's the long string between /d/ and /edit).
• sheetName: Use the exact name of the tab where your form responses live (usually "Form Responses 1").
• statusColumn: Replace with the letter of the column that contains the "LEFT" status (e.g., "B" if it's the second column).

Step 4: Run the Script & Authorize #

1. Click the run button (▶️) next to the function name deleteLeftMembers.
2. The first time you run it, you'll see a "Authorization required" prompt. Click Review Permissions.
3. Select your Google account, then click Advanced > Go to [Your Script Name] (unsafe) (this is safe—you're granting access to your own script).
4. Grant the necessary permissions, and the script will start deleting matching rows.

Step 5 (Optional): Set Up Auto-Trigger for Regular Cleanup #

To avoid running this manually every time, set up a scheduled trigger:

1. In the script editor, click the clock icon ⏰ (Triggers) on the left sidebar.
2. Click Add Trigger.
3. Configure the trigger:
   • Choose deleteLeftMembers as the function.
   • Set the event source to Time-driven.
   • Pick a frequency (e.g., weekly, daily) that fits your needs.
4. Save the trigger, and it will automatically run at your chosen interval.

Important Notes #

• Backup First: Before running the script, make a copy of your response sheet as a backup—just in case!
• Test with a Small Dataset: If you're unsure, test the script on a copy of your sheet first to verify it only deletes the correct rows.
• Direct Form Responses: If your responses are only stored in Google Forms (not linked to Sheets), you can use the Forms Service API instead, but linking to Sheets is far easier for bulk operations.

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