从 Google 表格获取 JavaScript 对象数组

• 下载源代码 - 329.9 KB

引言

有时，您可能需要为网页提供数据以填充报告网页等，这些数据无法直接从数据库中获取，但您仍然希望拥有某种程度的“实时”数据。

在这种情况下，您可以将数据“发布”到 Google 表格，然后从那里获取数据。从那时起，就可以非常轻松地“异步”读取数据，并将信息网格转换为 JavaScript 对象数组。

Google 表格 API 很容易获取信息，并且有很多包装库可为程序员提供便捷的选择。然而，这些有时会提供比您实际需要的功能更多，并且有自己的学习曲线需要克服。

本文解决了在 Google 表格中批量加载代表数据对象列表的“网格”数据的基本需求。它将通过在 ES5 风格的“模块”中提供一个单一方法来实现。您可以在随附的源代码文件“dalGoogleSheetReader.js”中找到它。

背景

有时，需要在后端系统完成必要工作之前构建应用程序前端的元素。在创建概念验证应用程序时尤其如此，这些应用程序通常没有意愿（或预算！）来介入后端数据库管理员。

您始终可以选择将一些示例数据（JSON 文件等）硬编码到这些内容中，但这即使对于原型/PoC 应用程序来说也是一种浪费的努力。

您希望处理的数据通常已经存在于各种旧应用程序中，并且几乎总有一种方法可以将这些应用程序的提取数据放入 Excel 表格或类似文件中，然后从那里，就可以非常轻松地将其加载到 Google 表格中。

届时，您几乎就可以将数据拉入您的项目，并使用“真实”数据向人们展示您的进度。这样，当您的用户社区看到它时，他们就可以专注于您的应用程序正在做什么，而不是那些伪造的记录。

所需材料

要使用该库，您需要能够获取和使用数据，需要以下四项：

1. Google 表格标识符 - 表格必须可以通过共享链接（或公开）访问
2. API 密钥 - 在 Google 开发者 控制台中生成
3. 表格中数据的“范围”列表 - 例如：['Sheet1!A1:D', 'Sheet2:B2:F6', ...]
4. 一个“回调”函数，用于接收一个由指定范围（在 (3) 中）中的数据对象填充的数组列表

一旦您拥有了这些，您只需要写一行代码！

simpleGoogleSheetUtility.googleSheetBatchDataReaderAsPromise(sheetId, key, aRanges).then(
  fCallbackExpectingAnArrayOfArraysHoldingJSObjects
)

显然，幕后进行了大量工作，我将在下面讨论。但在此之前，让我们来看看我构建的一个使用它的示例网页。您可以在随附的存档中找到它。

示例文件编写得可以与文件协议一起使用，因此您只需解压 zip 文件，然后使用“现代”浏览器打开 index.html 文件即可。（代码使用“一些”现代 ES2015 语法，因此不进行转译将无法与 IE11 一起使用！）

该示例有两个使用该实用程序的区域。

1. 获取数据

   

2. 实践示例

   

库如何使用 Google 表格

两个示例都使用了此链接处的 Google 表格。

传递给函数的第三个参数是一个 string 数组（A1 范围规范），用于标识 Google 表格中的数据网格，如下所示：

上图显示了“Student!A1:C”的网格。

Google 表格 API 采用此类范围规范，并返回一个包含二维字符串数组的“网格”的 JSON 对象。您将获得类似以下的内容：

