Extract Email Text from Google Sheet using App Script

ExtractSpecificTextFromEmail.txt

//Sample email that is received and whose details are extracted:
/*
*Someone* just submitted your form named Online Course Booking from your
website
https://www.abcd.com/
Message Details
Full Name: Mohamed Ali
Email Address: [email protected]
Phone Number: 009725991122334
Nationality: Palestine
Wish List Items: JavaScript for Beginners, Android App Development

To edit your email settings, go to your Inbox on desktop.
*/



var ui = SpreadsheetApp.getUi();
function onOpen(e){
  
  ui.createMenu("Gmail Manager").addItem("Get Emails by Label", "getGmailEmails").addToUi();
  
}

function getGmailEmails(){
  var input = ui.prompt('Label Name', 'Enter the label name that is assigned to your emails:', Browser.Buttons.OK_CANCEL);
  
  if (input.getSelectedButton() == ui.Button.CANCEL){
    return;
  }
  
  var label = GmailApp.getUserLabelByName(input.getResponseText().trim());
  var threads = label.getThreads();
  
  for(var i = threads.length - 1; i >=0; i--){
    var messages = threads[i].getMessages();
    
    for (var j = 0; j <messages.length; j++){
      var message = messages[j];
      if (message.isUnread()){
        extractDetails(message);
        GmailApp.markMessageRead(message);
        }
    }
    threads[i].removeLabel(label); //delete the label after getting the message
    
  }
  
}

function extractDetails(message){
  
  var emailData = {
    date: "Null",
    sender: "Null",
    subject: "Null",
    body: "Null",
    fullName: "Null",
    emailAddr: "Null",
    phoneNum: "Null",
    nationality: "Null",
    wishlistItems: "Null"
  }
  var emailKeywords = {
    fullName: "Full Name:",
    emailAddr: "Email Address:",
    phoneNum: "Phone Number:",
    nationality: "Nationality:",
    wishlistItems: "Wish List Items:"
  }
  
  
  emailData.date = message.getDate();
  emailData.subject = message.getSubject();
  emailData.sender = message.getFrom();
  emailData.body = message.getPlainBody();
  
  var regExp;
  
  regExp = new RegExp("(?<=" + emailKeywords.fullName + ").*");
  emailData.fullName = emailData.body.match(regExp).toString().trim();
  
  
  regExp = new RegExp("(?<=" + emailKeywords.phoneNum + ").*");
  emailData.phoneNum = emailData.body.match(regExp).toString().trim();
  
  regExp = new RegExp("(?<=" + emailKeywords.emailAddr + ").*");
  emailData.emailAddr = emailData.body.match(regExp).toString().trim();
  
  regExp = new RegExp("(?<=" + emailKeywords.nationality + ").*");
  emailData.nationality = emailData.body.match(regExp).toString().trim();
  
  regExp = new RegExp("(?<=" + emailKeywords.wishlistItems + ").*");
  emailData.wishlistItems = emailData.body.match(regExp).toString().trim();
  
  
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  var emailDataArr = [];
  
  for(var propName in emailData){
    emailDataArr.push(emailData[propName]);
  }
  activeSheet.appendRow(emailDataArr);
}

@johannasumelius,
Well, from the error message that you face, it means that your email template does not match with the email template that I demonstrated in my video or that your email sender applies some internal HTML formatting that prevents the program to work. I would like you to send me a sample email over [email protected] and I will help you out.

The email looks like this ================================================================================= Test: request contact External Inbox ToBeProcessedByGAS ***@***.*** via <https://support.google.com/mail/answer/1311182?hl=en-GB> mandrillapp.com Fri, 27 Jan, 16:12 (2 days ago) to service.technique, +candice.gould This Message Is From an External Sender This message came from outside your organization. Please include the following information so we can further assist you: 1. Application Area: Mechanical warewashing, Textile washing, Kitchen Cleaning, Personal Care or other 2. Serial number of equipment 3. Country* 4. Site/ Location Name* asdfas 5.Site Address: Max-liebermann-weg 6. your name* Candice Gould 7. contact phone number* 8. Summary of the problem* 9. Attach pictures, video 10. Preferred service date* 11. Preferred service time*

…

