GSheet Script: How to Optimize my Row and Sheet Iterator

Question

Long story short, I have this bit of Google Script that clears content automatically in a GSheet. It is set on a trigger and it works...the code does what it's supposed to do. The issue is that it runs slow. It takes 2 to 3 minutes for the iterator to run. To help you scope the size of the task: there is 150 rows on each of the 8 sheets.

The objective of the code is to clear a number of rows on each sheet based on the value of the cell in the first column of a row.

So I would like to know if anyone has any insight or suggestion to improve the running time. I understand my method of using a for loop checks rows one by one, and that's a time-consuming task. I couldn't think of an alternate method with arrays or something?

Thanks all!

Here's the code:

     function Reset_Button() {

      var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
     
       for (var i = 1; i < sheets.length ; i++ ) {
    
         var sheet = sheets[i];
         sheet.getRange("C2").setValue(new Date());
    
         var rangeData = sheet.getDataRange();
         var lastRow = rangeData.getLastRow();
         var searchRange = sheet.getRange(1,1, lastRow, 1);

            for ( j = 1 ; j < lastRow ; j++){

              var value = sheet.getRange(j,1).getValue()
                if(value === 0){
                sheet.getRange(j,2,1,5).clearContent()
  }}}}

Answer 1

Typically you want to do as few writes to the spreadsheet as possible. Currently your code goes through each line and edits it if necessary. Instead get the entire data range you will be working with into one variable (let's say dRange and use .getValues() to get a 2d array of all the values into a second variable (let's say dValues). Then simply iterate over dValues, setting a blank "" in each you want to clear. Once you are done going over all values, just do a dRange.setValues(dValues) (that's why I said to keep the range in a separate variable). So as an example, the following will clear columns B through F if column A has a 0

function test(){
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  for (var i = 1; i <sheets.length; i++) {
    sheets[i].getRange("C2").setValue(new Date());
    var dRange = sheets[i].getDataRange();
    var dValues = dRange.getValues();

    for (var j = 1; j < dRange.getLastRow(); j++){
      if (dValues[j][0] == 0) {
        for (var c = 1; c < 6; c++) {
          dValues[j][c] = ""
        }
      }
    }
    dRange.setValues(dValues);
  }
}

For a single sheet of ~170 rows this takes a few seconds. One thing to note is that I wrote it based on your script, you set a date value in C2 however in your sript (and thus in the one I wrote based on yours) that falls within the range you are checking to be cleared, so double check your ranges