{
  "spreadsheetId": "1lH1TL9T1Ad_9irqkQVadrIkVdA_7Nkpnfn7CAUINY04",
  "valueRanges": [
    {
      "range": "Student!A1:C14",
      "majorDimension": "ROWS",
      "values": [
        [
          "Person-Id",
          "Forename",
          "Lastname"
        ],
        [
          "AA",
          "Andrew",
          "Alpha"
        ],
. . . .
     ]
  }
}

但是，这并不是我们想处理的！

信息在这里，但我们想要的是将上面的内容转换为类似以下的内容：

[
  [
    { "Person-Id": "AA", "Forename": "Andrew", "Lastname": "Alpha" },
    { "Person-Id": "BB", "Forename": "Belinda", "Lastname": "Beta" },      
// More objects . . . . 
  ],
// Additional arrays of objects
]

这种结构的优点是，您可以编写代码通过属性名称引用“记录”中的信息，而不是某个索引偏移量，因为后者很容易因在表格数据中插入列而损坏。

转换表格数据

在我们检查 JavaScript 函数及其实现的具体细节之前，我认为值得展示转换过程的伪代码。

create a "result array list"
for each "valueRange" from google-sheet
  extract the 2D array in "values"

  use the 1st row as a property name map

  for each subsequent row
    create an object using the map of property names and the cells as values 
    add the object to an array
add the array of objects to the "result array list"    
return the "result array list" as a "Resolved Promise"

现在是真实的代码！

理解模块代码

命名空间“simpleGoogleSheetUtility”和方法“googleSheetBatchDataReaderAsPromise”总共只有 67 行代码，包括宽松的注释，但我不会在这里逐行介绍。那会很无聊，您可以在下载中访问代码，所以请在您喜欢的编辑器中打开它，那里您将获得所有漂亮的语法着色，您已经习惯了！我在这里将专注于我正在使用的关键概念。

揭示模块模式

首先要指出的是，我使用的命名空间 simpleGoogleSheetUtility 实际上只是“揭示模块模式”的一种实现。这是相当知名的，互联网上有很多教程可供您参考。但是，为了省去您跳转到首选搜索引擎查找文章的麻烦，我为您找到了一篇：揭示模块模式

这个特定实现的精髓是：

var simpleGoogleSheetUtility = (function () { // eslint-disable-line no-unused-vars
  'use strict'
  /* globals fetch R */
  // 60 lines of code here

  return {
    googleSheetBatchDataReaderAsPromise: googleSheetBatchDataReaderAsPromise
  }
})()

正如您所看到的，我在模块中公开的唯一“方法”是 googleSheetBatchDataReaderAsPromise。

对于我使用的冗长名称，我毫不道歉。我以前读过 Robert C. Martin 的书“Clean Code”，并且（大部分）同意其中包含的建议。基本上，我喜欢这样一个事实：几个月后，当我再次查看使用此模块的代码时，我知道从该方法中获得的是一个“Promise”。因此，当您在应用程序中使用它时，您就知道需要编写类似的代码：

simpleGoogleSheetUtility.googleSheetBatchDataReaderAsPromise(
  sheetId,
  apiKey,
  ['Sheet1!A1:C', 'Sheet2!B2:F6']).then(
    function (aObjectLists) {
      console.log(`There are ${aObjectLists.length} list of objects from the google sheet.`)
    }
  )

googleSheetBatchDataReaderAsPromise 函数

该函数实际的工作是调用 Google REST API 端点。这将是一个异步操作。使用此类端点的代码可能会变得非常“嵌套”，特别是如果您有几个必须按顺序执行的操作。为了使代码清晰，我诉诸于现代 ES2015 语法糖 async 和 await。

函数本身声明如下：

async function googleSheetBatchDataReaderAsPromise (sheetId, apiKey, aRanges)

这实际上意味着“无论”从函数返回什么都将被包装在一个 Promise 对象中。它还意味着在函数内部，我们可以使用 await 关键字。这将使我们编写的异步代码看起来像普通的同步代码。它阻止我们不得不将 then 函数“嵌套”成难以阅读的复杂代码金字塔。

三个输入参数用于构建 URL。我将不描述这部分代码，这只是 string 操作。在示例应用程序中，这最初被调用并构建以下 URL：

https://sheets.googleapis.com/v4/spreadsheets/1lH1TL9T1Ad_9irqkQVadrIkVdA_7Nkpnfn7CAUINY04/values:batchGet?key=AIzaSyARrbqEv9KsydppJNtIjk5ndmw0dfJ7dV8&ranges=Student!A1:C&ranges=Class!A1:C&ranges=Student_Class!A1:I

有趣的部分在于对 fetch 函数的调用。fetch 在现代浏览器中可用，它本质上是旧的 XmlHttpRequest 类型服务的标准化包装器（或者更可能是 jQuery 的 $.ajax 方法！）。

const fetchOptions = {
  method: 'GET',
  mode: 'cors',
  cache: 'default'
}

let response = await fetch(url, fetchOptions)
let oSheetBatchJson = await response.json()

有趣的是 fetch 是一个异步函数。与一大堆嵌套的 thenable 函数调用相比，这使得代码更容易阅读和理解。

在将数据获取到 oSheetBatchJson 后，我们只需要将其转换为所需的格式并将其作为“已解决的 Promise”返回。

return oSheetBatchJson.valueRanges.map(googleSheetConvertValueRangeToObjectArray)

函数 googleSheetConvertValueRangeToObjectArray 对我们的模块是“私有”的。它的唯一目的是将 Google 表格格式的批量数据项转换为我们所需的、对象数组。

  function googleSheetConvertValueRangeToObjectArray (valueRange) {
    const aValues = valueRange.values
    const aHeaders = R.compose(
      R.map(prop => R.trim(prop)),
      R.head
    )(aValues)

    const fnTransformArrayPairToObject = function (aProperty, aValues) {
      let ret = {}
      // NOTE: The G Sheets API will return "ragged" arrays of strings. It is necessary
      //     : to check both input arrays are long enough to make properties from.

      for (let i = 0; i < aProperty.length && i < aValues.length; i++) {
        ret[aProperty[i]] = R.trim(aValues[i])
      }
      return ret
    }

    // Since aHeaders is not going to change we can curry the transformer
    const fnTransformArrayRowToObject = R.curry(fnTransformArrayPairToObject)(aHeaders)

    let aObjects = R.compose(
      R.map(fnTransformArrayRowToObject),
      R.tail
    )(aValues)

    return aObjects
  }

到目前为止，我还没有提到这个库实际上有一个依赖项：Ramda。

Ramda - 函数式库

Ramda 库包含大量有用的辅助函数，可以极大地简化对象和列表的处理。我不想纠缠于函数式编程的细节，这不是本文和实用库的目的，但重要的是您要知道它需要它。而且，我远不是该主题的专家，只是一个因为 Ramda 有效且清晰而使用它的学生。

该函数针对 oSheetBatchJson 中的每个 valueRange 条目进行调用。其中一个的内容如上所示。

我们首先要做的是提取值的二维数组。

我们期望第一“行”包含属性名称列表。考虑到这些数据来自 Google 表格，我不想假设标题名称格式正确，因此我想修剪文本末尾的任何空格。因此，我使用了一个小的 Ramda 组合：

const aHeaders = R.compose(
   R.map(prop => R.trim(prop)),
   R.head
   )(aValues)

Ramda 的 compose 函数通过从右到左（或从下到上，如下图所示）将某些“数据”通过一系列函数进行传递来工作。

关于 Ramda 函数最重要的事情是它们“永不”改变输入数据。因此，从右到左读取，这里发生的是：

二维数组传递给 R.head，它只提取第一行，并将其传递给 R.map，在本例中，R.map 逐个处理输入数组的项，并使用一个函数。这里的映射函数是：

function (prop) { return R.trim(prop) }

最后，映射的结果被传递给 aHeaders。

简而言之，我们从 aValues

[
  [" a ", "b", "c ", " d"],
  ["a one", "B 1", "C1", "1D"],
  ["a two", "B 2", "C2", "2D"],
  ...
]

到 aHeaders

["a", "b", "c", "d"]

重要的是，aValues 没有改变！

下一步是创建一个新函数，该函数接受两个值数组并将它们组成一个对象：

const fnTransformArrayPairToObject = function (aProperty, aValues) {
  let ret = {}
  // NOTE: The G Sheets API will return "ragged" arrays of strings. It is necessary 
  // : to check both input arrays are long enough to make properties from.
  for (let i = 0; i < aProperty.length && i < aValues.length; <code>i</code>++) {
    ret[aProperty[i]] = R.trim(aValues[i])
  }
  return ret
}

当涉及到将此“转换”函数应用于所有数据时，有一个有用的函数式工具称为“柯里化”，即可以将一个函数包装在另一个函数中，其中一些参数已预先传递。

// Since aHeaders is not going to change we can curry the transformer
const fnTransformArrayRowToObject = R.curry(fnTransformArrayPairToObject)(aHeaders) 

最后，这个转换器的“柯里化”版本用于另一个组合来处理数据行：

let aObjects = R.compose(
  R.map(fnTransformArrayRowToObject),
  R.tail
)(aValues)

return aObjects

同样，我们从 aValues 开始，其中删除了标题行（R.tail），然后将剩余的行通过 map 函数，该函数应用柯里化的 fnTransformArrayRowToObject 函数，最终得到一个对象数组。

这就是使用实用库所需知道的一切。只需一行代码和一个 .then 处理程序，您就可以使用对象数据数组了！

Google 表格数据注意事项

虽然可以像这样轻松地从 Google 表格获取数据，但有一些注意事项您应该了解！

1. 不规则数组
2. 格式化数据

不规则数据

Google 表格 API 试图通过忽略行中末尾的空数据来减少不必要的内容。

例如，如果您有一个这样的网格：

ID	值	注释
1	23.3
2	123	Expensive

您将获得的数据数组是：

[
  ["id","Value","Comment"],
  ["1", "23.3"], // This row has only 2 entries - It is ragged
  ["2", "123", "Expensive"]
]

这本身并不是问题，但如果您需要这样的对象……

{"id": "1", "value": "23.3", "Comment": ""} instead of {"id": "1", "value": "23.3"}

……那么您将不得不处理对象列表或更改表格中的输入数据。

请注意，在执行后者时，我喜欢的技巧是使用 Google 表格数组公式生成一列无用的“1”值或类似内容，位于网格的右侧。这样，API 就会给您类似这样的结果：

[ 
  ["id","Value", "Comment",   "Pointless"], 
  ["1", "23.3",  "",          "1"],
  ["2", "123",   "Expensive", "1"] 
]

格式化数据

默认情况下，您在 Google 表格中看到的内容就是您的数据内容。如果您有特定的数字格式（货币等），您必须预期这些会出现在您的数据中！

例如，如果您有一个这样的网格：

ID	值	注释
1	£23.30
2	£123.00	Expensive

您将获得的数据数组是：

[
  ["id","Value","Comment"],
  ["1", "£23.30"], 
  ["2", "£123.00", "Expensive"]
]

而且，您获得的所有数据都是文本！如果您想要其他内容，则必须进一步处理结果。

可以通过传递给 fetch() 的选项来更改此行为，但这是默认行为。

请参阅 方法：spreadsheets.values.batchGet

创建特定数据结构

在示例应用程序中，“实践”示例展示了 Highcharts 库中的甘特图。此库需要一组数据，这些数据需要“开始”和“结束”时间代码才能显示活动条。问题在于，传递的是文本字符串，如“yyyy-mm-dd”。

转换此类数据并不难，但您确实需要注意库会为您提供格式化的“string”。

由于我们已经在使用 Ramda 来处理列表，因此示例甘特图加载器也使用了它。

如果您查看下载中的示例，文件 index.js 在第 66 行使用了实用库：

let chartConfig = { .....}
simpleGoogleSheetUtility.googleSheetBatchDataReaderAsPromise(sheetId, key, aRanges).then(
  function (o) {
    // THE CHART
    let aData = o[0] //First (and only) object array
    // The data from the googleapis will be {string}, but Highcharts requires 
    // the start and end to be Unix like time code
    // We transform the data using Ramda evolve function on each item "date" property

    let fnStringDateToTimecode = s => new Date(s).getTime()

    let oTransformMap = { start: fnStringDateToTimecode, end: fnStringDateToTimecode }
    let fnConvertData = R.curry(R.evolve)(oTransformMap)
    aData = aData.map(fnConvertData)
    chartConfig.series[0].data = aData
    Highcharts.ganttChart('divGantt', chartConfig)
  }
)

Ramda 在此提供的关键帮助是其 R.evolve 函数（请参阅 https://ramdajs.com/docs/#evolve）。

此函数接受两个参数：一个将属性名称映射到转换函数的对象，以及要转换的某个对象。

在这种情况下，我们构建了一个转换对象，其中“start”和“end”属性将使用转换函数 fnStringDateToTimecode。此函数将一个可以解析为底层时间码的“string”日期转换为时间码。

由于我们要处理一个对象数组，我们可以使用内置的 Array map 函数来“演化”每个对象。但是，R.evolve 需要两个参数，第二个是数据。因此，我们使用 R.curry 将 evolve 函数包装成一个单参数函数，其中已应用了转换对象。因此，只需五行有效代码，我们就将一个由“string”组成的对象数组转换为了 Highcharts Gantt 可以使用的数据！

希望您能看到这对于模拟您的下一个应用程序并获得构建真正酷的东西的批准有多么有用！

历史

• 2020年5月5日：初始版本