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!