このエントリーをはてなブックマークに追加

BigQueryでデータ利活用をする際、手動でテーブルを簡単に作成できると便利ですよね。
今回はスプレッドシートからBigQueryのテーブルを作成/データ投入する方法を解説します。

目次

実施スキーム

今回実現するスキームはこちら。

スプレッドシート上に実行ボタンを設け、
実行ボタン押下でGASスクリプトを実行するようにします。

完成品

スプレッドシート


1行目のC列にBigQueryのプロジェクト番号、
E列にテーブル識別子(プロジェクト名.データセット名.テーブル名)を記入します。

2~4行目には各カラムのデータ型・論理名・物理名を記入します。
今回対応したデータ型はこちら。

  • STRING
  • INT64
  • FLOAT64
  • NUMERIC
  • BOOLEAN
  • DATE
  • TIME
  • DATETIME

5行目以降には、投入するデータを記入します。
セルに表示されている値(Display Value)を投入するので、関数で出力しても大丈夫です。

右上のボタンには、実行スクリプトを割り当てています。

スクリプト

GASに登録しているスクリプトです。

/**
 * シートのレコード数を計算する関数
 * @return {number}
 */
function getRecordCountFromSheet() {
  const target_sheet = SpreadsheetApp.getActiveSheet();
  let record_count = target_sheet.getLastRow();
  const values = target_sheet.getRange(1, 2, record_count, 1).getValues();
  for (let i = record_count - 1; i >= 0; i--) {
    if (values[i][0] !== '') {
      record_count = i + 1;
      break;
    }
  }
  return record_count - 4;
}

/**
 * シートのカラム数を計算する関数
 * @return {number}
 */
function getColumnCountFromSheet() {
  const target_sheet = SpreadsheetApp.getActiveSheet();
  let column_count = target_sheet.getLastColumn();
  const values = target_sheet.getRange(2, 1, 1, column_count).getValues()[0];
  for (let i = column_count - 1; i >= 0; i--) {
    if (values[i] !== '') {
      column_count = i + 1;
      break;
    }
  }
  return column_count - 1;
}

/**
 * 実行確認ダイアログを表示する関数
 */
function confirmExecute() {
  const ui = SpreadsheetApp.getUi();
  const title = "実行確認";
  const prompt = "テーブルを作成します。\n" + getRecordCountFromSheet() + "件のレコードが登録されます。\nよろしいですか？";
  const response = ui.alert(title, prompt, ui.ButtonSet.YES_NO);
  if (response == ui.Button.YES) {
    createTableFromSheet();
    exportTable();
  }
}

/**
 * シートの内容でテーブルを再作成する関数
 */
function createTableFromSheet() {
  const target_sheet = SpreadsheetApp.getActiveSheet();
  const project_number = target_sheet.getRange(1, 3).getValue();
  const table_name = target_sheet.getRange(1, 5).getValue();
  const column_count = getColumnCountFromSheet();
  const data_types = target_sheet.getRange(2, 2, 1, column_count).getValues()[0];
  const descriptions = target_sheet.getRange(3, 2, 1, column_count).getValues()[0];
  const columns = target_sheet.getRange(4, 2, 1, column_count).getValues()[0];
  const records = target_sheet.getRange(5, 2, getRecordCountFromSheet(), column_count).getDisplayValues();
  let table_columns = new Array();
  columns.forEach((column_name, i) => {
    table_columns.push(`${column_name} ${data_types[i]} OPTIONS (description = '${descriptions[i]}')`);
  });
  const create_sql = "CREATE OR REPLACE TABLE `" + table_name + "` (" + table_columns.toString() + ")";
  executeQuery(project_number, create_sql);

  let insert_values = new Array();
  let insert_sql = "INSERT INTO `" + table_name + "` (" + columns.toString() + ") VALUES";
  records.forEach((record, i) => {
    let values = new Array();
    record.forEach((column_value, j) => {
      if (column_value === "") {
        values.push("NULL");
      } else if (data_types[j] === "STRING" || data_types[j] === "DATE" || data_types[j] === "TIME" || data_types[j] === "DATETIME") {
        values.push(`'${column_value}'`);
      } else {
        values.push(column_value.replace(",", ""));
      }
    });
    insert_values.push("(" + values.toString() + ")");
  });
  insert_sql += insert_values.toString();
  executeQuery(project_number, insert_sql);
}

/**
 * テーブルデータをシートへ出力する関数
 */
