Google-sheets – Can this script be more optimized and why is it running slow sometimes

google sheetsgoogle-apps-script

I just started using Google App Script and wrote this script (mostly copy and paste) to check the status of my website and its pages.

Sometimes, mostly after opening the spreadsheet, but even if it is open for a while, the script starts very slow, needing half a minute per row to check the status. If I cancel and wait for a bit, it runs normal, taking about 1-2 seconds (in few cases 5 seconds) per row.

I can't really pin down if it is a problem on the webserver side, a problem of inefficiency in the script or something else. I even tried to run the script on a sunday at midnight, when there shouldn't be much stress on the webserver but the script didn't finish because it was running over 6 minutes.

function UpdateAllStatus()
{
var aSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("URL   Status");

if (aSheet == null)
return;

var row = 4;
var url = aSheet.getRange(row, 2).getValue();

var options1 = {
contentType: "text/html ",
muteHttpExceptions: true
};

// I run the script in a loop until it hits the end of the row where I placed an "!" to show the script this is the end  
while (url != "!")
{

// There are gaps in the URL row, so the script skips to the next row
  if (url == "")
  {
  row++;
  url = aSheet.getRange(row, 2).getValue();
  }

//  if there is an URL in the row, the script gets the response code from the website
  else 
  {
  var response_code = UrlFetchApp.fetch(url, options1).getResponseCode().toString() ;
  var stext = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("URL Status").getRange(row, 1).getValue();
  var ttext = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Title").getRange(row, 1).getValue();
  var dtext = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Description").getRange(row, 1).getValue();

// if the response code is 404 the script writes the code into the according fields    
    if (response_code == "404")
    {
    aSheet.getRange(row, 3).setValue(response_code);
    aSheet.getRange(row, 4).setValue("#N/A");
    aSheet.getRange(row, 5).setValue("#N/A");
    aSheet.getRange(row, 6).setValue("#N/A");

// here the script checks, if the rows in the sheets "Title" and "Description" are the same as in the "URL Status" sheet, just really simple check to find if there is a row too much or missing. The check needs to be done, since the other sheets get their url from "URL Status" and would check the wrong title or description if one row was missing/too much/wrong order.

    if(stext==ttext && stext==dtext)
    {
    aSheet.getRange(row, 7).setBackground('#00FF00');
    }
    else
    {
    aSheet.getRange(row, 7).setBackground('#FF0000');
    }
    row++;
    url = aSheet.getRange(row, 2).getValue();
    }

//if the response code is not 404, the script gets the content from the website to fetch and write the response code, the robots text, view_group and item_group

      else 
      {
      var code = UrlFetchApp.fetch(url, options1).getContentText();
        if (code != "")
        {
        var i1=code.indexOf('<meta name="robots" content="');
        var i2=code.indexOf('">',i1);
        var i3=code.indexOf('"hd_prev_view_group" value="');
        var i4=code.indexOf('">',i3);
        var i5=code.indexOf('"hd_prev_item_group" value="');
        var i6=code.indexOf('">',i5);
        var robottext=code.substring(i1+29,i2);
        var viewtext=code.substring(i3+28,i4);
        var itemtext=code.substring(i5+28,i6);
        aSheet.getRange(row, 3).setValue(response_code);
        aSheet.getRange(row, 4).setValue(robottext);
        aSheet.getRange(row, 5).setValue(viewtext);
        aSheet.getRange(row, 6).setValue(itemtext);  

        if(stext==ttext && stext==dtext)
        {
        aSheet.getRange(row, 7).setBackground('#00FF00');
        }
        else
        {
        aSheet.getRange(row, 7).setBackground('#FF0000');
        }
        row++;
        url = aSheet.getRange(row, 2).getValue();
        }
// if the url is available (no 404) but the html-page is empty, it write these values in the according row (might be rare but could happen          
        else 
        {
        aSheet.getRange(row, 3).setValue("#N/A");
        aSheet.getRange(row, 4).setValue("#N/A");
        aSheet.getRange(row, 5).setValue("#N/A");
        aSheet.getRange(row, 6).setValue("#N/A");
        if(stext==ttext && stext==dtext)
        {
        aSheet.getRange(row, 7).setBackground('#00FF00');
        }
        else
        {
        aSheet.getRange(row, 7).setBackground('#FF0000');
        }
        row++;
        url = aSheet.getRange(row, 2).getValue();
        }
  }
}
}    
return;
}

I'm sure there is plenty of space for improvement as I'm a total Noob in writing scripts. Some suggestions and help would be really appreciated!

Here is how it looks like after the script has run:
Sheet with URL status after the script has run
As you can see, product 9 and 13 are miss-matched, but it's just of different spelling. If the product was missing in the other sheets, everything afterwards would be red.

In any case, merry christmas

Best Answer

The first thing to do is to minimize the number of API calls interacting with the sheet, such as getValue() and setValue(). Think of the script as a factory and the sheet as its warehouse across the town. If a worker has to drive to warehouse every time they need a detail, they won't get much done. Instead, they should get all the materials they need at the beginning, and turn in all finished product at the end.

So, instead of the multiple .getRange(row, column).getValue() and .getRange(row, column).setValue(), there should be

  1. one call to get values: e.g., sheet.getDataRange().getValues() which gets you all the data in the sheet. (Or, if you know a specific range you'll be working in, get all values from there). Then loop over this double array, analyzing its contents
  2. one call to set new values with .setValues(), from a double array of the same size (like [['This year', 2015], ['Next year', 2016]]
  3. one call to set backgrounds with .setBackgrounds(), also from a double array.

In between 1 and 2, you work purely in JavaScript, processing one array and forming two others.

Sometimes you can't have just one array in 2 or 3, because, e.g., setValues would overwrite some formulas in the middle of your data. In this case, split the data range into groups of columns that can be handled at once. You will still have only a few setValues calls.

Related Topic