Edited at

Google SpreadSheet のデータを JSON 形式で取得する Web API をサクッと作る

(データを追加する Web API は Google SpreadSheet にデータを追加する Web API をサクッと作る をご覧ください)

Google SpreadSheets のデータをJSON 形式で取得する Web API を、10分くらいでサクッと作ります。

レスポンスタイムが長いので、あくまで試作用としてご利用ください。


シートにある全ての情報を取得する

seets.png

だったら、

[

{
"title": "TechCrunch Japan",
"feed_url": "http://jp.techcrunch.com/feed/"
},
{
"title": "Engadget Japanese",
"feed_url": "http://japanese.engadget.com/rss.xml"
}
]

が取得できます。


新しいシートを作る

https://docs.google.com/spreadsheets/create で新規のspreadsheetを作ります。


Script Editor でコードを書く

シート上部のメニュー Tools > Script Editor... を選び、Code.gs に以下のコードを記入します。

getData の引数はシート名(ここでは Sheet1)です。

function getData(sheetName) {

var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
var rows = sheet.getDataRange().getValues();
var keys = rows.splice(0, 1)[0];
return rows.map(function(row) {
var obj = {};
row.map(function(item, index) {
obj[String(keys[index])] = String(item);
});
return obj;
});
}

function doGet() {
var data = getData('Sheet1');
return ContentService.createTextOutput(JSON.stringify(data, null, 2))
.setMimeType(ContentService.MimeType.JSON);
}


Web app として公開する

Script Editor 上部のメニュー Publish > Deploy as web app... を選び、Web app として公開します。

注意:コードを修正した場合、Project version に New を選択し、Web app として公開し直す必要があります。

webapp.png


  • Project version:


    • New を選ぶと、Code.gs を新しいバージョンとして保存した上で公開します。この時、このバージョンのコードを説明するコメントを入力できます(コミットメッセージ)。



  • Execute the app as


    • me を選ぶと、自分がこのコードを実行することになります。



  • Who has access to the app


    • Anyone, even anonymos を選ぶとあらゆる人がこの Web API を実行できるようになります。



webapp2.png

Deploy ボタンを押した後、Current web app URL にアクセスすると、JSON 形式のデータを取得できます。

cURLで確認するなら -L オプションをつけるといいです ( https://developers.google.com/apps-script/guides/content#redirects )。

webapp3.png


コードのポイント

@kazinoue さんに教えていただきました。どうもありがとうございます。

参考: Google Apps Script で Spreadsheet にアクセスする方法まとめ

上記したように、スプレッドシートのメニューから Script Editor を選んで作ったスクリプトは Container Bound Script と呼ばれます。CBSだと、

const spreadsheet = SpreadsheetApp.getActive()

で、スプレッドシートにアクセスできます。

もし、スプレッドシートと紐づいていない独立したスクリプトを作った場合は、スプレッドシートの id をメモしておき、

const spreadsheet = SpreadsheetApp.openById(id);

でアクセスする必要があります。スプレッドシートの id は、スプレッドシートの URL が

https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0

だったら、abc1234567 (実際はもっと長い)です。

Class SpreadsheetApp  |  Apps Script  |  Google Developers

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openById(String)


キーワードを含む行だけを取得する

呼び出す URL に ?q=<キーワード> を与えると、そのキーワードを含む行だけを取得できるようにします。

function getData(sheetName, query) {

var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
var rows = sheet.getDataRange().getValues();
var keys = rows.splice(0, 1)[0];
return rows.filter(function(row) {
if (!query) return true;
return row.join(':::').toLowerCase().indexOf(query.toLowerCase()) !== -1;
}).map(function(row) {
var obj = {};
row.map(function(item, index) {
obj[keys[index]] = item;
});
return obj;
});
}

function doGet(e) {
var data = getData('Sheet1', e.parameter.q);
return ContentService.createTextOutput(JSON.stringify(data, null, 2))
.setMimeType(ContentService.MimeType.JSON);
}

注意:大事なことなのでもう一度。コードを修正した場合、Project version に New を選択し、Web app として公開し直す必要があります。


参考

Web Apps  |  Apps Script  |  Google Developers

https://developers.google.com/apps-script/guides/web#deploying

Content Service  |  Apps Script  |  Google Developers

https://developers.google.com/apps-script/guides/content