Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

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

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

Google Sheets のデータを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 上部のボタン Deploy(デプロイ)から、Test deployments(デプロイをテスト)を選びます。

image.png

image.png

このURLはテスト用になっていて、末尾が/devになっています。
URLをクリックして、WebブラウザーでWeb APIにアクセスし、JSONが表示されればOKです。なお、テスト用のURLは自分からしかアクセスできません。

image.png

"Authorization is required to perform that action."(承認が必要です)というエラーが出た場合は、スクリプトエディターでRun(実行)してみましょう。

image.png

Permissionsを設定した後に、もう一度 Web API にアクセスしてみてください。

上手く動作したら、Web APIを公開します。Script Editor 上部のボタン Deploy(デプロイ)から、New deployment(新しいデプロイ)を選びます。

image.png

image.png

Deployすると、新しいURLが払い出されます。一般公開されたURLの末尾は/execになっています。

テスト用のURL /devは、自分からしかアクセスできませんが、エディターで保存されている最新のコードで動作します。一方、管理されたURL /execは、デプロイ時の設定によって一般公開も可能ですが、デプロイ時のコードでしか動作しません。コードを編集したら忘れずにNew deployment(新しいデプロイ)で登録してください。

古いScript Editorの場合

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 (スプレッドシートを開いているWebブラウザーのアドレスバーに書いてある 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);
}

注意:大事なことなのでもう一度。コードを修正した場合、New deployment(新しいデプロイ)で公開しなおしましょう(古いScript Editorなら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

takatama
東京で働くソフトウェアエンジニアです。
https://twitter.com/takatama_jp
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away