CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggersgoogle-appsheet
Anthony Christian
Anthony Christian

Reputation: 1

Google Scripts onEdit not recognising data being edited on a sheet during a sync from a mobile device

I have a Google sheet that is updated by a mobile app created on AppSheet. I have a column of data that I need to keep a history of so wrote a script to copy the column to a fblank column in another sheet.

function readdailyChecks() {
    var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Todays Checks");
    var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("History");
    // Copy from 5th column, all rows for one column 
    var valuesToCopy = sheetFrom.getRange("D2:D100").getValues()
    //Paste to another sheet from first cell onwards
    sheetTo.getRange(1,sheetTo.getLastColumn()+1,valuesToCopy.length,1).setValues(valuesToCopy);
}

I then wrote another script to do this task when the sheet was edited thinking the sync would edit the sheet data.

function onEdit(e) {
    if(e); 
   readdailyChecks(e)
}

The idea being that the app would sync once the form was completed, update the sheet and trigger the onEdit code to do it's stuff. The problem is that the sync changes the sheets data without editing it so the historical data is not created!

Is there an onSync code or a way that when the data changes the script can be triggered?

Upvotes: 0

Views: 976

Answers (1)

Praveen Seshadri
Praveen Seshadri

Reputation: 86

I work at AppSheet. When updates are made to Google sheets via the Google Sheets backend API, they do not fire the onEdit trigger. I'm not sure why exactly this is, but it is a limitation imposed by Google. So that is what you are observing.

The AppSheet documentation suggests that you try a timed trigger instead, polling for changes. https://appsheethelp.zendesk.com/hc/en-us/articles/206483017-Google-Drive

Not the greatest, but it does work. Some AppSheet users have reported success with the onChange trigger instead of the onEdit trigger. To me, this defies logic based on the documented meaning on an onChange trigger, but it appears to work for these users, so worth a shot.

Upvotes: 1

Related Questions