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?
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.
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.
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
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.
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.
When I add a new entry using the Add button and form with the Data Picker, it sends a recognized Date. When I modify the date or enter a new date into the date column of a Data Grid it adds a apostropheā¦
So Iām getting '1/1/2023 in my sheet. This makes it largely unusable.
As for the comments above about using a Script with an onChange trigger, Iāve been playing with that but the sheetās range seems to be unavailable to the script when the sheet is modified by the onChange.
I have the data column set to be a Date only, with the Short option selected. One thing I see though is that the data column still has the little A icon as if itās text entry. It will only accept a date, but when it comes over to the sheet, itās just a string.
Ah, got it. It is a date type column, but the grid makes it appears as a text column. Not sure if thatās a bug, or because one of the dates is actually a text string.
I guess in my case, I donāt do anything special in the google sheet. The data coming in from Glide is fine with me as long as glide is interpreting it correctly on itās end. So in my case, even if a date came in as text, itās fine as long as glide still treats it as a date.
I would be more curious to know what you do in the google sheet that requires it to be recognized as a date instead of text. Maybe there is some logic that can be moved to glide instead.
As for the date column, it is ridiculously flexible at interpreting dates. You can type something arbitrary, such as ā3 days from nowā and it will convert it to a date. That text is obviously a text string, but glide is able to interpret a date from that. Manually typing a date is just typing text, so itās sent to the google sheet as text. More on the in the thread below.
If you can explain the main problem you are having, I may have a solution. You mentioned something about sorting. Is the data not sorting correctly if a user manually enters a date through the grid? I havenāt done much yet with the grid, especially with dates and allowing users to edit dates, so Iām learning too.
Yeah unfortunately I have a ton of logic on the Google Sheet. So like the table referenced above applies specific pricing to deals sold between those dates or finds the active price (no Inactive Date). We have dates all over the sheet that do various things ( think service and install company functionality with invoice dates, commission dates, install dates, etc ).
It all works fine with this one exception. I can use other collection types to open the item editor, but thatās a clunky option when the datasets are large.
Ideally Glide would provide a date picker for situations like that. Maybe someday.
About the only suggestion I can provide is to add a column in your sheet with an arrayformula that contains the following formula. This should convert text based dates to a real date with some reasonable flexibility if the users use different formats when typing the dates. So youāll have the original date column that is editable by the user, but youāll also have the arrayformula column that can be used in you google sheet as a true date column.
Not a bad idea. The issue you run into with that is have a formula prepulated in the row. It makes it so that new rows cant be added to rows that have formulas. I ran into that with another sheet problem and am using an onChange event script to add the formula when a new row is added. But thereās other issues with that.
Iāll keep looking for the work around.
Also if there was a way to open the popup editor for the row that would be super helpful. Every other collection type gives you this option. I posted about this elsewhere, but you can add an edit button to a datagrid but it will only open the first time in the table. Just lots of close but no cigar.
I was today years old when Jeff Hager and Darren Murphy gave me a gift of arrayformula. Now I can delete the onchange script iāve been fighting to add formulas to rows on row creation. This solves 1,000 other issues I was having.
Not the cleanest solution but it absolutely helps.
I am seeing that with the array formula in place, it still wants to add the row to the very bottom of the sheet, so Iām just going through and deleting the sheets rows down to data length. Thank you gentlemen.
