使用Google Apps Script从Google Sheets创建图表

使用Google Apps Script从Google Sheets创建图表

Google Apps Script是一种基于云的工具,用于自动化基本任务。您可以使用现代JavaScript编写代码。使用Apps Script,您可以添加自定义菜单,针对Google表格编写自定义函数和宏,并可以发布您的Web应用程序。您可以在Google Sheets、Google Docs、Gmail等中自动化任务。要使用此工具,您需要一个Google账号,并在您的系统中安装Google Chrome。该脚本是在Google Chrome的脚本编辑器中编写的。

在本文中,我们将编写一个脚本,从Google Sheets中的可用数据创建图表。在这里,我们将使用 Chart.js 这是一个JavaScript库。我们基本上通过发布脚本来开发一个Web应用。

Web应用: 如果脚本包含函数 doGet(e)doPost(e) ,并且该函数必须返回HTML Service HtmlOutput 对象,那么该脚本可以被发布为Web应用。您将在下面讨论的步骤中学习如何将HTML文件与脚本链接起来。

在脚本编辑器中编写代码的步骤:

  • 首先创建一个新的Google Sheets。
  • 然后在菜单中点击【工具】选项卡, **工具 >脚本编辑器 **
  • 将会打开一个新窗口,如下所示:

使用Google Apps Script从Google Sheets创建图表

  • 您可以将您的代码插入到myFunction块的花括号之间。

让我们逐步了解实施过程。

步骤1: 准备您的Google表数据。我们拥有印度各邦Covid病例数量的数据,单位为十万,如下所示。因此,第一步是准备您的数据。

使用Google Apps Script从Google Sheets创建图表

步骤2: 将标准的Google Apps Script函数doGet(e)添加到code.gs文件中。下一步是进入工具并选择脚本编辑器。将其保存为“Chart”或任何其他名称。在这里我们保存为code.gs。同时创建一个新的HTML文件并保存为“webappchart.html”。

在code.gs文件中添加以下代码。

code.gs

function doGet(e) { 
  return HtmlService.createHtmlOutputFromFile('webappchart'); 
    
}
JavaScript

以上的函数是标准的Google Apps Script函数,用于发布/部署您的Web应用程序。此函数返回一个输出文件,即您的HTML文件。

现在让我们了解HTML文件和code.gs文件之间的关系:

  • HtmlService:
  • 此类用作从脚本返回HTML和其他文本内容的服务
  • createHtmlOutputFromFile(filename):
  • 上述函数从代码编辑器中的文件创建一个新的HtmlOutput对象。在我们的示例中,我们将文件命名为webappchart.html。所以我们正在使用返回HtmlOutput对象到我们的HTML页面的doGet(e)函数。文件名应以字符串形式给出。如果找不到文件,则该函数返回错误。

步骤3: 在您的HTML文件中添加所需的CDN。接下来打开您的HTML文件,即webappchart.html,然后我们将包含用于制作图表的jQuery和Chartjs的CDN。

<script src=”https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js”></script>   
<script src=”https://cdnjs.cloudflare.com/ajax/libs/Chart.js/3.4.1/chart.js” integrity=”sha512-lUsN5TEogpe12qeV8NF4cxlJJatTZ12jnx9WXkFXOy7yFbuHwYRTjmctvwbRIuZPhv+lpvy7Cm9o8T9e+9pTrg==” crossorigin=”anonymous” referrerpolicy=”no-referrer”>
HTML

步骤4: 为图表渲染创建画布。在下一步中,我们通过在body标签中的div元素中添加用于显示图表的canvas来完成我们的html代码,如下所示:

HTML

<div style='width:800px height:400px'> 
    <canvas id='barchart' class='chartjs-render-monitor'></canvas> 
</div>
JavaScript

HTML代码已经完成了,接下来我们添加脚本代码。

步骤5: 添加jQuery脚本代码。我们首先编写标准的jQuery方法,该方法在屏幕加载完整的HTML代码后调用,并在其中调用getCases()方法,我们将在下一步中定义该方法。

