Good day,
I have been busy with an app that reads data from a sheets which contains a date. But when I read the date from the sheet it displays like this 2021-12-19T22:00:00.000Z in the apps textbox. The date is formatted as follows in the sheet MM/dd/yyyy.
How can I fix the formatting to display the date as dd/MM/yyyy in the textbox when read from the sheet and not display this date format 2021-12-19T22:00:00.000Z?
I would start on the Google side of the sheet, checking if the column in question is numeric with one of Google's date formats.
You can customize those.
That enables date math and sorting and filtering on the Google side.
Look in the Sensor drawer for the Clock component, which has date format blocks.
Google Sheet dates are internally measured in days from 1899, and AI2 Milliseconds are counted in milliseconds from 1970.
AI2 Instants are complex objects, best left in volatile RAM and not DB friendly.
Thank you for the assistance. However I need to possibly make it clear that the dates, that are being read from the sheet, don't all look as per my example date, but that is the format they appear in. So having dateData look for that specific set date won't really fix the issue. Could your fix be modified to look for any date in that T-Z format and correct it to dd/MM/yyyy?
Also, dumb question, where would I call fixDateDisplay?
As @ABG suggested fixing things upstream on your google sheet would make more sense.
How are you retrieving the data from your google sheet?
An alternative would be to use a google apps script web app, which can convert your data to "displayValues" returning "what you see" on the sheet, instead of the underlying unformatted data.
Apologies ABG, I don't know what one would call this type of format or otherwise explain so I just call it the TZ format but its this type of format: 2021-12-19T22:00:00.000Z. It has a T and Z in it, hence TZ format.
As to what I meant when replying to TIMAI2 was simply that the TZ formatted dates are different dates and not a single date in other words some would be 2019-03-25T22:00:00:000Z or 2022-07-12T22:00:00:000Z. Like I said having dateData look for one specific date wouldn't really help however it is a step in the right direction, I was wondering if his code could be altered to look for all dates containing TZ?
I have had no opportunity to test the code @TIMAI2 posted because it's not draggable but from reading it closely it works entirely off the TZ format delimiter and positions, so it should work for you.
I am on my phone so I can not provide draggable blocks.
Perhaps you are new to procedures?
I will certainly give it a try thank you.
Yes this is new-ish to me, haven't programmed in a long while so re-learning as I go a long.
Be sure to test against dates in the early parts of the month and year to verify you get double digits, if you use segment code.
Before diving into the world of google apps script web apps, it may be worth trying a gviz query to return you data. There should be no mixed data types in any column (e.g. one row = text, next row = number, they must all be text or numbers). On testing gviz appears to return display values and not the underlying values.
