【Googleスプレッドシート】SQLite結果をJSON経由で取り込み!REST API化の手法

SQLiteデータベースに蓄積したデータをGoogleスプレッドシートで活用したいと考えたことはありませんか。スプレッドシートはデータ分析や可視化に便利ですが、直接SQLiteに接続することはできません。そこで、JSON形式を介してデータを取り込み、さらにそのデータをREST APIとして提供する方法が有効です。この記事では、Google Apps Scriptを使ってSQLiteの結果をJSON経由で取り込み、スプレッドシートをAPI化する手法を解説します。

SQLiteデータをJSON形式で出力する準備

まず、SQLiteデータベースの内容をJSONに変換する方法を説明します。SQLiteからJSONを生成するには、コマンドラインツールやプログラミング言語を使用します。例えば、Pythonのsqlite3モジュールを使ってクエリ結果をJSONに変換できます。変換したJSONファイルは外部からアクセス可能な場所に配置します。WebサーバーやGoogle Cloud Storageなどのクラウドストレージにアップロードし、公開URLを取得してください。このURLが後のインポート手順で使用します。

GoogleスプレッドシートにJSONデータを取り込む2つの方法

JSONデータをスプレッドシートに取り込む方法は2つあります。IMPORTDATA関数は単純ですがJSONには対応していません。一方、Google Apps Scriptを使えば柔軟にJSONを処理できます。ここでは後者を中心に解説します。

IMPORTDATA関数ではJSONを直接インポートできない点に注意

IMPORTDATA関数はCSVやTSV形式のデータしか読み込めません。JSONをそのまま指定するとエラーになります。そのため、JSONを事前にCSVに変換するか、次のApps Scriptを使う方法を選択してください。

Google Apps ScriptでUrlFetchAppを使う方法

Google Apps Scriptを使用すると、外部URLからJSONを取得し、シートに書き込むことができます。手順は以下の通りです。

1. スクリプトエディタを開く
   Googleスプレッドシートの「拡張機能」メニューから「Apps Script」を選択し、エディタを開きます。
2. JSON取得関数を作成する
   以下のコードを貼り付けます。function fetchJSON() { var url = ‘https://example.com/data.json’; var response = UrlFetchApp.fetch(url); var json = JSON.parse(response.getContentText()); var sheet = SpreadsheetApp.getActiveSheet(); var data = json.data; for (var i = 0; i < data.length; i++) { sheet.getRange(i+1, 1, 1, data[i].length).setValues([data[i]]); } }
3. 関数を実行する
   スクリプトエディタでfetchJSON関数を選択し、実行ボタンを押します。初回は権限の承認が必要です。承認後、シートにデータが書き込まれます。

上記のコードでは、JSONオブジェクトのdata配列を1行ずつシートに書き込んでいます。JSONの構造に応じて処理を調整してください。また、大量データの場合はループ処理の効率化を検討します。

スプレッドシートデータをREST APIとして公開する手順

スプレッドシートの内容を外部からJSON形式で取得できるREST APIとして公開するには、Google Apps ScriptでWebアプリを作成します。以下の手順に従ってください。

1. doGet関数を記述する
   先ほどのスクリプトエディタに、以下のコードを追加します。function doGet(e) { var sheet = SpreadsheetApp.getActiveSheet(); var data = sheet.getDataRange().getValues(); var json = JSON.stringify(data); return ContentService.createTextOutput(json).setMimeType(ContentService.MimeType.JSON); }
2. Webアプリとしてデプロイする
   スクリプトエディタで「デプロイ」ボタンをクリックし、「新しいデプロイ」を選択します。種類を「ウェブアプリ」に設定し、アクセス権限を「全員(匿名ユーザーを含む)」にします。「デプロイ」を実行するとURLが発行されます。
3. 発行されたURLをテストする
   ブラウザでそのURLにアクセスすると、シートのデータがJSON形式で表示されます。このURLがREST APIのエンドポイントになります。

注意点として、デプロイのたびに新しいURLが発行されます。バージョン管理を行う場合は、特定のバージョンを固定してください。

注意点とよくある失敗

この手法を実践する際に注意すべき点をいくつか挙げます。

CORS制限への対応

Webアプリとして公開したAPIは、異なるオリジンからのアクセス時にCORSエラーが発生することがあります。対策として、doGet関数内でレスポンスヘッダーにAccess-Control-Allow-Originを追加します。ただし、Google Apps Scriptではヘッダー設定が制限されるため、代理サーバーを経由する方法も検討してください。

データ量の制限

UrlFetchAppで取得できるデータサイズは約50MBまでです。また、Webアプリのレスポンスサイズも制限があります。大規模データの場合は分割して複数回のリクエストに分けるか、圧縮を検討します。

権限と公開範囲

スクリプトの実行やWebアプリの利用には適切な権限が必要です。特にWebアプリを全員に公開する場合は、機密データが流出しないよう注意してください。アクセス制限をかけたい場合は、認証機能を追加します。

IMPORTDATAとApps Scriptの比較

JSONデータを取り込む方法を比較します。以下の表を参考にしてください。

まとめ

この記事では、SQLiteデータをJSON経由でGoogleスプレッドシートに取り込み、さらにそのデータをREST APIとして公開する方法を解説しました。Google Apps Scriptを使えば、外部URLからJSONを取得してシートに反映できるだけでなく、doGet関数を使ってスプレッドシートを簡単にWeb API化できます。応用として、JSONの構造に合わせたデータ変換や、トリガーによる定期更新を組み合わせることで、より柔軟なデータ連携が実現できます。ぜひ実際のワークフローに組み込んで活用してください。