在.gs函数中提示用户输入范围，将数组传递给html脚本并重新关注HTML对话框 [英] Prompt user for range in .gs function, pass array to html script and re-focus on the HTML dialog

问题描述

我有一个Google工作表，我想提示用户选择要从中获取信息的范围，将其存储到数组中，然后在html弹出窗口中创建图表.我已经阅读了一些有关 google.script.run 的内容.功能，并了解到最后没有withSuccessHandler(HTMLFunction).FunctionToCall()语法，HTML脚本将移至下一行.我下面有一个.gs文件和一个.html文件，当我在.gs函数中输入静态数组时，我能够使图形正常工作.但是，我似乎在为如何将焦点返回到编辑器以获取范围，然后将带有图表的HTML对话框备份并为绘制图表的函数获取正确的数据而苦苦挣扎.我看到了此处，我可以使用google.script.host调用editor.focus()函数，以便用户现在可以选择单元格，但是我似乎无法在不再次调用HTML文件的情况下将焦点返回到HTML弹出窗口.这是我的.gs函数:

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;
     }

这是我的html，它在正文中(堆栈溢出有点严格，所以我不会麻烦显示所有HTML；这只是在正文中，这是与正文进行通信的地方) .gs文件):

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();
  }

我尝试的最后一件事是打电话给 SpreadsheetApp.getUi().showModalDialog(html，"Title")函数再执行一次，但是没有再次调用html文件并创建一个无限循环，我似乎没有办法做到这一点.知道如何实现吗?

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?

推荐答案

下面是一个使用无模式对话框选择范围的简单示例.仅添加了一些额外的功能就可以了.

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

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>

这篇关于在.gs函数中提示用户输入范围，将数组传递给html脚本并重新关注HTML对话框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！