function exportTable() {
  const target_sheet = SpreadsheetApp.getActiveSheet();
  const project_number = target_sheet.getRange(1, 3).getValue();
  const table_name = target_sheet.getRange(1, 5).getValue();
  const select_sql = "SELECT * FROM `" + table_name + "` ORDER BY 1";
  const split_size = 10000;

  let offset = 0;
  let result_count = split_size;
  while (result_count == split_size) {
    const query = `${select_sql} LIMIT ${split_size} OFFSET ${offset}`;
    const query_results = executeQuery(project_number, query);
    const table_rows = query_results.getRows();
    result_count = query_results.getTotalRows();

    if (result_count > 0) {
      const result_schema = query_results.getSchema();

      // 書式作成
      const schema_fields = result_schema["fields"];
      let cell_formats = new Array();
      for (let i = 0; i < schema_fields.length; i++) {
        const field_type = schema_fields[i]["type"];
        if (field_type === "STRING") {
          cell_formats.push("@");
        } else if (field_type === "INTEGER") {
          cell_formats.push("#,##0");
        } else if (field_type === "FLOAT" || field_type === "NUMERIC") {
          cell_formats.push("#,##0.###");
        } else if (field_type === "DATE") {
          cell_formats.push("yyyy-mm-dd");
        } else if (field_type === "TIME") {
          cell_formats.push("hh:MM:ss");
        } else if (field_type === "DATETIME") {
          cell_formats.push("yyyy-mm-dd hh:MM:ss");
        } else {
          cell_formats.push("");
        }
      }
      let range_formats = new Array();
      for (let i = 0; i < result_count; i++) {
        range_formats.push(cell_formats);
      }

      // 値作成
      let result_values = new Array();
      for (let i = 0; i < result_count; i++) {
        const cols = table_rows[i].getF();
        let result_row = new Array();
        for (let j = 0; j < cols.length; j++) {
          result_row.push(cols[j].getV());
        }
        result_values.push(result_row);
      }
      target_sheet.getRange(5 + offset, 2, result_count, schema_fields.length).setNumberFormats(range_formats).setValues(result_values);
    }
    offset += split_size;
  }
}

/**
 * SQLを実行する関数
 * @param {string} project_number - SQL実行プロジェクトのプロジェクト番号
 * @param {string} sql - 実行するSQL
 * @return {Object} クエリの実行結果
 */
function executeQuery(project_number, sql) {
  let query_results;
  const resource = {
    query: sql,
    useLegacySql: false,
    timeoutMs: 600000
  };
  try {
    query_results = BigQuery.Jobs.query(resource, project_number);
    const job_id = query_results.getJobReference().getJobId();
    let continue_flg = query_results.getJobComplete();
    while (continue_flg == false) {
      Utilities.sleep(3000);
      query_results = BigQuery.Jobs.getQueryResults(project_number, job_id);
      continue_flg = query_results.getJobComplete();
    }
  } catch (error) {
    Logger.log(error);
    Browser.msgBox(error);
    return;
  }
  return query_results;
}

サービスにBigQuery APIのみ追加しています。

実行結果

実行すると、このようにテーブルが作成されデータ投入されます。

スクリプトの解説

ここからは各関数について解説します。

getRecordCountFromSheet()

シートに記入されているレコード数をカウントする関数です。

今回、シートの行は実際に登録するレコード数よりも多く設けて行番号を振っているため、
レコード数がgetLastRow()で取得できません。
そのため、2列目の最終行を取得し、ヘッダーの行数をマイナスすることでレコード数を計算しています。

getColumnCountFromSheet()

シートに記入されているカラム数をカウントする関数です。

基本的にはgetLastColumn()で事足りるのですが、テーブルのカラム数が3カラム以下の場合、
1行目のテーブル名の記入部分が最終列となりエラーが発生してしまいます。
そのため、2行目の最終列を取得し、行番号列をマイナスすることでカラム数を計算しています。

confirmExecute()

「Execute」押下で実行される関数です。

スプレッドシートのUI上に確認ダイアログを表示し、
「はい」押下でcreateTableFromSheetとexportTableを実行します。

createTableFromSheet()

テーブル作成/データ投入を行う関数です。

スプレッドシートの記入内容をもとに、
テーブルをCREATE OR REPLACEするSQLとテーブルへINSERTするSQLを作成し実行します。

なおデータ投入にはBULK INSERT（1回のINSERTで複数データ投入する方法）をとっています。

exportTable()

テーブルの中身をスプレッドシートに出力する関数です。

クエリ実行時のエラーは画面に返却されますが、
SQLにエラーがなく登録内容やフォーマットに誤りがあった場合には気づくことができないため、
登録実施後に登録結果を同じシートへ反映します。

ただデータを取得してシートに記入するだけだと、
意図しない形式（頭0が落ちる、など）となってしまうことがあるため、
テーブルのデータ型に応じてセルの書式を整えた上で値を出力します。

executeQuery(project_number, sql)

BigQueryへクエリを実行する関数です。

引数として渡されたプロジェクト番号とSQLを使ってクエリを実行し、
実行結果を返却します。

このエントリーをはてなブックマークに追加