1. 簡介
本程式碼研究室的目標是協助您瞭解如何編寫 Cloud Function，以便對上傳至 Cloud Storage 的 CSV 檔案做出反應、讀取檔案內容，並使用 Sheets API 更新 Google 試算表。
這可視為自動執行原本需要手動操作的「匯入為 CSV」步驟。這樣一來，您就能在資料 (可能由其他團隊產生) 可用時，立即在試算表中分析資料。
實作方式如下：
2. 設定和需求
自修實驗室環境設定
請記住專案 ID，這是所有 Google Cloud 專案中不重複的名稱 (上述名稱已遭占用，因此不適用於您，抱歉！)。本程式碼研究室稍後會將其稱為 PROJECT_ID。
- 接著，您必須在 Cloud 控制台中啟用帳單，才能使用 Google Cloud 資源。
完成本程式碼研究室的費用應該不高，甚至完全免費。請務必按照「清除」部分的指示操作，瞭解如何停用資源，避免在本教學課程結束後繼續產生帳單費用。Google Cloud 新使用者可參加價值$300 美元的免費試用計畫。
3. 建立及設定 Google 試算表，並啟用 API
首先，請建立新的 Google 試算表文件 (這份試算表可屬於任何使用者)。建立後，請記下其 ID，我們稍後會將其做為所編寫函式的環境變數：
前往 GCP 控制台，依序點選「API 和服務」和「API 程式庫」部分，在新建立的專案中啟用 Google Sheets API：
在「IAM 與管理」專區中，前往「服務帳戶」，並記下 App Engine 預設服務帳戶的電子郵件地址。格式應為 your-project-id@appspot.gserviceaccount.com。當然，您也可以建立專用於這項動作的服務帳戶。
最後，使用「共用」按鈕將試算表的編輯權授予這個服務帳戶：
完成這項設定後，我們就能編寫 Cloud Function，並將其設定為使用這個服務帳戶。這個應用程式將可寫入我們剛才建立的試算表文件。
4. 建立儲存空間值區
現在要建立 bucket，供 Cloud Function 監控新的 CSV 檔案。
在控制台中，使用左側選單前往「儲存空間」... :
... 並建立名為 csv2sheet-POSTFIX 的新 bucket (將 POSTFIX 替換為不重複的內容)，其餘設定則全部保留預設值：
5. 建立 Cloud 函式
現在可以建立名為 csv2sheet 的 Cloud Function，在檔案上傳至特定 Cloud Storage bucket 時觸發。程式碼將以 Node.js 8 編寫，並使用 Cloud 控制台的內嵌編輯器，以 async 函式執行：
請務必將觸發條件設為「Cloud Storage」，並將值區名稱調整為您在上一步建立的名稱。
此外，請將我們即將編寫的函式進入點更新為 csv2sheet：
現在請將函式主體變更為：
- 使用 Cloud Storage 和 Google 試算表 API
- 將
csv2sheet函式標示為async - 從 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"
}
}
按照上述說明完成所有設定後，請點按「建立」！函式應會在短時間內建立及部署完成。
6. 設定驗證和 Sheets API
使用內嵌編輯器在 Cloud Function 中撰寫任何其他程式碼之前，我們需要使用適當的 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 參照
瞭解這點後，以下函式會使用 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 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 值)，就會因這個特定程式碼而失敗。
如果一切順利，此時您可以：
- 儲存更新後的函式
- 將 CSV 檔案拖曳至值區
- 試算表就會顯示對應資料！
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 和儲存空間，甚至是整個專案。
12. 接下來要做什麼？
本程式碼研究室到此結束。您已瞭解如何透過 Cloud Function 監聽 Cloud Storage bucket 的上傳作業，並使用適當的 API 更新 Google 試算表。
以下是幾個後續步驟：
- 參閱 Cloud Functions 使用指南 (內含部分最佳做法)
- 完成其中一個 Cloud Functions 教學課程
- 進一步瞭解 Google Sheets API
如果您在本程式碼研究室遇到任何問題，歡迎使用左下角的連結回報。
歡迎提供意見！