infinite loading in google spreadsheet in function script

Question

i use function script in google spread sheet.

this function is used maybe... 150,000 cell.

my question is... infinite loading.

when i use my custom function in sheet, infinite loading appear

how can i resolve that?

here is my script code :

function s2hex(str1){
  var s2hex = 0;
  var byte_check = 0;
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tbl"); 
  var range1 = sheet1.getRange(1, 1, sheet1.getMaxRows(), 
  sheet1.getMaxColumns());

  for(var i = 0; i < str1.length; ++i){    
    for(var k = 1; k < sheet1.getMaxRows(); ++k){      
      if(str1[i] == range1.getCell(k, 2).getValue() || str1[i] == " "){
        s2hex = s2hex + 1;
        byte_check = 1;
        break;
      } 
    }
    if(byte_check == 0){
        s2hex = s2hex + 2;
    }
    byte_check = 0;
  }
  return s2hex;
};

TheMaster · Accepted Answer · 2018-03-19 08:40:31Z

getMaxRows() will return all rows in the sheet. If you have content in A1:A10 and there are empty rows from A10 to A100000. It'll return 100,000 instead of 10. Use getLastRow() instead.
If you're only using the second column,Specify 2 as column number and 1 instead of getMaxColumns()
Use getValues() to get a single array instead of making multiple calls to spreadsheet.

Try Modifying from:

var range1 = sheet1.getRange(1, 1, sheet1.getMaxRows(), sheet1.getMaxColumns());
for(var i = 0; i < str1.length; ++i)
{
for(var k = 1; k < sheet1.getMaxRows(); ++k)
{      
  if(str1[i] == range1.getCell(k, 2).getValue() || str1[i] == " ")
  {
    s2hex = s2hex + 1;

To


    var lr =sheet1.getLastRow(); //One last row call to be used in multiple places
    var range = sheet1.getRange(1, 2, lr,1); 
    var range1 = range.getValues(); //get all values in a single call
    for(var i = 0; i < str1.length; ++i)
    {
    for(var k = 0; k < lr; ++k) //k to 0.Array index start at 0
    { 
      if(str1[i] == range1[k][0] || str1[i] == " ") //Check the 2D value of already retrieved array
      {
        s2hex = s2hex + 1;

tehhowch · Accepted Answer · 2018-03-19 05:45:51Z

0

150,000 function calls will slow down any sheet.

That said, your function is in dire need of optimization: you need to remove the need for extensive calls over the Spreadsheet interface. Each such call can take as much as 0.1 second. You can do this simply, by reading the entire range of your values to search with getValues(), and then iterating that in the same manner as you do currently.

answered Mar 19, 2018 at 5:45

tehhowch

9,9124 gold badges27 silver badges47 bronze badges

Collectives™ on Stack Overflow

infinite loading in google spreadsheet in function script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related