api
GoogleAppsScript
JSON
GoogleSpreadSheet

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