Solved: Google sheets locale in template apps

Kirk_Masden · 10-16-2022 02:16 AM

I have an app that I have shared as a template / sample. Dates and times are used in the app but I also have the Google sheets doing some important calculations with dates and times that are then fed back into the app eventually.

Until mid-September or so, when my template was copied, the Locale would be Seattle, Washington -- presumably because that is the location of AppSheet's headquarters. I thought that was kind of weird but I built my app to accommodate that. Now, however, when students in Japan copy my template, it seems that the Locale is set to Japan. That's not a minor issue because locale "affects formatting details such as functions, dates, and currency." Dates are the main problem for me. This change has broken my app for new downloads. And, fixing it is complicated -- merely changing the Locale setting back to Seattle is not enough. I had to find columns that parsed dates and times and then adjust the format settings on them -- very tedious.

Now, here are my questions:

1. I don't think this change was mentioned on "Release Notes & Announcements." I could be wrong but I searched for "Locale" there and couldn't find anything between the time when my app copied with the Seattle "Locale" and the most recent instance of in being copied as with the locale (for me, problematic) locale of "Japan." Was this significant change not announced? Is it only affecting people like me who publish templates? Might it not also affect other types of app publication?

2. I would like to fix my app to make it impervious to this kind of issue. Currently, however, when I record dates and times to my spreadsheet, I record them in this format:

10/16/2022 2:42:33

Then, I need to used Google Spreadsheet formulas to interpret this text. If the "Locale" is set to Japan at the outside, it throws all that interpretation off. Can anyone share some strategies with me for how to avoid sheet corruption caused by Locale changes? Thanks

P.S. I've looked at the Locale articles in AppSheet help but couldn't find the specific issue I'm writing about here:

https://support.google.com/appsheet/search?hl=en&q=locale&from_promoted_search=true

Aleksi

If everything goes well with the testing, the fix for this should go out when we next deploy.

View solution in original post

Kirk_Masden

Here's a little update. I tried to test this to confirm that what I happened to my student's sheet is now the norm when an app is copied but couldn't do so. Here's the URL of my sample app:

https://www.appsheet.com/templates?appGuidString=c5a144dc-7064-4862-af7f-e20c5ade0307

Here are the images of me trying to copy it:

