使用Google App Script和Google Sheet自动生成数据仪表盘

本文中提供了一种相对简单而又灵活的方式来创建自动化仪表盘。这个方法使得KPI的变更、更新和扩展变得更加轻松。

仪表盘是将数据内容有效地传达给团队的方法之一。举例来说,仪表盘可以用来跟踪关键性能指标(KPI)的进度。在Lucid,有一个KPI就是我们的产品在第三方市场中的排名和表现。虽然已经有企业级的产品来帮助我们收集和可视化这种类型的数据,但是你也可以选择只使用Google App Script和Google Sheet来生成自动化的仪表盘。我们使用这种方法来跟踪我们的应用程序在Atlassian Marketplace中的表现,这项技术也可以与很多公共API搭配使用,比如:

  • Github
  • Google(借助Google Play或者Chrome Webstore中的非官方的第三方API)
  • iTunes

让我们通过一个实际中的场景进行讲解。假设你的团队最近在Github上发布了一个开源项目。你想要追踪一些KPI指标:stargazers,订阅者以及开放的issue。这些指标可以让你深入了解这个项目的受欢迎程度以及其解决出现的issue的能力强弱。所以你想创建一个仪表盘来跟踪这些KPI并进行直观的展示。

从表面上来看这是一个简单的任务,但是在使用仪表盘前需要记住以下两点:

  1. 保持数据的易用性。
  2. 保持数据的时效性,无需繁琐的数据输入就可以使数据保持最新的状态。

上面的第一点已经在我的队友发布的如何使用Google Sheet制作杀手级的数据仪表盘一文中得到了解决。这周我们专注于利用Google App Script来实现仪表盘数据的自动更新。

我保存了最终的电子表格以供读者参考。注意:2017年12月13号之前的数据是模拟数据。

步骤1: 熟悉你将访问的API

你可以通过上面提到的三个公共API收集到大量的数据。花一些时间来熟悉你将要使用的API是很有必要的。下面概述的方法非常灵活,但是在某些类型数据或者数据量很大的情况下,使用专用软件可能是更佳的选择。在我们上面假设的场景中,我们可以通过Github API的REST URL来轻松地获取我们需要追踪的数据:

https://api.github.com/:owner/:repo

该请求的响应包括stargazers、订阅者、开放的issue的数量以及更多可以被跟踪监控的信息。在本文中,我们将关注用户lucidsoftware的两个项目:xtractrelate

步骤2:创建Google App Script从API拉取数据

Google App Script 是一门基于JavaScript的语言,你可以用它来对Google Sheets(以及其他Google套件)进行操作,你可以从菜单中的 工具 > 脚本编辑器来访问它。

首先让我们创建一个函数来向Github的API发送请求。下面给出的代码片段通过访问Github的API获取到了xtract的stargazers数目并将值填充到A2单元格当中。后面我们会在其基础上进行扩展。

function updateGithubSheet(){
  // Make a request to GitHub's rest API and get the number of star gazers
  var restUrl = 'https://api.github.com/repos/lucidsoftware/xtract';
  var data = makeJsonRequest(restUrl)
  var numStarGazers = data['stargazers_count'];
  // Write to cell A1 of the active sheet
  var activeSheet = SpreadsheetApp.getActiveSheet();
  activeSheet.getRange("A2").setValue(numStarGazers);
}
function makeJsonRequest(url){
  var response = UrlFetchApp.fetch(url);
  return JSON.parse(response.getAs('application/json').getDataAsString());
}

现在我们可以从Github上获取数据了!这看起来不错,但是不是很有用。下面我们将创建updateGithubSheet方法,每当这个方法被调用时都会创建新的一行,该行的第一列会填充相应的时间。

function updateGithubSheet(){
  ...
  var activeSheet = SpreadsheetApp.getActiveSheet();
  addRow(activeSheet, numStarGazers);
}
function addRow(sheet, numStarGazers){
  var lastRow = sheet.getLastRow();
  var nextRow = lastRow + 1;
  var column = 1;
  sheet.getRange(nextRow, 1).setValue(new Date());
  sheet.getRange(nextRow, column).setValue(numStarGazers);
}

这段脚本现在比之前更有用,但是还不具有泛用性。就像一个优秀的开发者会写出可重用的代码一样,一个优秀的电子表格制作者会制作出可重用的电子表格。改进我们上面的电子表格和脚本的一个方法是通过模板表格模式(Template Sheet Pattern)来定义用户、项目名以及感兴趣的字段。

模板表格模式

模板表格模式包含两部分内容:

  1. 模板表格会将合约(Contract)中指定位置的单元格中的信息提供给脚本进行相应操作。
  2. 脚本会根据合约来更新模板表格。