JavaScript

$(document).ready(function(){ 
      getcases(); 
    });
JavaScript

然后我们将getCases()函数定义如下:

Javascript

function getCases(){ 
  google.script.run.withSuccessHandler(function(ar){ 
    console.log(ar); 
    var data=[]; 
    var label=[]; 
    ar.forEach(function(item,index){ 
      data.push(item[1]); 
      label.push(item[0]); 
    }); 
  }); 
} 
JavaScript

在上面的代码中,我们调用了 Google Apps Script 的标准函数。

语法:

 google.script.run.withSuccessHandler(function(ar)){
}.getCases(); 
JavaScript

步骤6: 在code.gs文件中定义函数,返回从Google Sheet中检索到的数据。在这里调用的函数getCases在 code.gs 文件中如下所示。将下面的代码添加到code.gs文件中:

Javascript

function getCases(){ 
  var ss=SpreadsheetApp.getActiveSpreadsheet(); 
  var casesSheet=ss.getSheetByName('Sheet1'); 
  var getLastrow=casesSheet.getLastRow(); 
  
  return casesSheet.getRange(2,1,getLastrow-1,2).getValues(); 
} 
JavaScript

说明: 在上面的代码中,我们将活动电子表格设置为 ss ,将所需的工作表即“Sheet 1”设置为 casesSheet 。然后,我们获取最后一行并将其设置为 getLastrow 变量。最后,我们从上述给定的范围返回值,即从第二行开始等等。返回这里的是一个包含整个工作表值的数组,可以通过 ar 进行访问。然后,我们遍历数组并将第一个项目推入一个数组, label[] 将第二个项目推入另一个数组, data[] 。我们将使用这些数组来创建图表。

步骤7: 创建图表。现在数据已经准备好,我们创建一个柱状图,如下所示:

Javascript

var ctx=document.getElementById("barchart").getContext('2d'); 
    
var barchart=new Chart(ctx, { 
  type: "bar", 
  title:{ 
    display: true, 
    text: "covid cases in different states of India"
  }, 
  data : { 
    labels: label, 
    datasets: [{ 
      label: 'Covid cases in different states of India', 
      data: data, 
      backgroundColor: [ 
        'rgba(255, 99, 132, 0.2)', 
        'rgba(54, 162, 235, 0.2)', 
        'rgba(255, 206, 86, 0.2)', 
        'rgba(75, 192, 192, 0.2)', 
        'rgba(153, 102, 255, 0.2)', 
        'rgba(255, 159, 64, 0.2)', 
        'rgba(255, 99, 132, 0.2)', 
        'rgba(54, 162, 235, 0.2)', 
        'rgba(255, 206, 86, 0.2)', 
        'rgba(75, 192, 192, 0.2)', 
        'rgba(153, 102, 255, 0.2)', 
        'rgba(255, 159, 64, 0.2)'
      ], 
      borderColor: [ 
        'rgba(255,99,132,1)', 
        'rgba(54, 162, 235, 1)', 
        'rgba(255, 206, 86, 1)', 
        'rgba(75, 192, 192, 1)', 
        'rgba(153, 102, 255, 1)', 
        'rgba(255, 159, 64, 1)', 
        'rgba(255,99,132,1)', 
        'rgba(54, 162, 235, 1)', 
        'rgba(255, 206, 86, 1)', 
        'rgba(75, 192, 192, 1)', 
        'rgba(153, 102, 255, 1)', 
        'rgba(255, 159, 64, 1)'
      ], 
      borderWidth: 1 
    }] 
  }, 
     
  options: { 
    legend: {display: false}, 
    title: { 
      display: true, 
      text: "Covid Cases in India"
    } 
  } 
}) 
JavaScript

解释: 在上述代码中,我们将案例数分配给数据,并且标签只是我的情况下不同州的名称。然后,我们设置了条形图中所有条形的背景颜色和边框颜色。

