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

Question

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

After

Tanaike · Accepted Answer · 2019-10-05 00:37:05Z

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.

@AlejandroRod Thank you for replying. If your issue was still not resolved, please tell me. I would like to modify it.

MartinZPetrov · Accepted Answer · 2019-10-04 17:11:07Z

0

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.

answered Oct 4, 2019 at 17:11

MartinZPetrov

3165 silver badges20 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Pattern 1:

Modified script

Pattern 2:

Modified script

References:

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Pattern 1:

Modified script

Pattern 2:

Modified script

References:

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related