Google Sheets开发：提取非空单元格邮箱批量加编辑器（遇空即停）

Got it, let's work through this script together. You need to pull email addresses from a Google Sheets column, send editor invites, and stop right when you hit an empty cell (since it's linked to a form, those empty cells below mean there are no more entries to process). Here's the complete, refined code plus breakdowns of how it works:

function addEditor() {
  // Grab the specified sheet
  var sheet = SpreadsheetApp.getActive().getSheetByName('GuestList');
  // Quick check to make sure the sheet exists (avoids errors)
  if (!sheet) {
    SpreadsheetApp.getUi().alert('Oops, the "GuestList" sheet wasn\'t found!');
    return;
  }

  // Set your target column (A=1, B=2, etc.) and starting row (skip headers)
  var targetColumn = 1; // Update this to your email column number
  var currentRow = 2; // Start here if row 1 is your header

  // Loop until we hit an empty cell
  while (true) {
    var cellValue = sheet.getRange(currentRow, targetColumn).getValue();
    
    // Stop immediately if the cell is empty
    if (!cellValue) {
      break;
    }

    // Validate that the value is a proper email address
    var emailPattern = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    if (emailPattern.test(cellValue)) {
      try {
        // Send the editor invitation
        SpreadsheetApp.getActive().addEditor(cellValue);
        Logger.log('Invitation sent successfully to: ' + cellValue);
      } catch (error) {
        // Log any issues (like duplicate editors or invalid emails)
        Logger.log('Failed to invite ' + cellValue + ': ' + error.message);
      }
    } else {
      Logger.log('Skipping invalid email format: ' + cellValue);
    }

    currentRow++;
  }

  // Let you know when the process is done
  SpreadsheetApp.getUi().alert('Invitation process finished! Check the script logs for details.');
}

• Sheet Existence Check: The first check ensures the script doesn't crash if someone renames or deletes the "GuestList" sheet.
• Adjust Column/Row: Tweak targetColumn to match where your emails are stored (e.g., if emails are in column C, set it to 3) and currentRow if your headers are in a different row.
• Early Termination: The break when hitting an empty cell is exactly what you need for form-linked columns—no need to keep checking rows below once you hit a blank.
• Email Validation: The regex filters out non-email values, so you don't waste time trying to invite invalid entries.
• Error Handling: The try/catch block handles common issues like already-invited users or invalid email domains, keeping the script running instead of stopping abruptly.
• Feedback: The UI alert gives you immediate confirmation, and logs (found in the script editor under View > Logs) let you review exactly what happened with each email.

A quick extra tip: If you want to track which invites were sent, you can add a line like sheet.getRange(currentRow, targetColumn + 1).setValue('Invited'); right after sending the invitation—this marks the adjacent column with a status.

内容的提问来源于stack exchange，提问作者W. Reese