如何解析特定主题的Gmail并导入Google Sheets？脚本排查求助

Let's walk through the issues in your script step by step—right now it's failing to pull the correct Gmail threads and has a few other bugs that'll break the data import entirely:

1. Incorrect Gmail Search Syntax #

Your search query subject: "New submission from Free Diagnosis*" has a syntax quirk that's preventing Gmail from matching the right threads. In Gmail's search rules:

• The space after subject: can throw off the matching, and quotes around the phrase combined with a wildcard can restrict results unexpectedly.
• Wildcards (*) work best when attached directly to the end of your target phrase without extra spacing.

Fix the search line to either of these options:

// Loose match for subjects starting with the phrase
var threads = GmailApp.search('subject:New submission from Free Diagnosis*');

Or if you want to enforce exact matching for the base phrase before the wildcard:

var threads = GmailApp.search('subject:"New submission from Free Diagnosis"*');

2. Broken Array Assignment #

Right now, a[j]=parseMail(messages[j].getPlainBody()); is overwriting elements in your a array instead of adding new ones. Since j resets to 0 for every thread, you'll only end up with the last message from the final thread (or undefined values if later threads have fewer messages than earlier ones).

Replace that line with push() to safely append each parsed result:

a.push(parseMail(messages[j].getPlainBody()));

3. Missing parseMail Function #

Your script calls parseMail() but never defines it—this will throw an error and stop the script cold. You need to add this function to extract the specific data you want from the email's plain text body. Here's a customizable template based on common email submission formats:

function parseMail(body) {
  var parsedData = [];
  // Customize these regex matches to match your email's actual fields
  var nameMatch = body.match(/Name: (.*)/);
  if (nameMatch) parsedData.push(nameMatch[1].trim());
  
  var emailMatch = body.match(/Email: (.*)/);
  if (emailMatch) parsedData.push(emailMatch[1].trim());
  
  var issueMatch = body.match(/Diagnosis Request: (.*)/);
  if (issueMatch) parsedData.push(issueMatch[1].trim());
  
  return parsedData;
}

Full Corrected Script #

Putting all fixes together, here's the working version of your script:

function amy() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var s = ss.getActiveSheet(); 
  // Fixed search query
  var threads = GmailApp.search('subject:New submission from Free Diagnosis*'); 
  var a = []; 
  for (var i = 0; i < threads.length; i++) { 
    var messages = GmailApp.getMessagesForThread(threads[i]); 
    for (var j = 0; j < messages.length; j++) { 
      // Append parsed data instead of overwriting
      a.push(parseMail(messages[j].getPlainBody())); 
    } 
  } 
  var nextRow = s.getDataRange().getLastRow() + 1; 
  // Only write data if there's something to avoid errors
  if (a.length > 0) {
    var numRows = a.length; 
    var numCols = a[0].length; 
    s.getRange(nextRow, 1, numRows, numCols).setValues(a); 
  }
}

// Custom email parsing function - adjust to your email's structure
function parseMail(body) {
  var parsedData = [];
  // Example field extraction - modify these to match your actual email content
  var nameMatch = body.match(/Name: (.*)/);
  if (nameMatch) parsedData.push(nameMatch[1].trim());
  
  var emailMatch = body.match(/Email: (.*)/);
  if (emailMatch) parsedData.push(emailMatch[1].trim());
  
  var diagnosisMatch = body.match(/Symptoms: (.*)/);
  if (diagnosisMatch) parsedData.push(diagnosisMatch[1].trim());
  
  return parsedData;
}

Quick Pro Tips #

• Test your Gmail search query directly in your inbox first to confirm it pulls the right threads—this rules out whether the issue is with the script or the search itself.
• Add is:unread to your search query and mark threads as read after processing to avoid duplicate entries in your sheet.
• Tweak the parseMail function's regex or parsing logic to exactly match the fields in your "Free Diagnosis" submission emails—this is critical for getting accurate data into Sheets.

内容的提问来源于stack exchange，提问作者Sharath P