はじめに

世の中にはいろいろ便利なツールがありますが、個別最適化されたツールを使っているとデータがあちこちにちらばってしまうという欠点があると思います。
そこで、APIを公開しているツールから情報をSpreadSheetに転記し、誰でも簡単に集計作業やマスター作成を行えるようになる方法を紹介します。

APIとは？

みなさんの使用しているツールはAPIを公開しているでしょうか？そもそもAPIとは、ある特定の認証をクリアした場合にツールを外部から操作することができる機能をいいます。これを利用することで、外部のツールに対しSpreadSheet上に情報を転記するように要求することができます。

具体的な方法

まず、情報を転記したいスプレッドシートを開き、「ツール」から「スクリプトエディタ」を開いてください。そこに、以下のコードを書きます。

function myFunctionJSON() {
  // 変数spreadsheetに指定のスプレッドシートオブジェクトを取得します
  var sp_url = "スプレッドシートのURL";
  var spreadsheet = SpreadsheetApp.openByUrl(sp_url);

  // spread sheetのシート設定
  var sheet = spreadsheet.getSheetByName("シートの名前");

  // 認証に使用するアクセストークンを登録
  var token="使用したいサービスのアクセストークン"
  var options =
   {
     "method"  : "get",
     "headers" : {"Authorization" : "Bearer "+ token}
   };

  // 取得したい情報のキーを登録
  var keys = {
    取得したいキー:" "
  };

  //転記する行をここにためておく
  var row=1;

  // page_numberなどの変数はurlのなかに本来であれば混ぜます。使用法としてはpageごとの情報の取得が終わったら次のページにめくってもらう用途です。
  for (var page_number=1;page_number<=100;page_number++){
    var url1 = "情報取得のためのURL";
    var json = UrlFetchApp.fetch(url1,options).getContentText();
    var jsonData = JSON.parse(json);
     // per_page=10で指定したため、10回繰り返したあとでページをめくっています。
     for (var i=0; i<=9; i++) {
      var j = 1;
      for (var key in keys) {
       sheet.getRange(row, j).setValue(jsonData[i][key]);
       j++;
     }
     row++;
    }
   }
}

これで一旦完成です。

なにをしているのか？

このコードで何をしているか説明します。
まず最初に、そもそもどのスプレッドシートに転記するのかをURLで指定し、さらにその中のどのシートに転記するのかをシートの名前でしていしています。

つぎに、アクセストークンを指定しています。アクセストークンは、APIの認証に必要なパスワードのようなもので、各Webサービスで発行しています。アクセストークンの取得方法は各Webサービスにお問い合わせください。
その下のoptionsというものはおまじないと思って無視してください(詳しく知りたい方はhttp通信についての記事がネットにたくさんありますのでそこを御覧ください)

次に、取得したい情報のキーを配列で記録しています。通常、webサービスからの情報はJSONという形式で送られてきます。JSONでは

key:value

という形式で情報が送られてきます。イメージ的には、keyのところにタイトルが書いてあってvalueの中にその詳細が書いてある感じです。例えば

国名:日本

のようなイメージでしょうか。
そこで、ここではJSONの中でどの情報を記録したいのかをキーをもとに指定します。例えば、国名を取得したければ

国名:" "

という形で羅列していってください(尤もkeyは通常英語なので国名、というキーは存在しませんが)

そもそもどんなKeyがあるのか？というのは各APIによるところが大きいです。APIには必ず使用解説書があるのでそれをお読みください。

その後、行数を保存するためのrowという変数を指定したあと、forループを用いて具体的にシートへの転記を進めていきます。
最初に、URLを指定していますが、これも各APIの使用解説書にどのようなURLをうつとどのような情報が返ってくるのかが書いてありますのでそれを参考にしてください。

さて、話は前後しますがここでfor文の定義の中を見ると、page_numberという変数を指定しています。これは、APIでは一度に送れる情報に限りがあるので、本に例えれば本全体を一度に送るのではなく各ページごとに送るという仕様があるため、何ページ目を送ってほしいのかを指定するために使用しています。通常URLの中にページ数の情報を含めることができるので、page_numberは

"URLの一部" + page_number + "URLの一部"

みたいな感じで使用して各ループごとに違うページを参照できるようにしています。

また、APIでは各ページにどれくらいの情報を載せることができるのか指定できます。このスクリプトの場合は各ページに10の情報を載せたので(per_page=10と表現しています)二層目のforループでは0から9までの10回を1ループとしています。

問題点

このスクリプトの問題点としては、JSONの階層構造の中で、一層のものしか取得できないことです。つまり、JSONの特徴としてValueとして配列を指定することができるのですが、その配列の情報を取得しようと思うと少しコードを変えなくてはいけないという点です。
機会があれば、配列があるverのコードについても解説したいと思います。

おわりに

わからないところがあればお気軽にご質問ください。僕はエンジニアではなく管理部の人間ですので、非エンジニアからの質問も大歓迎です。

GASを使ってAPIを公開しているwebサービスのデータをスプレッドシートに転記する方法

はじめに

APIとは？

具体的な方法

なにをしているのか？

問題点

おわりに