Convert return values to numeric data types

[fastfedora]

Add an option to convert the return values from ImportJSON to numbers instead of strings. Currently all return values are strings.

See this comment for more info on the issue.

[athace]

Hi, am new to git but I paste here what I added into my gs. May need further testing, but it may help you. Keep in mind that parseInt and parseFloat methods return a number even if only the beginning of the given string was a number. (e.g. parseInt("1.2.1") returns 1, but should not be converted, since it's more of a version number)

Added to the transform function: if (hasOption_(options, "parseNumbers")) { var num = filterFloat(data[row][column]); if (!isNaN(num)) { data[row][column] = num; } }

New function: function filterFloat(value) { if(/^(-|+)?([0-9]+(.[0-9]+)?|Infinity)$/.test(value)) { return Number(value); } return NaN; }

[Artanis]

@athace Looks good to me. (edit: Google Script doesn't like the regex. :frowning:)

Though most of the time people are going to want numeric values to be numeric, so make parsing them default behavior, with an option noParseNumbers to disable it.

[adriens]

Hi, i'm currently facing this issue : my json numeric values are rendered as string instead of numbers. So, what option should i add the the function call please ? Same question for dates in YYYY-MM-DD format please ? That would be really great 🥇

[adriens]

I guess i have to deal with the

[adriens]

@param {parseOptions}

[adriens]

Hi, just to mention that since today, the patch does not work anymore. I had to come back to original non-patched code. I only get text cells now...but the spreadsheet is loading the right way.

[adriens]

By the way, is this project still maintained please ?

[gaia]

Just wrap the result in VALUE() https://support.google.com/docs/answer/3094220?hl=en

[pabloab]

@gaia Is a nice workaround, just separate the query for the numeric column, use VALUE using array forumulas (=ArrayFormula(VALUE(ImportJSON(... ,"/mynumbers",...).

[christophermh44]

Hi, am new to git but I paste here what I added into my gs. May need further testing, but it may help you. Keep in mind that parseInt and parseFloat methods return a number even if only the beginning of the given string was a number. (e.g. parseInt("1.2.1") returns 1, but should not be converted, since it's more of a version number)

Added to the transform function: if (hasOption_(options, "parseNumbers")) { var num = filterFloat(data[row][column]); if (!isNaN(num)) { data[row][column] = num; } }

New function: function filterFloat(value) { if(/^(-|+)?([0-9]+(.[0-9]+)?|Infinity)$/.test(value)) { return Number(value); } return NaN; }

There is an error in the Regex that makes the script crash. You have to escape the first "+" character. Here is the corrected version :

if (hasOption_(options, "parseNumbers")) {
  var num = filterFloat(data[row][column]);
  if (!isNaN(num)) {
    data[row][column] = num;
  }
}

function filterFloat(value) {
  if(/^(-|\+)?([0-9]+(.[0-9]+)?|Infinity)$/.test(value)) {
    return Number(value);
  }
  return NaN;
}

[krijnsent]

This issue stems from the "noTruncate" option in the "defaultTransform_" function. If you don't have the noTruncate option added, the value/boolean will be transformed into a string and sliced to max 256 characters. Why do that by default??? Something like this could solve it for numbers & boolean:

  if (!hasOption_(options, "noTruncate") && data[row][column]) {
    if ((typeof data[row][column]) === 'number' || (typeof data[row][column]) === 'boolean') {
      data[row][column] = data[row][column];
    } else {
      data[row][column] = data[row][column].toString().substr(0, 256);
    }
  }

[MarkJSheffield]

I get a lot of use out of this function, but still run into problems with numbers coming across as strings, not numbers. I've tried inserting the above suggestions into my script, but the data still comes across as a string. I even set up a conditional format on the cells to highlight the ones that were not numbers and it's easy to see that in the same column, 0's are treated as numbers, but any number is treated as a string.

Wondering if anyone has come up with a better solution to this problem?