Set raw values in Google Sheets through App Script

Question

The behaviour of Google Sheets App Script Range.setValues function seems to be tied to the number format of the cell to be modified:

If you set Format->Number->Automatic, it will try to parse numeric strings as numbers.
If you set Format->Number->Plain text, it will coerce boolean false to string "false".

What I'd much enjoy is a sensible behaviour similar to Google Sheets API v4, where you can force it to interpret values as raw.

What I am looking for is unambiguous behaviour, where javascript

string "" stays string.
number 1.23 stays number.
boolean true stays boolean.
Date Date(...) stays date.

Does anyone know whether something like that is possible?

If you need raw entry à la Sheets API, then use the Sheets API? It is available in Apps Script. — tehhowch
Could you elaborate little bit more? Is it possible to use Sheets API directly from script associated with that Sheet without a need to create service worker, sending requests through REST, etc...? — Samuel Hapak
Thank you! It sounds like the right solution @tehhowch. I will happily accept it as a right answer if you write it down. — Samuel Hapak

Samuel Hapak Samuel Hapak · Accepted Answer · 2019-03-23T20:25:18

There is a workaround for when you have Format->Number->Automatic.

function sanitizeData(data) {
  return data.map(function(row) {
    return row.map(function(value) {
      return typeof value === 'string' ? "'" + value : value;
    });
  });
}

This will fail if the Number Format is explicitly set to Plain Text. However, you can clear the formatting using the clear(options) method.

Set raw values in Google Sheets through App Script

1 Answers