Google Sheets Driver を使うと、Google スプレッドシートを、データベースのテーブルのように扱うことができます。
今回はその応用例として、Google Sheets Driver でスプレッドシートを新規に作成したり、1つのシートに複数の表がある場合や、表形式ではないシートで値を読み書きする方法についてご紹介します。
この方法を使うと、Google Sheets Driver を使って、日ごとのシートを自動作成したりすることができます。
Google Sheets Driverには、ODBC, JDBC, ADO.NET, Python, SSIS, Excel Add-In, Power BI, Tableau, FireDAC などいくつもの種類があります。
今回は、JDBC(DbVisualizer) を使用して、具体的に実行するSQL をご紹介しますが、他のドライバーでも同様にSQL を実行することができます。
1.ドライバーのインストール
ドライバーをダウンロードして、インストールを行います。
下記URLから30日間の無償評価版をダウンロードすることができます。
Google Sheets Drivers & Connectors - CData Software Japan
2.接続設定
ポイントは、接続プロパティで「Header=False 」を設定して、列名を1行目のセルの値ではなく、A,B,C といった疑似列名を使用するようにする点です。
CData JDBC Driver for Google Sheets - Header
-DbVisualizer にドライバーを追加
1) メニューバーの「Tools 」→「Driver Manager... 」をクリックします。
2) ＋ボタンをクリックして項目を追加し、名前を指定します。
3) cdata.jdbc.googlesheets.jar ファイルを追加します。
-データベースの追加
Create Database Connection をクリックしてデータベースの接続を作成します。
-接続文字列を作成
1) cdata.jdbc.googlesheets.jar を実行して接続プロパティ画面を開きます。
2) 接続に必要なプロパティを指定します。
今回設定しなければならないのは、「InitiateOAuth=GETANDREFRESH 」と、「Header=False」に2点です。
3)「接続テスト」をクリックすると、Webブラウザ画面が表示されるので許可をクリックすると、認証が行われます。
4) 接続が完了すると接続文字列が生成されるので、「コピー」をクリックし、DbVisualizer のDatabase URL に貼り付けます。
5) 「Connect 」ボタンをクリックして、接続ができることを確認できれば、接続設定は完了です。
3.スプレッドシートの新規作成
スプレッドシートを新規に作成するには、「CreateSpreadsheet 」ストアドプロシージャを使用します。
CData JDBC Driver for Google Sheets - CreateSpreadsheet
指定する主なパラメーターは、スプレッドシート名(Title) 、作成するフォルダーを指定する場合は、親フォルダのId(Parents) です。
今回は、Sample というフォルダー内にスプレッドシートを作成します。
・フォルダIdを取得するSQL
SELECT [Id], [Name] FROM Folders WHERE [Name] = 'Sample';
・Sample フォルダ内に、スプレッドシートを作成するSQL
EXEC CreateSpreadsheet @Title='シートタイトル', @Parents='<フォルダId>';
4.シート名の変更
シート名を変更するためには、まず、名前を変更したいシートのIdを取得します。
・シートのIdとシート名を取得するSQL
SELECT [SpreadsheetId], [SpreadsheetName], [SheetId], [SheetName] FROM Sheets WHERE SpreadsheetId='<スプレッドシートId>';
・「UpdateSheet 」ストアドプロシージャを使用して、シート名を変更します。
CData JDBC Driver for Google Sheets - UpdateSheet
・シート名を変更するSQL
EXEC UpdateSheet @SpreadsheetId='<スプレッドシートId>', @SheetId='0', @Title='新しいシート';
実行後にシート名を取得すると、シート名が変更されていることが分かります。
5.シートに値を書き込む
シートのA2セルに「Hello, world!」という文字を書き込んでみます。
・シート範囲を指定して、A2セルに値を書き込むSQL
UPDATE [新しいスプレッドシート_新しいシート#A1:A2] SET [A] = 'Hello, world!' WHERE id=2;
スプレッドシートをブラウザで開くと、値がシートに反映されていることが確認できます。
SQL とスプレッドシートの対応関係はこのようになっています。
6.書き込んだ値を読み込む
この範囲を指定した指定方法は、SELECT とUPDATE で使用することができるので、以下のように指定することで書き込んだ値を取得することができます。
・シート範囲を指定して、読み込むSQL
SELECT * FROM [新しいスプレッドシート_新しいシート#A1:A2];
7.まとめ
Google Sheets Driver を使うことで、スプレッドシートをまるでデータベースのテーブルのように扱うことができるだけでなく、今回ご紹介した方法を使えば、表形式ではないシートでも柔軟に読み書きを行うことができます。
ぜひGoogle Sheets と Google Sheets Driver を活用してみてください。
関連コンテンツ
