Google BigQuery

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

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

今回は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. 最速で理解したい人のためのIT用語集

関連記事

  1. Adobe Analytics

    Adobe AEP SDKでReactNativeアプリを計測する (Android編)

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

  2. Google BigQuery

    BigQueryのユーザー定義関数(UDF)をTableauで使う

    こんにちは、エクスチュアの渡部です。今回はTableauでstan…

  3. Adobe Analytics

    AdobeAnalytics Datafeed: BigQueryのSIGN関数を使った小ワザ

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

  4. Google BigQuery

    GCPのBQMLを使ってKaggleコンペに挑んでみた(その1)

    こんにちは。エクスチュアでインターンをさせて頂いている中野智基です。…

  5. Adobe Analytics

    AdobeAnalytics: スマホのスワイプにカスタムリンクを実装する

    こんにちは、エクスチュアの権泳東/コン・ヨンドン(@exturekwo…

  6. Databricks

    Databricks: Spark DataFrameでピボットグラフを作る

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

最近の記事

  1. 回帰分析はかく語りき Part2 重回帰分析
  2. 第14回関西DB勉強会-Snowflake Summit参加…
  3. Open Interpreter+VScode+Docker…
  4. LangChainって何?: 次世代AIアプリケーション構築…
  5. 回帰分析はかく語りき Part1 単回帰分析
  1. Adobe Analytics

    Report&Analytics グラフについて
  2. Matillion Associate Certification

    Matillion

    Matillion Associate Certification 合格体験記
  3. Amazon Web Services

    System Managerを利用したVPC内Redshiftへの接続
  4. IT用語集

    クローリング(Crawling)って何?
  5. 海外カンファレンス

    Adobe Summit 2018 レポート(2):経験の創造者たち
PAGE TOP