GASでスプレッドシートの表をJSON形式で出力する方法！WebアプリによるAPI化も

2023年4月3日

スプレッドシートのシート上にある表をJSON形式で出力したいケースがあります。

そうした場合、Google Apps Script(GAS)を処理することで簡単にJSON形式で出力可能です。

GASでスプレッドシートの表をJSON形式に変換する方法に加え、Webアプリケーションとして、URLを叩けばJSONが表示されるAPIの作成方法を解説します。

1. スプレッドシートの表をJSON化したい
2. スプレッドシート表はGASで簡単にJSON化
3. 多くの場合はmap関数を使ったやり方
4. GASでスプレの表をJSONにするサンプルコード
5. JSONを出力するAPIのウェブアプリも可能
6. まとめ・終わりに

スプレッドシートの表をJSON化したい

Googleスプレッドシートでは色々な表を入力する機会が多いです。

そうしたスプレッドシートのシート上にある表をJSON化したいケースがあります。

JSONとはJavaScript Object Notationの略で、プログラミングでよく使われるデータフォーマットです。

スプレッドシートの表をJSON化したい場合にはGoogle Apps Script(GAS)で簡単にJSON化が可能

例えば、上記のようなスプレッドシートの表に対して、プログラミングで処理を行うため、JSONに変換します。

スプレッドシート表はGASで簡単にJSON化

スプレッドシートにある表をJSON形式のデータに変換する際は、Google Apps Script(GAS)がおすすめです。

GASはGoogleのスクリプトサービスなので、スプレッドシートと手軽に連携できます。

スプレッドシートのシート上のデータをJSON化する処理をGASサンプルコードで記述すれば、簡単にJSONデータを取得可能です。

多くの場合はmap関数を使ったやり方

Google Apps Script(GAS)でスプレッドシートの表データをJSON形式に変換する方法はネット上にも多く存在しています。

スプレッドシートの表をJSON化するGASのコードはmap関数を使ったものが多いです。

しかし、map関数は引数に関数を指定したりと少し複雑なため、GAS初心者には理解しづらい傾向があります。

そこで、GASに詳しくない人でもわかりやすいように、map関数を使わずにスプレッドシートの表をJSON化する方法を紹介します。

GASでスプレの表をJSONにするサンプルコード

スプレッドシートの表をJSON形式のデータに変換するGoogle Apps Script(GAS)のサンプルコードを解説します。

前述したスプレッドシートの表をJSON化します。

スプレッドシートにはシートが1つだけある初期状態で、1行目に各列要素の表頭がある表形式を想定しています。

スプレッドシートのシート上にある表をJSON形式のデータに変換するGoogle Apps Script(GAS)のサンプルコード

function getJson() {
  //スクリプトに紐づいたアクティブなシートを取得
  const ss = SpreadsheetApp.getActiveSheet();
  //シート上から表を取得する(A1セルから最終行・最終列まで)
  const data = ss.getRange(1,1,ss.getLastRow(),ss.getLastColumn()).getValues();
  //まずは配列形式で変数を定義
  let objectArray = [];
  //表頭があると想定してそれ以外の行数分forループ
  for(let i=1;i<data.length;i++){
    //1次元配列にオブジェクトを初期代入
    objectArray[i-1] = {};
    //要素の列数分forループで表頭をキーに表データを格納
    for(let j=0;j< data[0].length;j++){
      objectArray[i-1][data[0][j]] = data[i][j];
    }
  }
  //オブジェクトの変数をJSON形式に変換
  const json = JSON.stringify(objectArray);
  //JSONデータをログ出力
  console.log(json);
}

まずスプレッドシートから表データから取得します。

※スプレッドシートの表の位置に応じて、セルの取得範囲を変更してください。

JSONデータとして出力する前にオブジェクト形式でデータを格納するため、データの個数分forループで繰り返し処理します。

オブジェクトはObject[キー]= 値という形で追加し、キーには1行目の表頭をセットします。

最後にオブジェクトをJSON.stringifyメソッドの引数に指定して変換して完了です。

スプレッドシートの表をJSONとしてログ出力するGASサンプルコードを実行すると、実行ログにJSONが表示されます。

Google Apps Script(GAS)でスプレッドシートの表をJSON形式のデータ変換してログ出力するサンプルコードの実行結果

サンプルコードを使えば、シートにある表をJSONとして出力可能です。

JSONを出力するAPIのウェブアプリも可能

先程のスプレッドシートの表をJSONとして出力するGoogle Apps Script(GAS)サンプルコードは、実行ログにJSONを出力していました。

JSONを利用する際にはAPIで出力して、他のサービスから取得できると便利です。

GASではWebアプリケーションとしてデプロイ(公開)することで簡易的なAPIが作成できます。

そこで、GASでスプレッドシートの表をJSONに変換して出力するAPIのサンプルコードと、Webアプリケーションの公開手順を解説します。

function doGet() {
  //スクリプトに紐づいたアクティブなシートを取得
  const ss = SpreadsheetApp.getActiveSheet();
  //シート上から表を取得する(A1セルから最終行・最終列まで)
  const data = ss.getRange(1,1,ss.getLastRow(),ss.getLastColumn()).getValues();
  //まずは配列形式で変数を定義
  let objectArray = [];
  //表頭があると想定してそれ以外の行数分forループ
  for(let i=1;i<data.length;i++){
    //1次元配列にオブジェクトを初期代入
    objectArray[i-1] = {};
    //要素の列数分forループで表頭をキーに表データを格納
    for(let j=0;j< data[0].length;j++){
      objectArray[i-1][data[0][j]] = data[i][j];
    }
  }
  //オブジェクトの変数をJSON形式に変換
  const json = JSON.stringify(objectArray);
  //そのままJSONをreturnできないためContentServiceを生成
  let output = ContentService.createTextOutput();
  //ContentServiceのMIMEタイプをJSONを設定し、JSONをセット
  output.setMimeType(ContentService.MimeType.JSON);
  output.setContent(json);
  //Webアプリケーションの戻り値としてContentServiceのJSONを返却
  return output;
}

先程のGASのサンプルコードと途中まで同じ内容です。

jsonをそのままreturnで返却できればよいのですが、そのままではエラーしてしまいます。

そのため、ContentServiceを利用してAPIで出力可能な形に設定してreturnに指定しています。

スプレッドシートの表をJSON出力する簡易APIをWebアプリケーションとして公開する手順を解説します。

１．GASのスクリプトエディタ右上の「デプロイ」ボタンから「新しいデプロイ」を選択します。