2

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.

  • Welcome to Web Applications. Please add more details about your search/research efforts as is suggested in How to Ask as well a sample of the data returned by IMPORTJSON (could be fake data but the sample should include the same number of columns and the same data type for each of them. – Rubén May 31 at 16:06
2

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

| improve this answer | |
  • This is a nice solution, as I don't need to mess with queries and helper tables. I ended up adding a handler for parseDates option that applies a date parser to the data. – Julsteri Jun 3 at 13:55
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

Convert ISO-8601 Finnish timestamp to Google Sheets Date time

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.