虽然实现上面的模式依赖的概念很简单,但它是重用电子表格和代码有效的方法之一。在我们讨论完本文中使用的合约之后,我们会再次回顾并佐证这一点。

合约(Contract)

首先我们需要一种方法来声明一个电子表格是遵守某个合约。其中一个方法是在A1单元格中指定一个键值。举例来说,如果键值的内容为Github,意味着我们会向Github的API发送请求并存储指定字段的值。下面给出本教程中我们做出的合约。

Contract: GITHUB
The Sheet will:
Have the Github Username in field B2.
Have the Github Repository in field B3.
Have row 4 starting at column B contain the Field Names in the response JSON. The Field Names terminate on the first column with an empty row 4.
The Script will:
Add one row after the last row on the sheet.
The new row will have the timestamp in column 1 followed by the Field Values corresponding to the Field Names from row 4.

我们将根据合约来获取xtract项目中我们所需的三个KPI指标

如你所见,如果你想添加第四个指标(如fork数),你只需要在E4单元格中填入相应的字段即可。相似地,如果你需要跟踪一个新的项目的KPI,你只需要复制这份表格并修改表格中项目的名称即可。建立的合约使追踪新的API变得更加容易。

在编写代码适配这一合约之前,让我们先创建一个简单的方法来扫描当前表格遵守的合约名称。下面的方法会将所有键值为Github的电子表格路由至updateGithubSheet方法,如果你创建了额外的合约,你也可以参照以下代码进行路由。

function updateAllSpreadsheets(){
 // Go through each spreadsheet and check for the key
  SpreadsheetApp.getActive().getSheets().map(function (sheet){
    var sheetKey = sheet.getRange("A1").getValue();
    if (sheetKey === "GITHUB"){
      updateGithubSheet(sheet);
    }
    // Route to other keys here
  });
}

合约的适配

下面让我们修改之前的updateGithubSheet方法来适配Github的合约。首先,我们需要根据模板表格来动态生成所需的REST URL。

function updateGithubSheet(sheet){
  // Make a request to GitHub's rest API
  var userName = sheet.getRange("B2").getValue();
  var repoName = sheet.getRange("B3").getValue();
  var restUrl = 'https://api.github.com/repos/' + userName + "/" + repoName;
  var data = makeJsonRequest(restUrl)
  ...
}

然后创建函数来获取合约中第四行指定的所有字段名。这部分的结果是完全依赖于合约的内容的,我们约定当遇到空白的单元格时就认为已经获取了所有需要的字段名。当然,你也可以通过检查输入等手段使这个方法具有更强的鲁棒性,但是出于教程的目的,这里我们假设所有获取到的值都是严格遵守合约的。

function updateGithubSheet(sheet){
  ...
  var data = makeJsonRequest(restUrl)
  var fieldValues = getFieldValues(sheet, 4, 2, data);
  ...
}
function getFieldValues(sheet, fieldRow, startColumn, data){
  var fieldValues = [];
  var offset = 0;
  while(!sheet.getRange(fieldRow,startColumn + offset).isBlank()){
    var fieldKey = sheet.getRange(fieldRow, startColumn + offset).getValue();
    fieldValues[offset] = data[fieldKey];
    offset = offset + 1;
  }
  return fieldValues;
}

最后,我们需要更新方法来将根据指定字段名获取到的字段值数组添加至模板表格。

function updateGithubSheet(sheet){
  …
  var fieldValues = getFieldValues(sheet, 4, 2, data);
  addRow(sheet, fieldValues, 2);
}
function addRow(sheet, fieldValuesArray, startColumn){
  var lastRow = sheet.getLastRow();
  var nextRow = lastRow + 1;
  sheet.getRange(nextRow, 1).setValue(new Date());
  fieldValuesArray.forEach(function(fieldValue, offset) {
    sheet.getRange(nextRow, startColumn + offset).setValue(fieldValue);
  });
}

注意:这个合约只允许你在root的层级基础上对元素进行访问。你也可以根据REST URL的响应自定义合约来从不同的层级进行访问。本文中给出的合约是非常基础的。

现在updateGithubSheet方法运行时会根据GITHUB这一键值和模板表格中指定的字段名来对模板表格进行更新。

以上模式为收集数据提供了更灵活,复用性更强的方法,这大大减少了我们追踪新的字段以及源时所需的重复性操作。

步骤3:设置一个自动触发器来拉取数据

脚本的自动化可以通过一个触发器周期性地去执行改脚本来实现。创建触发器只需在脚本编辑器的工具栏中点击以下按钮:

在本文的场景下,我们设定一个每天触发一次的触发器即可。当设定触发器时,一定要注意API的请求速率限制——如果你设置的触发器访问过于频繁(比如每分钟执行一次),那么很可能会超出速率限制。

