I have an Autocrat job set up in Google Sheets using info from Form, which I later process in the same answer sheet. Part of that processing includes a VLOOKUP search which looks for dates in a second sheet.
The problem is that since the new form entries are inputted as a completely new row, with default settings, the dates are produced as numbers, instead of in the format I have set in the column (DD/MM/YYYY in one case and a long date in another (ie. Monday, 13th January 2025).
Is there a formula to set this in the entire column (for example, using a formula with arrayformula?).
Thanks in advance for any input.
I tried processing the data in a separate sheet and calling back to it with importsheet, but processing was too slow for Autocrat and the job generated the document faster than it took for the Sheets processing to be done, resulting in incomplete documents with missing tags.
Form responses are always inserted in newly created rows that cannot be formatted in advance.
The usual workaround is to Insert > Sheet and use a formula in cell A1 to copy all responses, like this:
={ 'Form Responses 1'!A1:Z }
The new sheet can be formatted normally, and the formats will stick as further form responses come in.
The question indicates that form responses are further processed with AutoCrat. In the event you are running that add-on on a "when a form response is submitted" trigger, and must therefore retrieve the data to AutoCrat directly from the form responses sheet, you may want to add a helper column that contains the dates as text strings in the format you need. Put this formula in row 1 of a free column in the form responses sheet:
=vstack("dates_as_text"; arrayformula(text(D2:D; "yyyy-MM-dd")))
The question also mentions the use of vlookup(). As Harun24hr pointed out, that function operates with dateserials rather than formatted dates, so the existing numeric dates should be fine for use as search keys, provided that the lookup table also includes numeric dates rather than text strings that just look like dates. You can tell the difference by testing the values with isnumber().