背景
社内ツールのUIとしてGoogleフォームを使い、入力されたデータをスプレッドシートに溜め、集計や分析のためにBigQueryにデータを流したいと思ったことはありませんか？
今回はGAS(Google Apps Script)を利用して、スプレッドシートにデータが挿入されるたびに、BigQueryにデータを流す方法を解説していきます。
用意するもの
- Googleフォーム
- GoogleフォームにリンクされたGoogleスプレッドシート
- BigQueryが有効化されたGoogle Cloud Platformのアカウント
GAS(Google Apps Script)にBigQueryのサービスを追加する
1. スプレッドシート内の [拡張機能] から [Apps Script] をクリック
2. Google Apps Script内 [サービス] の [+] を押下する
3. [サービスを追加] から [BigQuery] を選択し、[追加] を押下する
GAS(Google Apps Script)上でコードを書いていく
// onFormSubmitでGoogleフォームからデータを受け取ります
function onFormSubmit(e) {
insertToBigQuery(convertToCSV(e))
}
// Googleフォームから取得したデータをCSV形式の変換します
function convertToCSV(data) {
const namedValues = data.namedValues
const isAdmin = namedValues["管理者ですか？"][0] === "はい"
const csv = `name,email,description,is_admin
${namedValues["名前"][0]},${namedValues["メールアドレス"][0]},${namedValues["説明"][0]},${isAdmin}`
return csv
}
// BigQueryへデータをインサートします
function insertToBigQuery(csv) {
const projectId = '<input-your-project-id>'
const datasetId = '<input-your-dataset-id>'
const tableId = '<input-your-table-id>'
const job = {
configuration: {
load: {
destinationTable: {
projectId: projectId,
datasetId: datasetId,
tableId: tableId
},
skipLeadingRows: 1
}
}
};
try {
const blob = Utilities.newBlob(csv).setContentType('application/octet-stream')
const jobResult = BigQuery.Jobs.insert(job, projectId, blob);
console.log(`Load job started. Status: ${jobResult.status.state}`);
} catch (err) {
console.log('unable to insert job');
}
}
トリガーの設定
1. [トリガー] に移動する
2. [実行する関数] が onFormSubmit になっていることを確認し、[イベントの種類を選択] で フォーム送信時 を選択
BigQueryにデータがインサートされるか検証する
ここまでで、設定は完了です。実際にGoogleフォームから値を入力し、BigQueryにデータが入っているか確認しましょう。
Googleフォームから入力されたスプレッドシートがしっかりBigQueryにインサートされていますね。
スプレッドシート
BigQuery
ローコードで直接BigQueryにデータ入力ができるUIを構築する選択肢も
今回はGAS(Google Apps Script)を使って、Googleフォームで入力されて、スプレッドシートに溜まったデータをBigQueryにインサートする処理を解説してきました。
GAS(Google Apps Script)を使えば、とても簡単にBigQueryへデータを挿入することができました。
しかし、この方法では回答が多くなってくるとGoogleフォームやスプレッドシートが重くなり、入力体験が低下していくというデメリットもあります。
そんなときに、BigQueryに直接入力できるフォームをローコードで構築するという方法もございます。詳しくは以下の記事をご覧ください。
Discussion