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

Have you checked the cell's formatting? Is it set to any sort of "date" format? — ross, Jul 24 '19 at 15:11
What's `maxHeight` ? What's `maxWidth`? If we can't run your code to verify the problem then it's difficult for us to provide assistance. Welcome to StackOverFlow please take this opportunity to take the [tour] and learn how to [ask] and [mcve]. — Cooper, Jul 24 '19 at 17:18
Although I'm not sure whether this is the direct solution, if the value of a cell is changed from `8055-1` to `Fri Jan 01 8055 00:00:00 GMT-0600 (CST)` when the value is retrieved by `getValues()`, I think that the reason of your issue is that `8055-1` is used as the date object. In that case, for example, how about using `getDisplayValues()` instead of `getValues()`? But I'm not sure about the detail of your situation. If this was not the result you want, can you provide the sample Spreadsheet? By this, I would like to think of the issue. — Tanaike, Jul 24 '19 at 22:35
For anyone coming to this in need of help, here is a helpful post on this `https://stackoverflow.com/questions/34691425/difference-between-getvalue-and-getdisplayvalue-on-google-app-script` — xyz, Jul 25 '19 at 18:34

score 1 · Accepted Answer · answered Jul 25 '19 at 09:17

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).

Alberto, thank you. this is a fine approach, it workehed as hoped — xyz, Jul 25 '19 at 18:26

Trim without turning some numerical values into date values Google script

1 Answers1