This is what glide stores in a google sheet.
When I look at the cell it is:
I am trying to do a vlookup on 5/1/2023 so I need to get from ‘Convert Date’ (the Glide/Sheets date format stored in sheets) to CVT-Date2 and I can not find any formatting/transformation that works simply. The monstrous way I got it to work was transforming the Glide date into text then pulling each compenent out (M-D-Y) then formatting them appropriately and concatenating them back together and finally using datevalue().
Here is the final result which I am trying to get to without using 7 columns in sheets:
I then use vlookup in this table:
Why do you need to do a VLOOKUP in your Google Sheet?
The only reason I could imagine for doing that would be if you’re using the data outside of Glide. Otherwise, just use relations and lookups.
Anyway, assuming that you have a good reason, my suggestion would be to convert all your dates to numbers in YYYYDDMM format. Then you’ll find them a lot easier to work with. You can do that with a single math column:
Year(Date) * 10000
+ Month(Date) * 100
+ Day(Date)
Of course, because that’s a computed column, you’ll need an action to write it to a basic column so that it shows up in your Google Sheet.
1 Like
The rather poor reason I am doing this is because lookup/compute columns do not work as the source of a query. I can do the same lookup in sheets and it does work in the query since the source is now a sheets field and not a lookup/compute glide column. But getting the date to lookup properly has been a challenge. I found a way to do it but it is ugly.
It is stop-gap until query works with compute/lookup columns.
But working with glide dates in sheets has been…challenging.
Okay. I never really figured out what causes dates coming from Glide to show up as ISO formatted strings. I used to get hung up about it - even to the point of writing scripts to try and convert them back to dates. Now, I just don’t bother, I have sheets that are littered with date/time columns that look like so:
But I don’t care anymore, because I don’t actually do anything with the data in the Google Sheet. It’s just a repository.
I know I’m not answering your question, but my advice would be don’t bother. It’s a really deep rabbit hole that’s best avoided. Just do whatever you need to do in Glide 
1 Like
I am a few hours into this rabbit hole and it is a stop-gap. I will use my kludgy method - it works and hopefully temporary. Thanks for confirming this is something to not do in the future.
Last note on this topic. I do have to fill the sheet with the formula. I found this apps script formula - si this a ‘good practice’?
function fillDown() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Example 7”);
ss.getRange(“B2”).setFormula(“=A2*0.05”);
var lastRow = ss.getLastRow();
var fillDownFormulaRange = ss.getRange(2, 2, lastRow-1);
ss.getRange(“B2”). copyTo(fillDownFormulaRange);
}
The problem with that script is that you’ll need to execute it every time a new row is added to your sheet. You’d be better off with an ARRAYFORMULA.
=ARRAYFORMULA(A2:A*0.05) would do the same job as that script, and will automagically be applied to new rows.
Make sure you remove all empty rows from the bottom of the sheet.
Exiting the rabbit hole. Going with your other suggestion and instead of using the lookup for the query I am doing the lookup in the ‘create’ form and writing that to a new google field. Then query on that.
Much easier on my brain.
Not super related, but I use BYROW + LAMBDA a lot these days. It works with functions that don’t work with ARRAYFORMULA.
2 Likes
