Data Change Condition Expression only when a certain column is changed

[holtmeier]

I have an app that uses Google Sheets as a database. The client wants an SMS to be sent every time that the “Status” column changes (if the ‘status’ on row 1 changes, the phone number on row 1 is sent a message.

I have added on the AppSheet Events add-on for google sheets. The client updates the status column in the sheet.

On the appSheet side, I have created an event which fires when an update to the database is called:

However, my condition ([_THISROW_BEFORE].[STATUS] <> [_THISROW_AFTER].[STATUS]) appears to be insufficient. If I add text to the ‘Repair Notes’ column, this event is triggered even though the status is unchanged.

Is this an issue of Google Sheets? Is my condition incorrect? Please help!

[Marc_Dillon]

I just tested this myself as well. Got the same result, it sent an email even when a different column was edited.

My guess is that because the edit didn’t happen within the app, it has no way to know what the previous value was.

Maybe @prithpal can comment on this?

I’d imagine that the external eventing is more for the purpose of catching new records in a sheet from other sources, whereas catching fine detail updates is perhaps a bit out of its scope.

[holtmeier]

Marc_Dillon:

My guess is that because the edit didn’t happen within the app, it has no way to know what the previous value was.

I was afraid of that. Customer is used to being able to edit the google sheet directly and sending messages but they might not be so lucky this time

[Dan_Bahir]

@Marc_Dillon is correct the Google Sheet add on does not support before/after information. Only in app change provide that level of information.

[Marc_Dillon]

Just a thought on how to possibly make it work for you.

You could maintain a “mirror” Table. Just make an exact copy of your Table and load it into the app as well.

Then create 2 separate Bots, the first one has an Event that only fires if your certain value is different than the value held in the mirror Table, the second one fires on all changes, and simply updates the record in the mirror Table to match the new changes in the main Table.

But I’m not sure how you would force the first Bot to run before the second, but I also haven’t looked at it at all yet, just theory for now.

[WillowMobileSys]

There is piece of info missing in my mind for this.

Is it that the changes are being made by external processes to the Google sheet and then you are trying to integrate into AppSheet solely for sending the SMS?

Or do the changes originate from an AppSheet App (same or other app), changes are applied to sheet and then you are trying to use the Google sheet change to trigger the SMS?

In other words…

holtmeier:

I have an app that uses Google Sheets as a database.

Is the app in this statement above an AppSheet app or some other app?

[holtmeier]

These are all good questions @WillowMobileSystems.

Is the app in this statement above an AppSheet app or some other app?

The app is an Appsheet app.

WillowMobileSystems:

Is it that the changes are being made by external processes to the Google sheet and then you are trying to integrate into AppSheet solely for sending the SMS?

I think you’re correct here - the ‘external processes’ is literally a guy with the spreadsheet open who manually is making edits to cells.

[WillowMobileSys]

holtmeier:

the ‘external processes’ is literally a guy with the spreadsheet open who manually is making edits to cells.

Ok, got it! If at all possible, I would strongly encourage to have that “guy” make the necessary changes from AppSheet. Then you can EASILY create whatever triggers are needed for the SMS … and whatever other automated needs arise in the future.