Google Sheets Script: Get Value From Script Function Into Sidebar

Question

For an analysis spreadsheet, a function does a lot of calculations for each of ~240 rows of data. The function is slow, so I added code to check how long it's been running whilst looping.

Before 360 secs is up I exit - first saving the last row processed in Project Properties, and providing an alert. I can then rerun the function, which checks if the function did not complete, and asks if I want to continue from the last row processed. All good.

However, this can take up to half an hour to finally complete , so I've been looking online for ways to run multiple instances of the function simultaneously. I found a suggestion that uses the Sidebar.

The Sidebar is opening as expected, but despite some help, my script in the HTML is not setting the table cells with the initial "size". The server-side function c_Optimise_Stabilator_Allocation() is correctly returning an array like [87.0, 74.0, 62.0, 25.0], but the client-side function setSize(arrSize) is not working.

The GS code is:

// display sidebar in gs
function c_Optimise_Stabilator_Sidebar(){
  var html = HtmlService.createHtmlOutputFromFile('StabSidebar').setTitle('Optimise Stabilator').setWidth(250);
  SpreadsheetApp.getUi().showSidebar(html);
}

function c_Optimise_Stabilator_Allocation(tranche, operation){
  var debug = true;
  const ui = SpreadsheetApp.getUi();

  // Get numRows
  const sAngle = "Optimise_Stab_Angle";
  const range = c_GetRangeObjByName(sAngle);
  const numRows = range.getLastRow(); // s/b ~248 rows
  
  // set different num rows per tranche, using percentage
  const pct1 = 0.35, pct2 = 0.30, pct3 = 0.25;
  
  const size1 = Math.round(numRows*pct1),   startRow1 = 0;
  const size2 = Math.round(numRows*pct2),   startRow2 = size1;
  const size3 = Math.round(numRows*pct3),   startRow3 = size1+size2;
  const size4 = numRows-size1-size2-size3,  startRow4 = size1+size2+size3;
            if (debug) { Logger.log ('T1: '+size1+'; T2: '+size2+'; T3: '+size3+'; T4: '+size4); }
  
  if (operation=='size') {
    var arrSize = [size1, size2, size3, size4];
    if (debug) { Logger.log(arrSize); }
    return arrSize;
    
  } else if (operation=='run') {
    switch(tranche) { 
      case 'tranche1' :
          //        c_Optimise_Stabilator(startRow1, size1); 
        break;
      case 'tranche2' :
          //        c_Optimise_Stabilator(startRow2, size2); 
        break;
      case 'tranche3' :
          //        c_Optimise_Stabilator(startRow3, size3); 
        break;
      case 'tranche4' :
          //        c_Optimise_Stabilator(startRow4, size4); 
        break;
      default :
        break;
    }
  }
return;
}

The HTML code:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>

  <button onclick='run()'>Run Stabilator Optimisation</button><br>
  
  <table>
    <tr> <th>Tr</th> <th>Status</th>                  <th>Optimised</th>   <th>Tot.Rows</th>     <th>Time</th> </tr>
    <tr> <td>1</td>  <td id='status1'>Waiting...</td> <td id='opt1'>0</td> <td id='size1'>0</td> <td id='time1'>xxx</td> </tr>
    <tr> <td>2</td>  <td id='status2'>Waiting...</td> <td id='opt2'>0</td> <td id='size2'>0</td> <td id='time2'>xxx</td> </tr>
    <tr> <td>3</td>  <td id='status3'>Waiting...</td> <td id='opt3'>0</td> <td id='size3'>0</td> <td id='time3'>xxx</td> </tr>
    <tr> <td>4</td>  <td id='status4'>Waiting...</td> <td id='opt4'>0</td> <td id='size4'>0</td> <td id='time4'>xxx</td> </tr>
  </table>

<script>

window.onload=function(){
  console.log('onload');
  google.script.run
    .withSuccessHandler( function(arrSize) {
      setSize(arrSize);
      }
    )
    .c_Optimise_Stabilator_Allocation('all', 'size');
}
function setSize(arrSize){
  console.log('setSize');
  for(var i=0;i<vA.length;i++) {
    document.getElementById('size' + (i+1)).innerHTML=arrSize[i];
  }
}
function run() {
  google.script.run.c_Optimise_Foil('tranche1', 'run'); document.getElementById('status1').innerHTML='Running...';
  google.script.run.c_Optimise_Foil('tranche2', 'run'); document.getElementById('status2').innerHTML='Running...';
  google.script.run.c_Optimise_Foil('tranche3', 'run'); document.getElementById('status3').innerHTML='Running...';
  google.script.run.c_Optimise_Foil('tranche4', 'run'); document.getElementById('status4').innerHTML='Running...';
}


