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);
}

@xtianbalagtas,
Yes, I have replied to your email.

Hi @SneakyBonBon ,

This means the regular expression evaluation failed. Your email format is not similar to the sample email that I demonstrated here. Please provide me with a sample email. You can forward to me a sample email to [email protected].

hello how can we get the values corresponding to the names from the table?

Hi @emrec76,

Extracting such data from a table in Gmail would require parsing your template HTML code. Since HTML formatting can widely be different from email to email based on the email provider template, I would actually need to have a sample email. You may attach the sample email here or send it to my email as a file. My email is [email protected]. You may download Gmail emails as I demonstrate in the link below:
https://i.imgur.com/NfwPmi3.gif

Hi, @moayadhani , I sent the e-mail to Gmail. Thank you so much for your help, I've been working on this for 3 days:(

@moayadhani You are a king! You have saved me so much time. I owe you!

@tim-bloxsome

You are most welcome. I am happy that it worked well for you

Hello,

I have been using this script to automatically pull data from emails successfully for a few months now. About a week ago, I started getting this error:

4:33:01 PM
Error
TypeError: Cannot read property 'toString' of null
extractDetails @ GMailExtractor.gs:48
getGmailEmails @ GMailExtractor.gs:13

This is extractDetails @ GMailExtractor.gs:48
var dDate = emailData.commentsDue = emailData.body.match(regExp).toString().trim();

This is getGmailEmails @ GMailExtractor.gs:13
extractDetails(message);

I use a modified version of the code you developed, and can't, for the life of me, figure out why or how this particular part broke.

Full code is below:

  var label = GmailApp.getUserLabelByName("Plan Review Emails");
  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);
        SpreadsheetApp.flush();
        GmailApp.markMessageRead(message);
        }
        
    }

  }
  
}
function extractDetails(message){
  var emailData = {
    date: "Null",
    sender: "Null",
    subject: "Null",
    body: "Null"
  }
  var emailKeywords = {
    commentsDue: "Reviewers' Comments Due:",
    timeDue: "Time:",
    projectName: "Project:",
    projectNum: "Project No.:",
    projectMgr: "Project Manager:",
    folderName: "PLAN REVIEW:",
    projectMgrEmail: "or:",
    designPhase: "Design Phase:"
  }
  
  emailData.date = message.getDate();
  emailData.sender = message.getFrom();
  emailData.body = message.getPlainBody();
  
  var regExp;
  
  regExp = new RegExp("(?<=" + emailKeywords.commentsDue + ").*");
  var dDate = emailData.commentsDue = emailData.body.match(regExp).toString().trim();

  regExp = new RegExp("(?<=" + emailKeywords.timeDue + ").*");
  var dTime = emailData.timeDue = emailData.body.match(regExp).toString().trim();

  regExp = new RegExp("(?<=" + emailKeywords.projectName + ").*");
  var pName = emailData.projectName = emailData.body.match(regExp).toString().trim();

  regExp = new RegExp("(?<=" + emailKeywords.designPhase + ").*");
  emailData.designPhase = emailData.body.match(regExp).toString().trim();

  regExp = new RegExp("(?<=" + emailKeywords.projectNum + ").*");
  emailData.projectNum = emailData.body.match(regExp).toString().trim();

  regExp = new RegExp("(?<=" + emailKeywords.projectMgr + ").*");
  emailData.projectMgr = emailData.body.match(regExp).toString().split("@")[0].trim();

  regExp = new RegExp("(?<=" + emailKeywords.projectMgrEmail + ").*");
  emailData.projectMgrEmail = emailData.body.match(regExp).toString().trim();

  var emailDataArr = [];
  var planReviewList = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Plan Review List").getDataRange();
  planReviewList.getFilter().remove();
  SpreadsheetApp.flush();
  planReviewList.createFilter();
        SpreadsheetApp.flush();
  for(var propName in emailData){
    emailDataArr.push(emailData[propName]);
  }

  planReviewList.appendRow(emailDataArr);

  var emailList = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email List").getRange("A2:A1000").getValues();

  var body = "Please log into the <a href = https://drive.google.com/drive/folders/insert-folder-code-here>Plan Review Workgroup Drive</a> to start review of the <strong>*" + pName + "</strong> project. It is due on <strong>*" + dDate + "</strong> at <strong>*" + dTime + "</strong>.<br>br> Snooze this email until a week before this email is due as a reminder.";

  GmailApp.sendEmail(emailList,"New Plan Reviews due " + dDate,body,{htmlBody: body});
}