I was going to post it as a video but it would basically be a video with just these two frames: I click on "Copy app" after setting the name (I've tried different names too) and then I go immediately back to the template web page -- no copying icon or anything. Very strange.

By the way, I was logged in with a different account from the one that owns the template in order to attempt to test the copying process and results. Also, I tried this on both Safari and Chrome with the same results.

Kirk_Masden

I just tried to copy an AppSheet template and the same issue occurred. I think this is a major bug. Could others please check to see if you can copy AppSheet templates:

https://www.appsheet.com/Templates

I had the same issue on my phone so it's not just an issue with my computer.

Kirk_Masden

Hi! I just wanted to report that I have contacted support about the inability to copy templates. If any of you have time to copy a simple template to see if the function is still working for you, please do so and report here. Thanks!

https://www.appsheet.com/Templates

Kirk_Masden

Logged in on a different computer but was still unable to copy any of the official AppSheet template apps.

Aleksi

If everything goes well with the testing, the fix for this should go out when we next deploy.

Kirk_Masden

I'm not sure what caused the problem in my student's sheet but I was able to test the copying process and everything worked well. I set my default Google language to Japanese so that the locale of new spreadsheets I make is set to "Japan" as a default. Nonetheless, when I copied my app into that account (different from the one on which I made the app) the locale of me sheet was not changed. Great!! Thanks very much!!

Kirk_Masden

Thanks!!!

Kirk_Masden

By the way, @Aleksi .I wonder which issue the fix you are referring to relates to -- the inability to copy templates or the changing of the Google sheet locale when a template is copied?

Steve

The fix for copying templates should go out today.

Aleksi

Copying the template 🙂

Steve

@Kirk_Masden wrote:
Until mid-September or so, when my template was copied, the Locale would be Seattle, Washington -- presumably because that is the location of AppSheet's headquarters.

I wonder if this might be the culprit:

May 26, 2022 - Google Cloud Community

Thanks to @lizlynch for pointing this out!

Kirk_Masden

Thanks @Steve!

First of all, copying of my app with many different students in Japan went smoothly in September, so I don't this the issue you pointed out would have caused the more recent issue I experienced with the latest copy of my app being corrupted. I'll come back to that later in this response.

Regarding the May 26 change:

I had missed this announcement from @lizlynch but, actually, I think I remember it affecting my app. As I recall, I went through my app and changed formulas on the AppSheet side (not the Google spreadsheet side) so that dates appear properly, regardless of the browser configuration. Here's an example of what I have now in English:

concatenate(ifs(MONTH([Date])=1,"January",MONTH([Date])=2,"February",MONTH([Date])=3,"March",MONTH([Date])=4,"April",MONTH([Date])=5,"May",MONTH([Date])=6,"June",MONTH([Date])=7,"July",MONTH([Date])=8,"August",MONTH([Date])=9,"September",MONTH([Date])=10,"October",MONTH([Date])=11,"November",MONTH([Date])=12,"December",)," ",day([Date]),", ",year([Date])," (",ifs(WEEKDAY([Date])=1,"Sunday",WEEKDAY([Date])=2,"Monday",WEEKDAY([Date])=3,"Tuesday",WEEKDAY([Date])=4,"Wednesday",WEEKDAY([Date])=5,"Thursday",WEEKDAY([Date])=6,"Friday",WEEKDAY([Date])=7,"Saturday"),")")

This consistently produces dates like "October 14, 2022 (Friday)" and I make Japanese versions ("2022年10月14日（金）") with this:

concatenate(YEAR([Date]),"年",MONTH([DATE]),"月",DAY([Date]),"日（",
ifs(WEEKDAY([Date])=1,"日",WEEKDAY([Date])=2,"月",WEEKDAY([Date])=3,"火",WEEKDAY([Date])=4,"水",WEEKDAY([Date])=5,"木",WEEKDAY([Date])=6,"金",WEEKDAY([Date])=7,"土"),
"）")

My current problem

The problem I'm having is with expressions within the Google spreadsheet. It doesn't matter how these expressions look -- they just need to function properly. On the spreadsheet side, I have a large number of datetimes that the spreadsheet rearranges in chronological order using complicated expressions such as this one:

=sort(unique(TRIM(transpose(split(textjoin("|",true,iferror(split(indirect("'Long Term'!D2:D1000"),",")),indirect("Current!AH2:AH1000")),"|")))))

In this case, I'm splitting collections of AppSheet datetimes that are written to the sheet in cells that contain multiple datetimes as text. The problem is that the Google spreadsheet fails to "understand" the meaning of the text properly if the locale in the spreadsheet is changed to "Japan."

In writing this, it occurs to me that it might be a good idea for me to have dates written to the spreadsheet as numbers (e.g. "44695.75295") but I'm not even sure how to do that in AppSheet. In Google spreadsheet, I can use value() to convert a datetime into a number but number() doesn't seem to work the same way in AppSheet. So, in my current configuration of my app, I need to be sure that the locale of the spreadsheet will not be changed when my app is copied. Otherwise, expressions like value() seem to stop working properly on the spreadsheet side.

Kirk_Masden

I'm having trouble figuring out how to replicate the sort of problem that occurs on my spreadsheet but here's the error:

This has only happened once recently -- when the copied sheet's locale was changed to "Japan."

In sum, I don't fully understand what goes wrong in this sort of case but I'm pretty sure that if the locale is not changed in the spreadsheet when the template app is copied that this error will not occur.

Kirk_Masden

P.S. As I wrote above, the issue seems to be solved now -- as long as AppSheet continues to respect the locale of the sheet in the template, not the default locale of the person doing the copying. So, the problem described above may be rather academic; if the platform remains stable, my app should work as it is now. 🙂