這個 Cloud 函式可自動將 CSV 資料匯入 Google 試算表

1. 簡介

本程式碼研究室的目標是協助您瞭解如何編寫 Cloud Function，以便對上傳至 Cloud Storage 的 CSV 檔案做出反應、讀取檔案內容，並使用 Sheets API 更新 Google 試算表。

這可視為自動執行原本需要手動進行的「匯入為 CSV」步驟。這樣一來，您就能在資料 (可能由其他團隊產生) 可用時，立即在試算表中進行分析。

實作方式如下：

2. 設定和需求條件

自修實驗室環境設定

1. 登入 Cloud 控制台，建立新專案或重複使用現有專案。(如果沒有 Gmail 或 G Suite 帳戶，請先建立帳戶)。

注意：記住以下網址，即可輕鬆存取 Cloud 控制台：console.cloud.google.com。

請記住專案 ID，這是所有 Google Cloud 專案中不重複的名稱 (上述名稱已遭占用，因此不適用於您，抱歉！)。本程式碼研究室稍後會將其稱為 PROJECT_ID。

注意：如果您使用 Gmail 帳戶，可以將預設位置設為「沒有機構」。如果您使用 G Suite 帳戶，請選擇適合貴機構的位置。

2. 接著，您必須在 Cloud 控制台中啟用帳單，才能使用 Google Cloud 資源。

完成本程式碼研究室的費用應該不高，甚至完全免費。請務必按照「清除」部分的指示操作，瞭解如何停用資源，避免在本教學課程結束後繼續產生帳單費用。Google Cloud 新使用者可參加價值$300 美元的免費試用計畫。

3. 建立及設定 Google 試算表，並啟用 API

首先，請建立新的 Google 試算表文件 (這份試算表可屬於任何使用者)。建立後，請記下其 ID，因為這會做為我們將編寫函式的環境變數：

前往 GCP 控制台，依序點選「API 和服務」和「API 程式庫」部分，在新建立的專案中啟用 Google Sheets API：

在「IAM 與管理」專區中，前往「服務帳戶」，並記下 App Engine 預設服務帳戶的電子郵件地址。格式應為 [e-mail]。當然，您也可以建立專為這項動作使用的服務帳戶。

最後，只要使用「共用」按鈕，授予這個服務帳戶試算表的編輯權限即可：

完成這項設定後，我們就能編寫 Cloud Function，並將其設定為使用這個服務帳戶。這個應用程式可以寫入我們剛才建立的試算表文件。

4. 建立儲存空間值區

現在來建立 bucket，供 Cloud Function 監控新的 CSV 檔案。

在控制台中，使用左側選單前往「儲存空間」... :

... 並建立名為 csv2sheet-POSTFIX 的新 bucket (將 POSTFIX 替換為不重複的名稱)，其餘設定則保留預設值：

注意：您可以自行選擇位置、位置類型和儲存空間級別。這應該不會影響其餘的程式碼研究室。如有疑問，請使用預設值。

5. 建立 Cloud 函式

現在可以建立名為 csv2sheet 的 Cloud 函式，在檔案上傳至特定 Cloud Storage bucket 時觸發。程式碼將以 Node.js 8 編寫，並使用 Cloud 控制台的內嵌編輯器，以 async 函式執行：

請務必將觸發條件設為「Cloud Storage」，並將 bucket 名稱調整為您在上一步建立的名稱。

此外，請將我們即將編寫的函式進入點更新為 csv2sheet：

現在請將函式主體變更為：

1. 使用 Cloud Storage 和 Google 試算表 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 的環境變數，該變數應與您先前建立的試算表文件相符：

最後一個設定步驟如下，這是 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"
    }
}

按照上述說明完成所有設定後，請點按「建立」！函式應會在短時間內建立及部署完成。

注意：本實驗室中討論的所有後續程式碼，都必須新增至我們剛建立的這項 Cloud Function。

6. 設定驗證和 Google Sheets API

使用內嵌編輯器在 Cloud 函式中編寫任何其他程式碼之前，我們需要使用適當的 Storage 和 Sheet 範圍建立 Google Client API (請注意，這是 async 函式的一部分)。

在控制台的函式編輯器中，按一下「EDIT」，然後將下列程式碼新增至 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 用戶端，我們可以在文件中建立簡單的新試算表，但在此之前，請先快速瞭解詞彙：

• 試算表是實際文件，並由其 ID 參照 (如上所述，且顯示在文件網址中)
• 工作表是文件中的其中一個分頁，可透過名稱 (分頁名稱) 或工作表建立時產生的 ID 參照

考量到這一點，以下函式會使用 Google Sheets API 用戶端，在位置 2 (通常是預設的「工作表 1」之後) 建立空白工作表，其中包含 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 Function，並使用 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 Function，現在已完成 99%！

請注意，資料和樣式會合併為多個 requests，並呼叫單一 Google 試算表 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 檔案上傳至正確的 bucket，試算表就會更新，並新增含有檔案內容的工作表。如果您沒有現成的 CSV 檔案，可以下載這個範例。

試著將多個檔案上傳到 bucket，看看會發生什麼事！

11. 大功告成！拆除基礎架構

開玩笑的，我們不需要拆除任何基礎架構，因為這一切都是以無伺服器方式完成！

您可以視需要刪除建立的 Cloud Function 和 Bucket，甚至是整個專案。

12. 接下來要做什麼？

本程式碼研究室到此結束。您已瞭解如何透過 Cloud Function 監聽 Cloud Storage 值區的上傳作業，並使用適當的 API 更新 Google 試算表。

請參考下列幾個後續步驟：

• 參閱 Cloud Functions 使用指南 (內含部分最佳做法)
• 完成其中一個 Cloud Functions 教學課程
• 進一步瞭解 Google Sheets API

如果您在本程式碼研究室遇到任何問題，歡迎使用左下角的連結回報。

歡迎提供意見！