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

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