はじめに

弊社（VENECT株式会社）では、毎年SNSやECに関するアンケートを実施し、その集計結果をもとにスライド資料をPowerPointで作成してきました。今年度、そのCSVファイル形式のアンケートデータをLooker Studioでグラフ化するプロジェクトをインターンメンバーで実施しましたので、3回の記事に分けてトピックを紹介します。
今回の記事では、スプレッドシートからGoogle Apps Script(GAS)を使用してBigQueryのデータを簡単に更新する方法を紹介します。

イメージ

関連記事

必要なもの

Google Cloudのアカウント
- プロジェクトは事前に作成されている前提です。本記事では「MyProject」という名称の前提で記載していますので、適宜読み替えてください。
BigQuery APIの有効化

参考：BigQueryの利用を開始する―Google Cloud PlatformでBigQuery APIを有効にする

データの準備

BigQueryコンソールでデータセット→テーブルの順番で作成します。
本記事では Demo→Test という名称としています。データセットは東京リージョン（asia-northeast1）で作成しています。

また、テーブル作成時にフィールド名を追加してカラムを作成します

本記事内では、カラムを一つだけ作成して解説します。
今回実施したプロジェクトではSNS等のアンケート調査結果の可視化が目的でしたので、例としてTwitter→Xのように何かの名前が変更されたことを想定した解説としています。

カラム型	データ型	説明
SNS	STRING	SNSの名称

データ挿入

デモデータとして、BigQueryのコンソールからデータを挿入しておきます。
BigQueryのコンソールでSQLクエリを新規作成し、以下のSQLを実行してください。なお、プロジェクト名やデータセット名、テーブル名は適宜置き換えてください。

INSERT INTO `MyProject.Demo.Test` (SNS)
VALUES
  ('Twitter'),
  ('Instagram'),
  ('TikTok'),
  ('Facebook'),
  ('Threads'),
  ('Bluesky')

これを実行してBigQuery側の準備は完了です。

GASの作成

新規にスプレッドシートを作成後、 [拡張機能] → [Apps Script] を選択してGASの編集画面を開きます。
まず、スプレッドシート上のメニューバーにGAS実行のための操作メニューを追加していきます。今回は「BigQuery」という名前で操作メニューを追加します。

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menuItems = [
    { name: '①テーブルを参照', functionName: 'referenceTable' },
    { name: '②テーブルを更新', functionName: 'updateTable' },
  ];
  const menu = ui.createMenu('BigQuery');
  menuItems.forEach(item => {
    menu.addItem(item.name, item.functionName);
  });
  menu.addToUi();
}

onOpenという関数はGASの予約済み関数です。スプレッドシートが開かれた時に自動で実行されます。

「BigQuery」というメニューがメニューバーに追加され、その中に「①テーブルを参照」「②テーブルを更新」というメニューが表示されています。

続いて「①テーブルを参照」「②テーブルを更新」の関数の中身を実装します。

①テーブルを参照

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const projectId = "MyProject"
const datasetId = "Demo"
const tableId = "Test"
const column = "SNS"

function referenceTable() {
  const query = `
        SELECT ${column}
        FROM ${projectId}.${datasetId}.${tableId}
    `;
  const queryResults = BigQuery.Jobs.query(
    {
      useLegacySql: false,
      query: query,
      timeoutMs: 20000,
      location: 'asia-northeast1',
    },
    projectId,
  );

  const texts = [];
  queryResults.rows.forEach(row => {
    texts.push(row.f[0].v);
  });
  const maxId = texts.length;
  const range = sheet.getRange(2, 1, maxId, 1);
  const rule = SpreadsheetApp.newDataValidation().requireValueInList(texts, true).build();
  range.setDataValidation(rule);
}

実行することでスプレッドシート上のA列にプルダウンリストが作成されます。プルダウンの中身はBigQueryから取得したリストです。
今回想定している作業は、スプレッドシート上でA列から変更があるもののみを選択し、B列に変更後の名称を書いて更新する、といったものです。

②テーブルを更新

変更前と変更後の対応を連想配列で格納します。

