Google Sheets and Script REALLY SLOW compared to Excel and VBA

Question

I have an Excel s/s which has some simple VBA in the background that I wrote myself. Table inside has about 5000 rows by 6 columns and depending on user selection looks at rank of how often a particular word or character appears - it's a language tool. The macros simply allow the user to increase rank by one each time; reveal answers line by line; or start from beginning (set rank back to 1). Works fine in VBA and quickly too.

However I want to share with Mac people who don't have Excel! I have a working version in Google Sheets with script which also works - programming in script wasn't too difficult to pick up as this was pretty basic and I have VBA background. Example of script below though I don't think that's the issue - probably my understanding of how google script works in general.

Firstly just adjusting the choice of study level taken 15 seconds (that doesn't depend on script) - excel it's about 5sec - there are lots of countifs and rank formula. However in VBA running any of the code takes under 1sec. In script it takes 11 sec. I can't believe the speed differences!

I have made several copies of the s/s so I can play with developing and see what optimises the calculations (not the script - there's not too much to optimise there) - and notice all share the same script - this is alien to me as in VBA the code stays with the s/s. Is this one of the issues?

Why is Google Sheets so slow?

Simple code functions:

function IncreaseRankSelection(){
  var spreadsheet = SpreadsheetApp.getActive();
  if (spreadsheet.getRange('Q13').getValues()=="Yes"){
    spreadsheet.getRange('K3:M67').activate();
    spreadsheet.getActiveRangeList
    ().setFontColor('#ffffff') }
  else {
    spreadsheet.getRange('K3:M67').activate();
    spreadsheet.getActiveRangeList().setFontColor('#999999') }  

  if (spreadsheet.getRange('Q8').getValues()==spreadsheet.getRange('Q5').getValues()){
    Browser.msgBox('There are no more Sets to study!')}
  else {
    var startvalue = spreadsheet.getRange('Q8').getValues();
    var newvalue = (Number(startvalue) + 1);
    spreadsheet.getRange('Q8').setValue(newvalue);
  };

  spreadsheet.getRange('T17').setValue('1'); 
  newvalue2=spreadsheet.getRange('T10').getValues();
  spreadsheet.getRange('Q9').setValue(newvalue2);
  spreadsheet.getRange('Q8').activate();

};

There are plenty of things that can be optimized in your script. Your vba background is a liability than a asset. There are stuff you shouldn't do here as it's a entirely new language running in the cloud. — TheMaster, May 19 '20 at 10:03
For formulas, you should use `ARRAYFORMULA`, `QUERY`, `VLOOKUP` in a array as a single formula rather than INDEX/MATCH and multiple formulae. Query in particular would be extremely fast. — TheMaster, May 19 '20 at 10:10
Thanks so much - will learn and try all these sequentially see which makes most difference — SteveB, May 19 '20 at 15:24
Try this:`function IncreaseRankSelection(){var sh = SpreadsheetApp.getActive();if (sh.getRange('Q13').getValues()=="Yes"){sh.getRange('K3:M67').setFontColor('#ffffff');}else {sh.getRange('K3:M67').setFontColor('#999999');}if (sh.getRange('Q8').getValue()==sh.getRange('Q5').getValue()){Browser.msgBox('There are no more Sets to study!')}else {sh.getRange('Q8').setValue(sh.getRange('Q8').getValue()+1);};sh.getRange('T17').setValue('1');sh.getRange('Q9').setValue(sh.getRange('T10').getValue());}` — Cooper, May 19 '20 at 19:38

Jescanellas · Answer 1 · 2020-05-19T09:34:52.847

Your code can be optimized. You don't need to activate each range to use it, and it's better if you use getValue for single cells instead of getValues wich returns a 2D array. Also, there is no need to use a Range List, you can apply the background color directly into the range. If you are going to access the same range several times, it's also a good practice to save it in a variable so it doesn't have access the object through the Spreadsheet each time:

    function IncreaseRankSelection(){
      var spreadsheet = SpreadsheetApp.getActive();
      var rangeQ8 = spreadsheet.getRange('Q8');
      var rangeK3M67 = spreadsheet.getRange('K3:M67');

      if (spreadsheet.getRange('Q13').getValue()=="Yes"){
        rangeK3M67.setFontColor('#ffffff');
         }
      else {
        rangeK3M67.setFontColor('#999999') 
      }  

      if (rangeQ8.getValue() == spreadsheet.getRange('Q5').getValue()){
        Browser.msgBox('There are no more Sets to study!')}
      else {
        var startvalue = rangeQ8.getValue();
        var newvalue = (Number(startvalue) + 1);
        rangeQ8.setValue(newvalue);
      };

      spreadsheet.getRange('T17').setValue('1'); 
      newvalue2 = spreadsheet.getRange('T10').getValue();
      spreadsheet.getRange('Q9').setValue(newvalue2);
      rangeQ8.activate(); 

    }

If you are using lots of formulas, you might be interested in using getDisplayValue() instead of getvalue, in case you only need the value displayed in the cell.

Another option would be the batchupdate requests of the Sheets API, which are faster than the SpreadsheetApp when making changes to a Sheet.

Google Sheets and Script REALLY SLOW compared to Excel and VBA

1 Answers1