步骤8:运行Web应用程序:

使用Google Apps Script从Google Sheets创建图表

现在让我们来看一下上述实现的完整代码。

code.gs

function doGet(e) { 
  return HtmlService.createHtmlOutputFromFile('webappchart'); 
    
} 
function getCases(){ 
  var ss=SpreadsheetApp.getActiveSpreadsheet(); 
  var casesSheet=ss.getSheetByName('Sheet1'); 
  var getLastrow=casesSheet.getLastRow(); 
  
  return casesSheet.getRange(2,1,getLastrow-1,2).getValues(); 
} 
JavaScript

index.html

<!DOCTYPE html> 
<html> 
  
<head> 
  <base target="_top"> 
  <script src= 
"https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"> 
  </script> 
  <script src= 
"https://cdnjs.cloudflare.com/ajax/libs/Chart.js/3.4.1/chart.js"
    integrity= 
"sha512-lUsN5TEogpe12qeV8NF4cxlJJatTZ12jnx9WXkFXOy7yFbuHwYRTjmctvwbRIuZPhv+lpvy7Cm9o8T9e+9pTrg==" 
    crossorigin=" anonymous" 
    referrerpolicy="no-referrer"> 
  </script> 
</head> 
  
<body> 
  <div style='width:800px; height:400px;'> 
    <canvas id='barchart' 
      class='chartjs-render-monitor'> 
    </canvas> 
  </div> 
  
  <script> 
    $(document).ready(function () { 
      getcases(); 
    }); 
      
    function getcases() { 
      google.script.run 
        .withSuccessHandler(function (ar) { 
          console.log(ar); 
          var data = []; 
          var label = []; 
            
          ar.forEach(function (item, index) { 
            data.push(item[1]); 
            label.push(item[0]); 
          }); 
  
          var ctx = document.getElementById( 
            "barchart").getContext("2d"); 
                  
          var barchart = new Chart(ctx, { 
            type: "bar", 
            title: { 
              display: true, 
              text: "covid cases in different states of India", 
            }, 
            data: { 
              labels: label, 
              datasets: [{ 
                label: "Covid cases in different states of India", 
                data: data, 
                backgroundColor: [ 
                  "rgba(255, 99, 132, 0.2)", 
                  "rgba(54, 162, 235, 0.2)", 
                  "rgba(255, 206, 86, 0.2)", 
                  "rgba(75, 192, 192, 0.2)", 
                  "rgba(153, 102, 255, 0.2)", 
                  "rgba(255, 159, 64, 0.2)", 
                  "rgba(255, 99, 132, 0.2)", 
                  "rgba(54, 162, 235, 0.2)", 
                  "rgba(255, 206, 86, 0.2)", 
                  "rgba(75, 192, 192, 0.2)", 
                  "rgba(153, 102, 255, 0.2)", 
                  "rgba(255, 159, 64, 0.2)", 
                ], 
                borderColor: [ 
                  "rgba(255,99,132,1)", 
                  "rgba(54, 162, 235, 1)", 
                  "rgba(255, 206, 86, 1)", 
                  "rgba(75, 192, 192, 1)", 
                  "rgba(153, 102, 255, 1)", 
                  "rgba(255, 159, 64, 1)", 
                  "rgba(255,99,132,1)", 
                  "rgba(54, 162, 235, 1)", 
                  "rgba(255, 206, 86, 1)", 
                  "rgba(75, 192, 192, 1)", 
                  "rgba(153, 102, 255, 1)", 
                  "rgba(255, 159, 64, 1)", 
                ], 
                borderWidth: 1, 
              }, 
            ], 
            }, 
  
            options: { 
              legend: { display: false }, 
              title: { 
                display: true, 
                text: "Covid Cases in India", 
              }, 
            }, 
          }); 
          }) 
          .getCases(); 
        } 
    </script> 
</body> 
  
</html> 
JavaScript

输出:

使用Google Apps Script从Google Sheets创建图表

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程

登录

注册