Google Sheets – Convert ISO 8601 Strings to Dates

formulasgoogle sheetsgoogle-sheets-custom-functionimportjson

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.

Best Answer

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

Related Topic