How to increase speed of my script containing loops in Google Sheets?

Question

I have an issue with a slow speed of my Google Apps Script containing a loop. I'm looking for recommendations how to increase the speed.

I have the following input data on a sheet named "Test" (just an extract for demonstration purposes but have in mind that the original data is much larger - with much more products and suppliers):

I want to achieve on a separate sheet named "Suppliers" the following result:

The idea is the suppliers with their respective products and prices to be reorganized in the format shown above. The conditions are:

Exclude inactive products;
Exclude inactive suppliers;
include only products per supplier with an available price;
Dynamic update of the data when adding new products/ suppliers, or changing anything on the input table.

I have done the following script - it's working, but speed is quite slow considering the fact that original data is large. I would highly appreciate any advises how to optimize it. I spent much time trying to optimize it by myself but to no avail. Many thanks in advance!

function Suppliers() {
    var file                 = SpreadsheetApp.getActiveSpreadsheet();
    var sheetSourceName      = 'Test';
    var sheetDestinationName = 'Suppliers';
    var sourceSheet          = file.getSheetByName(sheetSourceName);
    var numRows              = sourceSheet.getLastRow();
    var numCols              = sourceSheet.getLastColumn();
    var destinationSheet     = file.getSheetByName(sheetDestinationName);  
    var sheet                = file.getActiveSheet();

    if( sheet.getSheetName() == sheetSourceName ) {

        var lastRow = destinationSheet.getLastRow();  

        destinationSheet.getRange( 2, 1, lastRow, 3 ).clear( { contentsOnly : true } );    

        var lastRow = 1;

        for( var s = 3; s < numCols + 3; s++ ){
            for( var p = 3; p < numRows + 3; p++ ){

                var product        = sourceSheet.getRange( p, 2 ).getValues();
                var activeProduct  = sourceSheet.getRange( p, 1 ).getValues();
                var price          = sourceSheet.getRange( p, s ).getValues();
                var supplier       = sourceSheet.getRange( 2, s ).getValues();
                var activeSupplier = sourceSheet.getRange( 1, s ).getValues();

                if( activeProduct == "active" && price > 0 && activeSupplier == "active" ){
                    lastRow = lastRow + 1;

                    destinationSheet.getRange( lastRow, 1 ).setValues( product  );
                    destinationSheet.getRange( lastRow, 2 ).setValues( price    );
                    destinationSheet.getRange( lastRow, 3 ).setValues( supplier );

                }
            }
        }
    }
}

Possible duplicate of [Google app script timeout ~ 5 minutes?](https://stackoverflow.com/questions/14450819/google-app-script-timeout-5-minutes) — TheMaster, Mar 01 '19 at 09:29
Double-nested `getRange(..).getValue()` is the biggest issue. — tehhowch, Mar 01 '19 at 12:53
Thanks! Any advice on how I can avoid the double-nested getRange(..).getValue() in the script? — Delyana Boyadzhieva, Mar 01 '19 at 13:17
Yes, read & learn. Linked above is a very useful post. A simple internet search for "apps script best practices" will yield official documentation with examples. — tehhowch, Mar 01 '19 at 15:24

score 1 · Accepted Answer · answered Mar 01 '19 at 14:55

No guarantee because I don't have a data sheet set up to match your situation but I think this will work for you. Try it in a copy of your spreadsheet. Note that in your getRange you use column and row which start with 1. I'm loading everything into an array a looping through its values where the index starts with 0. So it becomes your row/col -1.

function Suppliers() {
  var file                 = SpreadsheetApp.getActiveSpreadsheet();
  var sheetSourceName      = 'Test';
  var sheetDestinationName = 'Suppliers';
  var sourceSheet          = file.getSheetByName(sheetSourceName);
  var numRows              = sourceSheet.getLastRow();
  var numCols              = sourceSheet.getLastColumn();
  var destinationSheet     = file.getSheetByName(sheetDestinationName);  
  var sheet                = file.getActiveSheet();

  if( sheet.getSheetName() == sheetSourceName ) {
    destinationSheet.getRange( 2, 1, lastRow, 3 ).clear( { contentsOnly : true } );    
    var sourceData = sourceSheet.getRange(1,1,sourceSheet.getLastRow(),sourceSheet.getLastColumn()).getValues();
    var destinationData = [];
    // Not sure why +3 added to numRows and numCols because there is no data there
    for( var s=2; s<numCols; s++ ) {
      for( var p=2; p<numRows; p++ ) {
        var product        = sourceData[p][1];
        var activeProduct  = sourceData[p][0];
        var price          = sourceData[p][s];
        var supplier       = sourceData[1][s];
        var activeSupplier = sourceData[0][s];      
        if( activeProduct == "active" && price > 0 && activeSupplier == "active" ){
          destinationData.push([product,price,supplier]);
        }
      }
    }
    destinationSheet.getRange(2,1,destinationData.length,3).setValues(destinationData);
  }
}

Thank you so much, it works perfectly! You helped me a lot. The only thing which I added is variable declaration of 'lastRow' before clearing the destinationSheet (as it's used there). — Delyana Boyadzhieva, Mar 02 '19 at 01:33

How to increase speed of my script containing loops in Google Sheets?

1 Answers1