スプレッドシートからBigQueryへデータを自動更新する方法

こんにちは、GMOアドマーケティングのS.Rです。

スプレッドシートはGoogle が提供する表計算ツールです。スプレッドシートでは簡単にデータを分析し、共同作業などが凄く楽になります。
BigQueryはデータの分析業務によく使われているGoogle Cloud Service の重要なサービスです。日々の業務中は手動でスプレッドシートのシートを BigQuery のテーブルへuploadすることがよくあります。今回はスプレッドシートからシートの内容に変動がある場合に自動的にBigQueryへuploadするAppscriptのバッチの作成方法を紹介します。

App scriptとは

Apps Script は、Google Workspace の統合、自動化、拡張のためのビジネス ソリューションをすばやく簡単に構築するための唯一のローコード プラットフォームです。Apps Script を使えば、ビジネス ユーザーは本格的なソフトウェア開発の経験がなくても、Google Workspace 上にカスタム ソリューションを構築できます。Apps Script は、Gmail アカウントをお持ちであればどなたでもご利用いただけます。

1	Apps Script は、Google Workspace の統合、自動化、拡張のためのビジネスソリューションをすばやく簡単に構築するための唯一のローコードプラットフォームです。Apps Script を使えば、ビジネスユーザーは本格的なソフトウェア開発の経験がなくても、Google Workspace 上にカスタムソリューションを構築できます。Apps Script は、Gmail アカウントをお持ちであればどなたでもご利用いただけます。

詳細については【 GoogleのApps Scriptのホームページ（リンク：https://workspace.google.co.jp/intl/ja/products/apps-script/】をご覧ください。

流れ

下記はバッチの流れ図です。ユーザーがスプレッドシートのシートに何か変更を行うとバッチは自動的に呼ばれてシート
の内容をcsvファイルでGoogle Driveに保存します。その後このcsvファイルをBigQueryのテーブルへuploadします。

さあ早速Appscriptのバッチを作成しましょう。

スプレッドシートを作成

まずテスト用のシートを作ります。今回は例として下記のデータを入れました。

App Scriptのプログラムを作成

拡張機能->App Scriptを選んてください。

新しく作成されたApp Scriptの様子は下記です。

App Scriptのサービス設定

左側の”サービス”を押してください。

BigQuery ApiとDriveを追加しましょう。

Google Driveで新しいフォルダを作成

csv ファイルを保存するための新しいフォルダを作成してください。作成できたらmouseの右ボタンをクリックしてリンクを取得を押しましょう。

取得できたリンクのID（https://drive.google.com/drive/folders/後の部分）をメモしてください。

バッチのコードを書く

シートの内容をDrive のcsvファイルへ書く関数を作成

var FOLDERID = "フォルダID"
function save2Drive(filename, csv) {
  var destfolder = DriveApp.getFolderById(FOLDERID);
  return destfolder.createFile(filename, csv)
}

var FOLDERID = "フォルダID"

function save2Drive(filename, csv) {

var destfolder = DriveApp.getFolderById(FOLDERID);

return destfolder.createFile(filename, csv)

}

csvファイルをBigQueryのテーブルへuploadする関数を作成

function importCSVtoBigQuery(table, file) {
  projectId = table.tableReference.projectId
  datasetId = table.tableReference.datasetId
  tableId = table.tableReference.tableId

  try {
    BigQuery.Tables.remove(projectId,datasetId, tableId)
  } catch (e) {
    Logger.log(e.toString());
  }
  BigQuery.Tables.insert(table, projectId, datasetId);
  data = file.getBlob().setContentType('application/octet-stream');
  var job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        },
        skipLeadingRows: 1
      }
    }
  };
  job = BigQuery.Jobs.insert(job, projectId, data);
  Logger.log('Job status for %s https://bigquery.cloud.google.com/jobs/%s', file.getName(), projectId);

}さ

