G Suite邮箱特定自动邮件数据同步至共享Google Sheets脚本优化需求

Hey there! The issue you're hitting is super common with Google Apps Script: by default, GmailApp uses the authorization of the user running the script, so it pulls data from their inbox instead of your G Suite account. To fix this, we need to set up a service account with domain-wide delegation so the script always accesses your specific mailbox, no matter who runs it.

Here's a step-by-step solution tailored to your code:

Step 1: Prep the Service Account & Domain Delegation #

First, you'll need to set up a service account in Google Cloud Console and configure domain-wide access in your G Suite Admin panel:

• Go to the Google Cloud Console, create a new project, and enable the Gmail API.
• Create a service account for the project, generate a JSON key file, and save it somewhere safe.
• In your G Suite Admin Console, navigate to Security > API controls > Domain wide delegation, add your service account's client ID, and grant it these scopes:
  • https://www.googleapis.com/auth/gmail.readonly
  • https://www.googleapis.com/auth/gmail.modify (needed to add the "done" label)

Step 2: Add the OAuth2 Library to Your Script #

In your Google Sheet's script editor:

1. Click Resources > Libraries
2. Paste this library ID: 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF
3. Select the latest version and save.

Step 3: Update Your Code #

Replace your existing getGmail function (and add the service account config) with the code below. Make sure to fill in your service account details and your G Suite email:

function onOpen() { 
  const spreadsheet = SpreadsheetApp.getActive(); 
  let menuItems = [ 
    {name: 'Gather emails', functionName: 'gather'}, 
  ]; 
  spreadsheet.addMenu('DIFM', menuItems); 
} 

function gather() { 
  let messages = getGmail(); 
  let curSheet = SpreadsheetApp.getActiveSheet(); // Fixed to target the active sheet, not the entire spreadsheet
  messages.forEach(message => {curSheet.appendRow(parseEmail(message))}); 
} 

// --------------------------
// Replace these with your details!
// --------------------------
const SERVICE_ACCOUNT_KEY = {
  "type": "service_account",
  "project_id": "YOUR_PROJECT_ID",
  "private_key_id": "YOUR_PRIVATE_KEY_ID",
  "private_key": "YOUR_PRIVATE_KEY_KEEP_THE_NEWLINES",
  "client_email": "YOUR_SERVICE_ACCOUNT_EMAIL@YOUR_PROJECT.iam.gserviceaccount.com",
  "client_id": "YOUR_CLIENT_ID",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "YOUR_CLIENT_CERT_URL"
};
const TARGET_USER_EMAIL = "your-g-suite-email@domain.com"; // Your specific G Suite mailbox

function getGmail() {
  // Set up OAuth2 service for the service account
  const client = OAuth2.createService('GmailService')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setPrivateKey(SERVICE_ACCOUNT_KEY.private_key)
    .setIssuer(SERVICE_ACCOUNT_KEY.client_email)
    .setSubject(TARGET_USER_EMAIL)
    .setPropertyStore(PropertiesService.getScriptProperties())
    .setScope(['https://www.googleapis.com/auth/gmail.readonly', 'https://www.googleapis.com/auth/gmail.modify']);

  // Check if we have valid access
  if (!client.hasAccess()) {
    throw new Error('Authorization failed: ' + client.getLastError());
  }

  const query = "from:domain.com AND subject:subject NOT label:done";
  // Fetch threads matching your query via Gmail API
  const threadsResponse = UrlFetchApp.fetch(
    `https://www.googleapis.com/gmail/v1/users/${TARGET_USER_EMAIL}/threads?q=${encodeURIComponent(query)}&maxResults=10`,
    { headers: { Authorization: 'Bearer ' + client.getAccessToken() } }
  );
  const threads = JSON.parse(threadsResponse.getContentText()).threads || [];

  // Get or create the "done" label
  const labelsResponse = UrlFetchApp.fetch(
    `https://www.googleapis.com/gmail/v1/users/${TARGET_USER_EMAIL}/labels`,
    { headers: { Authorization: 'Bearer ' + client.getAccessToken() } }
  );
  const labels = JSON.parse(labelsResponse.getContentText()).labels || [];
  let doneLabelId = labels.find(label => label.name === 'done')?.id;
  
  if (!doneLabelId) {
    const createLabelResponse = UrlFetchApp.fetch(
      `https://www.googleapis.com/gmail/v1/users/${TARGET_USER_EMAIL}/labels`,
      {
        method: 'POST',
        headers: {
          Authorization: 'Bearer ' + client.getAccessToken(),
          'Content-Type': 'application/json'
        },
        payload: JSON.stringify({ name: 'done', labelListVisibility: 'labelShow', messageListVisibility: 'show' })
      }
    );
    doneLabelId = JSON.parse(createLabelResponse.getContentText()).id;
  }

  const messages = [];
  for (const thread of threads) {
    // Fetch the first message in the thread and decode its plain text body
    const firstMessageId = thread.messages[0].id;
    const messageResponse = UrlFetchApp.fetch(
      `https://www.googleapis.com/gmail/v1/users/${TARGET_USER_EMAIL}/messages/${firstMessageId}?format=full`,
      { headers: { Authorization: 'Bearer ' + client.getAccessToken() } }
    );
    const messageData = JSON.parse(messageResponse.getContentText());
    const encodedBody = messageData.payload.body.data || messageData.payload.parts[0].body.data;
    const decodedBody = Utilities.newBlob(Utilities.base64Decode(encodedBody, Utilities.Charset.UTF_8)).getDataAsString();
    
    messages.push(decodedBody);

    // Add the "done" label to the thread
    UrlFetchApp.fetch(
      `https://www.googleapis.com/gmail/v1/users/${TARGET_USER_EMAIL}/threads/${thread.id}/modify`,
      {
        method: 'POST',
        headers: {
          Authorization: 'Bearer ' + client.getAccessToken(),
          'Content-Type': 'application/json'
        },
        payload: JSON.stringify({ addLabelIds: [doneLabelId] })
      }
    );
  }

  return messages;
}

function parseEmail(message){ 
  let parsed = message.replace(/,/g,'')
    .replace(/\n*.+:/g,',')
    .replace(/^,/,'')
    .replace(/\n/g,'')
    .split(','); 
  // Add fallback for missing indices to avoid errors
  let result = [6,7,8,9,10,11].map(index => parsed[index]?.trim() || ''); 
  return result; 
}

Key Notes: #

• Why this works: Instead of using GmailApp (tied to the executing user), we use the Gmail API with a service account that's been delegated access to your specific G Suite mailbox. This ensures the script always pulls data from your inbox, no matter who runs it.
• Small fixes: I also adjusted gather() to target the active sheet (instead of the entire spreadsheet) and added a fallback in parseEmail() to handle cases where the email format doesn't have all expected indices.

内容的提问来源于stack exchange，提问作者Trevor Waldspurger