google-apps-scriptifttt

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


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!


Solution

  • 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.