利用 Cloud Functions 函数自动将 CSV 数据导入 Google 表格

1. 简介

此 Codelab 的目标是让您了解如何编写 Cloud Function 来响应 CSV 文件上传到 Cloud Storage 的操作，读取其内容并使用 Sheets API 将其用于更新 Google 表格。

e9c78061022a6760.png

这可以看作是将原本需要手动执行的“以 CSV 格式导入”步骤自动化。这样可确保您在电子表格中分析数据（可能由其他团队生成）时，数据一经提供即可使用。

实现如下所示：

52df703605ae4bd3.png

2. 设置和要求

自定进度的环境设置

  1. 登录 Cloud 控制台，然后创建一个新项目或重复使用现有项目。 （如果您还没有 Gmail 或 G Suite 账号，则必须创建一个。）

dMbN6g9RawQj_VXCSYpdYncY-DbaRzr2GbnwoV7jFf1u3avxJtmGPmKpMYgiaMH-qu80a_NJ9p2IIXFppYk8x3wyymZXavjglNLJJhuXieCem56H30hwXtd8PvXGpXJO9gEUDu3cZw

ci9Oe6PgnbNuSYlMyvbXF1JdQyiHoEgnhl4PlV_MFagm2ppzhueRkqX4eLjJllZco_2zCp0V0bpTupUSKji9KkQyWqj11pqit1K1faS1V6aFxLGQdkuzGp4rsQTan7F01iePL5DtqQ

8-tA_Lheyo8SscAVKrGii2coplQp2_D1Iosb2ViABY0UUO1A8cimXUu6Wf1R9zJIRExL5OB2j946aIiFtyKTzxDcNnuznmR45vZ2HMoK3o67jxuoUJCAnqvEX6NgPGFjCVNgASc-lg

请记住项目 ID，它在所有 Google Cloud 项目中都是唯一的名称（上述名称已被占用，您无法使用，抱歉！）。它稍后将在此 Codelab 中被称为 PROJECT_ID。

  1. 接下来，您需要在 Cloud 控制台中启用结算功能，才能使用 Google Cloud 资源。

运行此 Codelab 应该不会产生太多的费用（如果有费用的话）。请务必按照“清理”部分中的所有说明操作，该部分介绍了如何关停资源，以免产生超出本教程范围的结算费用。Google Cloud 的新用户符合参与 $300 USD 免费试用计划的条件。

3. 创建和配置 Google 表格并启用 API

首先，我们来创建一个新的 Google 表格文档（此表格可以属于任何用户）。创建后，请记住其标识符；它将用作我们将编写的函数的环境变量：

dd77d5fc1364ad3e.png

在 GCP 控制台中，前往“API 和服务”部分，然后前往“API 库”部分，在新创建的项目中启用 Google Sheets API：

c64e2e98b8b55f16.png

在“IAM 和管理”部分，前往“服务账号”，然后记下 App Engine 默认服务账号的电子邮件地址。应采用 your-project-id@appspot.gserviceaccount.com 格式。当然，您也可以创建专门用于此操作的服务账号。

6e279d7e07d4febf.png

最后，只需使用“共享”按钮向此服务账号授予对您的电子表格的修改权限：

c334062465ddf928.png

完成此设置后，我们现在可以编写 Cloud Functions 函数，并将其配置为使用此服务账号。它将能够写入我们刚刚创建的这个电子表格文档。

4. 创建存储分区

我们来创建存储分区，以便我们的云函数监控其中是否有新的 CSV 文件。

在控制台中，使用左侧菜单前往“存储空间”... :

2ddcb54423979d25.png

... 并创建一个名为 csv2sheet-POSTFIX 的新存储分区（将 POSTFIX 替换为唯一值），并将所有其他设置设为默认值：

dd637080ade62e81.png

5. 创建 Cloud Functions 函数

现在，我们可以创建一个名为 csv2sheet 的 Cloud Functions 函数，该函数会在文件上传到特定 Cloud Storage 存储分区时触发。代码将使用内嵌编辑器直接在 Cloud 控制台中以 Node.js 8 编写，并使用 async 函数：

6ee1a5ce63174ae8.png

请务必将触发器设置为“Cloud Storage”，并将存储分区名称调整为在上一部分中创建的名称。

同时，将我们要编写的函数的入口点更新为 csv2sheet：

