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

1. 簡介

本程式碼研究室的目標是協助您瞭解如何編寫 Cloud 函式，以回應上傳至 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 專案的專屬名稱 (已經有人使用上述名稱，很抱歉對您不符！)。稍後在本程式碼研究室中會稱為 PROJECT_ID。

  1. 接下來，您需要在 Cloud 控制台中啟用計費功能，才能使用 Google Cloud 資源。

執行這個程式碼研究室並不會產生任何費用，如果有的話。請務必依照「清除所用資源」一節指示本節將說明如何關閉資源，這樣您就不會產生本教學課程結束後產生的費用。Google Cloud 的新使用者符合 $300 美元免費試用計畫的資格。

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

首先，請建立新的 Google 試算表文件 (這份工作表適用於所有使用者)。建立完成後，請記住 ID；該變數會做為我們編寫的函式的環境變數：

dd77d5fc1364ad3e.png

在 GCP 控制台中前往「API 和服務」，為新建立的專案啟用 Google Sheets API其次是「API 程式庫」區段：

c64e2e98b8b55f16.png

在「IAM 與管理員」前往「服務帳戶」專區並記下 App Engine 預設服務帳戶的電子郵件。格式應為 your-project-id@appspot.gserviceaccount.com。當然，您也可以建立自己的服務帳戶來執行這項動作。

6e279d7e07d4febf.png

最後，只要使用 [共用] 選單，將編輯權限授予此服務帳戶即可按鈕：

c334062465ddf928.png

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

4. 建立儲存空間值區

讓我們建立 Cloud 函式來監控新 CSV 檔案的值區。

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

2ddcb54423979d25.png

... 然後建立名為 csv2sheet-POSTFIX 的新值區 (將 POSTFIX 替換為不重複的項目)，並將其他所有設定均設為預設值：

dd637080ade62e81.png

5. 建立 Cloud 函式

我們現在可以建立名為 csv2sheet 的 Cloud 函式，這個函式會在檔案上傳至特定 Cloud Storage 值區時觸發。程式碼將以 Node.js 8 編寫，並直接透過 Cloud 控制台的內嵌編輯器，以非同步函式編寫。

6ee1a5ce63174ae8.png

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

同時更新要寫入 csv2sheet 的函式進入點：

446e7c7c992c2d8a.png

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

  1. 使用 Cloud Storage 和 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. 設定 Auth API 和 Sheets API

在使用內嵌編輯器編寫任何進一步程式碼之前，我們必須在建立具備適當儲存空間和試算表範圍的 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 用戶端，我們可以在文件中建立簡單的新工作表，但在此之前，在此先簡單說明一下詞彙：

  • 試算表指的是實際文件，由文件 ID 參照 (如前文所述，可在文件網址中查看)
  • 工作表是文件中的其中一個分頁標籤，可以用名稱 (分頁名稱) 或在工作表建立時產生的 ID 參照。

請注意，以下函式會透過 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 函式，並使用 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 函式中，目前作業已完成 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 函式和您建立的值區，甚至是整個專案。

12. 接下來要做什麼？

本程式碼研究室將逐步引導您聆聽相關步驟，以監聽 Cloud 函式中上傳至 Cloud Storage 值區的作業，以使用適當的 API 更新 Google 試算表。

以下是一些後續的步驟：

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

感謝你提供寶貴意見！