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
}]
|
デモデータ
コード.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.gsvar 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 Visualization APIでシートのデータを取得する
Google Visualization APIの vはvalueで、fはformettedValueっぽい
