I'm writing a Google Apps For Google Sheets. I want to read cell contents as a string and then parse it. In a cell I have a ma delimited list of IDs (e.g. "13,57,29"). Then I wrote a code that turns this into an array of strings (so "13,57,29" bees ["13", "57", "29"]). So far I used this code:

var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();  
var blockedListString = data[0][0];
var blockedArray = blockedListString.split(",");

This works fine for the example I gave above. But I'm running into problem when the cell has only "13" in it. I get an error saying:

TypeError: cannot find function split in object 13.

So basically it seems like it stops being treated as a string and bees something else (int?). The getValues() function returns an Object[][] so there's probably some indirect cast going on.

How can I fix that / force the cell to be read as string / force something to be cast to a string?

I'm writing a Google Apps For Google Sheets. I want to read cell contents as a string and then parse it. In a cell I have a ma delimited list of IDs (e.g. "13,57,29"). Then I wrote a code that turns this into an array of strings (so "13,57,29" bees ["13", "57", "29"]). So far I used this code:

var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();  
var blockedListString = data[0][0];
var blockedArray = blockedListString.split(",");

This works fine for the example I gave above. But I'm running into problem when the cell has only "13" in it. I get an error saying:

TypeError: cannot find function split in object 13.

So basically it seems like it stops being treated as a string and bees something else (int?). The getValues() function returns an Object[][] so there's probably some indirect cast going on.

How can I fix that / force the cell to be read as string / force something to be cast to a string?

• javascript
• google-sheets
• google-apps-script

• 8 .toString().split(",") Alternately, use Range#getDisplayValues() instead of .getValues() – tehhowch Commented Jan 12, 2019 at 17:20
• .toString() worked like a charm - thank you very much! – ilozen Commented Jan 20, 2019 at 16:37
• @tehhowch: You could expand your ment to an answer ... – user1251007 Commented Jun 11, 2021 at 21:07

2 Answers 2

as per accepted ment

var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getDisplayValues();
var blockedListString = data[0][0].toString();
var blockedArray = blockedListString.split(",");

This could be solved by doing a slight change.

From

var data = sheet.getDataRange().getValues();

To

var data = sheet.getDataRange().getDisplayValues();

The problem occurs when the cell has only 13 because SpreadsheetApp.Range.getValues() returns a number for that cell instead of a string as occurs when the cell includes values separated as mas.

One option is to use SpreadsheetApp.Range.getDisplayValues() as this method will return the displayed value of each cell as a string.

Other options

From

var blockedListString = data[0][0];

To

Any of the following:

• var blockedListString = data[0][0] + '';

• var blockedListString = `${data[0][0]}`;

• var blockedListString = data[0][0].toString();

• var blockedListString = String(data[0][0]);

• var blockedListString = JSON.stringify(data[0][0]);

Related

• How do I get the Formatted Value of a cell using Google Apps Script
• Data does not display as in google sheets format