Replace text in Google Sheet without reformatting values using Google App Script

Question

I have a problem i need some help with.

I am using a function (which i found on elsewhere this site) to loop through all my spreadsheet rows to replace the word "undefined" with a blank space ( " " ).

The code works fine, but it seems to also reformat the dates from mm/dd/yyyy format to a text string, due to a .toString method in the code.

I do not want the date (or numbers) reformatted, but I haven't found a way to rewrite my code without the .toString method.

Would anyone know of a way i can replace text in Google Sheet without having to format it to a string first?

Here is the code below. I appreciate any help on this.

function replaceInSheet(sheet, to_replace, replace_with) {

  var Data = SpreadsheetApp.getActiveSpreadsheet(); // DATA spreadsheet
  //get the current data range values as an array
  var values = Data.getDataRange().getValues();

  //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("undefined","");
    });

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

  //write the updated values to the sheet
  Data.getDataRange().setValues(values);
}

thanks for the reply. I had already found the answer to my question in the thread below. I thought i has responded to it, but it got deleted somehow. In any case, thanks for responding. — Neo, Jan 26 '20 at 23:23
@Kos just to add, this is another way to address my problem, so it is helpful to know. Thanks for the suggestion! — Neo, Jan 27 '20 at 00:59

score 2 · Accepted Answer · answered Jan 24 '20 at 23:50

You want to replace the string of "undefined" to "" for the cells of Spreadsheet using Google Apps Script.
You don't want to affect the cells which have no value of "undefined".

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

For example, as one of several workarounds, how about using TextFinder? When TextFinder is used, the cells which have no value of "undefined" are not affected. In this case, please modify your script as follows.

Modified script:

function replaceInSheet(sheet, to_replace, replace_with) {
  var Data = SpreadsheetApp.getActiveSpreadsheet();
  Data.getDataRange().createTextFinder("undefined").replaceAllWith("");
}

References:

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

Yes, this answers my question! I used it in my code and now I'm all set. Sorry for the delay, as I thought i already commented earlier. — Neo, Jan 26 '20 at 23:20
@Neo Thank you for replying. I'm glad your issue was resolved. Thank you, too. — Tanaike, Jan 26 '20 at 23:22

Replace text in Google Sheet without reformatting values using Google App Script

1 Answers1

Modified script:

References: