Google Sheets Locale
The Google Sheets Locale setting controls how Date, DateTime, Decimal, Price, Percent, and Time values are entered into Google Sheets.
For Date value, when the Google Sheets Locale is United States, dates are normally entered in MM/DD/YYYY format. Conversely, when the Google Sheets Locale is United Kingdom, dates are normally entered in DD/MM/YYYY format.
For Decimal values, when the Google Sheets Locale is United States, United Kingdom or Japan, you must enter a period as the decimal separator between the whole and fractional parts of Decimal, Price, and Percent values. Conversely, when the Google Sheets Locale is Germany, France, or Brazil, you must enter a comma as the decimal separator.
If your Google Sheet was generated as a response sheet for a Google Form, setting the sheet locale may not may not automatically apply the new locale formatting to existing columns. To fix this, highlight the column and apply the appropriate formatting from the Format / Number menu in the toolbar.
AppSheet Table Locale
The AppSheet Table Locale setting controls how AppSheet reads and writes values from Google Sheets. The AppSheet Table Locale must match the Google Sheets Locale. This ensures that your application reads and writes Date, DateTime, Decimal, Price, Percent, or Time values to the Google sheet in the proper Locale format.
When you add a new table to your application using the AppSheet Editor, the Editor automatically retrieves the Locale setting from the Google worksheet and sets the table's Locale appropriately.
If you later change the Google Sheets Locale setting, the Editor automatically retrieves the Locale setting from the Google worksheet and sets the table's Locale appropriately the next time you open the application in the Editor.
You can view the AppSheet Table Locale in the Editor by going to Data>Tables>Data Locale.
Note: Data Locale does not impact Price Currency settings and AppSheet does not provide currency conversions. Data should be entered in your spreadsheet/app according to the currency you would like displayed. See How do I convert from US Dollars to my local Currency.
Configuring the Locale
- Make certain that your Google Sheet specifies the appropriate locale. Do this by opening the Google Sheet. From the Google Sheets "File" menu, select the "Spreadsheet settings". On the "Settings" dialog set "Locale" to your locale. For example, in Thailand select "Thailand".
2. Make certain that each Date, Time, DateTime, Number, Currency, and Percent value in the Google Sheet is formatted appropriately. Do this by selecting all of the cells in the column containing the data values. From the "Format" menu select "Number" and then the appropriate formatting style. For example, for date values select "Date", for currency values select "Currency", for time values select "Time", and so forth. Do this for each Date, Time, DateTime, Number, Currency, and Percent column.
3. Add the Google worksheet as a Table using the AppSheet Editor. The Editor will automatically set the table's Locale to match the Google worksheet's Locale.
If you later change the Google worksheet Locale setting, the Editor will automatically set the table's Locale to match the Google worksheet's Locale when you next open the application in the Editor.
4. Make certain that your browser or device is set to use your locale. For example, in the United Kingdom select the UK locale. In Thailand select the Thai locale. The browser or device setting completely determines how data values are displayed within the AppSheet app running on your browser or device.
5. If you use a calendar other than the Gregorian calendar, make certain that your browser or device is set to use that calendar. For example, in Thailand select the "Buddhist" calendar. Many countries use the Gregorian calendar, so in many cases you can skip this step.
6. Click the "Sync" button in the application to read the latest values from the Google Sheet. See if the Date, DateTime, Decimal, Price, Percent, and Time values are displayed correctly in the AppSheet application. If not, verify your browser or device locale settings.
7. Try updating a Date, DateTime, Decimal, Price, Percent, or Time value and saving the changes to the server. See if the correct values appear in the Google Sheet and the AppSheet application.
8. All data is sent between the AppSheet client and the AppSheet server in a common universal format. This makes it easier for the client and server to communicate. It has no effect on how data is stored in the worksheet or displayed in the client.
One of the Locale values appearing in the Locale dropdown menu is the Compatibility Locale. This value is present for backward compatibility only. It preserves AppSheet’s old, limited Locale behavior. If you choose Compatibility Locale, we use a period as the decimal separator between the whole and fractional parts of Decimal, Price, and Percent values. We use simple rules for saving Date, DateTime, and Time values. We save formulas in the United States Locale format, which works for many Locales that use a period for the decimal separator.
If your Google Sheet does not specify a Locale, we set the AppSheet Table Locale to the Compatibility Locale. However, we strongly recommend you specify a Google Sheet Locale.
We currently support approximately 70 Locale values. We selected these locales based upon the locales that Google Sheets currently supports. Please let us know if a locale you need is missing from AppSheet.
Locale for Excel
It is not necessary to set the AppSheet locale for AppSheet applications that store data in Excel files on Box, Dropbox, Google Drive, Office365, or OneDrive. This is a consequence of the way we add and update data and formula values in Excel files. The AppSheet table locale should be set to either United States or Compatibility.
Locale for Smartsheet
It is not necessary to set the AppSheet locale for AppSheet applications that store data in Smartsheet. This is a consequence of the way we add and update data and formula values in Smartsheet. The AppSheet table locale should be set to either English (United States) or Compatibility. If your app involves workflow rules that send emails or SMS messages, we utilize the locale of your table/sheet to determine how to format the values (like dates and times) in the message body. In this case, it is important to set the locale of your Smartsheet appropriately.
Note: Changing the table's Locale in AppSheet doesn’t retroactively change the format of previous written entries, nor does it affect how values are displayed in the app emulator. What’s displayed in the app emulator is based on the locale settings of your mobile phone or web browser. The table's Locale setting only affects how data is read and written to the spreadsheet.