Skip to content

Instantly share code, notes, and snippets.

@moayadhani

moayadhani/ExtractSpecificTextFromEmail.txt

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.
Download ZIP
Extract Email Text from Google Sheet using App Script
Raw
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: 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

@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 •
edited
Loading

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 •
edited by moayadhani
Loading

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