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