Data written to spreadsheet from app as Text format not Time format

I have a number of fields which are choice. They list from a table in th esheet that is formatted as Time values are e.g. 07:30:00 etc

The destination cells in Google Sheet are formatted as time as well.

Data appearing in Gilde sheet is left justified indicating it is recognised as Text
Data in Google sheet becomes '07:30:00 which is Text. When I check the formatting on the cells after the app writes to the sheet it is changed to automatic.
Where can you direct Glide to treat data as a particular format?

Sounds like you need to set the Glide column type to date/time…

Note that “Time Only” is a formatting option, however it’s still stored as a full date/time under the hood.

1 Like

Thanks Darren but it doesn’t fix the problem. Still adding a single quote when writing to the Google Sheet.
Interestingly, if you select Date / Time there is not quote added but that’s not useful for the time field I need anyway.

Might be a Google Sheet issue automatically deciding a time format is text. Not helpful when you then want to compare the time to another that is correctly formatted as time…

Probably need a script to change formatting back to time onEdit. manually doing this makes formulae work fine.

Have a guy who can do that.

In my experience, the key is to first ensure that the Glide column is formatted as date/time. Once you’ve done that it will write a date/time value into the spreadsheet. You can then apply column formatting in the spreadsheet to get it displayed as desired, and that formatting will be respected as new rows are added.

As an aside, assuming that you’re using these values in sheet formulas, I’d encourage you to explore the option of moving those calculations to Glide. Doing that has several advantages, including solving these sorts of issues.

Hi Darren

It’s definitely writing a “correct” time value … just with a troublesome single quote added to front.

Its not a row adding issue. The app allows Tutors to modify their available times for different days. This means editing column values in existing rows. This consistently places a single quote in front of a Time Only data string, even when the existing string is correctly formatted. Doesn’t happen for any other format. date Time is no good as it is a day of the week calendar not date calendar.

Moving formulae to Glide is simply not an option. They are huge formulae over a very large number of cells using quite complex logic and maths.

Just waiting on a quote for the spec to reformat using a script but it will be way less than the cost of my time to switch formulae to Glide … assuming glide can do what I have implemented anyway. Already have substantial scripts doing things standard Google Sheets cant handle so no drama adding another function.

Do you know offhand if a data update to an existing sheet cell by glide is seen as a Edit by Google Sheets? If it is, the reformat can be activated each time a Tutor edits their data.

Okay, that’s surprising. When I have some time, I’ll test that myself as I don’t believe it should work that way.

Unfortunately, no.
Glide updates will only ever fire an onChange() trigger.
You can still do what you want with a script, it’s just a little more work.

Careful what you say there, that’s a bit like waving a red flag at a bull :laughing:

3 Likes

A time without a date is not really a valid date/time, so that may be why it’s being recognized as a string. Glide is really good at taking partial dates or even words, and treating them to a valid date, but it’s been a long time since I’ve done any sort of logic in a google sheet, so I’m not even sure how it stores partial date data there. Like @Darren_Murphy said, make sure your choice value is set as a date column in glide date editor, as well as the destination column. Also make sure the sheet columns are formatted correctly as well.

I guess what I would try is to set up your choices with two columns. One for the display value of time and one for the underlying value of an actual valid date/time. It could be a date equal to today, or some set date in the past. So your choice will just show times, but you will be writing a full date/timestamp to the sheet. I don’t know if that will help, but it’s something to try.

Another thought it’s to just use an arrayformula in another column to either strip off the single quote, or convert the value to a date value. Going full on script seems overkill for something like that.

3 Likes

Thanks, but everything you suggest has been tried. And sorry, but your comment about time not being a real value is not consistent with the way Google Sheets work. They mathematically manipulate time only and have a format for that which does not require a date included. The problem is the glide / googlesheet interface actually adds a single quote and alters the cell format when updated if you select Time Only. If I remove the single quote with a formula in Google Sheets it is still seen as text and hence any numeric manipulation returns a FALSE even when TRUE. It is only when the quote is removed and cell format changed back to Time the formulae work.

I have reported the issue as a bug but alas the tech guys have just taken the customer service approach of “Send us a video”. Excuse me. You cant take a video of something happening in background guys.

I have already had someone write a script to fix the data each time it updates.