Ciber Geek > General > Google Sheets IMPORTDATA, parsing dates and money

Google Sheets IMPORTDATA, parsing dates and money

Google Sheets are probably the most popular no-code tool on the whole solar system. The poor man’s Zapier. If you are here probably you are scratching your head trying to stop GSheets from parsing something.

When using Sheets as a no-code tool the IMPORTDATA function is one of the ways available to get data from external sources, it accepts data in CSV or TSV format, with the former being one of the most popular ways of exporting data on the web.

IMPORTDATA’s locale parameter is really important

The function’s docs are -to put it lightly- lacking, you can check it here, for the sake of history I’m gonna quote the syntax of the function down here.

Syntax
IMPORTDATA(url)

url – The url from which to fetch the .csv or .tsv-formatted data, including protocol (e.g. http://).

The value for url must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.

IMPORTDATA actually has 3 parameters:

  • URL: The URL from which to fetch the .csv or .tsv-formatted data, including protocol
  • Separator: It must be a single character and may not be a double quote or whitespace character other than a tab. If unspecified, the delimiter is inferred from the file name: comma for CSV, tab for TSV.
  • Locale: A language and region locale code to use when parsing the data. If unspecified, the document locale will be used.

The “Locale” is key, it’s what the parsing engine used by GSheets uses to “understand” dates and numbers, and in most cases is what causes strange results, that mostly end with some field showing an integer that it’s not what the original file had.

The “Locale” is also present on the sheet itself, under “File” > “Settings”. Sometimes changing the “Locale” there fixes the issue, but it doesn’t always work.

If the source’s locale is not known there a several sites with a list of locales that show how numbers and date are formatted, Oracle has one that is really simple.

If it looks like a date, it must be a date.

GSheets parser
GSheets import tool

If you can use the Import functionality, use it. It has the option to NOT parse numbers, dates and formulas (see the checkbox) and it’s the easiest way to solve these kind of problems. Unfortunately sometimes using IMPORTDATA is the only option to get the data needed, because one of the limitations of the Import function is that it cannot import data from an URL.

What’s the worst that can happen?

The worst is basically having the wrong values because of the parser trying to interpret something in the wrong way.

One of the files I had to work with had a field that was some text which had 3 numbers separated by commas. That’s gotta be a date! – yelled the parser – but it wasn’t and it ended up trying to interpret the last number as a year, so it converted a “2” to “2002”.

The only way to solve that problem without writing a custom App Script was to change the software that generated the CSV and use a separator that wouldn’t confuse the parser.

Some of the error can be solved from the Sheet, changing the “Format” of the cell.

Thing to try to solve the problem

  • Change the document “Locale” going to “File” > “Settings”
  • Change the column/cell from the “Format” menu
  • Change the “Locale” parameter used by IMPORTDATA

If none of those options help, you could try using Google App Script to fetch the data.