スプレッドシートのデータをAPI化して他のスプレッドシートで活用できないかと思い、
色々と試行錯誤していた中で一旦
スプレッドシートをJSONデータにして他のスプレッドシートへ情報を入れるまではできたので、その記録です。
まずスタンドアロンスクリプトで作っていたので下記のスクリプトを記載。
function getData() {
var ssId = '******';
var shtName = '******';
var values = SpreadsheetApp.openById(ssId).getSheetByName(shtName).getDataRange().getValues();
return values;
}
function doGet(e) {
var data = getData();
var res = ContentService.createTextOutput();
res = res.setMimeType(ContentService.MimeType.JSON); //※1
res = res.setContent(JSON.stringify(data, null, 2)); //※2
return res;
}
※1
Mimeタイプの種類は下記URLを参照
https://developers.google.com/apps-script/reference/content/mime-type.html
※2
JSON.stringify()は引数を3つとることができるようです。
2つ目の引数については下記が参考になります。
https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/JSON/stringify
ただ上記は難しかったので私が参考にしたのは下記です。
https://www.sejuku.net/blog/79911
なくても動きますが、インデントをいくついれるかを数字で指定が可能。
上記のコードを「ウェブアプリケーションとして公開」します。
そこで作成されたURLを使って下記のコードを書きます。
function setData() {
var url = 'https://script.google.com/macros/s/******/exec'; //上記で作成されたURL
var res = UrlFetchApp.fetch(url);
var values = JSON.parse(res.getContentText());
var ssId = '******';
var shtName = '******';
var values = SpreadsheetApp.openById(ssId).getSheetByName(shtName ).getRange(1,1,values.length,values[0].length).setValues(values);
}
上記で実装完了。
ただ、日付データや時間データが入る場合はJSONデータにエンコードした際に、
世界標準時間に変更されてしまうので注意!
つまり、時間が-9時間されてしまいます。
例えば下記の様なA列に日付データ、B列に時間データが入っている場合は次のように処理します。
function setData() {
var url = 'https://script.google.com/macros/s/******/exec'; //上記で作成されたURL
var res = UrlFetchApp.fetch(url);
var values = JSON.parse(res.getContentText());
values.forEach(function(row, i) {
//1行目にタイトルが入っているのでそれ以外の行で実行
if(i !== 0){
var date = new Date(row[0].substr(0,10)); //※3
date.setDate(date.getDate() + 1);
values[i][0] = Utilities.formatDate(date, 'JST', 'yyyy/MM/dd');
var time = new Date('1899/12/30 '+row[1].substr(11,8)); ※3
time.setHours(time.getHours() + 9);
values[i][1] = Utilities.formatDate(time, 'JST', 'HH:mm:ss');
}
});
var ssId = '******';
var shtName = '******';
var values = SpreadsheetApp.openById(ssId).getSheetByName(shtName ).getRange(1,1,values.length,values[0].length).setValues(values);
}
※3
JSONデータにすると日付/時間データはそれぞれ下記のように変わっているので
substrで必要なところだけデータを文字だけを抽出