1. はじめに
この Codelab の目的は、Cloud Storage への CSV ファイルのアップロードに応答して Cloud Functions を作成し、そのコンテンツを読み取って Sheets API を使用して Google スプレッドシートを更新する方法を理解することです。
これは、手動で行う「CSV としてインポート」の手順を自動化したものと見なすことができます。これにより、データ（別のチームが作成したデータなど）が利用可能になったらすぐにスプレッドシートで分析できるようになります。
実装は次のようになります。
2. 設定と要件
セルフペース型の環境設定
- Cloud Console にログインし、新しいプロジェクトを作成するか、既存のプロジェクトを再利用します（Gmail アカウントまたは G Suite アカウントをお持ちでない場合は、アカウントを作成する必要があります）。
プロジェクト ID を忘れないようにしてください。プロジェクト ID はすべての Google Cloud プロジェクトを通じて一意の名前にする必要があります（上記の名前はすでに使用されているので使用できません）。以降、このコードラボでは PROJECT_ID と呼びます。
- 次に、Google Cloud リソースを使用するために、Cloud Console で課金を有効にする必要があります。
このコードラボを実行しても、費用はほとんどかからないはずです。このチュートリアル以外で請求が発生しないように、リソースのシャットダウン方法を説明する「クリーンアップ」セクションの手順に従うようにしてください。Google Cloud の新規ユーザーは、300 米ドル分の無料トライアル プログラムをご利用いただけます。
3. Google スプレッドシートを作成して構成し、API を有効にする
まず、新しいスプレッドシート ドキュメントを作成します（このシートは任意のユーザーに属することができます）。作成したら、その識別子を覚えておいてください。これは、作成する関数の環境変数として使用されます。
GCP コンソールで、[API とサービス]、[API ライブラリ] の順に移動して、新しく作成したプロジェクトで Google Sheets API を有効にします。
[IAM と管理] セクションで、[サービス アカウント] に移動し、App Engine のデフォルトのサービス アカウントのメールアドレスをメモします。your-project-id@appspot.gserviceaccount.com の形式にする必要があります。もちろん、このアクション専用のサービス アカウントを作成することもできます。
最後に、[共有] ボタンを使用して、このサービス アカウントにスプレッドシートの編集権限を付与します。
この設定で、Cloud Functions を作成し、このサービス アカウントを使用するように構成できるようになりました。作成したスプレッドシート ドキュメントに書き込むことができます。
4. ストレージ バケットを作成する
新しい CSV ファイルを監視するバケットを作成しましょう。
コンソールで、左側のメニューを使用して [ストレージ] に移動します。:
... を実行して、他のすべての設定がデフォルト値に設定された csv2sheet-POSTFIX という名前の新しいバケットを作成します（POSTFIX は一意の値に置き換えます）。
5. Cloud Functions の関数を作成する
これで、特定の Cloud Storage バケットへのファイル アップロード時にトリガーされる csv2sheet という Cloud Function を作成できます。コードは、Cloud Console のインライン エディタを使用して、非同期関数を含む Node.js 8 で記述します。
トリガーを [Cloud Storage] に設定し、バケット名を前の手順で作成したバケット名に調整してください。
また、これから作成する関数のエントリ ポイントを csv2sheet に更新します。
関数本体を次のように変更します。
- Cloud Storage API と Sheets 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という環境変数を定義します。
最後の設定ステップとして、使用する 2 つの依存関係（Cloud Storage API と Google Sheet API）を含む package.json のコンテンツを次に示します（コンソールのインライン エディタの PACKAGE.JSON タブを使用します）。
{
"name": "csv2sheet",
"version": "0.0.42",
"dependencies": {
"googleapis": "^51.0.0",
"@google-cloud/storage": "^5.0.1"
}
}
説明どおりにすべてを設定したら、[作成] をクリックします。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 クライアントを使用すると、ドキュメントに新しいシンプルなシートを作成できます。ただし、先に進む前に、用語について簡単に説明します。
- スプレッドシートは実際のドキュメントであり、その識別子（上記で説明し、ドキュメントの URL に表示される）によって参照されます。
- シートはドキュメント内のタブの 1 つで、名前（タブ名）またはシートの作成時に生成された識別子で参照できます。
このことを踏まえて、Sheets API クライアントを使用して、26 列、2,000 行の空のシートを位置 2（通常はデフォルトの「シート 1」の後）に作成し、最初の行を固定する関数を次に示します（インライン エディタを使用して関数に追加します）。
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 という名前のシートがすでに存在する場合、作成は失敗します。
スプレッドシート 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. 新しく作成したシートにデータを入力します。
同じ Sheet クライアント 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 として 1 つの Sheets 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);
完全な関数ソースコードが必要な場合は、こちらで入手できます（おそらく、すべてを 1 つのセットで取得する方が簡単です）。
すべてが整ったら、CSV ファイルを適切なバケットにアップロードするだけで、スプレッドシートが更新され、ファイルの内容を含む新しいシートが追加されます。CSV ファイルの例を次に示します。
バケットに複数のファイルをアップロードして、何が起こるかを確認してみましょう。
11. これで、インフラストラクチャを破棄する
冗談です。インフラストラクチャを解体する必要はありません。すべてサーバーレスで行われました。
必要に応じて、作成した Cloud Functions とバケット、またはプロジェクト全体を削除できます。
12. 次のステップ
これで、Cloud Functions で Cloud Storage バケットへのアップロードをリッスンし、適切な API を使用して Google スプレッドシートを更新する手順を説明するこの Codelab は終了です。
フォローアップ情報として以下をご確認ください。
- Cloud Functions のハウツーガイド（ベスト プラクティスを含む）を確認する
- Cloud Functions のチュートリアルのいずれかを確認する
- Google Sheets API についてさらに詳しく調べる
この Codelab で問題が発生した場合は、左下隅のリンクを使用して問題を報告してください。
皆様のフィードバックをお待ちしております。