【GAS】JSONデータをスプレッドシートに書き込む方法

こんにちは、Ryohei（@ityryohei）です！

本記事では、Google Apps ScriptでJSONデータをスプレッド―シートに書き込む方法をご紹介しています。JSONデータをそのまま書き込むわけではなく、オブジェクト毎に行を分けてスプレッドシートに書き込む方法をとなります。

上記の疑問にお答えします。

では、解説していきます。

Google Apps ScriptでJSONデータをスプレッドシートに書き込む方法

Google Apps Script（以下GAS）でJSONデータをスプレッドシートに書き込む全体のスクリプトは下記の通りです。

function myFunction() {

  const spreadsheetId = 'SPREADSHEET_ID';
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const sheet = spreadsheet.getSheetByName("シート名");

  const json = [
    {
      'username': '田中太郎',
      'email': 'tanaka@example.jp',
      'tel': '0120123456'
    },
    {
      'username': '山田太郎',
      'email': 'yamada@example.jp',
      'tel': '0120789123'
    },
  ];

  json.forEach((obj)=>{
    let lastRow = sheet.getLastRow()+1;
    Object.keys(obj).forEach((key, index)=>{
        sheet.getRange(lastRow, index+1).setValue(obj[key]);
    });
  });
}

処理の流れは次のようになっています。

• スプレッドシートを取得する
• JSONデータを取得する
• JSONデータを繰り返し処理する
• JSONデータをスプレッドシートに書き込む

全体のスクリプトではわかりにくい部分があると思いますので、要所でまとめて処理内容を詳しく解説していきます。

スプレッドシートを取得する

Google Apps Scriptでスプレッドシートを取得します。スプレッドシートの取得方法には複数選択肢がありますが、ここではIDを使ってスプレッドシートを取得しています。

//スプレッドシートID
const spreadsheetId = 'SPREADSHEET_ID';
//スプレッドシートを取得する
const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
//スプレッドシートから対象のシートを取得する
const sheet = spreadsheet.getSheetByName("シート名");

getSheetByName()の引数に指定しているシート名は、実際のシート名（デフォルトでは「シート1」）を指定してください。

参考：SpreadsheetApp openById();

JSONデータを取得する

JSONデータを取得します。JSONデータは基本的に外部から取得することになると思いますが、ここではファイル内で定義した簡単なJSONデータを使用します。実際にはAPIを叩いた結果等に差し替えて利用することになると思います。

//JSONデータ
const json = [
  {
    'username': '田中太郎',
    'email': 'tanaka@example.jp',
    'tel': '0120123456'
  },
  {
    'username': '山田太郎',
    'email': 'yamada@example.jp',
    'tel': '0120789123'
   },
];

JSONデータを繰り返し処理する

JSONデータを繰り返し処理でオブジェクトに分けて処理します。JSONデータ内のオブジェクトの中身が増減しても対応できるように繰り返し処理を使って処理します。

//JSONデータ繰り返し処理
json.forEach((obj)=>{

  //JSONデータのオブジェクトのキーを取得して繰り返し処理
  Object.keys(obj).forEach((key, index)=>{
      //add script    
  });

});

JSONデータをスプレッドシートに書き込む

JSONデータをスプレッドシートに書き込みます。シートの最終行を取得し、最終行の次の行にJSONデータのオブジェクトの内容を書き込みます。データの書き込み後、繰り返し処理が終わるまで隣のカラムに移動するように処理します。

//JSONデータ繰り返し処理
json.forEach((obj)=>{

  //シートの最終行取得
  let lastRow = sheet.getLastRow()+1;

  //JSONオブジェクトのキーを取得して繰り返し処理
  Object.keys(obj).forEach((key, index)=>{

    //列を隣に移動してセルに値を書き込む
    sheet.getRange(lastRow, index+1).setValue(obj[key]);

  });

});

スクリプトを実行すると、スプレッドシートに下記のデータが書き込まれます。

田中太郎　tanaka@example.jp　0120123456
山田太郎　yamada@example.jp　0120789123

これでJSONデータをスプレッドシートに書き込むことができました。

最後に

Google Apps Scriptをスプレッドシートを組み合わせることで、JSONのような構造化データを視覚的に見やすい形式にすることが可能となります。JSONだけではなくCSVでもできますので、今度はCSVを使用したスクリプトについてもご紹介できればと思います。

以上、Google Apps ScriptでJSONデータをスプレッドシートに書き込む方法のご紹介でした！