【GAS】編集権限のないSpreadsheetをJSON形式で出力する。

はじめに

みなさんも生きてきて「あ～この編集権限のないSpreadsheetをJSON形式で出力したいな～」
と思ったことが1度や2度あったはずです。

そう思った人のためにこの記事を残します。

事例

このスプレッドをJSONで出力したいな～
でも

• セルの結合しすぎててそのままじゃ無理
• JSONで使うkeyの名前は英字にしたい
• いらない情報が多い
• 編集しようにも権限がない

※このスプレッドは以下サイトを用いてダミーデータを作成しています。
疑似個人情報生成 - 生成条件入力

編集権限のないSpreadsheetをどう整形するの？

一番簡単なのは、スプレッド自体をコピーしてしまうことですが
原本が更新された場合に毎回コピーし直さないといけないですよね。

一行書くだけで自動的に同期するクローンSpreadsheetを作れたらな～
なんて、都合のいいものあるわけないか…

> あります <

importrange関数を使います。
IMPORTRANGE - ドキュメント エディタ ヘルプ

データの読み込み元となるスプレッドシートの URL とセルを指定することでデータを読み込めます。
読み込み用のスプレッドを作成して早速使ってみましょう。

そのまま importrange 関数を使うと、空白の行も持ってきてしまいます。
空白の行を排除するために query 関数と併用しましょう。

参考 : 【GAS】importrangeを使ったSpreadsheetにgetDataRange()した時、空白の行まで取得してしまう。 - Qiita

query 関数では表示する列の制御もできるので、必要な項目のみ表示させるようにしましょう。今回は

• NO
• 名前
• 名前(カナ)
• メールアドレス
• 誕生日

のみJSON出力させたいので、以下のように指定しました。
(※importrangeでデータを参照している場合、一番左の列から順にCol1、Col2と連番が振られています)

= query(importrange("https://docs.google.com/spreadsheets/d/[シートID]/edit","シート1!A3:N"),"select Col1,Col2,Col3,Col5,Col6,Col13 where Col1 IS NOT NULL")

おお、これでJSONで出力するのに適した形になったぞ。

あとはJSONで出力するだけです。

GASを使ってサクッとJSONにしましょう。

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.JAVASCRIPT);
}

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

公開手順はリンク先を参考にしてください。

出力されたJSON

公開したAPIにアクセスするとJSONデータを閲覧できます。

[
  {
    "no": 1,
    "name": "小沢桜",
    "name_kana": "オザワサクラ",
    "phone": 453062941,
    "email": "[e-mail]",
    "birthday": "1983-06-25T15:00:00.000Z"
  },
  {
    "no": 2,
    "name": "正木米子",
    "name_kana": "マサキヨネコ",
    "phone": 285265192,
    "email": "[e-mail]",
    "birthday": "1977-09-25T15:00:00.000Z"
  },
  {
    "no": 3,
    "name": "菅和島",
    "name_kana": "カンワジマ",
    "phone": 652014766,
    "email": "[e-mail]",
    "birthday": "1976-07-31T15:00:00.000Z"
  },
  {
    "no": 4,
    "name": "横川胡桃",
    "name_kana": "ヨコカワクルミ",
    "phone": 24628512,
    "email": "[e-mail]",
    "birthday": "1988-07-29T15:00:00.000Z"
  },
  {
    "no": 5,
    "name": "大貫篤也",
    "name_kana": "オオヌキアツヤ",
    "phone": 279873191,
    "email": "[e-mail]",
    "birthday": "1998-03-28T15:00:00.000Z"
  },
  {
    "no": 6,
    "name": "安田紗良",
    "name_kana": "ヤスダサラ",
    "phone": 746460303,
    "email": "[e-mail]",
    "birthday": "1964-06-06T15:00:00.000Z"
  },
  {
    "no": 7,
    "name": "水島沙也佳",
    "name_kana": "ミズシマサヤカ",
    "phone": 987921415,
    "email": "[e-mail]",
    "birthday": "1990-04-24T15:00:00.000Z"
  },
  {
    "no": 8,
    "name": "馬場英俊",
    "name_kana": "ババヒデトシ",
    "phone": 768215964,
    "email": "[e-mail]",
    "birthday": "1991-02-28T15:00:00.000Z"
  },
  {
    "no": 9,
    "name": "橘栄次",
    "name_kana": "タチバナエイジ",
    "phone": 285916204,
    "email": "[e-mail]",
    "birthday": "1972-03-27T15:00:00.000Z"
  },
  {
    "no": 10,
    "name": "白木通夫",
    "name_kana": "シラキミチオ",
    "phone": 896951861,
    "email": "[e-mail]",
    "birthday": "1987-08-04T15:00:00.000Z"
  }
]

きれいに10件のJSONが取得できています。

もちろん、参照元のスプレッドが更新されたらJSONも自動で更新されます。