Problems Triggering Scripts from AppSheet

[ryanharvey]

Hey all,

In recent months, the on change event triggers I have setup to tie AppSheet and my AppScripts together have all stopped working. It literally won't process the change, though if I do the change manually, the script works fine. So it seems that the onChange trigger is simply not working when the change comes via AppSheet.

I understand there is now a native-script call feature in AppSheet, and I have followed the steps I've found to take advantage of them. Still no dice.

My main issue seems to be calling the script itself. In order to find a script in AppSheet, it must be a stand-alone script file not tied to a specific sheet. But the script I want to trigger is tied to a sheet. And when i copy it as a stand alone file, it won't allow me to create an onChange trigger for it, only time-based or calendar-based triggers.

Any ideas how to rectify this? Or how to change my script to make it work again?

This is the script that *was* working a few months ago:

---

function appsheetNotes(event) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = event.source.getActiveSheet();

var r = event.source.getActiveRange();

if (s.getName() !== "Contacts"){}

else{

var editColumn = r.getColumn();

var rowIndex = r.getRowIndex();

var headers = s.getRange(1, 1, 1, s.getLastColumn()).getValues();

var personalDetailsCol = headers[0].indexOf("Personal Details") + 1;

var personalDetailsVal = s.getRange(rowIndex, personalDetailsCol).getValue();

if (rowIndex > 0 && editColumn == personalDetailsCol && personalDetailsVal == "appsheet"){

Utilities.sleep(1000);

var callnotesCol = headers[0].indexOf("Call Notes") + 1;

var dateUpdate = Utilities.formatDate(new Date(), "GMT-5", "M/dd/yyyy");

var lastpointofcontactCol = headers[0].indexOf("Last Point of Contact") + 1;

var email = s.getRange(rowIndex, lastpointofcontactCol).getValue();

var firstnameCol = headers[0].indexOf("First Name") + 1;

var lastnameCol = headers[0].indexOf("Last Name") + 1;

var uniqueidCol = headers[0].indexOf("Unique ID") + 1;

var targetSheet = ss.getSheetByName("Notes");

var firstName = s.getRange(rowIndex, firstnameCol).getValue();

var lastName = s.getRange(rowIndex, lastnameCol).getValue();

var uniqueID = s.getRange(rowIndex, uniqueidCol).getValue();

var callNotes = s.getRange(rowIndex, callnotesCol).getValue();

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1);

var targetIndex = target.getRowIndex();

var targetHeaders = targetSheet.getRange(1, 1, 1,targetSheet.getLastColumn()).getValues();

var targetuniqueidCol = targetHeaders[0].indexOf("Unique ID") + 1;

var targetfirstnameCol = targetHeaders[0].indexOf("First Name") + 1;

var targetlastnameCol = targetHeaders[0].indexOf("Last Name") + 1;

var targetpointCol = targetHeaders[0].indexOf("Last Point of Contact") + 1;

var targetdateCol = targetHeaders[0].indexOf("Date of Last Contact") + 1;

var targettypeCol = targetHeaders[0].indexOf("Type of Last Contact") + 1;

var targetcallnotesCol = targetHeaders[0].indexOf("Call Notes") + 1;

targetSheet.getRange(targetIndex, targetuniqueidCol).setValue(uniqueID);

targetSheet.getRange(targetIndex, targetfirstnameCol).setValue(firstName);

targetSheet.getRange(targetIndex, targetlastnameCol).setValue(lastName);

targetSheet.getRange(targetIndex, targetpointCol).setValue(email);

targetSheet.getRange(targetIndex, targetdateCol).setValue(dateUpdate);

targetSheet.getRange(targetIndex, targetcallnotesCol).setValue(callNotes);

targetSheet.getRange(targetIndex, targettypeCol).setValue("Email");

s.getRange(rowIndex, personalDetailsCol).clearContent();

return compileNotes ();

}

else{}

}

}

Thanks!

[Koichi_Tsuji]

AppSheet/GAS integration only works for stand alone script.

Twist your script to call the spreadsheet, by calling with ID or name to trigger your GAS.

[Marc_Dillon]

I can confirm that changes via Appsheet do still trigger onChange triggers. Have you checked the execution logs? Have you confirmed that you still have the onChange trigger installed?

My main issue seems to be calling the script itself. In order to find a script in AppSheet, it must be a stand-alone script file not tied to a specific sheet. But the script I want to trigger is tied to a sheet. And when i copy it as a stand alone file, it won't allow me to create an onChange trigger for it, only time-based or calendar-based triggers.

---

The purpose of copying it to stand-alone script is so that you can directly call it from an Appsheet Bot, thus there is no more need for an onChange trigger. You'd have to open your spreadsheet and sheet by ids and names from a stand-alone script.

If you're making changes to the sheet via script, I'd probably stick to an onChange trigger on a linked script, since you automatically get the source of the change.