On Sun, 29 Jan 2023 at 19:41, Moayad Hani ***@***.***> wrote: Re: moayadhani/ExtractSpecificTextFromEmail. txt @moayadhani commented on this gist. @johannasumelius, Well, from the error message that you face, it means that your email template does not match with the email template that I demonstrated in ***@***.**** commented on this gist. ------------------------------ @johannasumelius <https://urldefense.com/v3/__https://github.com/johannasumelius__;!!NfTLTRnL1g!JyJVaEUEhVa2i0xioTlV4eomXpa8syJQuT8kkkRC5M6wMZValjj_p--4V8Ihcq5gi_bWkVOS5YxAQkTnzqiPolA7mBy5O6M$> , Well, from the error message that you face, it means that your email template does not match with the email template that I demonstrated in my video <https://urldefense.com/v3/__https://bit.ly/38LlwS3__;!!NfTLTRnL1g!JyJVaEUEhVa2i0xioTlV4eomXpa8syJQuT8kkkRC5M6wMZValjj_p--4V8Ihcq5gi_bWkVOS5YxAQkTnzqiPolA7XPeRSHI$> or that your email sender applies some internal HTML formatting that prevents the program to work. I would like you to send me a sample email over ***@***.*** and I will help you out. — Reply to this email directly, view it on GitHub <https://urldefense.com/v3/__https://gist.github.com/5835369fdebbecf980029f7339e4d769*gistcomment-4452535__;Iw!!NfTLTRnL1g!JyJVaEUEhVa2i0xioTlV4eomXpa8syJQuT8kkkRC5M6wMZValjj_p--4V8Ihcq5gi_bWkVOS5YxAQkTnzqiPolA7RVGRxdI$> or unsubscribe <https://urldefense.com/v3/__https://github.com/notifications/unsubscribe-auth/A5RHDWQ4J2QYHFVWWDDF6Q3WU2TS3BFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFQKSXMYLMOVS2I5DSOVS2I3TBNVS3W5DIOJSWCZC7OBQXE5DJMNUXAYLOORPWCY3UNF3GS5DZVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVEYTAMRRHE4DQMRYU52HE2LHM5SXFJTDOJSWC5DF__;!!NfTLTRnL1g!JyJVaEUEhVa2i0xioTlV4eomXpa8syJQuT8kkkRC5M6wMZValjj_p--4V8Ihcq5gi_bWkVOS5YxAQkTnzqiPolA72SXeRW4$> . You are receiving this email because you were mentioned. Triage notifications on the go with GitHub Mobile for iOS <https://urldefense.com/v3/__https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675__;!!NfTLTRnL1g!JyJVaEUEhVa2i0xioTlV4eomXpa8syJQuT8kkkRC5M6wMZValjj_p--4V8Ihcq5gi_bWkVOS5YxAQkTnzqiPolA7FXxu3yI$> or Android <https://urldefense.com/v3/__https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign*3Dnotification-email*26utm_medium*3Demail*26utm_source*3Dgithub__;JSUlJSU!!NfTLTRnL1g!JyJVaEUEhVa2i0xioTlV4eomXpa8syJQuT8kkkRC5M6wMZValjj_p--4V8Ihcq5gi_bWkVOS5YxAQkTnzqiPolA7CRbDFn4$> .

-- Johanna Sumelius Manager TCS & Application Experts Europe *Phone: *+358405110655

Hi, i have emails with attached pdf, is there a way to the pdf file to google drive and then get place the link to drive file in a column?

Hi @fjgb85
Yes this is possible. basically you need to do the following:

• read the email which this code does.
• get the email attachments using the code
  const attachments = message.getAttachments();
• get the main Drive folder to store the attachment in:
  let mainFolder = DriveApp.getRootFolder();
• loop through all attachments and create a copy of it as a file in mainFolder then get its link:

let attachmentLinks = [];
attachments.forEach(attachment => {
      attachmentLinks.push(subFolder.createFile(attachment.copyBlob()).setName(attachment.getName()).getUrl());
    });

• the join the attachmentLinks in a single line separated by commas:
  const finalJoinedLinks = attachmentLinks.join(',')
• put this variable in the emailData object:
  emailData.fileLinks = finalJoinedLinks

Feel free to drop me an email in case you need help or want to hire me for business solutions development over [email protected] and cc [email protected]

Hi @fjgb85 Yes this is possible. Basically you need to do the following:

• read the email which this code does.
• get the email attachments using the code
  const attachments = message.getAttachments();
• get the main Drive folder to store the attachment in:
  let mainFolder = DriveApp.getRootFolder();
• loop through all attachments and create a copy of it as a file in mainFolder then get its link:

let attachmentLinks = [];
attachments.forEach(attachment => {
      attachmentLinks.push(subFolder.createFile(attachment.copyBlob()).setName(attachment.getName()).getUrl());
    });

• the join the attachmentLinks in a single line separated by commas:
  const finalJoinedLinks = attachmentLinks.join(',')
• put this variable in the emailData object:
  emailData.fileLinks = finalJoinedLinks

Feel free to drop me an email in case you need help or want to hire me for business solutions development over [email protected] and cc [email protected]

Thanks, I send you and message at gmail

Many people have also asked me how to extract details from PDF files attached to incoming Gmail emails. Here is my new video that demonstrates how to do that in detail:
https://www.youtube.com/watch?v=G3M5coQf63w