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

1. 简介

此 Codelab 的目标是帮助您了解如何编写 Cloud Functions 函数，对上传到 Cloud Storage 的 CSV 文件做出响应、读取其内容，并使用 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

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

dd77d5fc1364ad3e.png

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

c64e2e98b8b55f16.png

在“IAM &管理员”部分，导航至“服务账号”并记下 App Engine 默认服务账号的电子邮件。其格式应为 your-project-id@appspot.gserviceaccount.com。当然，您也可以自行创建专用于此操作的服务账号。

6e279d7e07d4febf.png

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

c334062465ddf928.png

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

4. 创建存储分区

我们来创建一个存储分区，我们的 Cloud Functions 函数将在该存储分区中监控新的 CSV 文件。

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

2ddcb54423979d25

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

dd637080ade62e81.png

5. 创建 Cloud Functions 函数

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

6ee1a5ce63174ae8

确保将触发器设置为“Cloud Storage”将存储分区名称调整为您在上一步中创建的名称。

另外，更新我们即将写入 csv2sheet 的函数的入口点：

446e7c7c992c2d8a

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

  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 表格 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 函数中编写任何其他代码之前，我们需要阻止创建具有适当存储空间范围和表格范围（请注意，这是 async 函数的一部分）的 Google Client API。

在控制台的函数编辑器中，点击“修改”并将以下代码添加到 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"
  ]
});

然后，我们可以创建 Google 表格 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 Functions 函数，并使用 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. 填充新创建的工作表

现在，您可以填充我们使用同一表格客户端 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 Functions 函数中，现已完成 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 文件。

1efae021942e64fa.png

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

11. 大功告成！现在该清理基础架构了

开个玩笑，没有需要拆除的基础架构，这一切都是在无服务器模式下完成的！

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

12. 接下来做什么？

此 Codelab 到此结束，逐步引导您通过 Cloud Functions 函数监听上传到 Cloud Storage 存储分区的操作，以便使用适当的 API 更新 Google 表格。

下面是一些后续步骤：

如果您在使用此 Codelab 时遇到问题，请随时使用左下角的链接报告任何问题。

非常感谢您的反馈！