Google BigQuery

Google Apps Scriptを使ってスプレッドシートからBigQueryのテーブルを更新する

こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。

Twitter:@exturekwon

今回はAppsScriptを使って、スプレッドシートからBigQueryのテーブルを更新する方法についてです。

1.スプレッドシートのデータを用意する

例として、sstable1とsstable2というシート2枚から構成されるスプレッドシートを使います。

スキーマはいずれも:
id: INTEGER
name: STRING
memo: STRING
という簡単なものです。

sstable1

sstable2

2.Apps Scriptを作成する

スプレッドシートで開いているアクティブなシート名を取得して、そのシートの内容をBigQueryに書き込むスクリプトを書きます。

function main() {
  var projectId = 'my-project'; //プロジェクトID
  var datasetId = 'my-dataset'; //データセット

  var app = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = app.getActiveSheet();
  var tableId = sheet.getSheetName();
  
  var table = {
    tableReference: {
      projectId: projectId,
      datasetId: datasetId,
      tableId: tableId
    },
    schema: {
      fields: [
        {name: 'id', type: 'integer'},
        {name: 'name', type: 'string'},
        {name: 'memo', type: 'string'}
      ]
    }
  };
  try{
    BigQuery.Tables.remove(projectId, datasetId, tableId); 
  } catch(e) {}
  table = BigQuery.Tables.insert(table, projectId, datasetId);
  
  var range = sheet.getDataRange();
  var blob = Utilities.newBlob(convCsv(range)).setContentType('application/octet-stream');
  var job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        },
        skipLeadingRows: 1
      }
    }
  };
  job = BigQuery.Jobs.insert(job, projectId, blob);
}

function convCsv(range) {
  try {
    var data = range.getValues();
    var ret = "";
    if (data.length > 1) {
      var csv = "";
      for (var i = 0; i < data.length; i++) {
        for (var j = 0; j < data[i].length; j++) {
          if (data[i][j].toString().indexOf(",") != -1) {
            data[i][j] = "\"" + data[i][j] + "\"";
          }
        }
        if (i < data.length-1) {
          csv += data[i].join(",") + "\r\n";
        } else {
          csv += data[i];
        }
      }
      ret = csv;
    }
    return ret;
  }
  catch(e) {
    Logger.log(e);
  }
}

このスクリプトをスプレッドシートの[ツール] > [スクリプトエディタ]を開いて、エディタに登録します。

3.スプレッドシートとGCPプロジェクトをリンクする

スクリプトエディタの [リソース] > [Cloud Platform プロジェクト] を開いて、リンクするプロジェクトの「プロジェクト番号」を入れます。

そしてBigQuery APIを有効化します。
[リソース] > [Googleの拡張サービス]を開いて、BigQuery API v2 を有効化します。

4.スクリプトを実行する

さて、ではスクリプトを実行します
BigQueryにデータを書き込みたいシートを開いてる状態で、スクリプトエディタの[実行] > [関数を実行] > [main]を選びます。
これでスプレッドシートの内容がBigQueryのテーブルに書き込まれました。
すべてのシートを開いてこれを実行します。

sstable1

sstable2

5.シート編集時に自動的にBigQueryを更新するようにトリガーを設定する

シートを更新するたびにメニューから実行を選択しないでも自動的にテーブルが更新されるようにトリガーを設定します。

スクリプトエディタの[編集] > [現在のプロジェクトのトリガー]を開いて、新しいトリガーを追加します。

実行: main
イベント: スプレッドシートから 編集時

これで、シートを編集すると自動的にBigQueryのテーブルが更新されます。

今回はGoogleスプレッドシートから、Apps Scriptを使ってBigQueryのテーブルを更新する方法についてでした。
弊社ではGoogle Cloud認定データエンジニアがGCPを使った分析基盤構築や、分析の支援を行っております。
お問い合わせはこちらからどうぞ。

ブログへの記事リクエストはこちらまで

関連記事

  1. Adobe Analytics

    BigQuery: Adobe Datafeed: event_listカラムの手軽な扱い方

    こんにちは、エクスチュアCTOの権です。Twitter:@ex…

  2. Google BigQuery

    OWOX BI: GoogleSheetsアドオンでBigQueryをお手軽ビジュアライズ

    こんにちは、エクスチュアの權泳東(コン・ヨンドン)です。Twi…

  3. Adobe Analytics

    Adobe Analytics: データフィードをGoogle BigQueryのテーブルにロードす…

    ※2019年9月4日追記この記事は情報が古いので、新しい記事を書き…

  4. Adobe Analytics

    Looker: エンジニアがBIで分析ダッシュボードを作る

    こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。Twi…

  5. Adobe Analytics

    Adobe Analytics: DatafeedのログからフォールアウトレポートをBigQuery…

    こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。Twi…

  6. Adobe Analytics

    Metabase: カスタムマップで日本地図を追加する

    こんにちは、エクスチュアの權泳東(コン・ヨンドン)です。Twi…

最近の記事

  1. Adobe AEP SDKをTypeScriptで開発したR…
  2. DTMからAdobe Launchへの移行:4段階のアプロー…
  3. Metabotを使ってSlackにMetabaseのグラフを…
  4. GTMのdataLayerをAdobeAnalyticsの「…
  5. Metabase: カスタムマップで日本地図を追加する
  1. Adobe Analytics

    Adobe Analytics:マーケティングチャネルの設定方法 全流入経路を1…
  2. Adobe Analytics

    Adobe AnalyticsとGoogle Analyticsの違い③ カスタ…
  3. Google BigQuery

    オープンソースBI「Metabase」の使い勝手が丁度良かった
  4. Adobe Analytics

    Looker: Sankey Diagramを使ってサイト内フローを可視化する
  5. Mouseflow

    Mouseflow: MouseflowタグをWebサイトに導入する
PAGE TOP