Google Apps Script試行錯誤 Blog: Google Visualization APIでシートのデータを取得する（ヘッダをキーにしたオブジェクトを作る）

2019年11月4日月曜日

Google Visualization APIでシートのデータを取得する（ヘッダをキーにしたオブジェクトを作る）

Google Visualization APIでシートのデータを取得する
ではシートのデータを配列にして取得しましたが
今回はオブジェクトにして取得するコードを書きます。

例としてこのようなシートを用意して

そのシートのデータを
このようなオブジェクトにして取得したい。

[{
  date = 2019 / 11 / 01,
  task = task1,
  label = label1
}, {
  date = 2019 / 11 / 01,
  task = task2,
  label = label2
}, {
  date = 2019 / 11 / 01,
  task = task3,
  label = label1
}, {
  date = 2019 / 11 / 02,
  task = task4,
  label = label3
}, {
  date = 2019 / 11 / 02,
  task = task5,
  label = label2
}, {
  date = 2019 / 11 / 02,
  task = task6,
  label = label1
}]

デモデータ

date	task	label
2019/11/01	task1	label1
2019/11/01	task2	label2
2019/11/01	task3	label1
2019/11/02	task4	label3
2019/11/02	task5	label2
2019/11/02	task6	label1

コード.gs

var SS_ID = "スプレッドシートのID";
var SH_ID = 0;// シートの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, C");
  var response = UrlFetchApp.fetch(URL + query, options);
  var jobj = get_jobj(response);
  var array = get_array(jobj);
  Logger.log(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 col_labels = get_col_labels(jobj);
  var row = jobj["table"]["rows"];
  var array = [];
  for (var i = 0; i < row.length; i++) {
    var obj = {};
    for (var j = 0; j < col_labels.length; j++) {
      var value = '';
      var icj = jobj["table"]["rows"][i]["c"][j];
      if(icj === null) {// nullならば
        value = '';
      } else {
        value =icj["v"];
        var formatted = icj["f"];
        if(formatted) {// formattedが存在すれば
          value = formatted;// valueに入れる
        }
        obj[col_labels[j]] = value;
      }
    }
      array.push(obj);
  }
  return array;
}

function get_col_labels(jobj) {
  var cols = jobj["table"]["cols"];
  var col_labels = [];
  for(var i = 0; i < cols.length; i++) {
    var label = cols[i]['label'];
    col_labels.push(label)
  }
  return col_labels
}

補足

jobj["table"]["cols"]のlabelに値が入っている場合と入っていない場合があった。

手元で確認したこと
セルの表示形式を確認しました。

「自動」の場合はlabelに一行目の値が入っていました
「書式なしテキスト」の場合はlabelは空になっていました

labelが空の場合はrowsの0番目に一行目の値が入っているので、以下のコード2.gsを書きました。

コード2.gs

var SS_ID = "スプレッドシートのID";

var SH_ID = 0;// シートの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;
  var result_array = data_via_gviz(options);
  Logger.log(result_array);
}

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

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, C");
  var response = UrlFetchApp.fetch(URL + query, options);
  var jobj = get_jobj(response);
  var array = get_array(jobj);
  return 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 row0_values = get_row1_values(jobj);
  var rows = jobj["table"]["rows"];// 0番目は
  rows.shift();// 0番目はrow0_valuesで取得しているのでここで除外しておく
  var array = [];
  for (var i = 0; i < rows.length; i++) {
    var obj = {};
    for (var j = 0; j < row0_values.length; j++) {
      var value = '';
      var icj = jobj["table"]["rows"][i]["c"][j];
      if(icj === null) {// iのcのjがnullならば
        value = '';
      } else {
        value =icj["v"];
        var formatted = icj["f"];
        if(formatted) {// formattedが存在すれば
          value = formatted;// valueに入れる
        }
        obj[row0_values[j]] = value;
      }
    }
      array.push(obj);
  }
  return array;
}

function get_row1_values(jobj) {
  var row1s = [];
  var row0 = jobj["table"]["rows"][0]["c"];
  for(var i = 0; i < row0.length; i++) {
    var label = row0[i]["v"];
    row1s.push(label)
  }
  return row1s
}

Google Apps Script試行錯誤 Blog

2019年11月4日月曜日

Google Visualization APIでシートのデータを取得する（ヘッダをキーにしたオブジェクトを作る）

Latest post

Google Apps Scriptの障害時はIssueTrackerを見てみる - Incidents for Apps Script are reported on Issue Tracker