Extracting data from gmail into a spreadsheet using Google Apps scripts

Question

Case: I receive notifications through email for work with evaluations of the personal security system. This is what they look like (I blacked out some pieces because of privacy laws) enter image description here

I want to extract some information from this email to google sheets through Google Apps Script (javascript language)

So far I have only managed to extract the date from the email into Google sheets, with this code:

function onOpen(e){
  
    ui.createMenu("Tessa").addItem("Get Emails by Label", "getGmailEmails").addToUi();  //create button in google sheets, which will be used to add information from incoming emails to Google sheets. 
  
}

function getGmailEmails(){
  var label = GmailApp.getUserLabelByName("tobeprocessed");    //get emails with label 'tobeprocessed' (could also be another name).
  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 label after processing the message, so that it won't be added twice (or more if you press the button again)
    
  }
  
}

function extractDetails(message){
  
  var emailData = {
    Locatie: "Null",
    Score: "Null",
    Datum: "Null",
    Opmerking: "Null",
  }
  var emailKeywords = {
    Locatie: "",
    Score: " ",
    Opmerking: "",
  }
  
  emailData.Datum = message.getDate();

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  var emailDataArr = [];
  
  for(var propName in emailData){
    emailDataArr.push(emailData[propName]);
  }
  activeSheet.appendRow(emailDataArr);

Can someone please help me out with this? Thanks in advance!

mshcruz · Accepted Answer · 2021-08-19 14:25:52Z

1

You can get the message in the body of the email using getPlainBody(), which returns a string with the body contents.

After that, one way to retrieve pieces of the body would be to use regular expressions:

function extractDetails(message){
  var emailData = {
    Locatie: "Null",
    Score: "Null",
    Datum: "Null",
    Opmerking: "Null",
  }
  
  emailData.Datum = message.getDate();

  // Get the email's body
  var body = message.getPlainBody();
  //var body = 'Reno Vught van Heikant heeft een MIRS-melding gemaakt waarin het functioneren van het persoonsbeveiliging systeem werd beoordeeld met een 3.Toelichting melder: gaf locatie woonkamer aan terwijl ik bijna op de boerderij was. ik kwam vanuit de woonkamer rennen.Beschrijving incident: bij binnenkomst gebeurde er dit. en toen dit';

  // Retrieves everything between "van " and " heeft"
  var locatieRegex = /van (.*?) heeft/s;
  var match = body.match(locatieRegex);
  if (match.length > 1) {
    emailData.Locatie = match[1];
  } 

  // Retrieves everything between "met een " and a period (.)
  var scoreRegex = /met een (.*?)\./s;
  match = body.match(scoreRegex);
  if (match.length > 1) {
    emailData.Score = match[1];
  } 

  // Retrieves everything between "Toelichting melder: " and "Beschrijving incident:"
  var opmerkingRegex = /Toelichting melder: (.*?)Beschrijving incident:/s; 
  match = body.match(opmerkingRegex);
  if (match.length > 1) {
    emailData.Opmerking = match[1];
  }


  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  var emailDataArr = [];
  
  for(var propName in emailData){
    emailDataArr.push(emailData[propName]);
  }
  activeSheet.appendRow(emailDataArr);
}

edited Aug 19, 2021 at 14:25

answered Aug 18, 2021 at 1:21

mshcruz

1,9872 gold badges12 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tessa Beijloos Over a year ago

Hi, thanks very much!! this has been very helpful already. I have a question: I can delete the 'emailKeywords' variable, right? If I add your part of the code and delete variable 'emailKeywords', I get the error 'TypeError: Cannot read property '1' of null'. Looks like the email's body content is null. I have already tried putting !=null after the variable body, then the error is: 'TypeError: body.match is not a function. Also, I have tried putting the email body itself as a string in the variable string, to see if this would give output, but then I get the first error as well.

mshcruz Over a year ago

Yes, you can delete emailKeywords. The sample code I posted was based on a redacted image, so you're probably getting that error because the actual text is different. Check if the body has the same text as it's shown on the image you posted: Logger.log(message.getPlainBody()). If it doesn't you probably have to adjust the regular expressions to work with the actual body.

Tessa Beijloos Over a year ago

Hi, I used the Logger.log(message.getPlainBody()) to check if the body has the same text as it's shown on the image, and it looks to me like it does... I added the image in my question above, can you maybe look at it? Thanks!!

mshcruz Over a year ago

Instead of an image, please paste that string that is returned by Logger.log(message.getPlainBody()) so it's easier to copy and test. You can replace sensitive data with anything.

Tessa Beijloos Over a year ago

okay, i have pasted the string that was returned by that line!

|

Collectives™ on Stack Overflow

Extracting data from gmail into a spreadsheet using Google Apps scripts

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related