公開されているGoogle Spread Sheetの値をAPIっぽくJSON形式で取得してみる

Google App Scriptででない方法でGoogle Spread Sheetの値を取得できることを知ったので、実際に試してみました。
しかし、どうやら返されるデータが変わっていたようなので、自分で整形する関数を作りました。
ついでということで、JSONを取得する方法をまとめてみました。

0. はじめに

この方法ではスプレッドシートのデータがWebで公開している状態である必要があります。
共有をしない状態で取得したい場合は、Googleの提供するAPI経由でごちゃごちゃする必要があります。この記事ではその話はしておりませんのでご了承ください。

1. データを作る

兎にも角にもデータがないと何も始まりませんので、データを作りました。
(データがすでにある場合はこのステップをすっ飛ばしてお読みください。)

シート

リンク: Twitterから取ってきたかのようなデータ

ダミーテキストは日本語ダミーテキスト生成器から『夜明け前』のものを拝借しています。

2. スプレッドシートの「ウェブに公開…」をオンにする

ファイル→「ウェブに公開…」

sheet2_1

「リンク」タブの下にあるプルダウンから公開したいワークシートを選択します。
今回の場合は「シート1」を選択。
最後に下の青い「公開」ボタンを押すとウェブに公開された状態になります。

sheet2_2

スプレッドシートの設定は以上で終わりです。

3. シートIDとワークシートのIDを取得する

シートID

シートIDはスプレッドシートそのものに振られたIDで、URLに記載されています。
https://docs.google.com/spreadsheets/d/1o6MUl6eKO2jsi8jeckhk7rBVeYH5lIjxD8l6HEaXhMg/edit#gid=0
1o6MUl6eKO2jsi8jeckhk7rBVeYH5lIjxD8l6HEaXhMgの部分です。

ワークシートIDは自力で調べる必要があります。

ワークシートID

以下のURLの{シートID}の部分を書き換えてアクセスします。

https://spreadsheets.google.com/feeds/worksheets/{シートID}/public/basic

するとXML形式のデータがでてきます。
この中の<id>~</id>部分にURLが書いてあるのですが、それの末尾の部分がワークシートIDになります。
今回のシートの場合、ワークシートIDはod6となります。

sheet

実は、スプレッドシートの一枚目のワークシートIDはod6で固定されているので、シートの1枚目を使う場合はod6を入れるだけで大丈夫です。
2枚目以降の場合は上記方法を用いて調べましょう。

4. 取得・整形する

JSONデータは以下2通りのURLから取得できます。変更されているのは途中のlistcellsの部分だけです。

https://spreadsheets.google.com/feeds/list/{シートID}/{ワークシートID}/public/basic?alt=json
https://spreadsheets.google.com/feeds/cells/{シートID}/{ワークシートID}/public/basic?alt=json

cells一つ一つのセルの情報をベースにしたJSONデータを返します。
listは1行目を見出しとし、それ移行の内容は全部テキスト形にたJSONデータを吐き出します。

どうしてこうなった

説明だけだとわからないので、一旦URLにアクセスして見てみましょう。

(データの整形が楽そうだったので)今回はリスト形式を元に解説します。
以下はリスト形式の方にアクセスした際の画像です。

sheet02

うっ、汚い…

簡単に説明すると、以下のようにデータが入っています

  • 1列目の値はdata.feed.entry[i].title.$t
  • 2列目移行はdata.feed.entry[i].content.$t内に"1行目: 値, 2行目: 値, ..."

storevalue

理解したとこで、これをjQueryの$.ajaxでとってきて、整形しましょう。
とりあえずとってくるだけ。


const sheetId = '1o6MUl6eKO2jsi8jeckhk7rBVeYH5lIjxD8l6HEaXhMg';
const workSheetId = 'od6';

$.ajax({
    type: 'GET',
    url: `https://spreadsheets.google.com/feeds/list/${sheetId}/${workSheetId}/public/basic?alt=json`,
    dataType: 'json',
    cache: false
}).done((data, textStatus, jqXHR) => {
    // data内にシートのJSONデータが入っている
    console.log(data);
}).fail((jqXHR, textStatus, errorThrown) => {
    console.error('ajax function error!');
});

ただ取ってきただけでは汚いまんまなので、以下のように整形する関数formatSheetData(data.feed.entry)を作成しましょう。

[
    {
        "id": 1,
        "name": "ひろし",
        "user_id": "@hiroshi",
        "text": "木曽路は〜"
    },
    {
        "_": "以下続く"
    }
]

ここまで記事書いて気づいたのですが、list形式ではシート内のA1の値は取得できていないようです。
(別の書き方をすればいけるかもしれないので、判明し次第追記いたします。)


function formatSheetData(entry) {
    const entryLength = entry.length;
    let outputData = [];
    for(let i =0; i < entryLength; i++) {
        let categorizedString = entry[i].content.$t.split(', ');
        let column = {};
        for(let j = 0; j < categorizedString.length; j++) {
            let keys = categorizedString[j].split(': ');
            column[keys[0]] = keys[1];
        }
        // A1のセルだけは取得できないので手動対応
        column['id'] = parseInt(entry[i].title.$t, 10);
        outputData.push(column);
    }

    return outputData;
}

そして$.ajax.doneの部分で整形すれば完了です!お疲れ様でした。


const sheetId = '1o6MUl6eKO2jsi8jeckhk7rBVeYH5lIjxD8l6HEaXhMg';
const workSheetId = 'od6';

$.ajax({
    type: 'GET',
    url: `https://spreadsheets.google.com/feeds/list/${sheetId}/${workSheetId}/public/basic?alt=json`,
    dataType: 'json',
    cache: false
}).done((data, textStatus, jqXHR) => {
    const users = formatSheetData(data.feed.entry);
}).fail((jqXHR, textStatus, errorThrown) => {
    console.error('ajax function error!');
});

Codepenのほうにデモを載せているので、そちらも参考にしてください。

スプレッドシートの値をGETするデモ - Codepen

注意点など

JSON変換後にkeyにする部分は英語にしておく

1行目(あるいは1列目)などのjsonデータを整形する際にkeyにしたい部分は英語にしておきましょう。

最近では日本語の変数も使用できるブラウザはありますが、古いブラウザは動かない可能性があります。

(Adobe AnimateではCreate.jsで使える形式でjsアニメーションファイルを出力できるのですが、レイヤーに指定された日本語変数が普通に出力されたりします。おそろしや...)

古いAPIなのでいつ置き換わるかわからない

公式にそういった文言がのっていますので参考に。

Simple example of retrieving JSON feeds from Spreadsheets Data API

最後に

今回はAPI認証キーを使わずにGoogle SpreadSheetからデータを引っ張ってみました。
なぜあんなJSONを返しているのかは永久に謎です。

  • BLOG
  • >
  • web
  • >
  • 公開されているGoogle Spread Sheetの値をAPIっぽくJSON形式で取得してみる