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!

Answer 1

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 = "[email protected]";
  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.