Google Apps Script creating spreadsheet with unintentional E notations

Question

I'm using Google Apps Script to open a Spreadsheet and copy it to another Sheet. The data being copied contains a column called 'case file date' which is some text like '11E1234567' . When I copy the data to a new sheet it appears as '∞' (the infinity symbol) I'm assuming that GAS is interpreting my 'case number' as some sort of E notation scientific formula. I just want the number to copy over to the new sheet intact. I've tried manually formatting the original as Plain Text and many other types but no format seems to stop the infinity symbols from appearing.

The code I'm using to copy:

var SS_KEY = '_mykey_';
var firstDataRow = 3;

function createSheets() {
  var ss = SpreadsheetApp.openById(SS_KEY);
  var sheet = ss.getSheets()[0];  
  var numColumns = sheet.getLastColumn();
  var numRows = sheet.getLastRow();

  var newSS = SpreadsheetApp.create((ss.getName()+'_ Copied'), rangeLengthInRows, numColumns);
  var newSheet = newSS.getSheets()[0];

  var range = sheet.getRange(1000+firstDataRow, 1, rangeLengthInRows, numColumns);
  var headerRange = sheet.getRange(1, 1, 2, numColumns);

  newSheet.getRange(1, 1,  2, numColumns).setValues(headerRange.getValues());
  newSheet.getRange(firstDataRow, 1,  rangeLengthInRows, numColumns).setValues(range.getValues());
  SpreadsheetApp.flush();

}

So the cell with value '11E1100001' becomes '∞' in the new sheet. I just want the original value to copy over.

Thanks, Marc

AdamL AdamL · Accepted Answer · 2013-05-25T04:28:08

This is a result of the Sheets application coercing the string into a numerical value. The same will be observed if you type 11E1100001 directly into a cell.

The GAS solution is the same as the solution for directly entering the string; precede the string with a single quotation mark. As you are setting the values as a batch, you would need to iterate over the array. Perhaps you could use something like:

function plainText(array, columns) {
  //array is a 2-D array to be converted to plain text
  //columns is an array of zero-based column indices to convert, all other indices will be unchanged
  //if columns not specified, all columns will be converted
  for (var i = 0; i < array.length; i++) {
    for (var j = 0; j < array[0].length; j++) {
      if (columns == undefined || columns.indexOf(j) > -1) {
        array[i][j] = "'" + array[i][j];
      }
    }
  }
  return array;
}

and then in your main code you would use, for example:

newSheet.getRange(firstDataRow, 1, rangeLengthInRows, numColumns).setValues(plainText(range.getValues(), [1, 3]));

assuming you want only the second and fourth columns in the range to be set as plain text.

Google Apps Script creating spreadsheet with unintentional E notations

1 Answers