概要

タイトル通り、スプレッドシートの内容をJSON形式に変換してAPIで取得する方法を記述します。
基本的にはコピペで実装できるようにしたいと考えていますので、
エンジニアの方は勿論ですがプログラミング知識がなくても作れるようにしたいです。

そもそもどうして投稿しようと思ったか

もともとは仕事ではなくプライベートで使っていたのですが、
最近仕事でスプレッドシートをAPI化することがありまして、備忘録を作ろうと思った時に
・・・もしや、全世界の1人にでも参考にしてくれるのでは！？
って思ったので、ものは試しに書いてみることにしました。

たとえ閲覧してくださる方がいなくても備忘録として使って行こうと思います。

注意事項

・今回、この記事が初投稿になりますので読みにくいかもしれません。ごめんなさい。
・投稿時は動作確認出来ていますが、対応していない可能性がございます。
・基本的に頭が悪いので「日本語は変！！」とか「は？」ってなるかもですね。
・もともと私もいろんなサイトを閲覧して作りましたので、他のサイトの方が見やすいかも知れません。
　※参考したリンクを記載しておきます。
　Google SpreadSheet のデータを JSON 形式で取得する Web API をサクッと作る

つくってみよう！！

どうでもいい前置きは置いといて早速記述していきますね。
やることは以下４手順のみです。

・スプレッドシート作成
・データの入力
・GoogleAppsScriptの記述(ほぼコピペ)
・APIの設定と公開

1.スプレッドシートの作成

スプレッドシート作成します。

ここで気をつけることは1点

スプレッドシートの作成する場所です。
基本的にはどこでも良いのですが、公開したスプレッドシートの移動させてしまった場合、
データが取得されなくなる可能性があります。
又、データを取得するURLが変更する必要が出てきます。

2.データの入力

次に作成したスプレッドシートにデータ入れていきましょう。
今回はスプレッドシート1行目がデータ構造のKey(黄色)となります。
２行目以降がvalue(水色)となります。
key(黄色)の部分が極力英語で記述してください。
日本語でも大丈夫だと思いますけど、動作は保証しません。(PHPで取り扱うなら一応大丈夫・・・)
value(水色)の部分は好きなデータを入力してください。

ここではスプレッドシートIDとシート名を覚えておきましょう！！

スプレッドシートID -> スプレッドシートのファイル単位で生成させる文字列
スプレッドシート名 -> スプレッドシートファイル内に各シート名

スプレッドシートIDの表示場所は
https://docs.google.com/spreadsheets/d/ここがスプレッドシートIDです/edit#gid=0
です。

シート名は好きな名前を入れてください。
基本的には英語ですが日本語でも大丈夫です。

赤枠がスプレッドシートID
緑枠がシート名です。

3.GoogleAppsScriptの記述

作成したスプレッドシートにGoogleAppsScriptを記述します。
基本的には文法はJavaScriptと同じですので、そんなに難しくありません。
ただ一部ES6も使えるみたいですがconst変数等の動作がJavaScriptとは異なるみたいです。
アロー演算子使えないのは少し・・・なんでもないです

スプレッドシートのメニューからツール -> スクリプトエディタを開いてください

開いたら以下GoogleAppsScriptを記述します。
このソースコードを全部コピペしてもらえれば大丈夫です。

GoogleAppsScript


function getData(id, sheetName) {
  var sheet = SpreadsheetApp.openById(id).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[keys[index]] = item;
    });
    return obj;
  });
}

function doGet() {
  var data = getData('スプレッドシートID', 'シート名');
  return ContentService.createTextOutput(JSON.stringify(data, null, 2))
  .setMimeType(ContentService.MimeType.JSON);
}

記述内容は上記コピペで良いですが、先ほどのスプレッドシートIDとシート名が必要になります。
シングルコーテーション('')の中を書き換えてくださいね！！
該当箇所は自分のものと置き換えてください。コピペが終わったら保存してくださいね！！
※プロジェクト名の変更を求められたらなんでも良いですが、自分のわかりやすい名前にしましょう！！

4.APIの設定と公開

よく頑張った！！あとは公開設定するだけだ！！

プログラムをコピペしたファイルのメニューから　公開 -> ウェブアプリケーションとして導入　を選択します。

プロジェクトバージョンの記入と、公開範囲を設定します。
今回は特に気にしていませんが、
他人には見られたくないデータを使っている場合は、
他人が見えないように設定しましょう！！

設定が完了したら導入を押します。

導入を押すと以下画面が表示されます。
これで完了です。

「現在のウェブアプリケーションのURL」にあるURLをコピーします。
ここのURLは忘れないように保存しておきましょう。

コピーしたURLにアクセスします。
初アクセス時は作成したAPIの認証が表示されますので許可してください。
許可が完了したら以下のようにスプレッドシート内1行目をkey項目とし2行目以降をvalue項目として記入した内容が表示されます。

上記JSONはスプレッドシートの各行ごとにObjectが生成されます。
又、スプレッドシートの中身を変更したら自動的に内容も変更されます。
日付や時間、数値を扱う場合、スプレッドシートに表示されているものと異なる場合があります。
そちらはだいたいはスプレッドシートのセル設定を変更すれば治ります。

同一スプレッドシート内で別シートを追加したい場合、プログラムを少し書き換える必要があります。
もし、要望があればそちらのコードも記載します。

あとがき

長々となってしましたが、以上となります。
今回はJSON形式で簡単なデータ構造となっていますが、GoogleAppsScriptの内容を変更すれば
CSVやXML形式にしたり、ファイルの作成も勿論可能です。
又、AWS等へのアップロードやGoogleAppsScriptの共通化も出来たります。
出来ることが結構多いですね

最後に

過不足があればご指摘いただれば幸いです。
少しでも良いなっと思ったらLGTMやストックしていただけるととても嬉しいです。
拙い文章で申し訳ありませんでしたが、最後まで閲覧してくださってありがとうございます。

コピペでスプレッドシートをJSON形式のAPIにする方法

概要