NaN is being returned as value

Nin_o
Participant II

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.

0 11 1,349
11 REPLIES 11

Steve
Participant V

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

Hi Steve,

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

See below screenshot:
3X_9_e_9e6bce68eb6654b528e405faa26e7c136e92d039.png 3X_d_7_d7fb675da3fa0cd3bc4993dff368a387ee72055a.png 3X_0_2_02a7a1f87679eb0cc4760ecd87bbf87b3f514c00.png

And heres whats in the GoogleSheet:
3X_f_1_f1e1fa0f31104efb91688f635e028137eda66c77.png

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

Nin_o
Participant II

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

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.

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:

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.

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โ€ฆ

Top Labels in this Space