Xero のデータでGoogle Sheets を拡張

マクロ、カスタム関数、アドオンを使用してGoogle スプレッドシートからXero のデータとやり取りします。CData API Server は、ADO.NET Provider for Xero（またはその他の250+ ADO.NET Providers）と組み合わせることで、Google Sheets のようなクラウドベースのモバイルアプリケーションからXero のデータに接続できるようになります。API Server は、Xero およびCData ADO.NET Providers にサポートされるすべてのソースのOData サービスを生成する軽量のWeb アプリケーションです。

Google Apps Script（GAS）は、これらのOData サービスをJSON 形式で利用できます。この記事では、Google スプレッドシートにItems データを取り込み、変更を加えたときにXero のデータの更新を実行するシンプルなアドオンを作成する方法を説明します。

はじめに

---

API Server の設定

以下のリンクからAPI Server の無償トライアルをスタートしたら、セキュアなXero OData サービスを作成していきましょう。

Xero への接続

GAS からXero のデータを操作するには、まずXero への接続を作成・設定します。

1. API Server にログインして、「Connections」をクリック、さらに「接続を追加」をクリックします。
2. 「接続を追加」をクリックして、データソースがAPI Server に事前にインストールされている場合は、一覧から「Xero」を選択します。
3. 事前にインストールされていない場合は、コネクタを追加していきます。コネクタ追加の手順は以下の記事にまとめてありますので、ご確認ください。
   CData コネクタの追加方法はこちら >>
4. それでは、Xero への接続設定を行っていきましょう！

5. 接続には、認証用の値に加えてSchema 接続プロパティを設定してください。Xero はプライベートアプリケーション、 パブリックアプリケーション、パートナーアプリケーションに認証を提供します。設定したアプリケーションに応じて、XeroAppAuthentication プロパティを PUBLIC、PRIVATE、またはPARTNER に設定する必要があります。プライベートアプリケーションから接続するには、追加でOAuthAccessToken、OAuthClientId、 OAuthClientSecret、CertificateStoreType、CertificateStore、およびCertificateStorePassword を設定してください。

   パブリックまたはパートナーアプリケーションから接続する場合は、埋め込みOAuthClientId、OAuthClientSecret、 およびCallbackURL を指定するか、アプリを登録してOAuth の値を入手できます。

   Xero への認証については、ヘルプドキュメントの「はじめに」を参照してください。

6. 接続情報の入力が完了したら、「保存およびテスト」をクリックします。

接続には、認証用の値に加えてSchema 接続プロパティを設定してください。Xero はプライベートアプリケーション、 パブリックアプリケーション、パートナーアプリケーションに認証を提供します。設定したアプリケーションに応じて、XeroAppAuthentication プロパティを PUBLIC、PRIVATE、またはPARTNER に設定する必要があります。プライベートアプリケーションから接続するには、追加でOAuthAccessToken、OAuthClientId、 OAuthClientSecret、CertificateStoreType、CertificateStore、およびCertificateStorePassword を設定してください。

パブリックまたはパートナーアプリケーションから接続する場合は、埋め込みOAuthClientId、OAuthClientSecret、 およびCallbackURL を指定するか、アプリを登録してOAuth の値を入手できます。

Xero への認証については、ヘルプドキュメントの「はじめに」を参照してください。

API Server のユーザー設定

次に、API Server 経由でXero にアクセスするユーザーを作成します。「Users」ページでユーザーを追加・設定できます。やってみましょう。

1. 「Users」ページで ユーザーを追加をクリックすると、「ユーザーを追加」ポップアップが開きます。
2. 次に、「ロール」、「ユーザー名」、「権限」プロパティを設定し、「ユーザーを追加」をクリックします。
3. その後、ユーザーの認証トークンが生成されます。各ユーザーの認証トークンとその他の情報は「Users」ページで確認できます。

Xero 用のAPI エンドポイントの作成

ユーザーを作成したら、Xero のデータ用のAPI エンドポイントを作成していきます。

1. まず、「API」ページに移動し、 「 テーブルを追加」をクリックします。
2. アクセスしたい接続を選択し、次へをクリックします。
3. 接続を選択した状態で、各テーブルを選択して確認をクリックすることでエンドポイントを作成します。

OData のエンドポイントを取得

以上でXero への接続を設定してユーザーを作成し、API Server でXero データのAPI を追加しました。これで、OData 形式のXero データをREST API で利用できます。API Server の「API」ページから、API のエンドポイントを表示およびコピーできます。

Xero のデータを取得する

「Tools」->「Script Editor」とクリックして、スプレッドシートからScript Editor を開きます。Script Editor で次の機能を追加し、スプレッドシートにOData クエリの結果を入力します。

function retrieve(){
  var url = "https://MyUrl/api.rsc/Items?select=Id,Name,QuantityOnHand,Name";
  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 items = JSON.parse(json).value;

  var cols = [["Id","Name","QuantityOnHand","Name"]];
  sheet.getRange(1,1,1,4).setValues(cols);

  row=2;
  for(var i in items){
    for (var j in items[i]) {
      switch (j) {
        case "Id":
          a1.offset(row,0).setValue(account[i][j]);
          break;
        case "Name":
          a1.offset(row,1).setValue(account[i][j]);
          break;
        case "QuantityOnHand":
          a1.offset(row,2).setValue(account[i][j]);
          break;
        case "Name":
          a1.offset(row,3).setValue(account[i][j]);
          break;
      }
    }
    row++;
  }
}

次のステップに従って、開いたタイミングでスプレッドシートに入力するインストール可能なトリガーを追加します。

1. 「Resources」->「Current Project's Triggers」->「Add a New Trigger」とクリックします。
2. 「Run」メニューで「retrieve」を選択します。
3. 「From Spreadsheet」を選択します。
4. 「On open」を選択します。

ダイアログを閉じると、アプリケーションへのアクセスを許可するように要求されます。

Xero のデータへの変更を追加する

以下の関数を追加し、セルへの変更を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/Items("+id+")";
  var putdata = "{\"@odata.type\" : \"CDataAPI.Items\",  \""+col+"\": \""+changes.getValue()+"\"}";;
  UrlFetchApp.fetch(url,{
    method: "put",
    contentType: "application/json",
    payload: putdata,
    headers: {"Authorization":"Basic " + Utilities.base64Encode("MyUser:MyAuthtoken")}
  });

}

下記の手順に従って、アップデートトリガーを追加します。

1. 「Resources」->「Current Project's Triggers」とクリックします。
2. 「Run」メニューで「buildReqe」を選択します。
3. 「From Spreadsheet」を選択します。
4. 「On edit」を選択します。

「Publish」->「Test as Add-On」とクリックすることで、スクリプトを確認できます。バージョン、インストールタイプ、およびスプレッドシートを選択し、テストの構成を作成します。作成したら、選択して実行できます。

セルを変更すると、API Server はXero のデータのアップデートを実行します。