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!

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