console.log("My Code");
</script>

</body>
</html>

I have cobbled together the code for the HTML as best I could from various sources, but can't find why the functions to setSize aren't working. Any suggestions would be welcome. MTIA

score 2 · Accepted Answer · answered Dec 18 '20 at 17:08

2

I don't really want to debug your code for you but I did want to know if it would handle multiple callbacks so here's a simple example of it doing that:

HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  
  <script>
    window.onload=function(){
    google.script.run
  .withSuccessHandler(function(vA) {
    updateSelect(vA);
  })
  .getSelectOptions();
}
function updateSelect(vA,id){//the id allows me to use it for other elements
  var id=id || 'sel1';
  var select = document.getElementById(id);
  select.options.length = 0; 
  for(var i=0;i<vA.length;i++) {
    select.options[i] = new Option(vA[i],vA[i]);
  }
}

function sendRequest() {
  const n=document.getElementById('sel1').value;
  document.getElementById('div' + n).innerHTML='Initiating Request' + n;
  google.script.run
  .withSuccessHandler(function(v){
    document.getElementById('div' + v).innerHTML='Received Request' + v;
  })
  .initRequest(n);
}

function sendAll() {
  for(let i=1;i<5;i++) {
    document.getElementById('div' + i).innerHTML="Iniating Request" + i;
    google.script.run
    .withSuccessHandler(function(v){
      document.getElementById('div' + v).innerHTML='Received Request' + v;
    })
    .initRequest(i);
  }
}

console.log("My Code");
  </script>
  </head>
  <body>
     <select id='sel1'></select>
     <input type="button" value="Initiate" onClick="sendRequest();" />
     <input type="button" value="All" onclick="sendAll();" />
     <div id="div1"></div>
     <div id="div2"></div>
     <div id="div3"></div>
     <div id="div4"></div>
  </body>
</html>

CODE:

function getSelectOptions() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  const rg=sh.getRange(1,1,sh.getLastRow());
  const vs=rg.getValues();
  return vs;
}
function launchDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile("ah3"),"Testing");
}

function initRequest(n) {
  return n;
}

I don't expect any points for it. I just thought it might be helpful to know that it can work. Somebody will most likely vote it down and then I'll remove it.

answered Dec 18 '20 at 17:08

Cooper

59,616
6
23
54

Well, thanks from me! Tried it out, great on 2 counts: First, I can check your example to see what I'm doing wrong, and get my code working. Second, it answers my question of does it really run functions simultaneously - I noticed that the functions did not complete "in order" so requests were being fulfilled asynchronously. I think. Answers my question, and then some. Cheers. – maxhugen Dec 19 '20 at 01:33
1

That's basicly what I was interested in learning about. I noticed that most of the time it would do them in order but not all of the time. Frankly, I had never thought of even trying it so thanks for the question. – Cooper Dec 19 '20 at 03:29
Unfortunately, I don't properly understand callback functions, as I still can't get it to work. I've updated the script in my post to reflect what I've tried. The server-side function returns an array, but my client-side script can't get it? :( – maxhugen Dec 19 '20 at 15:51
I guess you’re talking about arrSize. And I guess what you’re really saying is you don’t know how to debug your code. – Cooper Dec 19 '20 at 16:00
Yes and yes. I can debug the server script OK, after 3 mths learning JS, so I know it's returning the array, but I'm having a problem trying to debug the client script. I tried console.log but the issue seems to be that the array is undefined, but I can't see why. – maxhugen Dec 19 '20 at 18:09
Have you read this: https://developers.google.com/apps-script/guides/html/communication#parameters_and_return_values – Cooper Dec 19 '20 at 19:13
Why do you declare startRow1,2,3,4 but never use them? – Cooper Dec 19 '20 at 19:22
We can't debug your code there's undefined variables and functions that are not provided I think you are on your own – Cooper Dec 19 '20 at 19:24
Those var are used by the call to function c_Optimise_Stabilator(startRow1, size1); These were commented out temporarily while I was checking code flow. – maxhugen Dec 19 '20 at 20:12

Google Sheets Script: Get Value From Script Function Into Sidebar

1 Answers1