CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetssynchronizationgmail
Morten Mouritzen
Morten Mouritzen

Reputation: 1

Google App Script to sync gmail inbox with sheets

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.

Upvotes: 0

Views: 1723

Answers (2)

Moayad Hani Abu Rmilah
Moayad Hani Abu Rmilah

Reputation: 830

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:

Upvotes: 1

Cooper
Cooper

Reputation: 64040

You could run your script from a timebased trigger and if you just rewrote the entire sheet each time then that would take care of eliminating the entries from deleted emails. If you could add the received date to the spreadsheet then you could order them by date.

Upvotes: 0

Related Questions