NaN is being returned as value

Hi,

I have set-up a column of type Price. The column uses a formula in GoogleSheet.
I have created a workflow to send SMS when a record is created or updated. The SMS body gets the value of the Price column. However, instead of the actual price Value, what the SMS message is showing is NaN. Why is this happening?

Any help would be greatly appreciated.

Your formula is producing a result that is not a number (NaN).

1 Like

Hi Steve,

It was working fine before and the data in GoogleSheet is properly displayed in the details view in Appsheet.

See below screenshot:
df1 df2 df3

And heres whats in the GoogleSheet:
gs1

1 Like

I’m afraid I cannot help troubleshoot spreadsheet formulas. Please contact support@appsheet.com for help with this.

Alright Steve. Thanks. The spreadsheet formula is working fine though as it returning the correct resulting. It is even formatted as a number in the spreadsheet.

Will reach out to support then

1 Like

Spreadsheet formulas are not advised for many scenarios, such as this one. You should try to replicate it as an App Formula.

The workflow would be triggered as soon as the data change happens. But since you’re using a spreadsheet formula to calculate the value, it has to sync before it gets the new value. Therefore the workflow has no way of knowing what the new value is. The system probably knows this, and outputs “NaN” instead of showing you what would probably be an incorrect value.

1 Like

Hi Marc.

The NaN only shows on iPhone when sending SMS. On Android, it picks up the value just fine.

I’m not sure that I can replicate the formula as an App formula as it is using a GoogleMaps function for GoogleSheets to calculate distance.

The App syncs first when saving data since the Delivery Fee column has to reference the driving distance calculated between 2 addresses using GoogleMaps functions.

With this scenario, would it be possible to use GoogleMaps functions directly in the AppFormula?

Thanks,

Ninia

What? The same SMS received on Android does NOT have NaN? Or…what?

No. But maybe:

1 Like

Yes, same SMS when using app on Android is able to capture the “Price” value but if the SMS is sent from the app on iOS shows NaN for “Price” value

[quote=“Marc_Dillon, post:8, topic:43971”]
No. But maybe:
[/quote] My understanding is that the DISTANCE() app formula is straight line distance and not driving distance. Do you know if the GOOGLEMAPS_DISTANCE() has been used successfully in the AppFormula?

You’ll probably have to contact support for this one as Steve already suggested. As further investigation, you should look at the actual SMS payloads from the workflow logs. Workflows are performed on the server, the type of device that the triggering data change originates from shouldn’t affect what is actually in the payload. SMSs are also just simple text, so the type of device receiving the message also shouldn’t affect it. At lease that’s my understanding.

No, you can’t use any google maps functions in appsheet.

2 Likes

Thanks Marc. Yeah, will probaby have to get in touch with support. Its mostly my observation as I tested the Send SMS workflow with my app from both Android and iOS phones.

I was reading through Integrating Google Appscript and Appsheet. I have the initial impression that maybe the GoogleMaps Functions in Appscript could be rewritten for Appsheet use? Just a thought

Having a GAS put a plain text value of the googlemaps function result into the cell, upon only changes that require a recalculation, as opposed to using a spreadsheet formula, would certainly be better in some regards.

Though you’d still have to sync the app, probably twice, before that resulting value would be available to the app, only then could the workflow be run successfully. Unless you took a further step of having the GAS POST to Appsheet’s API.

None of the above is trivial…

1 Like