如何用Google Script将正则匹配结果导出至新工作表？

Hey there! I see you've got a solid start with your regex script—great job! Let's tweak it so those matches show up in an actual sheet instead of just the logs. Here's a step-by-step breakdown with a modified script that's easy to follow:

First, the Modified Script #

Replace your existing code with this one—I'll explain each change below:

function findAndExportMatches() {
  // Grab your active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get the sheet with your text records (your 'sheet1')
  var sourceSheet = ss.getSheetByName('sheet1');
  
  // Create a "Matches" sheet if it doesn't exist, or use the existing one
  var resultsSheet = ss.getSheetByName('Matches');
  if (!resultsSheet) {
    resultsSheet = ss.insertSheet('Matches');
  }
  
  // Clear old results so we don't have duplicates every time we run it
  resultsSheet.clearContents();
  
  // Add a nice header to the results sheet (optional but helpful!)
  resultsSheet.getRange(1, 1).setValue('Matching Entries');
  
  // Track which row we'll write the next match to (start after the header)
  var nextResultRow = 2;
  
  // Your original regex pattern—no changes here!
  var regexPattern = /\W*(identity)\W*\s+(\w+\s+){0,5}(verification)|(verification)\s+(\w+\s+){0,5}(identity)/;
  
  // Loop only through rows that have content (faster than checking every row)
  var lastRowWithData = sourceSheet.getLastRow();
  for (var i = 1; i <= lastRowWithData; i++) {
    var cellText = sourceSheet.getRange('A' + i).getValue();
    
    // Check if the cell matches your regex
    if (regexPattern.exec(cellText) !== null) {
      // Write the matching text to the results sheet
      resultsSheet.getRange(nextResultRow, 1).setValue(cellText);
      nextResultRow++; // Move to the next row for the next match
    }
  }
  
  // Pop up a little message to let you know it's done
  SpreadsheetApp.getUi().alert('Done! Found ' + (nextResultRow - 2) + ' matching entries.');
}

What Changed & Why: #

Let's go through the key tweaks so you understand what's happening:

• Results Sheet Setup: The script checks if a sheet named "Matches" exists. If not, it creates one—this is your dedicated space for matches.
• Clear Old Data: Every time you run the script, it clears the previous results so you don't end up with messy duplicates.
• Header Row: Added a simple header ("Matching Entries") to make the results sheet easier to read. You can delete this line if you don't want it.
• Track Writing Rows: The nextResultRow variable keeps track of where to put the next match. It starts at 2 because row 1 is the header (if you remove the header, start at 1 instead).
• Efficient Looping: Instead of looping through every single row (even empty ones), we use getLastRow() to only check rows that have content. This makes the script run faster.
• Write to Sheet: Instead of Logger.log(), we use setValue() to put the matching text directly into the results sheet.
• Completion Alert: A little pop-up tells you how many matches were found—no more guessing if the script worked!

How to Use This: #

1. Open your Google Sheet.
2. Click Extensions > Apps Script to open the script editor.
3. Delete your old code and paste the new script above.
4. Save the script (click the floppy disk icon) and name it something like FindIdentityMatches.
5. Run the script (click the play button ▶️). The first time you run it, Google will ask you to authorize the script—this is totally safe, it just lets the script access your sheet data.
6. Go back to your sheet, and you'll see a new "Matches" sheet with all your matching text!

Quick Tips: #

• If you want a different name for the results sheet, just replace 'Matches' in the script with whatever name you want (keep the quotes).
• Your regex pattern is exactly the same as before—so it will find all the same matches you were seeing in the logs.
• If you don't want the header, delete the line resultsSheet.getRange(1, 1).setValue('Matching Entries'); and change nextResultRow = 2 to nextResultRow = 1.

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