Google App Script to sync gmail inbox with sheets

Question

I have a script pulling out data from a gmail account. The script scans the inbox for mails and finds the relevant lines of text and puts it in a Google sheet.

The email looks something like:

Vehicle: 5761364, Position: (URL to Google Maps)

The script i use to get the data to sheets is:

function processInboxToSheet() {
  var start = 0;
  var threads = GmailApp.getInboxThreads(start, 100);
  var sheet = SpreadsheetApp.getActiveSheet();
  var result = [];

  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();

    var content = messages[0].getPlainBody();

    if (content) {
      var tmp;
      tmp = content.match(/Vehicle:\s*([A-Za-z0-9\s]+)(\r?\n)/);
      var username = (tmp && tmp[1]) ? tmp[1].trim() : 'No vehicle';

      tmp = content.match(/Map Link:\s*([A-Za-z ][A-Za-z0-9!@#$%?=^.,:&*/ ]+)/);
      var comment = (tmp && tmp[1]) ? tmp[1] : 'No url';

      sheet.appendRow([username, comment]);

      Utilities.sleep(500);
    }
  }
};

Would it be possible to make a kind of synchronization function, where the Google sheet gets updated automatically with the emails in the inbox. Right now it makes duplicates every time it runs.

Also, could someone tell me if it is possible to get the script to delete the lines created if the email is deleted. So the sheet list always is in sync with the inbox?

Please ask me if it does not make sense.

Moayad Hani Abu Rmilah Moayad Hani Abu Rmilah · Accepted Answer · 2020-05-10T17:19:26

To solve the issue of duplicate emails, you need to check whether a message is unread or not. If it is unread, then read it and make it as read afterwards. I have made video series on YouTube to explain how this is possible and published the full code on github. You may watch it here:

https://youtu.be/nI1OH3pAz6s?t=9

You can also get the full code from GitHub from the following link:

Google App Script to sync gmail inbox with sheets

2 Answers