Trim without turning some numerical values into date values Google script

Question

I have

var rangeValues = sheet.getDataRange().getValues();
// iterate through all cells in the selected range
for (var cellRow = 0; cellRow < maxHeight; cellRow++) {
 for (var cellColumn = 0; cellColumn < maxWidth; cellColumn++) {  
    rangeValues[cellRow][cellColumn] = rangeValues[cellRow][cellColumn].toString().trim();
 }
}

and it turns some numerical values into date values

Ex:

8055-1 >> Fri Jan 01 8055 00:00:00 GMT-0600 (CST)

I think the issue is toString() not trim()

Is there a way to trim to avoid this problem?

Thanks

Answer 1

Try the following code:

function trimCells(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var rangeValues = sheet.getDataRange().setNumberFormat("@").getValues();
  // iterate through all cells in the selected range
  for (var cellRow = 0; cellRow < rangeValues.length; cellRow++) {
    for (var cellColumn = 0; cellColumn < rangeValues[0].length; cellColumn++) {  
      Logger.log("Before: " + rangeValues[cellRow][cellColumn])
      rangeValues[cellRow][cellColumn] = rangeValues[cellRow][cellColumn];
      Logger.log("After: " + rangeValues[cellRow][cellColumn])
    }
  }
}

Adding the setNumberFormat("@") to the range will make the range you selected into plain text. When you added the values to the sheet before, they were getting formatted as dates automatically because it matched the pattern. with this change you make it so that the format of the range is set as plain text.

Also, I changed maxHeight and maxWidth based on the length of rangeValues I'm assuming that's also how you got them. This code will set the values in the range to plain text and retrieve them, you won't even need toString() or trim() (unless you actually want the latter).