446e7c7c992c2d8a.png

现在，将函数正文更改为：

  1. 使用 Cloud Storage API 和 Sheets API
  2. 将 csv2sheet 函数标记为 async
  3. 从 Cloud Storage 事件元数据中获取 fileName，并为我们将要创建的新工作表派生一个名称：
const {google} = require("googleapis");
const {Storage} = require("@google-cloud/storage")

exports.csv2sheet = async (data, context) => {
  var fileName = data.name;
  // basic check that this is a *.csv file, etc...
  if (!fileName.endsWith(".csv")) {
    console.log("Not a .csv file, ignoring.");
    return;
  }
  // define name of new sheet  
  const sheetName = fileName.slice(0, -4);

  // TODO!
};

如我们稍后将看到的，此处必须使用 async 才能使用 await。

创建此函数时，有几个重要选项需要注意（点击屏幕底部的“更多”链接）：

  • 使用下拉菜单选择上文讨论的服务账号
  • 定义一个名为 SPREADSHEET_ID 的环境变量，该变量应与您之前创建的电子表格文档相匹配：

fd22d1873bcb8c66.png

作为最后的设置步骤，以下是 package.json 内容，其中 Cloud Storage 和 Google Sheet API 是我们将使用的两个依赖项（使用控制台的内嵌编辑器 PACKAGE.JSON 标签页）：

{
    "name": "csv2sheet",
    "version": "0.0.42",
    "dependencies": {
        "googleapis": "^51.0.0",
        "@google-cloud/storage": "^5.0.1"
    }
}

按照上述说明配置完所有内容后，点击“创建”！片刻之后，您的函数应该已创建并部署完毕。

6. 设置身份验证和 Sheets API

在内嵌编辑器中使用 Cloud Functions 函数编写任何进一步的代码之前，我们需要阻止创建具有适当 Storage 和 Sheet 范围的 Google Client API（请注意，这是 async 函数的一部分）。

在控制台的函数编辑器中，点击“修改”，然后将以下代码添加到 csv2sheet 函数的正文中：

// block on auth + getting the sheets API object
const auth = await google.auth.getClient({
  scopes: [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/devstorage.read_only"
  ]
});

然后，我们可以创建 Sheets API 客户端：

const sheetsAPI = google.sheets({version: 'v4', auth});

7. 使用 Sheets API 创建空白工作表

借助 Sheets API 客户端，我们可以在文档中创建一个简单的新工作表，但在继续之前，请先快速了解一下相关术语：

  • 电子表格是实际的文档，并通过其标识符（如上所述，可在文档网址中看到）进行引用
  • 工作表是文档中的一个标签页，可以通过其名称（标签页名称）或在创建工作表时生成的标识符来引用

考虑到这一点，以下函数使用 Sheets API 客户端在位置 2（通常在默认的“Sheet1”之后）创建一个空工作表，该工作表包含 26 列、2000 行，并且第一行处于冻结状态（使用内嵌编辑器将其添加到函数中）：

function addEmptySheet(sheetsAPI, sheetName) {
  return new Promise((resolve, reject) => {
    const emptySheetParams = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            addSheet: {
              properties: {
                title: sheetName,
                index: 1,
                gridProperties: {
                  rowCount: 2000,
                  columnCount: 26,
                  frozenRowCount: 1
                }
              }
            }
          }
        ]
      }
    };
    sheetsAPI.spreadsheets.batchUpdate( emptySheetParams, function(err, response) {
        if (err) {
          reject("The Sheets API returned an error: " + err);
        } else {
          const sheetId = response.data.replies[0].addSheet.properties.sheetId;
          console.log("Created empty sheet: " + sheetId);
          resolve(sheetId);
        }
      }
    );
  });
}

请注意，我们没有对电子表格的引用进行硬编码，而是依赖于之前创建的 SPREADSHEET_ID 环境变量。

我们需要记住 sheetId，以便进一步请求此特定工作表。此外，工作表名称必须是唯一的，如果已存在名为 sheetName 的工作表，则创建操作会失败。

Sheets API 中的 batchUpdate 函数是与文档互动的一种常用方式，此处对此进行了说明。

8. 从存储 CSV 文件读取数据

