Prompt user for range in .gs function, pass array to html script and re-focus on the HTML dialog

Question

I have a google sheet, I want to prompt a user to select ranges to get information from, store that into an array, and then create a chart in an html popup. I have read a bit about the google.script.run functionality, and understand that without the withSuccessHandler(HTMLFunction).FunctionToCall() syntax at the end, the HTML script moves onto the next line. I have a .gs file below, and an .html file, and I was able to get the graph to work when I just entered a static array in my .gs function. However, I seem to be struggling with how to return focus to the editor to get a range, and then to bring the HTML dialog box with the chart back up and get the right data to the function that plots the chart. I saw here that I could use the google.script.host to call the editor.focus() function so the user can now select cells, but I can't seem to get the focus back to the HTML popup without calling the HTML file all over again. Here is my .gs function:

function RetrieveData(){
  var ss = SpreadsheetApp.getActive();
  var sheets = ss.getSheets();
  var s = sheets[1];
  var UI = SpreadsheetApp.getUi();
  var response = UI.prompt("Please enter the first cell in the category").getResponseText();
  var ir = s.getRange(response);
  var n= 0;
  var stored = [];
  stored.push(["Income Category", "Frequency"]);
  while (ir.getValue()!= "") {
   n = n +1;
   ir = ir.offset(1, 0);
   }
ir = ir.offset(-n,0)

   for(i =0; i<n;i++) {
    stored.push([ir.getValue(),ir.offset(n+2,0).getValue()]);
    ir = ir.offset(1, 0);
   }
return stored;
     }

Here is my html that is within the body (Stack Overflow is a little strict, so I am not going to go through the trouble of showing all the HTML; this is just within the body and it is what is communicating with the .gs file):

google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(getdata);
function getdata() {
      google.script.run.withSuccessHandler(drawChart).RetrieveData();
      google.script.host.editor.focus();
    }
  function drawChart(stored) {
   //This apparently shows a log of the object
   //console.log(stored);
   var data = new google.visualization.arrayToDataTable(stored);
   console.log(data);
   var options = {'title':'Income',
                   'width':400,
                   'height':300,
                   'is3d':true};
    // Instantiate and draw our chart, passing in some options.
   var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
   chart.draw(data, options);
google.script.run.withSuccessHandler(drawChart).RetrieveData();
  }

One last thing I tried was to call the SpreadsheetApp.getUi().showModalDialog(html, "Title") function one more time, but without calling the html file all over again, and creating an endless loop, I don't seem to have a way to do that. Any idea how to accomplish this?

You could use a modeless dialog and then the user could select the range while the dialog is still up and come back and hit a button to let you know they're done. — Cooper
Thanks, is there a link or two to how that might be done in GAS? Could you recommend any similar examples onliine of how that might be done? Thanks! — DeCodened
Thanks Cooper, I am still having some trouble understanding how this would work. Currently, if the user selects from the drop-down, there is code that calls the HTML SpreadsheetApp.getUi().showModalDialog(html, "Statistics"); So, would I only have to change that to a modeless dialog? I am sort of confused about the timing of things and how I would work all of this into my code. Is there any way you know to just call the function DrawChart() without the rest of the HTML code? I am not clear if the modeless box would have buttons, display the code, sorry, not advanced enough for that ex. — DeCodened

Cooper Cooper · Accepted Answer · 2017-08-01T01:40:34

Here's a simple example of picking a range with a modeless dialog. With just a few extra features thrown in for good measure.

Code.gs:

function selRange()//run this to get everything started.  A dialog will be displayed that instructs you to select a range.
{
  var output=HtmlService.createHtmlOutputFromFile('pickRange').setWidth(300).setHeight(200).setTitle('Select A Range');
  SpreadsheetApp.getUi().showModelessDialog(output, 'Range Selector');
}

function selCurRng() 
{
  var sso=SpreadsheetApp.getActive();
  var sh0=sso.getActiveSheet();
  var rg0=sh0.getActiveRange();
  var rng0A1=rg0.getA1Notation();
  rg0.setBackground('#777700');
  return rng0A1;
}

function clrRange(range)
{
  var sso=SpreadsheetApp.getActive();
  var sh0=sso.getActiveSheet();
  var rg0=sh0.getRange(range);
  rg0.setBackground('#ffffff');
}

pickRange.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script>
     var grange='';
     function selectRange()
     {
       $('#btn1').prop('disabled',true);
       $('#btn2').prop('disabled',false);
       google.script.run
         .withSuccessHandler(setResponse)
         .selCurRng();
     }
     function setResponse(r)
     {
       grange=r;
       var msg='You have select the range '  + r; 
       $('#instr').css('display','none');
       $('#rsp').text(msg); 
     }
     function clearAndClose()
     {
       google.script.run.clrRange(grange);
       google.script.host.close();

     }
     console.log('My Code');
    </script>
  </head>
  <body>
    <div id="rsp"></div>
    <div id="instr">Please select your desired range.</div>
    <input type="button" id="btn1" value="Range Selected" onClick="selectRange();" />
    <br /><input type="button" id="btn2" value="close" onClick="clearAndClose();"; disabled="true" />
  </body>
</html>

Prompt user for range in .gs function, pass array to html script and re-focus on the HTML dialog

1 Answers