Google Apps脚本 - 可能的图表类型 [英] Google Apps Script - possible charts types

查看：155
本文介绍了Google Apps脚本 - 可能的图表类型的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我不熟悉Google Apps脚本，因此只是在探索我想实现的目标是否可行。

从Google表单中，我需要检索并在单独的文档上显示从每个单独表单提交中的数据创建的图表。我知道这是可以做到的。



我遇到的问题是我想要的图表类型似乎不可用，这是一个非常灵活的方式来检索数据。您的客户端js可以指定您感兴趣的数据范围，并且您可以使用查询语言来操作您将显示的数据，而无需修改源电子表格。 $ b 

  function sendQuery（）{
 var opts = {sendMethod：'auto'}; 
 var sheetId =---您的工作单ID ---; 
 var dataSourceUrl ='https://spreadsheets.google.com/tq?key=%KEY%&pub=1'
 .replace（％KEY％，sheetId）; 
 
 var query = new google.visualization.Query（dataSourceUrl，opts）; 
 
 //如果需要，指定查询字符串。 
 
 //用回调函数发送查询。 
 query.send（drawSeriesChart）;如果您不拥有源数据，例如 
 
 
 
 
 
 / p>  
 
  •  
    创建一个将提供电子表格数据的Web服务。这种方法使电子表格本身保持私密。 使用服务器和服务器之间的直接通信 客户端脚本，通过 google.script.run 。这样，电子表格保持私密。这个例子非常简单，因为它收集了整个电子表格，但是您可以通过筛选，排序或添加更多元数据来扩展它来操纵数据。
      function sendQuery（）{
     
     //使用回调函数发送查询。 
     google.script.run 
     .withSuccessHandler（drawSeriesChart）
     .getSpreadsheetData（）; 
     
     $ / code> 
    这要求函数 getSpreadsheetData（）在服务器端实现以返回所需的数据。这在下面的实际代码中已经显示。
    •   
     
     
     
     
     Code.gs 
     
     $ b $除了通常的yada-yada菜单创建之外，该文件还实现了一个 getSpreadsheetData（）函数，我们将使用该函数从表单中检索所有数据。      
     $ b 
      / ** 
     *添加一个包含项目的自定义菜单，对话。 
     * 
     * @param {Object} e一个简单的onOpen触发器的事件参数。 
     * / 
    函数onOpen（e）{
     SpreadsheetApp.getUi（）
     .createAddonMenu（）
     .addItem（'Bubble Chart Example'，'showBubbleEx'） 
     .addToUi（）; 
    } 
     
     / ** 
     *安装加载项时运行;调用onOpen（）以确保菜单创建，
     *任何其他初始化工作立即完成。 
     * 
     * @param {Object} e一个简单的onInstall触发器的事件参数。 
     * / 
     onInstall（e）{
     onOpen（e）; 
    } 
     
     / ** 
     *打开可视化对话框。 
     * / 
     function showBubbleEx（）{
     var ui = HtmlService.createTemplateFromFile（'BubbleEx'）
     .evaluate（）
     .setSandboxMode（HtmlService.SandboxMode.IFRAME ）
     .setWidth（450）
     .setHeight（350）; 
     SpreadsheetApp.getUi（）。showModalDialog（ui，Bubble Chart Example）; 
    } 
     
     / ** 
     *将第一张电子表格中的所有数据作为数组返回。可以通过google.script.run使用
     *获取数据，而不需要出版物
     *的电子表格。 
     * 
     *如果电子表格不包含多行，则返回null。 
     * / 
     function getSpreadsheetData（）{
     var data = SpreadsheetApp.getActive（）。getSheets（）[0] .getDataRange（）。getValues（）; 
     return（data.length> 1）？ data：null; 
    }



    BubbleEx.html



    这是根据表格附加模板进行调整的，并且引用了其中包含的 Stylesheet.html 文件。

    <！ - 使用模板化HTML打印scriptlet导入常用样式表。 - >
    <？！= HtmlService.createHtmlOutputFromFile（'Stylesheet'）。getContent（）; ？>

    <！ - 以下是定义对话框元素结构的HTML代码。 - >
    < div>
    < div id =series_chart_divstyle =width：400px; height：300px;>< / div>
    < div class =blockid =dialog-button-bar>
    < button id =dialog-cancel-buttononclick =google.script.host.close（）>取消< / button>
    < / div>
    < / div>

    <！ - 使用模板化HTML打印scriptlet导入JavaScript。 - >
    <？！= HtmlService.createHtmlOutputFromFile（'BubbleExJavaScript'）。getContent（）; ？>



    BubbleExJavaScript.html 



     < script src =// ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js\"> ;</script> 
    < script type =text / javascriptsrc =https://www.google.com/jsapi>< / script> 
     
    < script> 
     //加载Visualization API和所需的软件包。 
     google.load（'visualization'，'1.0'，{'packages'：['corechart']}）; 
     
     / ** 
     *在对话框加载时运行初始化。 
     * / 
     $（function（）{
     //设置一个回调函数，以便在加载Google Visualization API时运行
     google.setOnLoadCallback（sendQuery）; 
     
     //如果需要，在这里为对话框元素指定处理函数
     
     //在这里调用服务器来检索构建
    所需的任何信息//如果需要， 
    }）; 
     
     / ** 
     *发出电子表格数据的异步请求。 
     * / 
    函数sendQuery（）{
     google.script.run 
     .withSuccessHandler（drawSeriesChart）
     .getSpreadsheetData（）; 
    } 
     
     / ** 
     *使用响应参数中的数据生成可视化的回调函数。 
     * / 
    函数drawSeriesChart（响应）{
     
     if（response == null）{
     alert（'Error：Invalid source data。'）
    返回; 
    } 
     else {
     var data = google.visualization.arrayToDataTable（response，false）; 
     
     var options = {
     title：'预期寿命，生育率和一些世界国家的人口（2010年）之间的相关性'，
     hAxis：{title：data.getColumnLabel 1）}，//'Life Expectancy'
     vAxis：{title：data.getColumnLabel（2）}，//'Fertility Rate'
     bubble：{textStyle：{fontSize：11}} 
    }; 
     
     var chart = new google.visualization.BubbleChart（document.getElementById（'series_chart_div'））; 
     chart.draw（data，options）; 
    } 
    } 
     
    < / script>


    I'm new to Google Apps Script so am just exploring if what I want to achieve is possible.

    From a Google form, I need to retrieve and display on a separate document the chart created from data on each individual form submission. I know this can be done.

    The problem I have is that the chart type I want does not seem to be available here.

    The chart needs to show a category and two values. This could be done with a bar chart, height is one value and colour the other value - this looks as though it might be possible but I am not sure if the colour of the whole bar can be changed.

    An alternative is the bubble chart, X axis for category, Y axis for one value and size for the other value - but this type of chart does not seem to be supported.

    解决方案

    You can display any of the 25+ chart types provided by the Google Visualization API within the Google Apps Script HTML Service.

    Below is a modified version of the Bubble Chart example. Instead of fixed data, we'll pull data from a spreadsheet. The chart will be displayed in a modal dialog, as an add-on within that spreadsheet.

    The source data:

     A         B               C               D            E
ID  Life Expectancy Fertility Rate  Region          Population
CAN      80.66           1.67       North America   33739900
DEU      79.84           1.36       Europe          81902307
DNK      78.6            1.84       Europe           5523095
EGY      72.73           2.78       Middle East     79716203
GBR      80.05           2          Europe          61801570
IRN      72.49           1.7        Middle East     73137148
IRQ      68.09           4.77       Middle East     31090763
ISR      81.55           2.96       Middle East      7485600
RUS      68.6            1.54       Europe         141850000
USA      78.09           2.05       North America  307007000

    Client Side

    The rest of the design is pretty straight-forward, but for Apps Script programmers who aren't used to javascript use in the HTML service, especially the behaviour of asynchronous function calls & call-backs, it's what's happening in the client side code that's most interesting. Here's the basic flow.

    • Present html page with a placeholder for the visualization.

    • Load external JavaScript libraries. We'll be using jQuery (for easy manipulation of DOM) and of course Google's JavaScript API, aka jsapi, for the visualization objects.

    • When the page loads, request a callback. We'll call that sendQuery(), as it will retrieve our spreadsheet data. This is a different approach than the original example that simply displayed a chart, because we're not just using hard-coded data.

    • When the jsapi finishes loading, sendQuery() is called. It requests our data, and passes the asynchronous response to another callback, drawSeriesChart().

    • Once data is received by drawSeriesChart(), draw the chart.

    Options for retrieving data from spreadsheet

    Since our visualization will be run in a browser (aka client-side), we need to get the info from the spreadsheet (aka server-side). Depending upon your specific needs, there are a few ways to retrieve that data.

    1. Query via visualization API.

      For a published spreadsheet, this is a very flexible way to retrieve data. Your client-side js can specify the range of data you're interested in, and you can utilize the Query Language to manipulate the data you'll display without modifying the source spreadsheet.

      function sendQuery() {
  var opts = {sendMethod: 'auto'};
  var sheetId = "--- your sheet ID ---";
  var dataSourceUrl = 'https://spreadsheets.google.com/tq?key=%KEY%&pub=1'
                     .replace("%KEY%",sheetId);

  var query = new google.visualization.Query(dataSourceUrl, opts);

  // Specify query string, if desired.

  // Send the query with a callback function.
  query.send(drawSeriesChart);
}

      Handy for situations where you don't own the source data, for example

    2. Create a web service that will feed the spreadsheet data. This approach keeps the spreadsheet itself private.

    3. Use direct communication between the server & client side scripts, via google.script.run. This way, the spreadsheet remains private. This example is very simple, as it gleans the entire spreadsheet, but you could extend it to manipulate your data by filtering, sorting, or adding further metadata for formatting.

      function sendQuery() {

  // Send the query with a callback function.
  google.script.run
        .withSuccessHandler(drawSeriesChart)
        .getSpreadsheetData();
}

      This requires that function getSpreadsheetData() be implemented on the server side to return the desired data. That's shown in the actual code that follows.

    Code.gs

    Other than the usual yada-yada for menu creation, this file implements a getSpreadsheetData() function that we'll use to retrieve all the data from a sheet.

    /**
 * Adds a custom menu with items to show the sidebar and dialog.
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Bubble Chart Example', 'showBubbleEx')
      .addToUi();
}

/**
 * Runs when the add-on is installed; calls onOpen() to ensure menu creation and
 * any other initializion work is done immediately.
 *
 * @param {Object} e The event parameter for a simple onInstall trigger.
 */
function onInstall(e) {
  onOpen(e);
}

/**
 * Opens a dialog for a visualization.
 */
function showBubbleEx() {
  var ui = HtmlService.createTemplateFromFile('BubbleEx')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setWidth(450)
      .setHeight(350);
  SpreadsheetApp.getUi().showModalDialog(ui, "Bubble Chart Example");
}

/**
 * Return all data from first spreadsheet as an array. Can be used
 * via google.script.run to get data without requiring publication
 * of spreadsheet.
 *
 * Returns null if spreadsheet does not contain more than one row.
 */
function getSpreadsheetData() {
  var data = SpreadsheetApp.getActive().getSheets()[0].getDataRange().getValues();
  return (data.length > 1) ? data : null;
}

    BubbleEx.html

    This was adapted from the "Sheets add-on" template, and refers to the Stylesheet.html file included there.

    <!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<!-- Below is the HTML code that defines the dialog element structure. -->
<div>
  <div id="series_chart_div" style="width: 400px; height: 300px;"></div>
  <div class="block" id="dialog-button-bar">
     <button id="dialog-cancel-button" onclick="google.script.host.close()">Cancel</button>
  </div>
</div>

<!-- Use a templated HTML printing scriptlet to import JavaScript. -->
<?!= HtmlService.createHtmlOutputFromFile('BubbleExJavaScript').getContent(); ?>

    BubbleExJavaScript.html

    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript"  src="https://www.google.com/jsapi"></script>

<script>
  // Load the Visualization API and desired package(s).
  google.load('visualization', '1.0', {'packages':['corechart']});

  /**
   * Run initializations on dialog load.
   */
  $(function() {
    // Set a callback to run when the Google Visualization API is loaded.
    google.setOnLoadCallback(sendQuery);

    // Assign handler functions to dialog elements here, if needed.

    // Call the server here to retrieve any information needed to build
    // the dialog, if necessary.
  });

  /**
   * Issue asynchronous request for spreadsheet data.
   */
  function sendQuery() {
    google.script.run
      .withSuccessHandler(drawSeriesChart)
      .getSpreadsheetData();
  }

  /**
   * Callback function to generate visualization using data in response parameter.
   */
  function drawSeriesChart(response) {

    if (response == null) {
      alert('Error: Invalid source data.')
      return;
    }
    else {
      var data = google.visualization.arrayToDataTable(response,false);

      var options = {
        title: 'Correlation between life expectancy, fertility rate and population of some world countries (2010)',
        hAxis: {title: data.getColumnLabel(1)},  // 'Life Expectancy'
        vAxis: {title: data.getColumnLabel(2)},  // 'Fertility Rate'
        bubble: {textStyle: {fontSize: 11}}
      };

      var chart = new google.visualization.BubbleChart(document.getElementById('series_chart_div'));
      chart.draw(data, options);
    }
  }  

</script>

    这篇关于Google Apps脚本 - 可能的图表类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

    查看全文
    相关文章
    其他开发最新文章
    热门教程
    热门工具
    登录 关闭
    扫码关注1秒登录
    发送“验证码”获取 | 15天全站免登陆