Setting up a trigger when a Google Sheet gets updated by IFTTT

Question

I'm using IFTTT to update my Google Sheets when I receive a SMS. Now, I would like to take a step ahead and write a Google Apps Script that would do different things with the data updated by IFTTT into my Google Sheet. I tried to achieve the same using the Google Apps Script's onEdit function, but that does not work. I did a lot of search on multiple forums regarding this problem, and I learnt that onEdit works only when a "user" makes the changes to the Google Sheet and not when the changes are done over an API request (I believe IFTTT uses the same). I could not see even a single post with a working solution.

Any ideas? Thanks!

Depending on what IFTTT does (I suppose it adds some rows) you can easily use a timer trigger to check if the total row count has changed and react accordingly. — Serge insas, May 12 '18 at 07:09
The reason there is no "working solution" is because the solution for everyone is different because they do something different. For example, rather than have ifttt write to your sheet, you could have it `POST` to an Apps Script webapp, which then does the write and also the side effect you want to add. — tehhowch, May 12 '18 at 12:26
Hey @tehhowch, thanks for your reply. I don't quite understand how it could be achieved. I'm using IFTTT service as an Android app in my phone and an applet pushes every new SMS to a Google Sheet. How could I possibly avoid using IFTTT here? — Akshay Maldhure, May 14 '18 at 04:10
@AkshayMaldhure have it send the data to a URL of your choice. Publish your Script project as a webapp and use its url — tehhowch, May 15 '18 at 00:18

Akshay Maldhure · Accepted Answer · 2019-01-07T05:24:35.023

After a lot of Google search, I found below code to be working for me. It is inspired by this answer by Mogsdad.

function myOnEdit(e) {
  if (!e) throw new Error( "Event object required. Test using test_onEdit()" );
  // e.value is only available if a single cell was edited
  if (e.hasOwnProperty("value")) {
    var cells = [[e.value]];
  }
  else {
    cells = e.range.getValues();
  }
  row = cells[cells.length - 1];
  // Do anything with the row data here
}

function test_onEdit() {
  var fakeEvent = {};
  fakeEvent.authMode = ScriptApp.AuthMode.LIMITED;
  fakeEvent.user = "hello@example.com";
  fakeEvent.source = SpreadsheetApp.getActiveSpreadsheet();
  fakeEvent.range = fakeEvent.source.getActiveSheet().getDataRange();
  // e.value is only available if a single cell was edited
  if (fakeEvent.range.getNumRows() === 1 && fakeEvent.range.getNumColumns() === 1) {
    fakeEvent.value = fakeEvent.range.getValue();
  }
  onEdit(fakeEvent);
}

// Installable trigger to handle change or timed events
// Something may or may not have changed, but we won't know exactly what
function playCatchUp(e) {
  // Build a fake event to pass to myOnEdit()
  var fakeEvent = {};
  fakeEvent.source = SpreadsheetApp.getActiveSpreadsheet();
  fakeEvent.range = fakeEvent.source.getActiveSheet().getDataRange();
  myOnEdit(fakeEvent);
}

Hope this helps someone in future. Do note that the functions playCatchUp and myOnEdit must be set as "change" and "edit" action triggers respectively in Google Apps Script.

Setting up a trigger when a Google Sheet gets updated by IFTTT

1 Answers1