2

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?

DeCodened
  • 59
  • 2
  • 8
  • 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 Jul 28 '17 at 21:47
  • 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 Jul 28 '17 at 22:23
  • [This](https://stackoverflow.com/questions/43083563/real-time-retrieval-of-selected-range-in-spreadsheet-using-gas/43084904#43084904) is a not so simple example – Cooper Jul 29 '17 at 00:04
  • 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 Jul 31 '17 at 23:29
  • I left a much simpler example. – Cooper Aug 01 '17 at 01:42

1 Answers1

3

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>
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • So, if I follow, selRange() calls the HTML and also asks user to select a range. Then, all of the functions in the HTML run except setResponse() which is waiting for selCurRng() to finish. I am still not quite following where there is pause for the user or how the buttons are acting as the 'gateway' for lack of a better word to the script proceeding. Does the code in SelectRange() pause until a button is clicked? I think the rest makes sense. – DeCodened Aug 02 '17 at 13:02
  • One more question regarding the buttons Cooper. I went [here](https://developers.google.com/apps-script/reference/base/ui#showModelessDialog(Object,String)) and didn't see the syntax you are using to create "#btn1" and "#btn2". I am not finding anything that shows how these interact. Thanks for your help! – DeCodened Aug 02 '17 at 13:16
  • Whenever you click one of those buttons the process is directed by the onClick event and in this cause I actually put the onClick attribute right in the input tag. – Cooper Aug 02 '17 at 13:36
  • A couple questions here Cooper. I was trying to mess with your code a little so I could see if I could at all prompt the user for a range with a Ui.Prompt. I wanted to do that for non-continuous ranges. I also wanted to pass this data in as an array and then call ArrayToDataTable, as seen above. I haven't been successful in doing that and it seems once the initial dialog box goes away, there is no way of getting it back (the prompt caused this). Is there any way to go about it that way that you know of? Or am I going to have to try another methodology in conjunction with this? – DeCodened Aug 09 '17 at 04:25
  • I pretty sure that there is currently no support for non contiguous ranges in Google Apps Script. – Cooper Aug 09 '17 at 04:37
  • Hmm, what about re-calling your pickRange.html once it has lost focus? – DeCodened Aug 09 '17 at 04:49
  • The problem with the UI.prompt is that it's modal. So you can't make a selection during the time that the prompt is active. You could type in a range but I think it's easier just to select the range and return to the dialog and press a button. If you called another dialog while one is active then the one that was active is closed and so you simply have to open it again. This was just a simple example to show you how a modeless dialog can sometimes be of great value. – Cooper Aug 09 '17 at 05:08
  • Yes, I can definitely see how it can help. Thanks for this! – DeCodened Aug 09 '17 at 14:38