步骤4:对数据进行格式化和可视化

现在我们已经有了一个自动更新的数据集,下一步我们需要对数据进行格式化。电子表格设计的一个重要原则是保持逻辑和数据的分离。根据这一原则,我们将创建一个Summary选项卡来对数据进行格式化以供展示。

尽管我们是以天为单位来从Github获取信息,但可能以月为单位进行数据展示是更有意义的。我们将在Summary页面创建公式来计算每月的数据点位置并根据这些数据来绘制仪表盘(你也可以通过均值或者其他的方法来聚合数据)。下面的公式给出了一种汇总数据的方案(你也可以使用Google的query function做到这一点)。

=IFERROR(
 FILTER(XTRACT_SUBSCRIBERS_COLUMN,
   ROW(XTRACT_SUBSCRIBERS_COLUMN) = 
     MAX(FILTER(ROW(XTRACT_SUBSCRIBERS_COLUMN),
          ISNUMBER(XTRACT_SUBSCRIBERS_COLUMN),
          XTRACT_DATES_COLUMN<FIRST_DAY_OF_NEXT_MONTH, XTRACT_DATES_COLUMN>=FIRST_DAY_OF_MONTH
        )))
, IF(FIRST_XTRACT_DATE>=FIRST_MONTH,0,))

这个公式由两个主要部分构成。MAX函数中的表达式在xtract表中寻找某月第一天和下一个月第一天之间的数据并完成相应的计算。xtract的SUBSCRIBE值对应的就是前面获取到的数组中的最大值,如果某月范围内没有值,那么就会执行IF语句并在相应的位置填充0或者保持空值。

最后,我们可以根据格式化的数据创建得到仪表盘。其中的细节和技巧可以查阅 How to Make a Killer Data Dashboard with Google Sheets 。下面的仪表盘就是根据该文中的原则创建的。展示的开放issue数据是以两个项目叠加生成的面积图展示的,这有助于展示两个项目中一共有多少个问题等待解决。而将start和订阅的数据分开展示有助于显示两个项目KPI的值以及比例关系。

自动化跟踪KPI的仪表盘

结论

本文提供了一种相对简单而又灵活的方式来创建自动化仪表盘。这个方法需要在起始时创建相应的合约,但是这使得KPI的变更、更新和扩展变得更加轻松。

本文的版权归 ArrayZoneYour 所有,如需转载请联系作者。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏美团技术团队

美团外卖Android Crash治理之路

Crash率是衡量一个App好坏的重要指标之一。如果你忽略了它的存在,它就会得寸进尺,愈演愈烈,最后造成大量用户的流失,进而给公司带来无法估量的损失。本文讲述美...

2012
来自专栏比原链

剥开比原看代码14:比原的挖矿流程是什么样的?

Gitee地址:https://gitee.com/BytomBlockchain/bytom

932
来自专栏施炯的IoT开发专栏

微软移动 Nokia Lumia SensorCore SDK 介绍及上手体验

    早在今年的BUILD大会上,诺基亚就宣布了SensorCore以及它的部分演示。今天,它终于面世了,大家可以去Building Apps for Win...

2767
来自专栏LET

CPU Cache简介

真空中光速为299,792,458米/秒,目前,Intel的i7频率可以达到4GHz,简单换算一下,可以得出结论:光(电流)在一个Cycle内移动的距离约为0....

1662
来自专栏Java技术栈

分享18道Java基础面试笔试题(面试实拍)

上图来自Java技术栈微信群里的群友分享,看起来比较基础,但不一定人人都答得上来。 图片比较模糊,小编把题目进行了文字化。 1.你最常上的两个技术站和最常使用的...

46611
来自专栏xdecode

后端架构师技术图谱

转自: GitHub/architect-awesome , 大体结构如下(更新时间: 2018-06-22)

1.2K6
来自专栏FreeBuf

逆向华为路由器第三部分

引文 在前面两个部分(1,2)已经介绍了UART,BusyBox等部分的逆向调试,而这篇将会开始在流量分析方面下手,来逆向出更多的信息。 正文 请看下图,数据存...

2038
来自专栏晨星先生的自留地

MS17010之批量的一些思路

5135
来自专栏Kirito的技术分享

天池中间件大赛百万队列存储设计总结【复赛】

维持了 20 天的复赛终于告一段落了,国际惯例先说结果,复赛结果不太理想,一度从第 10 名掉到了最后的第 36 名,主要是写入的优化卡了 5 天,一直没有进展...

2123
来自专栏java技术学习之道

面试必看!2018年4月份阿里最新的java程序员面试题目

3704

扫码关注云+社区

领取腾讯云代金券