现在，我们已经有了一个可以转储数据的地方，接下来，我们将在内嵌编辑器中进一步开发云函数，并使用 Cloud Storage API 从刚刚上传的文件中获取实际数据，并将其存储在字符串中：

function readCSVContent(sheetsAPI, file, sheetName) {
  return new Promise((resolve, reject) => {
    const storage = new Storage();
    let fileContents = new Buffer('');
    storage.bucket(file.bucket).file(file.name).createReadStream()
    .on('error', function(err) {
      reject('The Storage API returned an error: ' + err);
    })
    .on('data', function(chunk) {
      fileContents = Buffer.concat([fileContents, chunk]);
    })  
    .on('end', function() {
      let content = fileContents.toString('utf8');
      console.log("CSV content read as string : " + content );
      resolve(content);
    });
  });
}

9. 填充新创建的工作表

现在，我们可以使用相同的 Google 表格客户端 API 和刚刚收集的数据来填充我们创建的表格了。我们还将借此机会为工作表的列添加一些样式（更改顶行的字号并将其设为粗体）：

function populateAndStyle(sheetsAPI, theData, sheetId) {
  return new Promise((resolve, reject) => {
    // Using 'batchUpdate' allows for multiple 'requests' to be sent in a single batch.
    // Populate the sheet referenced by its ID with the data received (a CSV string)
    // Style: set first row font size to 11 and to Bold. Exercise left for the reader: resize columns
    const dataAndStyle = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            pasteData: {
              coordinate: {
                sheetId: sheetId,
                rowIndex: 0,
                columnIndex: 0
              },
              data: theData,
              delimiter: ","
            }
          },
          {
            repeatCell: {
              range: {
                sheetId: sheetId,
                startRowIndex: 0,
                endRowIndex: 1
              },
              cell: {
                userEnteredFormat: {
                  textFormat: {
                    fontSize: 11,
                    bold: true
                  }
                }
              },
              fields: "userEnteredFormat(textFormat)"
            }
          }       
        ]
      }
    };
        
    sheetsAPI.spreadsheets.batchUpdate(dataAndStyle, function(err, response) {
      if (err) {
        reject("The Sheets API returned an error: " + err);
      } else {
        console.log(sheetId + " sheet populated with " + theData.length + " rows and column style set.");
        resolve();
      }
    });    
  });
}

此代码应添加到我们的 Cloud 函数中，该函数现在已完成 99%！

请注意，数据和样式是如何作为多个 requests 合并到单个 Sheets API batchUpdate 调用中的。这样可以实现更高效、更具原子性的更新。

另请注意，我们定义的编辑范围与已创建的工作表的大小一致。这意味着，如果内容超过 26 列（创建工作表时使用的 columnCount 值），则会因出现此特定代码而失败。

如果一切顺利，您现在可以执行以下操作：

  1. 保存更新后的函数
  2. 将 CSV 文件放入存储分区中
  3. 您会在电子表格中看到相应的数据弹出！

10. 将所有内容整合在一起并测试流程

对我们刚刚讨论的函数的调用可以在原始 csv2sheet 函数中作为连续的阻塞调用进行：

  const sheetId = await addEmptySheet(sheetsAPI, sheetName);
  const theData = await readCSVContent(sheetsAPI, data, sheetName);
  await populateAndStyle(sheetsAPI, theData, sheetId);

如果您需要完整的函数源代码，请点击此处获取（可能更容易一次性获取所有代码）。

一切就绪后，只需将 CSV 文件上传到正确的存储分区，即可看到电子表格更新为包含文件内容的新工作表。如果您没有现成的 CSV 文件，请参阅示例 CSV 文件。

1efae021942e64fa.png

尝试向存储分区上传多个文件，看看会发生什么情况！

11. 大功告成！拆除基础架构的时间

开个玩笑，无需拆除任何基础架构，因为这一切都是以无服务器方式完成的！

您可以根据需要删除已创建的 Cloud Functions 函数和存储分区，甚至删除整个项目。

12. 接下来做什么？

本 Codelab 至此结束，其中介绍了在 Cloud Functions 函数中监听 Cloud Storage 存储分区的上传事件，以便使用适当的 API 更新 Google 表格的步骤。

下面是一些后续步骤：

如果您在本 Codelab 中遇到任何问题，欢迎随时使用左下角的链接报告相应问题。

期待您的反馈！