Google Apps Script, simple yet powerful automation to pull emails from mailbox and nicely insert them to Google Sheets

Google Apps Script, what is it?

Google Apps Script is a rapid application development platform that makes it fast and easy to create business applications that integrate with Google Workspace. You write code in modern JavaScript and have access to built-in libraries for favorite Google Workspace applications like Gmail, Calendar, Drive, and more. There’s nothing to install—we give you a code editor right in your browser, and your scripts run on Google’s servers.

What can Apps Script do?

Apps Script is versatile. Among other things, you can:

Add custom menus, dialogs, and sidebars to Google Docs, Sheets, and Forms.
Write custom functions and macros for Google Sheets.
Publish web apps — either standalone or embedded in Google Sites.
Interact with other Google services, including AdSense, Analytics, Calendar, Drive, Gmail, and Maps.
Build add-ons and publish them to the Google Workspace Marketplace.

Prerequisits:

A google account ( https://script.google.com/home)

A bit WordPress skills (really not much)

A WordPress installed.

I won’t get here into the WP part as it requires a separate post so I am assuming that wordpress is up and running, here we will deal only with the google side config….

Now we are at the stage where we have the filled form that was sent from wordpress site in our mailbox, this mailbox belongs to the same Google account that owns the Google Sheet and Google Apps Script, it looks like this:

The next thing to do is to extract the data from the form fields in the email and pass it to relevant locations on google sheets, that will be accomplished with ‘Apps Script‘.

But before that let’s create the google sheet so we will have the necessary sheet ID and tab name, which we will use in our script.

Login to google sheets, create a sheet and copy it’s ID, then create a tab named ‘service-calls’.

It’s time to log in to the ‘Apps Script‘ console and create a new project

You will get the following page with a function to run…

For the dude’s case I used the following script that designed to:

1. Retrieve Gmail threads.
2. Check if the latest message in each thread contains the specified subject.
3. If it does, extract lines following words starting with ‘*’.
4. Check if the message has already been processed.
5. If not, append the extracted information to a Google Sheets document.

Let’s break down the main parts:

insertFollowingWordsStartingWithAsterisk():
This function is responsible for retrieving the latest 20 email threads from the Gmail inbox.
For each thread, it retrieves the latest message.
It checks if the subject of the message contains ‘New Car Form Entry’.
If the message hasn’t been processed before (checked via isMessageProcessed() function), it extracts information from the message body.
Specifically, it extracts lines that follow words starting with ‘*’.
It then formats the current date and time and opens a Google Sheets document.
Finally, it appends the extracted information along with the message ID and current date-time to the Google Sheets document.

isMessageProcessed(messageId):
This function checks if a message with a given ID has already been processed. It does so by comparing the message ID with existing IDs stored in a Google Sheets document.

The main steps of the script are:

Retrieve Gmail threads.
Check if the latest message in each thread contains the specified subject.
If it does, extract lines following words starting with ‘*’.
Check if the message has already been processed.
If not, append the extracted information to a Google Sheets document.

function insertFollowingWordsStartingWithAsterisk() {
  var threads = GmailApp.getInboxThreads(0, 20); // Get the latest 20 email threads
  for (var t = 0; t < threads.length; t++) {
    var messages = threads[t].getMessages(); // Get messages from each thread
    var message = messages[0]; // Get the latest message from each thread
    // Check if the subject contains 'New Car Form Entry'
    if (!message.getSubject().includes('New Car Form Entry')) {
      continue;
    }
    // Get message ID
    var messageId = message.getId();
    // Check if the message ID has already been processed
    if (isMessageProcessed(messageId)) {
      // Message already processed, log a notification
      Logger.log("Duplicate message ID: " + messageId);
      continue;
    }
    // Get email body
    var body = message.getPlainBody();
    // Extract lines following words starting with "*"
    var lines = body.split('\n');
    var followingLines = [];
    var asteriskFound = false;
    for (var i = 0; i < lines.length; i++) {
      if (asteriskFound && lines[i].trim() !== '') {
        followingLines.push(lines[i]);
        asteriskFound = false; // Reset the flag after finding the following line
      } else if (lines[i].startsWith('*')) {
        asteriskFound = true;
      }
    }
    // Get the current date and time in Asia/Jerusalem timezone
    var timeZone = "Asia/Jerusalem";
    var formattedDate = Utilities.formatDate(new Date(), timeZone, "yyyy-MM-dd HH:mm:ss");
    // Open the Google Sheets document
    var spreadsheet = SpreadsheetApp.openById('<GoogleSheetID>');
    var sheet = spreadsheet.getSheetByName('service-calls');
    // Append lines to Google Sheets if they don't already exist
    if (followingLines.length > 0) {
      var rowData = [];
      followingLines.forEach(function(line) {
        var values = line.split(':'); // Assuming values are separated by ':'
        rowData = rowData.concat(values);
      });
      // Append message ID and current date and time to rowData
      rowData.unshift(formattedDate);
      rowData.unshift(messageId);
      // Append rowData to Google Sheets
      sheet.appendRow(rowData);
    }
  }
}
function isMessageProcessed(messageId) {
  // Open the Google Sheets document
  var spreadsheet = SpreadsheetApp.openById('<GoogleSheetID>');
  var sheet = spreadsheet.getSheetByName('service-calls');
  // Get existing message IDs in the sheet
  var existingMessageIds = sheet.getRange('A:A').getValues().map(function(row) {
    return row[0];
  });
  return existingMessageIds.includes(messageId);
}

After adding the script, navigate to ‘Triggers’ on the left navigation bar and define your preferred trigger, i used based trigger.

Save and manually run the script to see that it operates correctly.
you would be able to see the execution logs here.

And if you will check the Google Sheet it will look like this, with a new entry that just has been processed.

And that concludes our session today :> the “automatico” is ready 😀