【GAS】スプレッドシートの値をjson化する

やりたいこと

以下のような「1行目がヘッダー、2行目以降がデータ行」の形式で入力されているスプレッドシートの値を
ヘッダー行の項目名をkeyに、値をvalueにしたjson形式に変換します。
そして、カラム・データの増減にも対応できるようにしていきます。

	A	B	C	D
1	ID	購入日	商品	価格
2	1	2023/02/01	りんご	100
3	2	2023/02/02	ねぎ	110
4	3	2023/02/02	キャベツ	138
5	4	2023/02/03	りんご	110

json出力イメージ

[
  { "ID": "1", "購入日": "2023/02/01", "商品": "りんご", "価格": "100" },
  { "ID": "2", "購入日": "2023/02/02", "商品": "ねぎ", "価格": "110" },
  { "ID": "3", "購入日": "2023/02/02", "商品": "キャベツ", "価格": "138" },
  { "ID": "4", "購入日": "2023/02/03", "商品": "りんご", "価格": "110" }
]

GASのコード

さっそくGASのコードです。
「シート1」という名前のシートのA1から入力されている想定です。

スプレッドシートの値をjson化

function main() {
  // スプレッドシート・シート取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("シート1");

  // 値が入力されている範囲を取得
  const range = sheet.getDataRange();
  // 画面表示されている通りに値を2次元配列に入れる
  const values = range.getDisplayValues();

  console.log(values);

  // -------- スプレッドシートの値取得ここまで 以下json変換 -------- 

  // jsonObject用配列を作成
  let jsonObj = [];

  // 2行目(データ行)からループ 1行目しかない(データがない)場合ループに入らない
  for (const row of values.slice(1)) {
    // 行データをobject化 {"項目名": "値", ... }形式 
    const tempObj = {};
    for (const index in row) { 
      tempObj[values[0][index]] = row[index];
    }
    // 行データobjectを配列に追加
    jsonObj.push(tempObj);
  }

  console.log(jsonObj);

  // ----- json変換ここまで 以下json化したデータの処理例-----
  
  // 購入日が"2023/02/02"の価格を合計
  let sumValue = 0;
  for (const item of jsonObj) {
    if (item["購入日"] == "2023/02/02") {
      sumValue += Number(item["価格"]);
    }
  }
  console.log(sumValue);
}

GAS解説

「1行目がヘッダー、2行目以降がデータ行」形式の2次元配列をjson化

大事な部分は変数 values「1行目がヘッダー、2行目以降がデータ行」形式の2次元配列をjson化してくれる処理になります。

変数 values

[
 [ 'ID', '購入日', '商品', '価格' ],
  [ '1', '2023/02/01', 'りんご', '100' ],
  [ '2', '2023/02/02', 'ねぎ', '110' ],
  [ '3', '2023/02/02', 'キャベツ', '138' ],
  [ '4', '2023/02/03', 'りんご', '110' ] 
]

２次元配列をjson化

  // jsonObject用配列を作成
  let jsonObj = [];

  // 2行目(データ行)からループ 1行目しかない(データがない)場合ループに入らない
  for (const row of values.slice(1)) {
    // 行データをobject化 {"項目名": "値", ... }形式 
    const tempObj = {};
    for (const index in row) { 
      tempObj[values[0][index]] = row[index];
    }
    // 行データobjectを配列に追加
    jsonObj.push(tempObj);
  }

「"項目名": "値"」 形式の 一時object を組み立てる

一時object を組み立て

for (const index in row) { 
    tempObj[values[0][index]] = row[index];
}
// 1回目のループでは row に['1', '2023/02/01', 'りんご', '100' ]が入っているので、このようなtempObjになる
{
    "ID": "1",
    "購入日": "2023/02/01",
    "商品": "りんご",
    "価格": "100"
}

一時objectを json配列に追加

// 行データobjectを配列に追加
jsonObj.push(tempObj);

// 一時objectの組み立てが終わったら配列に追加 を行データが無くなるまで繰り返す
[
    // ループ1回目の追加
    {
        "ID": "1",
        "購入日": "2023/02/01",
        "商品": "りんご",
        "価格": "100"
    },
    // ループ2回目の追加
    {
        "ID": "2",
        "購入日": "2023/02/02",
        "商品": "ねぎ",
        "価格": "110"
    }
    // 行データがなくなるまで繰り返し
]

このようにしてjson形式の配列を作成しています。

カラム・データの増減への対応

カラム・データの件数分ループするような処理にしているため、増減しても対応が可能です。

行数分のループ

for (const row of values.slice(1)) {
    // 行データ数分のループ
}

カラム数分のループ

for (const index in row) { 
    // カラム数分のループ    
}

例えば、データが1行追加され、D列にカラムが追加された場合もGASのコードを修正せずjson化できます。

	A	B	C	D(追加)	E
1	ID	購入日	商品	産地	価格
2	1	2023/02/01	りんご	青森	100
3	2	2023/02/02	ねぎ	群馬	110
4	3	2023/02/02	キャベツ	千葉県	138
5	4	2023/02/03	りんご	山形	110
6(追加)	5	2023/02/04	トマト	北海道	90

[
  {"ID": "1", "購入日": "2023/02/01", "商品": "りんご", "産地": "青森", "価格": "100"},
  {"ID": "2", "購入日": "2023/02/02", "商品": "ねぎ", "産地": "群馬", "価格": "110"},
  {"ID": "3", "購入日": "2023/02/02", "商品": "キャベツ", "産地": "千葉県", "価格": "138"},
  {"ID": "4", "購入日": "2023/02/03", "商品": "りんご", "産地": "山形", "価格": "110"},
  {"ID": "5", "購入日": "2023/02/04", "商品": "トマト", "産地": "北海道", "価格": "90"}
]

データが存在しない場合

1行目がヘッダー行なので .slice(1)で2行目から処理をしています。
ヘッダー行のみの場合はループに入らないため jsonObj は [] (空の配列) のままになります。

// 2行目(データ行)からループ 1行目しかない(データがない)場合ループに入らない
for (const row of values.slice(1)) {
    // ヘッダー行のみの場合ループに入らない
}

データ行なし、ヘッダー行のみ

	A	B	C	D(追加)	E
1	ID	購入日	商品	産地	価格

データ行なしの場合のjson

[]

length で配列の個数を調べて0(空)だったら、処理を終了させる。みたいなこともできます。

lengthで配列の個数を調べる

if (jsonObj.length == 0) {
    console.log("データが存在しません。");
    return;
}

jsonObjectからjson文字列に変換

今までの記載していたコードはGAS(javascript)で扱えるようにobjectになっているので、json文字列にする場合はJSON.stringifyで変換します。

javascript objectをjson化

const json = JSON.stringify(jsonObj);

おわりに

他にもっと簡単にできそうであれば教えてください。。

参考