I’m pulling data to my sheet from an API using an ImportJSON function. The data array contains mixed data, including several columns of ISO 8601 date strings in UTC. For example:
Google Sheets doesn’t apparently recognize these as dates, or at least applying a date format to these columns doesn’t do anything. Can I wrap a formula around the ImportJSON function that would parse certain columns to format the date and time properly?
A truncated example of what the data array might look like:
id | data1 | time1 | data2 | time2
23 | "abc" | "2020-06-02T00:00:00Z" | "def" | "2020-06-02T02:00:00Z"
24 | "efg" | "2020-06-02T03:00:00Z" | "ijk" | "2020-06-02T05:00:00Z"
25 | "lmn" | "2020-06-02T14:30:00Z" | "opq" | "2020-06-02T16:30:00Z"
I probably could query each time column separately. This means I should write several formulas, for example
=QUERY(data; "select A, B")
for each consecutive column group without formattable data, and
=ARRAYFORMULA(IFERROR(DATEVALUE(LEFT(QUERY(data; "select C"); 10)) + TIMEVALUE("klo " & MID(SUBSTITUTE(QUERY(data; "select C"); ":"; "."); 12; 8)); ""))
for each formattable date column and so on. But this gets very complicated and is tedious to change later, and I was wondering if this could be done in a single formula. I could not find any advice to transform multiple columns with mixed datatypes.