onEdit() script won't run when sheet is edited by Glide app

Do you have background refresh turned on in Glide? That will update a hidden metadata sheet every few minutes.

I’d also recommend an if statement at the beginning of your script to jump over the rest of the script if the current sheet is not the one you want to run the script against.

1 Like

These are my Refresh settings, do you suggest changing it to ‘only on edit’?

image

I do have an IF statement, so the script skips any unnecessary updates/modifications, my only concern is that the script is constantly being triggered.

onChange is triggered any time the spreadsheet is touched/changed on any sheet. Like I mentioned earlier, when using "While editing or using the app’’, glide will modify the hidden App: Metadata sheet periodically which would be enough to trigger the onChange event. That’s the whole point of the Reload Sheet function. It’s so any background scripts, functions like RAND or NOW() or ImportRange can run without any user intervention to get them to update. I have a script set up similar where it will run any time there is a change in the sheet or the background refresh is turned on, but I also have an if statement to only trigger for certain sheets. I wouldn’t worry about it much unless you are getting errors from google.

3 Likes

thanks, makes sense.

2 Likes

This was a helpful thread! In one of my apps, I can now send a text message to a customer whenever a restaurant marks an order as “Bring Prepared” or “Ready” using a choice component.

2 Likes

G’day @Robert_Petitto

I’d really like to see how you’re achieving the sending of the message. Would you mind elaborating and perhaps sharing the script?

I used this method to send SMS messages as emails:

The context is that Chefs will mark the status of the order. This updates column B of my “Orders” sheet.

Columns G-I of this sheet contain the phonenumer@carrier information and a message. The message column (I) is a template that contains the order status and the name of the restaurant.

I wanted to send an email (SMS message) when the status changes in Column B to either “Being Prepared” or to “Ready” (there are 4 possible statuses).

Here was the code I used:

// The column you want to check if something is entered.
var COLUMNTOCHECK = 2;
// Sheet you are working on
var SHEETNAME = 'Orders';

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  //checks that we're on the correct sheet.
  if(sheet.getSheetName() == SHEETNAME) { 
    var selectedCell = ss.getActiveCell();
    var selectedRow = selectedCell.getRow();
    var dataRange = sheet.getRange(selectedRow, 7, 1, 3);
    var data = dataRange.getValues();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if(selectedCell.getColumn() == COLUMNTOCHECK) { 
       for (var i = 0; i < data.length; ++i) {
         var row = data[i];
         var emailAddress = row[0]; // First column
         if (emailAddress.match('@')  === null){
           continue;  // skip this iteration of the loop and go to the next one
         };  
         var message = row[2]; // Third column
         if (message.match('!')  === null){
           continue;  // skip this iteration of the loop and go to the next one
         };
         if (message.match('complete')  !== null){
         continue;  // skip this iteration of the loop and go to the next one
         }; 
         var subject = 'Potluck Order Status';
         MailApp.sendEmail(emailAddress, subject, message, {name:'Potluck', noReply: true});
         SpreadsheetApp.flush();
         
       }
      }
  }
}

Finally, had to create an onChange trigger since the spreadsheet doesn’t recognize Glide writes-to-sheets as onEdit triggers.

Works like a charm!

5 Likes

Thank you Bob, was also thinking that this also has something to do with the number@carrier part but I can’t figure out what the format is for our carriers here in Vietnam lol, not that I have clients in my own country so far, just wanna explore.

1 Like

thanks @Robert_Petitto this is awesome, unfortunately this is a paid additional service for the major carries here in Australia.

There are actually ways around that Rogelio. You see, this is almost the same exact way they send texts from phone to phone. You just need to find the correct @sending ending :slight_smile:

hey @Drearystate any ideas on how i can find a way around to find a cost-free solution?

Hi,

looking at your script as I’m trying to implement something similar.

Where in your script do you actually do the check for “Being Prepared” or “Ready” for the status of the order?

@Jeff_Hager wow, mystery solved for me too. I’ve been wondering why that onChange is triggering constantly. I do get errors from google pretty regularly, but it doesn’t seem to be affecting anything that I can see, so I just disregard them.

What kind of “if statement” could I write so that it only triggers for certain sheets? What would that look like exactly? I’d like to implement that if I can. My script is only relevant to a few sheets, so there’s no reason for it to trigger on changes to that App: Metadata sheet.

You can use the getSheetByName method, I suppose.

I already use that method. It’s part of the fuction itself, so comes after the onChange trigger. How would that affect when the onChange command is triggered? I do have lots of if statements within that function that only perform any actual work if a change has been made to a certain sheet. But I thought Jeff was talking about using an if statement to forgo the onChange command completely under certain conditions.

Oh I use the same method as you, which is to check if the event happens on which sheet and whether to continue or not based on that condition. So the script is actually triggered behind the scenes, but whether it leads to an outcome or not depends on the condition.

The COLUMNTOCHECK specifies column 2 (B). This is the column that contains the values altered by the choice component.

I get that bit, but you also said that there are 4 possible order statuses.

Where do you do actual check for the correct status before sending out the notification?

eg; IF COLUMNTOCHECK == “Being Prepared” or “Ready” THEN SEND_NOTIFICATION

possibly this line, trying to figure this line out?

if (message.match('complete') !== null)

Right. That’s the one…there are 4 statuses. If null…nothing. If complete…nothing. Which leaves the other two statuses.

Check out the script here. Here you can see that the script only continues if the active sheet is “Contacts”. It doesn’t mean that the script won’t run at all, but it will just skip over all of the code if the changed active sheet is not the we want to run code against.

Yes I get occasional errors from Google, but I only have a couple of user’s, so it’s not a big deal. The code that needs to run eventually runs anyway.