Skip to content

Instantly share code, notes, and snippets.

@moayadhani
Last active March 7, 2024 07:02
Show Gist options
  • Save moayadhani/5835369fdebbecf980029f7339e4d769 to your computer and use it in GitHub Desktop.
Save moayadhani/5835369fdebbecf980029f7339e4d769 to your computer and use it in GitHub Desktop.
Extract Email Text from Google Sheet using App Script
//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: abcd123@gmail.com
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);
}
@moayadhani
Copy link
Author

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

@moayadhani
Copy link
Author

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 moa29000@gmail.com.

@emrec76
Copy link

emrec76 commented Nov 6, 2021

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

image

@moayadhani
Copy link
Author

moayadhani commented Nov 8, 2021

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 moa29000@gmail.com. You may download Gmail emails as I demonstrate in the link below:
https://i.imgur.com/NfwPmi3.gif

@emrec76
Copy link

emrec76 commented Nov 8, 2021

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:(

@tim-bloxsome
Copy link

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

@moayadhani
Copy link
Author

@tim-bloxsome

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

@kalel5654
Copy link

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

@moayadhani
Copy link
Author

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 moa29000@gmail.com

@thaihoangminh789
Copy link

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.

@jerrysts
Copy link

jerrysts commented Nov 3, 2022

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

@bkeneu
Copy link

bkeneu commented Dec 9, 2022

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.

@johannasumelius
Copy link

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

@moayadhani
Copy link
Author

@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 moayad@codewondo.com and I will help you out.

@johannasumelius
Copy link

johannasumelius commented Jan 29, 2023 via email

@fjgb85
Copy link

fjgb85 commented Mar 6, 2024

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?

@moayadhani
Copy link
Author

moayadhani commented Mar 6, 2024

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 moayad@codewondo.com and cc moa29000@gmail.com

@fjgb85
Copy link

fjgb85 commented Mar 7, 2024

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 moayad@codewondo.com and cc moa29000@gmail.com

Thanks, I send you and message at gmail

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment