在 .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.

代码.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屋！