CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetssidebar
maxhugen
maxhugen

Reputation: 1944

Google Sheets Script: Get Value From Script Function Into Sidebar

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

Upvotes: 2

Views: 536

Answers (1)

Cooper
Cooper

Reputation: 64100

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.

Upvotes: 2

Related Questions