2018年2月18日日曜日

Google Visualization APIでシートのデータを取得する


シート上ではQuery関数でSQLを書けますが
スクリプトで実現する方法を探し回ってたどり着いたのが
Google Visualization APIでした

色々試して実現できたコード.gsを書き残しておきます

今回の例で用いたシートのデータ

以下のようなデータが入力されていて
ABC
1あいうえおアイウエオ
2かきくけこカキクケコ
3


"SELECT A, B"
↑このようなクエリを書いて


[[あいうえお, アイウエオ], [かきくけこ, カキクケコ]]
↑このような配列で取得する


ということを実現するコードの例です


コード.gs
var SS_ID = "SS_ID";
var SH_ID = SH_ID;
var BASE_URL = "https://docs.google.com/spreadsheets/d/";
var URL = BASE_URL + SS_ID + "/gviz/tq?gid=" + SH_ID + "&tqx=out:json&tq=";

SpreadsheetApp.openById(SS_ID);

function get_public_data() {
  var options = null;
  data_via_gviz(options);
}

function get_private_data() {
  var access_token = get_access_token();
  var headers = get_headers(access_token);
  var options = get_options(headers);
  data_via_gviz(options);
}

function get_access_token() {
  var access_token = ScriptApp.getOAuthToken();
  return access_token;
}

function get_headers(access_token) {
  var headers = {
    "Authorization": "Bearer " + access_token
  }
  return headers;
}

function get_options(headers) {
  var options = {
    "contentType": "application/json",
    "headers": headers,
    "muteHttpExceptions": true
  }
  return options;
}

function data_via_gviz(options) {
  var query = encodeURIComponent("SELECT A, B");
  var response = UrlFetchApp.fetch(URL + query, options);
  var jobj = get_jobj(response);
  var array = get_array(jobj);
  Logger.log([response, array]);
}

function get_jobj(response) {
  var data = response.getContentText();
  data = data.split("google.visualization.Query.setResponse(")[1];
  data = data.substr(0, data.length - 2);
  var jobj = JSON.parse(data);
  return jobj;
}

function get_array(jobj) {
  var rowlen = jobj["table"]["rows"].length;
  var collen = jobj["table"]["cols"].length;
  var array = [];
  for (var i = 0; i < rowlen; i++) {
    var values = [];
    for (var j = 0; j < collen; j++) {
      values.push(jobj["table"]["rows"][i]["c"][j]["v"]);
    }
    array.push(values);
  }
  return array;
}
意訳
スプレッドシートのID
対象のシートのID
BASE_URL
URLを作る

「Google ドライブのスプレッドシートの表示と管理」の権限を許可しておかないとprivateの場合にUnauthorizedでデータ取得できないので承認フローを入れる

この機能がやること 公開されているシートのデータを取得する
optionsにnullを入れて
data_via_gviz()に渡す


この機能がやること 非公開のシートのデータを取得する
get_access_token()からaccess_tokenを取得して
get_headers()に渡してheadersを取得して
get_options()に渡してoptionsを取得して
data_via_gviz()に渡す


この機能がやること
ScriptApp.getOAuthToken()でaccess_tokenを取得して
返す


この機能がやること
headersを作成して
access_tokenを入れて

返す


この機能がやること
optionsのオブジェクトを作って
contentTypeはapplication/jsonにして
headersを入れて
muteHttpExceptionsをtrueにして（fetchでエラーが出ても例外を投げない）

返す


この機能がやること
queryを作って
URL+queryとoptionsでfetchして
get_jobj()にresponseを渡してjobjに入れて
get_array()に渡してarrayに入れて
ログに出す


この機能がやること
渡されたresponseのテキストを取得して
指定した文字列以降を抜き出して
最後の二文字を消して（ ); を消す）
JSON解析して
返す


この機能がやること
行数を取得して
列数を取得して
結果を入れる配列を用意して
一行ずつ以下を繰り返す
valuesの配列を用意して
一列ずつ以下を繰り返す
セルの値を取得してvaluesに追加する

values（一行分のデータ）をarrayに追加する

arrayを返す



補足


responseはこのようになっているので

/*O_o*/
google.visualization.Query.setResponse({
  "version": "0.6",
  "reqId": "0",
  "status": "ok",
  "sig": "1103093093",
  "table": {
    "cols": [{
      "id": "A",
      "label": "",
      "type": "string"
    }, {
      "id": "B",
      "label": "",
      "type": "string"
    }],
    "rows": [{
      "c": [{
        "v": "あいうえお"
      }, {
        "v": "アイウエオ"
      }]
    }, {
      "c": [{
        "v": "かきくけこ"
      }, {
        "v": "カキクケコ"
      }]
    }]
  }
});


コード.gs内の以下のコードで
google.visualization.Query.setResponse()内の文字列を抜き出しています
  • data = data.split("google.visualization.Query.setResponse(")[1];
  • data = data.substr(0, data.length - 2);

range.getValues()で取得する場合


シートIDを指定して.getValues()でデータを取得する例も書いておきます

var SS_ID = "SS_ID";
var SH_ID = SH_ID;

function get_values() {
  var ss = SpreadsheetApp.openById(SS_ID);
  var sh = get_sheet(ss, SH_ID);
  var range = sh.getRange("A1:B2");
  var values = range.getValues();
  Logger.log(values);
}

function get_sheet(ss, sh_id) {
  var shs = ss.getSheets();
  for (var i = 0; i < shs.length; i++) {
    if (shs[i].getSheetId() == sh_id) {
      break;
    }
  }
  var sh = ss.getSheets()[i];
  return sh;
}

PublicシートとPrivateシートの取得


SS_IDとSH_IDを指定してから
Publicシートの場合は get_public_data()
Privateシートの場合は get_private_data()
をそれぞれ実行するとデータを取得できます


Privateシートのデータの取得にはaccess tokenが必要で
ScriptApp.getOAuthToken() で取得しています



  • Webに公開されている(Public)シートのデータを取得する場合
    • get_public_data()を実行する
      • access_tokenの取得は不要なのでoptionsはnullにして
      • data_via_gviz(options)を実行する
  • 非公開(Private)シートのデータを取得する場合
    • get_private_data()を実行する
      • get_access_token()でaccess_tokenを取得して
        • get_headers(access_token)に渡して
        • get_options(headers)でoptionsを作って
      • data_via_gviz(options)に渡して実行する
      • SpreadsheetApp.openById(SS_ID)で「Google ドライブのスプレッドシートの表示と管理」の権限を許可するフローを入れてやると取得できるようになる

WHEREを使ってみる


上記コード.gsのdata_via_gviz(options){}内にある

var query = encodeURIComponent("SELECT A, B");

を

var query = encodeURIComponent("SELECT A, B WHERE A = 'あいうえお'");

としてやると


A列が あいうえお のA, B列の値を取得できる