Home Replace #N/A or #REF cells with blank in Google App Script, improve query execution

Questions

Replace #N/A or #REF cells with blank in Google App Script, improve query execution

April 14, 2022

I am trying to replace #N/A or #REF cells in a google sheet with a blank value.
I cannot use IFERROR as we are using the error values to conditional format the cells which might have them.

Steps taken:

Import range the original sheet into a new one
Using Google App Script copy the import range sheet into another
Loop through the new sheet to replace #N/A or #REF cells with blank

Issue: Execution takes little over 5 minutes to complete. (60 rows with 30 columns in actual google sheet). How can I speed up the execution?

Sample Sheet

function isError_(cell) {
  const errorValues = ["#N/A", "#REF"];
  for (var i = 0; i < errorValues.length; ++i)
    if (cell == errorValues[i])
      return true;
  return false;
}

function Sample() 
{
  var sheetName = "Sample"; // Please set the tab name you want to overwrite the data.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var final_table = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Final Sample");

  // ### PASTE VALUE TO ANOTHER SHEET
  sheet.getRange("A2:AP"+sheet.getLastRow()).copyTo(final_table.getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);

  var Avals = final_table.getRange("D2:D").getValues();
  var Alast = Avals.filter(String).length;

  var range = final_table.getDataRange();
  var vals = range.getValues();

  for (var row = 1; row <= Alast; ++row) 
  {
    for(var col = 0; col <= final_table.getLastColumn(); ++col)
    { 
      if (isError_(vals[row][col])) 
      {
        var data = "";
        SpreadsheetApp.getActive().getSheets()[1].getRange(row+1,col+1).setValue(data)
      }
    } 
  }

}

>Solution :

I thought that in your script, SpreadsheetApp.getActive().getSheets()[1].getRange(row+1,col+1).setValue(data) is used in a loop. I thought that this might be the main reason of your issue. In this case, the process cost will be high.

In order to reduce the process cost of the script, how about the following modification?

Modified script:

function Sample() {
  var sheetName = "Sample";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  var final_table = ss.getSheetByName("Final Sample");
  sheet.getRange("A2:AP" + sheet.getLastRow()).copyTo(final_table.getRange(1, 1), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  final_table.createTextFinder("#N\/A|#REF").useRegularExpression(true).matchEntireCell(true).replaceAllWith("");
}