Xlsx file in GDrive. On-update workflows being triggered on every edit

[Marc_Dillon]

I’m working on an app based off of an xlsx file in GDrive. I should have converted it to a GSheet a long time ago, but I figured it wouldn’t cause any real problems, just be an inconvenience. I’d rather not convert it now, although it is now causing a problem (I think).

I have a couple workflow emails, triggered by a user’s button press. The action sets a hidden [trigger] column to the value of NOW(). The workflow checks a condition of:

AND(
ISNOTBLANK( [trigger] ),
[_THISROW_BEFORE].[trigger] <> [_THISROW_AFTER].[trigger]
)

Just like how I’ve setup pretty much all my button-triggered emails for many months.

The problem here seems to be, that anytime any change is made to the data, the xlsx file needs to be “reloaded” in GDrive, and this seemingly makes it think that all values in the entire sheet were edited. I looked at the version history in drive, and it showed a blue background for the whole sheet (the blue background indicates a change since the previous version).

So what’s happening, is with any change in the data record, the workflow is being triggered, even if the [trigger] column is unchanged. I’ve double-checked the values, for example the previous [trigger] value was “3/29/20 11:00 am”, then I made an edit at 11:30 am, received a workflow email, and saw that the [trigger] value was still showing “3/29/20 11:00 am”.

Does this seem feasible to anyone? I can’t imagine any other reason, and have witnessed the xlsx seemingly “reload” or “refresh” whenever there is a change. But I feel like the _BEFORE and _AFTER should be internal to Appsheet, not dependent on the spreadsheet.

[Steve]

Maybe change this:

ISNOTBLANK( [trigger] )

to this?

ISNOTBLANK( [_THISROW_BEFORE].[trigger] )

Maybe the data types aren’t consistent? Make sure both the table column and worksheet column are DateTime. Also, ensure your spreadsheet locale is set properly.

Otherwise, I got nothing. Ask support@appsheet.com?

[Steve]

As a test, set the condition to FALSE. Does it still trigger?

[Marc_Dillon]

Thanks for the reply @Steve.

I’ve already moved on and fixed the issue another way, so I can’t really test things anymore, at least not with this project, maybe I’ll make a test app later.

I did not test this:

Steve:

ISNOTBLANK( [_THISROW_BEFORE].[trigger] )

I did fiddle with the format type in the spreadsheet a few different ways, did not seem to make a difference.

I just looked at the spreadsheet’s time zone setting, GMT-8, whereas I am GMT-5. So maybe that was it. Although that setting is only when you “open with google sheets”, not in the xlsx I don’t think.

I did also set the condition to FALSE and they did not run.

My advice for anyone else reading along, don’t use xlsx files inside of GDrive, just a big headache all around.