function createDict() {
  if (sheet.getLastRow() < 2) return {};
  const range = sheet.getRange('A2:B' + sheet.getLastRow());
  const values = range.getValues();
  const dict = {};
  for (let i = 0; i < values.length; i++) {
    let key = values[i][0];
    let value = values[i][1];
    if (value.length > 0 && key.length > 0) {
      dict[key] = value;
    }
  }
  return dict;
}

作成した連想配列を元に、BigQueryを更新するクエリを作成・実行します。

function updateTable() {
  const dict = createDict()
  const caseList = []
  const whereList = []
  for (let oldValue of Object.keys(dict)) {
    let newValue = dict[oldValue];
    caseList.push(`WHEN ${column} = '${oldValue}' THEN '${newValue}'`);
    whereList.push(`'${oldValue}'`);
  }

  let updateQuery = `
      UPDATE ${projectId}.${datasetId}.${tableId}
          SET ${column} =
              CASE ${caseList.join(' ')}
              ELSE ${column}
          END
      WHERE ${column} IN (${whereList.join(', ')})
  `;
  BigQuery.Jobs.query({
    query: updateQuery,
    useLegacySql: false
  }, projectId);
}

これでスプレッドシートからBigQueryのデータを変更できるようになりました。
実際にスプレッドシートでTwitterをXに変更してみます。

正しく変更されました。

最後に使用したコードをまとめておきます。以下を展開してご確認ください。

コードまとめ

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const projectId = "venect-sandbox"
const datasetId = "Demo"
const tableId = "Test"
const column = "SNS"

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menuItems = [
    { name: '①テーブルを参照', functionName: 'referenceTable' },
    { name: '②テーブルを更新', functionName: 'updateTable' },
  ];
  const menu = ui.createMenu('BigQuery');
  menuItems.forEach(item => {
    menu.addItem(item.name, item.functionName);
  });
  menu.addToUi();
}

function referenceTable() {
  const query = `
      SELECT ${column}
      FROM ${projectId}.${datasetId}.${tableId}
  `;
  const queryResults = BigQuery.Jobs.query(
    {
      useLegacySql: false,
      query: query,
      timeoutMs: 20000,
      location: 'asia-northeast1',
    },
    projectId,
  );

  const texts = [];
  queryResults.rows.forEach(row => {
    texts.push(row.f[0].v);
  });
  const maxId = texts.length;
  const range = sheet.getRange(2, 1, maxId, 1);
  const rule = SpreadsheetApp.newDataValidation().requireValueInList(texts, true).build();
  range.setDataValidation(rule);
}

function createDict() {
  if (sheet.getLastRow() < 2) return {};
  const range = sheet.getRange('A2:B' + sheet.getLastRow());
  const values = range.getValues();
  const dict = {};
  for (let i = 0; i < values.length; i++) {
    let key = values[i][0];
    let value = values[i][1];
    if (value.length > 0 && key.length > 0) {
      dict[key] = value;
    }
  }
  return dict;
}

function updateTable() {
  const dict = createDict()
  const caseList = []
  const whereList = []
  for (let oldValue of Object.keys(dict)) {
    let newValue = dict[oldValue];
    caseList.push(`WHEN ${column} = '${oldValue}' THEN '${newValue}'`);
    whereList.push(`'${oldValue}'`);
  }

  let updateQuery = `
      UPDATE ${projectId}.${datasetId}.${tableId}
          SET ${column} =
              CASE ${caseList.join(' ')}
              ELSE ${column}
          END
      WHERE ${column} IN (${whereList.join(', ')})
  `;
  BigQuery.Jobs.query({
    query: updateQuery,
    useLegacySql: false
  }, projectId);
}

まとめ

今回はスプレッドシートから簡単にBigQueryのデータを更新する方法を紹介しました。スプレッドシートからデータを更新できると、BigQueryやSQLが分からないユーザーでもデータの更新がしやすくなるため、エンジニアではない社員向けのツールを作りたい時などに活用できるかと思います。
最後までご覧いただきありがとうございました！