GASでSpreadSheetにあっさりアクセス
GASとは!?
GoogleAppScript(GAS)は、Googleのサービスを自動化するスクリプト言語です。
JavaScriptで記述する事が出来るので、気軽に開発をする事ができます。
今回はGASを利用して、SpreadSheetにアクセスしてみます。
SpreadSheetにアクセスする
捜査対象であるSpreadSheetにアクセスし、
"Googleスプレッドシートを使ってみる"をクリックします。
スプレッドシートを用意する
画面の右下にある"+"ボタンを押してスプレッドシートを用意します。
仮のデータを用意する
スプレッドシートに仮のデータを入力します。
1行目は、明示的にデータの種類(カラム)を記述しておきましょう。
ScriptEditorを起動する
上部のメニューにある、"Tools" -> "ScriptEditor"の順にクリックして"ScriptEditor"を起動します。
(2023年11月現在では、"Extensions" -> "Apps Script"で起動します)
ScriptEditorを操作する
初期状態では、"myFunction"関数が一つ用意されています。
Debug実行する
"ScriptEditor"では、コードのデバッグを簡単に行う事が出来ます。
コードを次の様に記述し、"myFunction"が選択されている事を確認ます。
メニュー上部にある"Debug"ボタンをクリックする事でデバッグをする事ができます。
function myFunction() {
console.log("Hello GAS!!");
}
"Executing log"に、実行結果が表示されます。(やりました!!)
インターネットからアクセスする
"SpreadSheet"にインターネットからアクセスをさせてみます。
次の手順に従って"Deploy"をしていきましょう。
doGet関数を用意する
"myFunction"関数を"doGet"関数に書き換えます。
この関数は、インターネットからアクセスされるタイミングで実行されます。
return値には実行元への出力値を指定します。(ここでは仮にJSON形式の文字列として出力します)
function doGet(e) {
const json = JSON.stringify({"items": "Great Work!!"}, null, 2);
const type = ContentService.MimeType.JSON;
return ContentService.createTextOutput(json).setMimeType(type);
}
Deployする
"Deploy" -> "New deployment"の順にクリックします。
Deployment typeを選択する
"歯車ボタン" -> "Web App"の順にクリックします。
アクセス権限を選択する
"Who has access"で、"Anyone"を選択し"Deploy"をクリックします。
アクセス先のURLを取得する
アクセス先のURLが表示されるので、"Copy"をクリックします。
ブラウザからURLへアクセスする
コピーしたURLをブラウザに入力してアクセスすると、"doGet"関数のreturn値で生成した文字列(JSON)がブラウザに表示されます。(やりました!!)
SpreadSheetにアクセスする
いよいよですが、SpreadSheetにあるデータにアクセスしてみます。
"doGet"関数を次の様に修正し、"Debug"をしてみましょう。
function doGet(e) {
// スプレッドシートにアクセス
const sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
// JSONオブジェクトに変換する
const rows = sheet.getDataRange().getValues();
const keys = rows.splice(0, 1)[0];
const data = rows.map(row => {
const obj = {};
row.map((item, index) => {
obj[String(keys[index])] = String(item);
});
return obj;
});
// JSON文字列に変換して出力する
const json = JSON.stringify({"items": data}, null, 2);
const type = ContentService.MimeType.JSON;
return ContentService.createTextOutput(json).setMimeType(type);
}
コードを実行するにあたり、パーミッションの設定が必要になる場合があります。
コードを初めて実行するとパーミッションを促すダイアログが出てくるので、次の手順に従ってパーミッションを設定します。(パーミッションについては自己責任の範囲で行ってください)
パーミッションを設定する
"Debug"を最初に実行すると、パーミッションが必要な場合に"Authorization required"(認証が必要です)ダイアログが出てきます。
"Revier permissions"ボタンを押します。
アカウントを選択する
コードの実行者であるアカウントを選択します。
(パーミッションについては自己責任の範囲で行ってください)
"Advanced"をクリックします。
"Go to untitled project(unsafe)"をクリックします。
"Allow"ボタンをクリックします。
修正するその都度Deployをする
コードの修正をするその都度、"Deploy"をする必要があります。(忘れやすいので注意しましょう)
先ほどと同じ様に"Deploy" -> "New deployment"の手順に進んで"Deploy"をします。
新しく出力されたURLを、ブラウザからアクセスする事で"SpreadSheet"の内容がJSON文字列として出力されます。(やりました!!)
スプレッドシートに追加、編集、削除するには?
スプレッドシートの行に追加、編集、削除をする事も可能です。
今回はGetパラメーターに対して"mode"を追加し、
その値が"create"の場合は追加、"edit"の場合は編集、"delete"の場合は削除。
という具合に場合分けしてみます。
"doGet"関数の部分に注目してみてください。
URLにアクセスするパラメーターによって、処理を振り分けている所がポイントです。
例1, 追加する場合
追加する場合は次の様にアクセスします
script.google.com/macros/s/あなたのID/exec?mode=create&title=鬼太郎&genre=アニメ
例2, 編集する場合
編集する場合は次の様にアクセスします
script.google.com/macros/s/あなたのID/exec?mode=edit&id=1&title=鬼太郎&genre=アニメ
例3, 削除する場合
削除する場合は次の様にアクセスします
script.google.com/macros/s/あなたのID/exec?mode=delete&id=1
const DEF_SHEET = "Sheet1";// アクセスするシート名
const DEF_MODE = "read";
function readData(sheet){
console.log("Read!!");
const rows = sheet.getDataRange().getValues();
const keys = rows.splice(0, 1)[0];
return rows.map(row => {
const obj = {};
row.map((item, index) => {
obj[String(keys[index])] = String(item);
});
return obj;
});
}
function createData(sheet, id, title, genre){
console.log("Create!!");
sheet.appendRow([id, title, genre]);
}
function updateData(sheet, id, title, genre){
console.log("Update!!");
const rows = sheet.getDataRange().getValues();
rows.forEach((row, index)=>{
if(row[0] == id){
sheet.getRange(index+1, 1, 1, row.length).setValues([[id, title, genre]]);
}
});
}
function deleteData(sheet, id){
console.log("Delete!!");
const rows = sheet.getDataRange().getValues();
rows.forEach((row, index)=>{
if(row[0] == id){
sheet.deleteRow(index+1);
}
});
}
function doGet(e){
console.log("doGet!!");
// Parameter
const name = (typeof e === "undefined" || typeof e.parameter.name === "undefined")?DEF_SHEET:e.parameter.name;
const mode = (typeof e === "undefined" || typeof e.parameter.mode === "undefined")?DEF_MODE:e.parameter.mode;
console.log(name, mode);
// Sheet
const sheet = SpreadsheetApp.getActive().getSheetByName(name);// Sheet
if(mode == "create") createData(sheet, Date.now(), e.parameter.title, e.parameter.genre);// Create
if(mode == "update") updateData(sheet, e.parameter.id, e.parameter.title, e.parameter.genre);// Update
if(mode == "delete") deleteData(sheet, e.parameter.id);// Delete
const json = JSON.stringify({"items": readData(sheet)}, null, 2);// Read
return ContentService.createTextOutput(json).setMimeType(ContentService.MimeType.JSON);
}
最後に
今回は、GoogleAppScript(GAS)を使って"SpreadSheet"の内容をそのまま出力する方法を取り扱いましたが、いかがでしたでしょうか?
"SpreadSheet"オブジェクトには、他にもデータの追加や削除など様々な機能が備わっています。
Class Sheetから確認する事が出来ますので是非試してみてくださいね。
ここまで読んでいただき有難うございました。
Discussion