Convert columns of ISO 8601 strings to dates in Google Sheets

Question

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:

2020-06-02T00:00:00Z
2020-06-02T03:00:00Z
2020-06-02T14:30:00Z

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.

Answer 1

The IMPORTJSON project include ImportJSONAdvanced. This function can't be used directly in a formula but could be used to build a custom function. The key part is the parameter transformFunc. It could be a JavaScript function that transform the ISO 8601 dates into JavaScript Date objects which could be transferred to the spreadsheet as dates.

This might work.

/**
 * Converts ISO 8601 dates in columns 2 and 4 into JavaScript Date objects
 */
function myTransformFunc_(data,row, column) {
  if(row > 0 && (column === 2 || column === 4)){
    data[row][column] = new Date(data[row][column]);
    data[row][column] = new Date(data[row][column]);
  } else {
    // do nothing;
  }
}

/**
 * @customfunction
 */
function myImportJSON(url, query, parseOptions) {
  return ImportJSONAdvanced(url, null, query, parseOptions, includeXPath_, myTransformFunc_);
}

---

From https://blog.fastfedora.com/projects/import-json#ImportJSONAdvanced

ImportJSONAdvanced

An advanced version of ImportJSON designed to be easily extended by a script. This version cannot be called from within a spreadsheet.

Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in the JSON feed. The remaining rows contain the data.

ImportJSONAdvanced takes 6 parameters:

url

The URL to a JSON feed.

fetchOptions

An Object whose properties are the options used to retrieve the JSON feed from the URL.

query

A comma-separated list of paths to import. Any path starting with one of these paths gets imported.

parseOptions

A comma-separated list of options that alter processing of the data.

includeFunc

A function with the signature func(query, path, options) that returns true if the data element at the given path should be included or false otherwise.

transformFunc

A function with the signature func(data, row, column, options) where data is a 2-dimensional array of the data and row & column are the current row and column being processed. Any return value is ignored. Note that row 0 contains the headers for the data, so test for row==0 to process headers only.

Related

• Fetching JSON in Google Sheets (ImportJSONAdvanced not defined?)

Answer 2

I believe you can simplify the whole procedure by slightly tweaking your already existing formulas and using helper columns.

Please follow these steps:

Step 1.
You can use either open or closed ranges. BUT.
Make sure all of the ranges have the same amount of rows.
If not, it will just be messy :)

Step 2.
Create your helper columns somewhere on the same or even another tab.
For ease of reference try starting them all on the same row as your data/input.
Use as headers the corresponding column letters to the input timestamps (C, E)

Step 3.
For the first Helper column M use your tweaked formula and drag it to the right to column N

=QUERY(ARRAYFORMULA(DATEVALUE(LEFT(QUERY($A$5:$E; "select "&M4&" "); 10)) + 
                    TIMEVALUE("klo " & MID(SUBSTITUTE(QUERY($A$5:$E; "select "&M4&" "); ":"; "."); 12; 8))); 
          "where Col1 is not null")

Step 4.
Use the following formula to get your results

=QUERY({A4:E4;A5:B\M5:M\D5:D\N5:N};"where Col1 is not null")

Step 5.
Format columns I and K as Date time from the menu.

Step 6.
Get yourself a glass of wine :)
...or not