スプレッドシートにデータを手動で入力するのは面倒ですよね。特に、外部のAPIから取得したJSONデータをスプレッドシートに反映させたいとき、どうすればいいのか悩む方も多いのではないでしょうか。今回は、Google Apps Script（GAS）を使って、JSONデータをスプレッドシートに自動で書き込む方法を、初心者にもわかりやすく解説します。
JSONデータとは？
Googleスプレッドシートのイメージ
JSON（JavaScript Object Notation）は、データを保存・送信するための軽量なフォーマットです。人間にも読みやすく、プログラムでも扱いやすいのが特徴です。例えば、以下のようなデータがJSON形式です。
{
"name": "田中太郎",
"age": 30,
"city": "東京"
}
このようなデータを、スプレッドシートに書き込む方法を学びましょう。
GASでJSONデータをスプレッドシートに書き込む手順
スプレッドシートを準備する
まず、Googleスプレッドシートを開きます。シートの1行目に、データの項目名（例name、age、city）を入力します。
スクリプトエディタを開く
スプレッドシートのメニューから「拡張機能」→「Apps Script」を選択し、スクリプトエディタを開きます。
スクリプトを記述する
以下のコードをスクリプトエディタに貼り付けます。
function writeJsonToSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var jsonData = [
{"name": "田中太郎", "age": 30, "city": "東京"},
{"name": "山田花子", "age": 25, "city": "大阪"}
];
jsonData.forEach(function(item) {
sheet.appendRow);
});
}
このスクリプトは、jsonDataという配列に格納されたデータを、スプレッドシートに1行ずつ追加していきます。
スクリプトを実行する
スクリプトエディタの上部にある再生ボタンをクリックして、スクリプトを実行します。初めて実行する際は、Googleアカウントの認証が求められるので、指示に従って許可してください。
結果を確認する
スプレッドシートに戻ると、jsonDataの内容が1行ずつ書き込まれているのが確認できます。
応用外部APIから取得したJSONデータをスプレッドシートに書き込む
外部のAPIからJSONデータを取得し、それをスプレッドシートに書き込むことも可能です。以下のコードをスクリプトエディタに追加します。
function fetchDataAndWriteToSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var url = 'https://jsonplaceholder.typicode.com/users';
var response = UrlFetchApp.fetch(url);
var jsonData = JSON.parse(response.getContentText());
jsonData.forEach(function(user) {
sheet.appendRow);
});
}
このスクリプトは、外部API（ここではjsonplaceholder.typicode.com）からユーザー情報を取得し、名前、メールアドレス、住所をスプレッドシートに書き込みます。
よくある質問や疑問
Q1: JSONデータの形式が複雑な場合、どうすればいいですか？
JSONデータがネストされている場合、データをフラットにする必要があります。再帰的にネストを解消する関数を作成し、各階層のデータを取り出して書き込むことができます。
Q2: スプレッドシートに書き込む際、特定のセルから開始したい場合はどうすればいいですか？
`sheet.getRange(row, col)`を使用して、特定のセルを指定できます。例えば、`sheet.getRange(2, 1)`はセルを指します。
Q3: JSONデータをスプレッドシートに書き込む際、ヘッダー行を自動で作成できますか？
はい、スクリプト内でヘッダー行を追加することができます。最初に`sheet.appendRow);`のようにヘッダーを追加し、その後にデータを書き込むと良いでしょう。
まとめ
Google Apps Scriptを使うことで、JSONデータを簡単にGoogleスプレッドシートに書き込むことができます。これにより、外部データの取り込みや自動化が可能になり、業務の効率化が図れます。ぜひ、今回の手順を試してみてください。
他にも疑問やお悩み事があればお気軽にLINEからお声掛けください。
コメント