Which is Faster? Value calculation or Formula - Google Sheets Script Editor:

1
votes

I would like to know if it's actually faster to...

-do value calculation directly within the scripts editor

OR

-run the script that outputs formulas into cells.

The following is what I currently have:

function recheck_qty(itemname){
    var itemcell = findcellbyvalue(itemname);
    var itemcellrange = SpreadsheetApp.getActive().getSheetByName('Inventory').getRange(itemcell);
    var row = itemcell.substring(1);
    var sold = checkqty_sold1(itemname);
    SpreadsheetApp.getActive().getSheetByName('Inventory').getRange("D"+row).setValue("=SUM(F"+row+":"+row+","+sold+")");
}
function checkqty_sold1(item) {
  var ss = SpreadsheetApp.getActive();
  var allsheets = ss.getSheets();
  var sold = "";
  var count = 0;
  for(var s in allsheets){
    var sheet = allsheets[s];
    var sheetname = sheet.getName();
    if(count>0){
      sold += ","
    }
    if(sheetname!='Inventory'){
        sold += 'IF(count(FILTER('+sheetname+'!F2:F,'+sheetname+'!B2:B="'+item+'"))<=0,0,-SUM(FILTER('+sheetname+'!F2:F,'+sheetname+'!B2:B="'+item+'")))';    
        count++;
    }
  } // end of loop
  return sold;
}

Previously, I was doing actual calculations for var sold and the value of "SUM(F"+row+":"+row". But I realize that the data actually take some time to populate into the cell. Could be due to all the getrange(), getsheets() functions that i'm calling.. but i'm not sure.

Changing to this current one, I was able to make it slightly faster but only at times. It does not always work as fast. Still take sometime to load.

1
javascriptgoogle-apps-scriptgoogle-sheetsspreadsheetgoogle-sheets-api
Hello @Lawrence, thank you for publishing this question. Could you please provide an example Sheets document where you would apply this code? That would allow us to better understand the context and provide you a better answer. Cheers - carlesgg97
Sure. This is the link. docs.google.com/spreadsheets/d/… - Lawrence

1 Answers

0
votes

The issue, according to my calculations and testing with your sheet seems to be mostly coming from the part where you search the item in your 'Inventory' sheet (function findcellbyvalue()), especially when you have a large amount of items. Below I propose a modification that may improve your performance:

Modification of findcellbyvalue():

  • It doesn't call sheet.getDataRange() as it is not needed (you can do sheet.getLastRow() instead.
  • It uses the TextFinder class to efficiently find the item you desire.
function findcellbyvalue(value) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Inventory');
  var lastRow = sheet.getLastRow();  
  var searchRange = sheet.getRange(3, 1, lastRow-1, 1);

  // returns a range (of the cell)
  var finder = searchRange.createTextFinder(value).matchEntireCell(true);
  return finder.findNext().getA1Notation();
}

Further to that I recommend that you visit the following links: