如何在Google Script中获取用户ID？邮件按钮触发场景

I’ve tackled similar workflows before—here’s a step-by-step approach to get your button clicks to send the user’s email and fetch their specific data:

1. Embed the User’s Email in the Email’s HTML Form #

When sending each email, include a hidden form field that holds the recipient’s email. The form will submit this value via POST to your deployed Google Script web app when the button is clicked.

Here’s the code to generate and send the emails:

function sendEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const allRows = sheet.getDataRange().getValues();
  
  // Skip header row (adjust index if your header is on a different row)
  for (let rowIndex = 1; rowIndex < allRows.length; rowIndex++) {
    const userEmail = allRows[rowIndex][0]; // Column A = Email
    const userSpecificData = allRows[rowIndex][1]; // Column B = Example data
    
    // Build HTML email with a form containing the hidden email field
    const emailHtml = `
      <div style="font-family: Arial, sans-serif; padding: 20px;">
        <p>Hi there,</p>
        <p>Click the button below to access your personalized data:</p>
        <form action="YOUR_WEB_APP_DEPLOYMENT_URL" method="POST">
          <input type="hidden" name="userEmail" value="${userEmail}">
          <button type="submit" style="padding: 10px 20px; background: #4285F4; color: white; border: none; border-radius: 4px; cursor: pointer;">
            View My Data
          </button>
        </form>
      </div>
    `;
    
    // Send the email
    MailApp.sendEmail({
      to: userEmail,
      subject: "Access Your Personal Data",
      htmlBody: emailHtml
    });
  }
}

2. Deploy Your Script as a Web App #

Turn your script into a web app that can accept POST requests:

• Go to Publish > Deploy as web app
• Set Execute as to Your Google Account (so it can access your spreadsheet)
• Set Who has access to Anyone (requires users to sign in with their Google account for security) or Anyone, even anonymous (no sign-in, but less secure)
• Copy the generated web app URL and replace YOUR_WEB_APP_DEPLOYMENT_URL in the code above.
• Redeploy as a new version whenever you update the script to apply changes!

3. Handle the POST Request to Fetch Data #

Add a doPost function to your script that retrieves the email from the form, looks up the corresponding data in your spreadsheet, and returns it to the user:

function doPost(request) {
  // Extract the user's email from the POST request
  const submittedEmail = request.parameter.userEmail;
  
  // Security check: Verify signed-in user matches submitted email (for "Anyone" access)
  const signedInUserEmail = Session.getActiveUser().getEmail();
  if (!submittedEmail || signedInUserEmail !== submittedEmail) {
    return ContentService.createTextOutput("Error: Unauthorized access. Please sign in with the correct account.").setMimeType(ContentService.MimeType.TEXT);
  }
  
  // Look up the user's data in the spreadsheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const allRows = sheet.getDataRange().getValues();
  let userData = null;
  
  for (let rowIndex = 1; rowIndex < allRows.length; rowIndex++) {
    if (allRows[rowIndex][0] === submittedEmail) {
      userData = allRows[rowIndex];
      break;
    }
  }
  
  // Return data as a user-friendly HTML page
  if (userData) {
    return HtmlService.createHtmlOutput(`
      <div style="font-family: Arial, sans-serif; padding: 20px; max-width: 600px; margin: 0 auto;">
        <h2>Your Personal Data</h2>
        <p><strong>Email:</strong> ${userData[0]}</p>
        <p><strong>Your Data:</strong> ${userData[1]}</p>
        <!-- Add more fields here based on your spreadsheet columns -->
      </div>
    `);
  } else {
    return ContentService.createTextOutput("Error: No data found for your email.").setMimeType(ContentService.MimeType.TEXT);
  }
}

Key Notes: #

• Security: Using Session.getActiveUser() ensures only the actual recipient (signed in with their email) can access their data, preventing spoofing. Skip this check if you don’t require sign-in, but be aware of the risk.
• Email Compatibility: Most modern email clients support HTML forms, but test with a few to confirm functionality.
• Spreadsheet Layout: Adjust column indexes (e.g., allRows[rowIndex][0]) to match your spreadsheet’s structure.

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