Hi folks
Summary:
After many hours of troubleshooting, it seems that AppSheet is ignoring the local and date format of source data when processing data.
Background:
I am building an app which needs to import data from an external system. Several columns in this data are Dates. The Google Sheets underpinning the app areboth set for Locale = Australia. The locale for the source and target tables in AppSheet have Locale = Australia.The Date columns in Google Sheets have formatted as “dd mmm yyyy”.
Problem statement:
I have an Action to read the data from one table, and paste it into another (using “Data: set the values of some solumns in this row”). When the Action reads a date “10 May 2019”, it works fine, and the date in the target table seems to be shown as “10 May 2019”. When it reads a date “21 May 2019”, it throws an “invalid Date” error. If I change the date from “21 May 2019” to “2 May 2019”, the amended date is transferred correctly (but the data is obviously incorrect).
Attempted solutions:
I have tried:
- various date formats in the Google Sheets
- using "TEXT([date field], “dd/mm/yyyy”) in the Action
- changing the Locale settings for the spreadsheets and associated AppSheet tables
Workaround:
I have reverted the schema for the Date fields in both tables to Text type for the time being.
Any suggestions are welcome!
Thanks