CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
AlejandroRod
AlejandroRod

Reputation: 83

How to keep the date from turning into a String when using a Google Apps Script in Google Sheets?

I have used a Script posted here in a large Google Spreadsheet, to replace a String with a Number.

It works great, but the problem is, the date gets turned into a String.

I believe that the problem is the method toString(), but I couldn't find any alternative to make the code work.

=> Does anyone have an idea to avoid this problem?

Script

function runReplaceInSheet(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3");
  //  get the current data range values as an array
  //  Fewer calls to access the sheet -> lower overhead 
  var values = sheet.getDataRange().getValues();  

  // Replace  
  replaceInSheet(values, 'datateam', '42007860');

  
  // Write all updated values to the sheet, at once
  sheet.getDataRange().setValues(values);
}

function replaceInSheet(values, to_replace, replace_with) {
  //loop over the rows in the array
  for(var row in values){
    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value) {
      return original_value.toString().replace(to_replace,replace_with);
    });

    //replace the original row values with the replaced values
    values[row] = replaced_values;
  }
}

Before

enter image description here

After

enter image description here

Upvotes: 0

Views: 187

Answers (2)

Tanaike
Tanaike

Reputation: 201723

  • You want to replace values of Spreadsheet.
    • In your script, you want to replace from datateam to 42007860.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Pattern 1:

In this pattern, getDisplayValues() is used instead of getValues().

Modified script

Please modify your script as follows. In this modification, the function of runReplaceInSheet() is modified.

function runReplaceInSheet(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3");
  //  get the current data range values as an array
  //  Fewer calls to access the sheet -> lower overhead 
  var values = sheet.getDataRange().getDisplayValues(); // Modified

  // Replace  
  replaceInSheet(values, 'datateam', '42007860');


  // Write all updated values to the sheet, at once
  sheet.getDataRange().setValues(values);
}

Pattern 2:

In this pattern, TextFinder is used instead of replaceInSheet() and setValues().

Modified script

Please modify your script as follows. In this modification, the function of runReplaceInSheet() is modified. And also, in this modification, replaceInSheet is not used.

function runReplaceInSheet(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3");
  sheet.createTextFinder('datateam').replaceAllWith('42007860'); // Added
}

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 2

MartinZPetrov
MartinZPetrov

Reputation: 316

I would check the template, your actual XLS file. Set the type of the cells before entering the data. I assume this might be your problem not your actual code. This is in the Home tab.

enter image description here

Upvotes: 0

Related Questions