Inserting string to sheet results in number inserted

Question

In my Google Apps Script I an appending a row to my spreadsheet. In this row being appended, I am trying to insert a string of value '0102', however when inserted it converts to the number 102. Is there any way to insert a value into sheets with Google Apps Script that will not format these types of numbers into integers but leave them as strings?

Answer 1

You need to format the cell as plain text before setting its value. From this answer, the (seemingly undocumented) trick is setNumberFormat('@STRING@'). For example,

sheet.getRange("A1").setNumberFormat('@STRING@').setValue('0102');

formats cell A1 as plain text, and sets the value '0102', which will remain a string.

Unfortunately, appendRow, being an atomic operation of adding a row and inserting values, does not leave room for formatting. So you'll need something more verbose, for example

sheet.insertRowAfter(sheet.getLastRow());
var range = sheet.getRange(sheet.getLastRow() + 1, 1, 1, 3);
range.setNumberFormats([['', '', '@STRING@']]).setValues([['Boston', 'MA', '02201']]);  

This code adds a row and fills the first three cells in it with Boston MA 02201, keeping the ZIP code 02201 as a string.

Answer 2

Thought I would add in my own solution that I had with this same issue.

The fix that I found is that if you add a ' character in front the value that you're trying to insert it will force it to remain as a string.

sheet.appendRow(["Boston", "MA", "'02201"])