More than 3 years have passed since last update.

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

Last updated at 2022-01-12Posted at 2022-01-07

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

しかし、問題発生です…

それは…

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

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

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

準備

Googleスプレッドシートのデータにアクセスするためには、
Google Cloud Platform の Google Sheets API を利用します。

1. Google Cloud Platform

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

Google Cloud Platform のページを開き、 プロジェクトの選択 をクリック。

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

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

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

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

1-2. Google Sheets API の有効化

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

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

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

1-3. 認証情報の作成

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

キー をクリック。

鍵を追加 をクリック。

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

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

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

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

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

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

2-1. 共有設定

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

右上の 共有 をクリック。

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

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

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

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

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

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

スプレッドシートキーは、 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

[
  {
    "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 を登録。

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. サービスアカウントキーの作成で保存したJSONファイル、gcp-credentials.json をルート直下に設置します。

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

env ファイルの作成

.env をルート直下に作成します。
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

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ファイルが生成されます

npm run json

gulp でJSONファイルを生成