function importCSVtoBigQuery(table, file) {

projectId = table.tableReference.projectId

datasetId = table.tableReference.datasetId

tableId = table.tableReference.tableId

try {

BigQuery.Tables.remove(projectId,datasetId, tableId)

} catch (e) {

Logger.log(e.toString());

}

BigQuery.Tables.insert(table, projectId, datasetId);

data = file.getBlob().setContentType('application/octet-stream');

var job = {

configuration: {

load: {

destinationTable: {

projectId: projectId,

datasetId: datasetId,

tableId: tableId

skipLeadingRows: 1

}

};

job = BigQuery.Jobs.insert(job, projectId, data);

Logger.log('Job status for %s https://bigquery.cloud.google.com/jobs/%s', file.getName(), projectId);

}さ

最後スプレッドシートからBigQueryのテーブルへuploadする関数を作成します。SPREADSHEET_URL、projectId、datasetIdを各自で設定してください

SPREADSHEET_URL = "spread sheetのURL"
function spreadsheet2Bigquery() {
  var sheets = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheets();
  //BigQuery テーブルの項目
  var data_csv = "name,price,number\r\n";
  if (sheets.length >= 1) {
    for (var i = 0; i < sheets.length; i++) {
      if (!sheets[i].isSheetHidden()) {
        var range = sheets[i].getDataRange();
        var values = range.getValues();
        for (var j = 1; j < values.length; j++) {
          var name = values[j][0].toString().trim()
          var price = values[j][1].toString().trim() + "_" + values[j][cate_index2].toString().trim()
          var number = values[j][2].toString().trim()
          data_csv += [name, price, number].join(",") + "\r\n";
        }
      }
    }
  }
  //BigQueryのprojectId、datasetId、table
  var projectId = 'projectId';
  var datasetId = 'datasetId';
  var table = {
    tableReference: {
      projectId: projectId,
      datasetId: datasetId,
      tableId: "test_data_blog"
    },
    //BigQuery テーブルのshcema
    schema: {
      fields: [
        { name: 'name', type: 'STRING' },
        { name: 'price', type: 'FLOAT' },
        { name: 'number', type: 'INT64' }
      ]
    }
  };
  var data_csv_file = save2Drive("data_csv.csv", data_csv)
  importCSVtoBigQuery(table, data_csv_file)
}

SPREADSHEET_URL = "spread sheetのURL"

function spreadsheet2Bigquery() {

var sheets = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheets();

//BigQuery テーブルの項目

var data_csv = "name,price,number\r\n";

if (sheets.length >= 1) {

for (var i = 0; i < sheets.length; i++) {

if (!sheets[i].isSheetHidden()) {

var range = sheets[i].getDataRange();

var values = range.getValues();

for (var j = 1; j < values.length; j++) {

var name = values[j][0].toString().trim()

var price = values[j][1].toString().trim() + "_" + values[j][cate_index2].toString().trim()

var number = values[j][2].toString().trim()

data_csv += [name, price, number].join(",") + "\r\n";

}

//BigQueryのprojectId、datasetId、table

var projectId = 'projectId';

var datasetId = 'datasetId';

var table = {

tableReference: {

projectId: projectId,

datasetId: datasetId,

tableId: "test_data_blog"

//BigQuery テーブルのshcema

schema: {

fields: [

{ name: 'name', type: 'STRING' },

{ name: 'price', type: 'FLOAT' },

{ name: 'number', type: 'INT64' }

]

}

};

var data_csv_file = save2Drive("data_csv.csv", data_csv)

importCSVtoBigQuery(table, data_csv_file)

}

一回実行してみましょう。

権限を承認します。

実行は完了まで少々待ってください。

実行できるとBigQueryの管理画面に新しくuploadされたテーブルが確認できます。

トリガー関数を追加

実行関数にspreadsheet2Bigqueryを選択しイベントのオプションを選んでください。

バッチを試す

スプレッドシートに新しい一行を追加しましょう。

BigQueryのテーブルに新しいデータを自動的に追加できました！

まとめ

今回はスプレッドシートのシートをBigQueryへ自動更新するバッチの作成方法を紹介いたしました。いかがでしょうか。もし皆さんの日々の分析業務にお役に立つと幸いです。

shicho ryu

App scriptとは

流れ

スプレッドシートを作成

App Scriptのプログラムを作成

App Scriptのサービス設定

Google Driveで新しいフォルダを作成

バッチのコードを書く

トリガー関数を追加

バッチを試す

まとめ

おすすめ

Railsのタグ機能でN+1問題解消

Professional Scrum Master ⅠとⅡに合格しました

background-imageを活用して、アップする画像を1個で済ませたい