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

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