CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Simone Avogadro
Simone Avogadro

Reputation: 819

How to avoid parsing of data with google app script range.setValues

I'm dealing with an annoying problem when importing data into Google Spreasheets using Google App Script Basically the code is as follows:

var csvData = Utilities.parseCsv(mySource,separator);
range.setValues(csvData);

usually this works perfectly but sometimes we have a productcode column which gets messed up

productcode value
01DECUB003 100
21MAR003 200

The second row gets messed up because the product code is interpreted as a Date and thus converted into 21mar2003 which in turn does not match any of the real product code and then raises errors in further export scritps.

Is there any way to fix this? I don't see any relevant option neither within Utilities nor within Range.

Is there any alternative APIs for doing the same?

Here's an example which reproduces the issue

function main() {
  var csvContent= 'code;value\n01mmabc001;111,1\n21mar003;222,2';
  var separator= ';';
  var csvData = Utilities.parseCsv(csvContent,separator);
  var sheet = SpreadsheetApp.getActiveSheet();
  var range;
  sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("IMPORT");
  range = sheet.getRange(1, 1, csvData.length, csvData[0].length);
  range.setValues(csvData);
}

Upvotes: 0

Views: 65

Answers (1)

Kessy
Kessy

Reputation: 2014

I have been able to do it by changing this:

range = sheet.getRange(1, 1, csvData.length, csvData[0].length);

to this:

range = sheet.getRange(1, 1, csvData.length, csvData[0].length).setNumberFormat('@');

What this does is while getting the range it sets the format of the cells to @:

Inserts the raw text for the cell, if the cell has text input. Not compatible with any of the other special characters and won’t display for numeric values (which are displayed as general format).

Reference:

setNumberFormat(numberFormat)

Number format tokens

Upvotes: 1

Related Questions