Google sheet script, times out. Need a new way or

问题:

noob here, so the script works great if there are less than 800 rows on a sheet, however this time I have a sheet of almost 1500 rows and the script times out.

Basically it is a quick way to get a quote. (quick here means 5-6mins, not an issue) It hides columns with calculations, hides columns with sensitive information and rows where there was no value in column H.

What I want to know is, if I can do the same with a different code, or if someone knows how to make getRange().getValue(); start at the bottom of the sheet, then I could have two scripts starting one after the other to finish the sheet and produce a printable quote.

Any help is greatly appreciated.

Many Thanks

here is the script:

function Quote()
{ 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Quote"); `


  var datarange = s.hideColumns(6);
  var datarange = s.hideColumns(9);
  var datarange = s.hideColumns(10);
  var datarange = s.hideColumns(12);
  var datarange = s.hideColumns(13);
  var datarange = s.hideColumns(14);

  var lastRow = s.getLastRow();
  for( i=1 ; i<=lastRow ; i++) {

       var status = s.getRange("H"+i).getValue();
       if (status == "") { 
         s.hideRows(i);
      }
  }
}

回答1:

Your problem is row:

s.getRange("H"+i).getValue()

This code takes data from spreadsheet, this is very slow process when you use it inside loop. You may use this conctruction:

  var data = s.getDataRange().getValues();

  for (var i=0; i < data.length; i++) {
      var status = data[i][7]; // takes column H          
      // other code goes here...          
  }

This way you read data from the spreadsheet only once using getDataRange(), and convert it into array with getValues(). Then loop should work way more faster.

When hiding rows, remember to add 1 because array starts from 0, heres code for hiding rows inside loop:

   if (status == "") { 
     s.hideRows(i + 1); // adding 1
  }