Cloudflare D1のデータをGASでGoogle Spreadsheetに同期してみる

2023年11月28日 13:21

先日オープンベータ版が発表された、サーバーレスのSQLiteデータベースであるCloudflare D1。

Cloudflare Workersにバインドできるため、エッジアプリケーション内で認証なしに直接アクセスでき、そこそこ高速で安価に利用できます。

しかし、まだベータ版なこともあり、データを取り出すには SQL クエリを叩くしかありません。Integration などあるはずがありません。

Cloudflare D1のデータのKPIをいい感じにBI的に可視化する方法無いかなあ…　WorkersでJSON返すAPI作れば連携できるかな
MetabaseかLooker StudioかPower BIあたりで見たい
— saip (@_saip_) November 27, 2023

ないので作りました。

手順

Cloudflare のダッシュボードで、API Key を取得します。
https://dash.cloudflare.com/profile/api-tokens
1. 「トークンを作成する」→「カスタムトークンを作成する」を選択
  1. 適当なトークン名を入力
  2. 「アクセス許可」は「アカウント」「D1」「編集」と設定
  3. その他は既定値
2. API Key は後で使用します。
Google Spreadsheet で新規スプレッドシートを作成します。
https://sheets.new/
「拡張機能」→「Apps Script」を選択します。
スクリプトエディタが開くので、以下のコードをコピーして貼り付けます。
- CF_ACCOUNT_ID: アカウント ID
  ログイン状態で https://dash.cloudflare.com/ を開くとURL末尾に入る英数混合の文字列
- CF_API_TOKEN: 先程取得した API Key
- CF_DATABASE_ID: データベース ID
- CF_TABLE_NAME: テーブル名
スクリプトエディタで保存し、関数fetchDataToSheetを実行してください。

※API Keyが漏れると誰でもデータベースにアクセスできてしまいます。取り扱いにはご注意ください。
デプロイしたり、スクリプトを共有する可能性がある場合は、スクリプトプロパティからアクセスするようにすると安全です。
参考：GASで環境変数(スクリプトプロパティ)を使う方法

GASのコード

列を動的に取得するパターン

あらゆるテーブルに対応できますが、タイムスタンプは自動変換されません

const CF_ACCOUNT_ID = "<PUT_HERE>"
const CF_API_TOKEN = "<PUT_HERE>"
const CF_DATABASE_ID = "<PUT_HERE>"
const CF_TABLE_NAME = "<PUT_HERE>"

function fetchDataToSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();  // 既存のデータをクリア

  var url = `https://api.cloudflare.com/client/v4/accounts/${CF_ACCOUNT_ID}/d1/database/${CF_DATABASE_ID}/query`;
  var options = {
  'method' : 'post',
  'headers': {
    'Content-Type': 'application/json',
    'Authorization': `Bearer ${CF_API_TOKEN}`
  },
  'payload' : JSON.stringify({
    "sql": `SELECT * FROM ${CF_TABLE_NAME};`
})
};
  var response = UrlFetchApp.fetch(url, options); // APIからデータを取得
  var json = response.getContentText(); 
  var data = JSON.parse(json);

  var rows = [];

  // 最初の要素からヘッダーを生成する
  if (data.result && data.result.length > 0 && data.result[0].results && data.result[0].results.length > 0) {
    var headers = Object.keys(data.result[0].results[0]);
    rows.push(headers); // ヘッダー行を追加

    // データ行を作成
    data.result.forEach(function(item) {
      item.results.forEach(function(result) {
        var row = headers.map(function(header) {
          return result[header]; // 各キーに対応する値を取得
        });
        rows.push(row);
      });
    });
  }

  // スプレッドシートにデータを書き込み
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

列をあらかじめ指定するパターン

この方法だと、ヘッダーやデータ型を静的に指定することができます。列の定義は実際のテーブルに応じて調整してください。

const CF_ACCOUNT_ID = "<PUT_HERE>"
const CF_API_TOKEN = "<PUT_HERE>"
const CF_DATABASE_ID = "<PUT_HERE>"
const CF_TABLE_NAME = "<PUT_HERE>"

function fetchDataToSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();  // 既存のデータをクリア

  var url = `https://api.cloudflare.com/client/v4/accounts/${CF_ACCOUNT_ID}/d1/database/${CF_DATABASE_ID}/query`;
  var options = {
  'method' : 'post',
  'headers': {
    'Content-Type': 'application/json',
    'Authorization': `Bearer ${CF_API_TOKEN}`
  },
  'payload' : JSON.stringify({
    "sql": `SELECT * FROM ${CF_TABLE_NAME};`
})
};
  var response = UrlFetchApp.fetch(url, options); // APIからデータを取得
  var json = response.getContentText(); 
  var data = JSON.parse(json);

  var rows = [];

  // ヘッダーを追加
  rows.push(['ID', 'User ID', 'Role', 'Content', 'Created At']);

  // データを解析し、行に変換
  data.result.forEach(function(item) {
    item.results.forEach(function(result) {
      rows.push([
        result.id, 
        result.user_id, 
        result.role, 
        result.content, 
        new Date(result.created_at), // Unix timestampをDateオブジェクトに変換
      ]);
    });
  });

  // スプレッドシートにデータを書き込み
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

追加機能

メニューバーから実行

// スプレッドシートが開かれた時に実行される関数
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // メニューを追加
  ui.createMenu('カスタムメニュー')
      .addItem('データを取得', 'fetchDataToSheet')
      .addToUi();
}

このスクリプトを保存してからスプレッドシートを再読み込みすると、スプレッドシートのメニューバーに「カスタムメニュー」という新しいメニュー項目が表示されます。「カスタムメニュー」をクリックし、「データを取得」を選択すると、fetchDataToSheet関数が実行され、APIからデータを取得してスプレッドシートのアクティブなシートに表示します。

シート名からテーブル名を取得する

アクティブなシートの名前をテーブル名として利用します。複数テーブルのデータを1つのスプレッドシートで管理したいときに有効です。

const CF_ACCOUNT_ID = "<PUT_HERE>"
const CF_API_TOKEN = "<PUT_HERE>"
const CF_DATABASE_ID = "<PUT_HERE>"

function fetchDataToSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const CF_TABLE_NAME = sheet.getName(); // タブ名（テーブル名）を取得
  sheet.clear();  // 既存のデータをクリア

  ...

なおデータを更新するにはシートを開いておく必要があります。

Spreadsheet に読み込めば、それを Google Looker Studio に連携して KPI をグラフなどで可視化することができるので、とても便利です。