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

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

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

# 事例

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

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

![JSONで出力したいSpread](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/260763/4c46d664-d6ea-45c8-5c94-3b0003c86813.png)

※このスプレッドは以下サイトを用いてダミーデータを作成しています。
[疑似個人情報生成 - 生成条件入力](http://hogehoge.tk/personal/generator/?)

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

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

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


<br>
<br>
<br>
 
 
**> あります <**

<br>
<br>
<br>

`importrange`関数を使います。
[IMPORTRANGE - ドキュメント エディタ ヘルプ](https://support.google.com/docs/answer/3093340?hl=ja)

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

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

参考 : [【GAS】importrangeを使ったSpreadsheetにgetDataRange()した時、空白の行まで取得してしまう。 - Qiita](https://qiita.com/fuga___/items/68126dad584ae9226acf)

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

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

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

```js
= 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")
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/260763/089db2df-9366-190d-7b4a-631c630dd9fa.png)

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

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

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

```js
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](https://qiita.com/takatama/items/7aa1097aac453fff1d53)

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

## 出力されたJSON

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

```json
[
  {
    "no": 1,
    "name": "小沢桜",
    "name_kana": "オザワサクラ",
    "phone": 453062941,
    "email": "sakura80230@rcxluloy.vefv.ocq",
    "birthday": "1983-06-25T15:00:00.000Z"
  },
  {
    "no": 2,
    "name": "正木米子",
    "name_kana": "マサキヨネコ",
    "phone": 285265192,
    "email": "yoneko674@xfckylfpqe.mp",
    "birthday": "1977-09-25T15:00:00.000Z"
  },
  {
    "no": 3,
    "name": "菅和島",
    "name_kana": "カンワジマ",
    "phone": 652014766,
    "email": "wajima40772@zllpumrcqq.vf",
    "birthday": "1976-07-31T15:00:00.000Z"
  },
  {
    "no": 4,
    "name": "横川胡桃",
    "name_kana": "ヨコカワクルミ",
    "phone": 24628512,
    "email": "kurumi01462@qzrmftgto.lv",
    "birthday": "1988-07-29T15:00:00.000Z"
  },
  {
    "no": 5,
    "name": "大貫篤也",
    "name_kana": "オオヌキアツヤ",
    "phone": 279873191,
    "email": "atsuya68818@cvjcux.yp",
    "birthday": "1998-03-28T15:00:00.000Z"
  },
  {
    "no": 6,
    "name": "安田紗良",
    "name_kana": "ヤスダサラ",
    "phone": 746460303,
    "email": "sarayasuda@rpefkag.jdp",
    "birthday": "1964-06-06T15:00:00.000Z"
  },
  {
    "no": 7,
    "name": "水島沙也佳",
    "name_kana": "ミズシマサヤカ",
    "phone": 987921415,
    "email": "sayaka26327@ljaj.trr",
    "birthday": "1990-04-24T15:00:00.000Z"
  },
  {
    "no": 8,
    "name": "馬場英俊",
    "name_kana": "ババヒデトシ",
    "phone": 768215964,
    "email": "hidetoshi900@frttojxxed.svt",
    "birthday": "1991-02-28T15:00:00.000Z"
  },
  {
    "no": 9,
    "name": "橘栄次",
    "name_kana": "タチバナエイジ",
    "phone": 285916204,
    "email": "eiji14785@uoqlmb.pxq",
    "birthday": "1972-03-27T15:00:00.000Z"
  },
  {
    "no": 10,
    "name": "白木通夫",
    "name_kana": "シラキミチオ",
    "phone": 896951861,
    "email": "michio56782@gutgsppkcq.ani",
    "birthday": "1987-08-04T15:00:00.000Z"
  }
]
```
きれいに10件のJSONが取得できています。

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