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 @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