ノーコードでクラウド上のデータとの連携を実現。
詳細はこちら →こんにちは！ドライバー周りのヘルプドキュメントを担当している古川です。
マクロ、カスタム関数、アドオンを使用してGoogle Sheets のAccess データとやり取りします。CData API Server は、ADO.NET Provider for Access（またはその他の250+ ADO.NET Providers）と組み合わせることで、Google Sheets のようなクラウドベースのモバイルアプリケーションからAccess データに接続できるようになります。API Server は、Access およびCData ADO.NET Providers にサポートされるすべてのソースのOData サービスを生成する軽量のWeb アプリケーションです。
Google Apps Script は、これらのOData サービスをJSON 形式で利用できます。この記事では、Google Spreadsheet にOrders データを取り込み、変更を加えたときにAccess データの更新を実行するシンプルなアドオンを作成する方法を説明します。
以下のステップに従って、セキュアなAccess OData サービスの作成を開始します。
API Server は独自のサーバーで実行されます。Windows の場合、スタンドアロンサーバーまたはIIS を使用してデプロイできます。Java サーブレットコンテナの場合は、API Server WAR ファイルをドロップします。詳細と操作方法については、ヘルプドキュメントを参照してください。
API Server はMicrosoft Azure、Amazon EC2、Heroku にも簡単にデプロイすることができます。
API Server とADO.NET Provider for Access をデプロイしたら、API Server 管理コンソールで「Settings」->「Connections」をクリックし、新しい接続を追加してAccess への接続に必要な認証値とその他の接続プロパティを指定します。
接続には、Access データベースのパスをDataSource プロパティに設定します。
次に、「Settings」->「Resources」とクリックしてAPI Server にアクセスを許可するAccess エンティティを選択することができます。
作成するOData サービスを決定したら、「Settings」->「Users」とクリックしてユーザーを認証します。API Server は、認証トークンベースの認証を使用して、主要な認証スキームをサポートします。IP アドレスを使用してアクセスを制限することも可能です。localhost を除くすべてのアドレスからの接続はデフォルトでブロックされるため、この記事ではGoogle のサーバーからの接続を許可する必要があります。SSL を使用すれば、接続の認証だけでなく、暗号化も可能です。
「Tools」->「Script Editor」とクリックして、スプレッドシートからScript Editor を開きます。Script Editor で次の機能を追加し、スプレッドシートにOData クエリの結果を入力します。
function retrieve(){
var url = "https://MyUrl/api.rsc/Orders?select=Id,OrderName,Freight,ShipCity";
var response = UrlFetchApp.fetch(url,{
headers: {"Authorization":"Basic " + Utilities.base64Encode("MyUser:MyAuthtoken")}
});
var json = response.getContentText();
var sheet = SpreadsheetApp.getActiveSheet();
var a1 = sheet.getRange('a1');
var index=1;
var orders = JSON.parse(json).value;
var cols = [["Id","OrderName","Freight","ShipCity"]];
sheet.getRange(1,1,1,4).setValues(cols);
row=2;
for(var i in orders){
for (var j in orders[i]) {
switch (j) {
case "Id":
a1.offset(row,0).setValue(account[i][j]);
break;
case "OrderName":
a1.offset(row,1).setValue(account[i][j]);
break;
case "Freight":
a1.offset(row,2).setValue(account[i][j]);
break;
case "ShipCity":
a1.offset(row,3).setValue(account[i][j]);
break;
}
}
row++;
}
}
次のステップに従って、開いたタイミングでスプレッドシートに入力するインストール可能なトリガーを追加します。
ダイアログを閉じると、アプリケーションへのアクセスを許可するように要求されます。
以下の関数を追加し、セルへの変更をAPI Server に追加します。
function buildReq(e){
var sheet = SpreadsheetApp.getActiveSheet();
var changes = e.range;
var id = sheet.getRange(changes.getRow(),1).getValue();
var col = sheet.getRange(1,changes.getColumn()).getValue();
var url = "http://MyServer/api.rsc/Orders("+id+")";
var putdata = "{\"@odata.type\" : \"CDataAPI.Orders\", \""+col+"\": \""+changes.getValue()+"\"}";;
UrlFetchApp.fetch(url,{
method: "put",
contentType: "application/json",
payload: putdata,
headers: {"Authorization":"Basic " + Utilities.base64Encode("MyUser:MyAuthtoken")}
});
}
下記の手順に従って、アップデートトリガーを追加します。
「Publish」->「Test as Add-On」とクリックすることで、スクリプトを確認できます。バージョン、インストールタイプ、およびスプレッドシートを選択し、テストの構成を作成します。作成したら、選択して実行できます。
セルを変更すると、API Server はAccess データのアップデートを実行します。