Hi @kalel5654 ,

This means that the regular expression that gets the TimeDue fails. Since you have been using the script for sometime with no issues, it means that your email providers has changed its format (it could be some internal HTML formatting). You need to check your email template. You may also send me an email and I will help you out. My email is [email protected]

I'm trying to use your code and It's totally useful and help me a lot.
Now I want to put the data into a particular cell or collum. I see you talk to use this code: "let mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Lead Data');
mySheet.getRange('C5').setValue('Lead 5');"
But for Lead5, I don't know what to fill in
Please give me an example to me understand it
For example, How can we put phoneNum into sheet names "sheet2", collum B.

Hi, how do i edit the code to only extract new emails?

Hi , my regular expression on regex101.com works fine but it will not work in the script.

my regex is : (?<=Name*\s$\n\n).*\w+\n

I need to extract data two lines below the work name, I tried to enter it directly without using the emailkeyword variable.
Is there away just to put the regex for the fields to be extracted, I only have a few fields so I'm happy not to use variables in my regex.

When I run the script with the following regex code

var regExp;

regExp = new RegExp("(?<=Name*\s$\n\n).*\w+\n");
emailData.fullName = emailData.body.match(regExp);

no data is returned in the xls sheet , I know the extract works because the main body is extracted but not other data is populated by the regex query in the cols next to the main body.

I'm sure I'm doing something very silly, it might just be a regex formatting issue.

I would really appreciate any help its been a frustrating day googling trying to fix this.

Thank you in advance.

Hi, I am new to this and not very good at this, butmanage with the first part to fetch the data from emails. Now I am trying to specific texts to columns and I am getting error: TypeError: Cannot read properties of null (reading 'toString')
This is my code:
function onOpen(e){
var ui = SpreadsheetApp.getUi();

ui.createMenu("Contact Request").addItem("Get Emails","getGmailEmails").addToUi();

}

function getGmailEmails(){
var label = GmailApp.getUserLabelByName("ToBeProcessedByGas");
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];
  extractDetails(message);
}

}

}

function extractDetails(message){

var emailData = {
date: "Null",
sender: "Null",
subject: "Null",
body: "Null",
country: "Null",
yourname: "Null",
phoneNum: "Null",
problem: "Null",
picture: "Null"
}
var emailKeywords = {
country: "3. Country*:",
yourname: "6. your name*",
phoneNum: "7. contact phone number*",
problem: "8. Summary of the problem*",
picture: "9. Attach pictures, video"
}

emailData.date = message.getDate();
emailData.subject = message.getSubject();
emailData.sender = message.getFrom();
emailData.body = message.getPlainBody();

var regExp;

regExp = new RegExp("(?<=" + emailKeywords.country + ").*");
emailData.country = emailData.body.match(regExp).toString().trim();

regExp = new RegExp("(?<=" + emailKeywords.phoneNum + ").*");
emailData.phoneNum = emailData.body.match(regExp).toString().trim();

regExp = new RegExp("(?<=" + emailKeywords.yourname + ").*");
emailData.yourname = emailData.body.match(regExp).toString().trim();

regExp = new RegExp("(?<=" + emailKeywords.problem + ").*");
emailData.problem = emailData.body.match(regExp).toString().trim();

regExp = new RegExp("(?<=" + emailKeywords.picture + ").*");
emailData.picture = 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);
}

Not sure what is wrong. I saw somebody else had this same issue, but there was nothing how to fix it
Regards Johanna

@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