GoogleスプレッドシートからJSONファイルを生成する

ExcelファイルからJSONファイルを生成できるようになり快適なコーディングライフを過ごしていました。
詳細は以下をご覧ください。

https://qiita.com/taqumo/items/51f8034801494d2b3588

https://qiita.com/taqumo/items/c9cfb578bc278ccc5239

しかし、問題発生です…:pensive:

それは…:cold_sweat:

Excelファイルを複数人で修正する場合、ファイルの受け渡しがとっても面倒くさい…:sob:

なので、ExcelではなくGoogleスプレッドシートを利用することにしました！

というわけで、GoogleスプレッドシートからJSONファイルを生成する方法を紹介します。

## 準備

Googleスプレッドシートのデータにアクセスするためには、
[Google Cloud Platform](https://console.cloud.google.com/?hl=ja) の [Google Sheets API](https://developers.google.com/sheets/api) を利用します。

### 1. Google Cloud Platform

#### 1-1. プロジェクトを作成

[Google Cloud Platform](https://console.cloud.google.com/?hl=ja) のページを開き、 `プロジェクトの選択` をクリック。

![01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/fd9e002c-35e6-fa02-f62e-43c7aebf7bbf.png)

モーダルウィンドウが開くので `新しいプロジェクト` をクリック。

![02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/1390257e-20e4-9580-af81-f7069e942c5e.png)

以下のように設定し、`作成` をクリック。

* プロジェクト名（任意の値）
  * Google Sheets
* 場所
  * 組織なし

![03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/396fb684-5049-26c3-ef51-c859d4c36040.png)

プロジェクト作成の通知が表示されます。

![04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/6538d690-b06f-cb59-cb6c-6c86607daad4.png)

#### 1-2. Google Sheets API の有効化

サイドバーの `APIとサービス` > `ライブラリ` をクリック。

![05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/f78fa649-942a-25c6-66d5-e1bed0cd1625.png)

APIを利用するプロジェクトを選択します。

![06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/baafd6f3-9002-baed-a6ee-3da54b206c74.png)

Google Sheets API を検索し、`有効にする` をクリック。

![07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/2a915663-3cef-78a9-a105-b5fd8ac88a31.png)

![08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/2fe1d8ad-2477-ee6d-aae6-2152e385b345.png)

![09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/1ceb0fc0-3db1-bb4d-b2fd-a7f33664dfcd.png)

#### 1-3. 認証情報の作成

Google Sheets API を利用するための認証情報を作成します。
`認証情報を作成` をクリック。

![10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/939e0547-b2c6-b78e-09ae-18241a87227b.png)

##### 1-3-1. 認証情報の種類

以下のように設定し、`次へ` をクリック。

* 使用する API
  * Google Sheets API
* アクセスするデータの種類
  * アプリケーション データ
* Compute Engine、Kubernetes Engine、App Engine、Cloud Functions でこの API を使用する予定はありますか？
  * いいえ、使用していません

![11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/3ffa28c4-e4ff-ec09-d0fa-5e0a66457eed.png)

#### 1-4. サービスアカウントの作成

##### 1-4-1. サービスアカウントの詳細

すべて任意の値となります。
とりあえず以下のように設定し、`作成して続行` をクリック。

* サービスアカウント名（任意の値）
  * spreadsheet
* サービスアカウントID（任意の値）
  * spreadsheet
* サービスアカウントの説明（任意の値）
  * Googleスプレッドシートの取得

![12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/67e7fc81-1205-9dd9-8edc-774e3e78aefc.png)

##### 1-4-2. このサービスアカウントにプロジェクトへのアクセスを許可する

ロール `閲覧者` を追加し、`続行` をクリック。

![13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/48fb63d8-3258-bae3-15f2-f40e9e477a89.png)

##### 1-4-3. ユーザーにこのサービスアカウントへのアクセスを許可

よくわからなかったので、空のまま `完了` をクリック。

![14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/d1d33226-5124-c1be-e87a-7bde4fff6c30.png)

サービスアカウントが作成されました。

:::note info
作成されたメールアドレスはスプレッドシートの共有時に利用するため控えておきます。
:::

![15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/8573a5da-828f-386a-5f3c-81e6938f74c6.png)

#### 1-5. サービスアカウントキーの作成

サービスアカウント右端にある、 `鉛筆アイコン ` をクリック。

![16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/6536dbd8-cf25-8cd3-5171-b6785a881205.png)

`キー` をクリック。

![17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/8747dec7-6927-fed1-8896-0d66b293b2ae.png)

`鍵を追加` をクリック。

![18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/8dc2ccf4-7708-9c13-f6f3-27dd1bd92015.png)

キーのタイプは `JSON` を選択し、`作成` をクリック。

![19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/7c508a83-74e5-3d2f-10b0-ee30136b2ad5.png)

JSONファイルがダウンロードされます。
そのままだと名前が長いので、`gcp-credentials.json` にリネームしました。

:::note info
JavaScript からのアクセス時に必要なため保存しておきます。
:::

![20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/4712c11b-9ed6-b37c-90e1-00880a545aeb.png)

:::note alert
サービスアカウントキーは、不正使用されるとセキュリティ上のリスクになる可能性がありますので管理には注意してください。
また、ダウンロード以外の方法もあるようなので以下を参考にしてください。
:::

https://cloud.google.com/iam/docs/workload-identity-federation

https://cloud.google.com/blog/ja/products/identity-security/how-to-authenticate-service-accounts-to-help-keep-applications-secure


以上で Google Cloud Platform の準備は完了です。

### 2. Googleスプレッドシート

#### 2-1. 共有設定

JSONファイル元となるGoogleスプレッドシートの共有設定にサービスアカウントを追加します。

右上の `共有` をクリック。

![21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/4c7258e6-651e-b92a-933e-d531df72042d.png)

表示されたモーダルウィンドウ内のテキストボックスに[1-4-3. ユーザーにこのサービスアカウントへのアクセスを許可](#1-4-3-ユーザーにこのサービスアカウントへのアクセスを許可)で作成したサービスアカウントのメールアドレスを入力し、`完了` をクリック。

![22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/bac12118-0b3e-bd82-8525-0d919e609933.png)

権限を `閲覧者` に変更し、`送信` をクリック。

![23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/ef1692ae-a2bc-817b-7fb1-e2ffb8da2fb3.png)

サービスアカウントが追加されたことを確認し、`完了` をクリック。

![24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492675/06ca6a77-38f8-a874-2bb2-0c8201e57051.png)

#### 2-2. スプレッドシートキーの取得

GoogleスプレッドシートのURL文字列内の以下 `xxxxxxxxxx` の部分がキーとなります。

```
https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#gid=0000000000
```

:::note info
スプレッドシートキーは、 JavaScript からのアクセス時に必要なため控えておきます。
:::

以上で Googleスプレッドシートの設定は完了です。

## 仕様

* Googleスプレッドシートの各シートがJSONファイルの対象
  * シート名が英数字・ハイフン（-）・アンダーバー（_）の場合はJSONファイルを生成する
  * シート名が日本語（英数字 `-_` 以外）の場合はJSONファイルを生成しない
* 生成されるJSONファイル名はシート名（英数字 `-_`）と同じ

### Googleスプレッドシートと生成されるJSONファイル

* Googleスプレッドシートの1行目の値が各アイテムの `key` となる
* Googleスプレッドシートの2行目以降が各アイテムとなり、セルが `value` となる
* セルが空の場合は空文字を出力
* 数字だけの文字列は数値に変換して出力

#### Googleスプレッドシートのサンプル

| A  | B  | C  |
|----|----|----|
|    | B1 | C1 |
| A2 | B2 | C2 |
| A3 | B3 |  3 |

#### 生成されるJSONファイル

```json:JSON
[
  {
    "A": "",
    "B": "B1",
    "C": "C1"
  },
  {
    "A": "A2",
    "B": "B2",
    "C": "C2"
  },
  {
    "A": "A3",
    "B": "B3",
    "C": 3
  }
]
```

## npm-scripts でJSONファイルを生成

### ファイル構造

Googleスプレッドシートの各シートのデータがjsonフォルダに格納されます。

```
root
├── json/
│   ├── hoge.json
│   ├── fuga.json
│   └── piyo.json
├── node_modules/
├── npm-scripts/
│   └── spreadsheet-to-json.js
├── .env
├── gcp-credentials.json
├── package.json
└── package-lock.json
```

### 準備

#### package.json の作成

`package.json` をルート直下に作成します。

`scripts` フィールドに `json` という名前で `spreadsheet-to-json.js` を実行する npm-scripts を登録。

```json:package.json
{
  "name": "spreadsheet-to-json",
  "version": "1.0.0",
  "private": true,
  "scripts": {
    "json": "node ./npm-scripts/spreadsheet-to-json.js"
  }
}
```

```
root
└── package.json
```

#### node_module のインストール

必要な `node_module` は以下となります。

| モジュール名         | 役割                     |
|---------------------|--------------------------|
| ansi-colors         | ログメッセージに色をつける |
| dotenv              | 環境変数を設定する |
| fs                  | ファイルを操作する |
| spreadsheet-to-json | GoogleスプレッドシートをJSON形式で取得 |

```
npm i -D ansi-colors dotenv fs spreadsheet-to-json
```

```
root
├── node_modules/
├── package.json
└── package-lock.json
```

#### サービスアカウントキーを設置

[1-5. サービスアカウントキーの作成](#1-5-サービスアカウントキーの作成)で保存したJSONファイル、`gcp-credentials.json` をルート直下に設置します。

```
root
├── node_modules/
├── gcp-credentials.json
├── package.json
└── package-lock.json
```

#### env ファイルの作成

`.env` をルート直下に作成します。
[2-2. スプレッドシートキーの取得](#2-2-スプレッドシートキーの取得)で保存したキーを設定します。

```:.env
SPREADSHEET_KEY=xxxxxxxxxx
```

```
root
├── node_modules/
├── .env
├── gcp-credentials.json
├── package.json
└── package-lock.json
```

#### npm-scripts の作成

npm-scriptsディレクトリを作成し、npm-scripts を記述した `spreadsheet-to-json.js` というファイルを格納します。

```
root
├── node_modules/
├── npm-scripts/
│   └── spreadsheet-to-json.js
├── .env
├── gcp-credentials.json
├── package.json
└── package-lock.json
```

```js:spreadsheet-to-json.js
require('dotenv').config()
const c = require('ansi-colors')
const { extractSheets } = require('spreadsheet-to-json')
const fs = require('fs')

class SpreadsheetToJson {
  constructor() {
    this.dest = 'json/'
    this.option = {
      // google spreadhsheet key
      spreadsheetKey: process.env.SPREADSHEET_KEY,
      // google oauth2 credentials or API_KEY
      credentials: require('../gcp-credentials.json'),
      // 選択シートのみを対象とする場合はシート名を配列で指定
      // sheetsToExtract: ['sheetName', 'sheetName', 'sheetName'],
      // セルの値のフォーマット関数
      formatCell: (val) => {
        // nullを空文字に変換
        if (val === null) return ''
        // 数値だけの文字列を数値に変換
        if (/^[-]?([1-9]\d*|0)(\.\d+)?$/.test(val)) return Number(val)
        return val
      }
    }
  }

  init() {
    // スプレッドシートをJSON形式で取得して処理を実行
    extractSheets(this.option, (err, data) => {
      if (err) {
        console.log('ERROR:', err)
      }
      // 各シートごとの処理
      for (let [sheetName, sheetData] of Object.entries(data)) {
        const sheetNameRegex = /^[0-9a-zA-Z_-]*$/
        // シート名に含まれる文字が「英数字_-」以外は処理を抜ける
        if (!sheetNameRegex.test(sheetName)) return
        this.writeJsonFile(sheetName, sheetData)
      }
    })
  }

  // JSONファイルの書き込み
  writeJsonFile(fileName, data) {
    const jsonStr = JSON.stringify(data, null, 2)
    const jsonPath = `${this.dest}${fileName}.json`
    // JSONファイルの格納ディレクトリがなければ作成
    if (!fs.existsSync(this.dest)) {
      fs.mkdirSync(this.dest, { recursive: true })
    }
    // JSONファイル作成
    fs.writeFile(jsonPath, jsonStr, (err) => {
      if (err) {
        console.error(
          c.red('ERR! ') +
            `Failed to create '${this.dest}${fileName}.json'：${err}`
        )
        return
      }
      console.log(`Created '` + c.green(jsonPath) + `'`)
    })
  }
}
const spreadsheetToJson = new SpreadsheetToJson()
spreadsheetToJson.init()
```

### npm-scripts の実行

以下コマンドを入力するとJSONファイルが生成されます:ok_hand:

```
npm run json
```

## gulp でJSONファイルを生成

### ファイル構造

Googleスプレッドシートの各シートのデータがjsonフォルダに格納されます。

```
root
├── json/
│   ├── hoge.json
│   ├── fuga.json
│   └── piyo.json
├── node_modules/
├── .env
├── gcp-credentials.json
├── gulpfile.js
├── package.json
└── package-lock.json
```

### 準備

#### package.json の作成

`package.json` をルート直下に作成します。

```json:package.json
{
  "name": "gulp-spreadsheet-to-json",
  "version": "1.0.0",
  "private": true
}
```

```
root
└── package.json
```

#### node_module のインストール

必要な `node_module` は以下となります。

| モジュール名         | 役割                     |
|---------------------|--------------------------|
| ansi-colors         | ログメッセージに色をつける |
| dotenv              | 環境変数を設定する |
| fancy-log           | 時刻付きのログを表示 |
| fs                  | ファイルを操作する |
| gulp                | gulp |
| spreadsheet-to-json | GoogleスプレッドシートをJSON形式で取得 |

```
npm i -D ansi-colors dotenv fancy-log fs gulp spreadsheet-to-json
```

```
root
├── node_modules/
├── package.json
└── package-lock.json
```

#### サービスアカウントキーを設置

[1-5. サービスアカウントキーの作成](#1-5-サービス-アカウント-キーの作成)で保存したJSONファイル、`gcp-credentials.json` をルート直下に設置します。

```
root
├── node_modules/
├── gcp-credentials.json
├── package.json
└── package-lock.json
```

#### env ファイルの作成

`.env` をルート直下に作成します。
[2-2. スプレッドシートキーの取得](#2-2-スプレッドシートキーの取得)で保存したキーを設定します。

```:.env
SPREADSHEET_KEY=xxxxxxxxxx
```

```
root
├── node_modules/
├── .env
├── gcp-credentials.json
├── package.json
└── package-lock.json
```

#### gulpfile.js の作成

ルート直下に、gulp の処理を記述した `gulpfile.js` を作成します。

```
root
├── node_modules/
├── .env
├── gcp-credentials.json
├── gulpfile.js
├── package.json
└── package-lock.json
```

```js:gulpfile.js
require('dotenv').config();
const c = require('ansi-colors')
const { extractSheets } = require('spreadsheet-to-json')
const fs = require('fs')
const log = require('fancy-log')

const spreadsheetToJson = (cb) => {
  const dest = 'json/'
  const option = {
    // google spreadhsheet key
    spreadsheetKey: process.env.SPREADSHEET_KEY,
    // google oauth2 credentials or API_KEY
    credentials: require('./gcp-credentials.json'),
    // 選択シートのみを対象とする場合はシート名を配列で指定
    // sheetsToExtract: ['sheetName', 'sheetName', 'sheetName'],
    // セルの値のフォーマット関数
    formatCell: (val) => {
      // nullを空文字に変換
      if (val === null) return ''
      // 数値だけの文字列を数値に変換
      if (/^[-]?([1-9]\d*|0)(\.\d+)?$/.test(val)) return Number(val)
      return val
    }
  }
  
  // JSONファイルの書き込み
  const writeJsonFile = (fileName, data) => {
    const jsonStr = JSON.stringify(data, null, 2)
    const jsonPath = `${dest}${fileName}.json`
    // JSONファイルの格納ディレクトリ作成
    if (!fs.existsSync(dest)) {
      fs.mkdirSync(dest, { recursive: true })
    }
    // JSONファイル作成
    fs.writeFileSync(jsonPath, jsonStr)
    log(`Created '` + c.green(jsonPath) + `'`)
  }

  // スプレッドシートをJSON形式で取得して処理を実行
  extractSheets(option, (err, data) => {
    if (err) {
      console.log('ERROR:', err)
    }
    // 各シートごとの処理
    for (let [sheetName, sheetData] of Object.entries(data)) {
      const sheetNameRegex = /^[0-9a-zA-Z_-]*$/
      // シート名に含まれる文字が「英数字_-」以外は次のシートを処理
      if (!sheetNameRegex.test(sheetName)) continue
      writeJsonFile(sheetName, sheetData)
    }
    cb()
  })
}

// gulp のタスクを登録
exports.spreadsheetToJson = spreadsheetToJson;
```

### gulpタスクを実行

以下コマンドを入力するとJSONファイルが生成されます:ok_hand:

```
npx gulp spreadsheetToJson
```