【GAS】スプレッドシートのデータをJSON化してAPIとして公開する方法

GASで色々とできる事を広げていくと、スプレッドシートをDBとして使用してAPIとして公開したい場合が出てきました。

ここでは、JSON形式にデータを変換して公開する手順まで紹介しています。

この記事の対象

スプレッドシートの準備

GASで作成したWebアプリで出力したいスプレッドシートを用意します。ここでは、2行3列しかデータがないように見えますが、23行目以降もデータはあります。

次にスプレッドシートのIDを控えておきます。IDが何の事か分からない方は以下をご参照下さい。

>>>Webスクレイピングしてスプレッドシートに出力する方法

gsファイル

任意の「***.gs」ファイルに以下を作成します。

  //1---スプレッドシートの ID を指定
  var spreadsheetId = '*****************';

function doGet() {
  //2---スプレッドシートを開く
  const ss = SpreadsheetApp.openById(spreadsheetId).getSheetByName('シート1');

  //3---データを取得
  const data = ss.getRange(1,1,ss.getLastRow(),ss.getLastColumn()).getValues();

  //4---データを JSON 形式に変換
  const jsonData = [];
  const headers = data[0];
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const rowObject = {};
    for (let j = 0; j < headers.length; j++) {
      rowObject[headers[j]] = row[j];
    }
    jsonData.push(rowObject);
  }

  //5---JSON 形式のデータを返す
  return ContentService.createTextOutput(JSON.stringify(jsonData))
    .setMimeType(ContentService.MimeType.JSON);
}

上記がプログラムになります。

以下は解説になります。

 //1---スプレッドシートの ID を指定
  var spreadsheetId = '*****************';

1の部分では、スプレッドシートのIDを指定しています。

  //2---スプレッドシートを開く
  const ss = SpreadsheetApp.openById(spreadsheetId).getSheetByName('シート1');

2の部分ではスプレッドシートを開いています。1で指定したスプレッドシートの「シート1」を開いています。

 //3---データを取得
  const data = ss.getRange(1,1,ss.getLastRow(),ss.getLastColumn()).getValues();

3の部分ではスプレッドシートのデータを取得する範囲を指定しています。ここでは、「getRange」で1行目、1列目から最終行、最終列まで取得しています。

 //4---データを JSON 形式に変換
  const jsonData = [];
  const headers = data[0];
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const rowObject = {};
    for (let j = 0; j < headers.length; j++) {
      rowObject[headers[j]] = row[j];
    }
    jsonData.push(rowObject);
  }

4の部分ではデータをJSON形式に変換しています。二次元配列で取得したデータを最初の行をキーとして格納していっています。

  //5---JSON 形式のデータを返す
  return ContentService.createTextOutput(JSON.stringify(jsonData))
    .setMimeType(ContentService.MimeType.JSON);

5の部分では、JSON形式にデータを返しています。ContentService オブジェクトを生成してJSON を返すための設定をしています。「***.setMimeType(ContentService.MimeType.JSON)」を使用して MIME タイプを JSON に設定しています。

結果

デプロイ

後はデプロイするだけです。デプロイ方法が分からない方は以下をご参照下さい。

>>>Webページを作成して公開する方法

GASを体系的に勉強したい方

GASを基本的な事から実践的な事まで勉強したい方は以下の書籍がおすすめです。JavaScriptの基礎からGoogleドライブ、スプレッドシート、Gmail、外部サイトへのアクセス方法などが丁寧に解説されています。