🔰

GASでSpreadSheetにかんたんアクセス

2021/10/13に公開

GASとは!?

GoogleAppScript(GAS)は、Googleのサービスを自動化するスクリプト言語です。
JavaScriptで記述する事が出来るので、気軽に開発をする事ができます。
今回はGASを利用して、SpreadSheetにアクセスしてみます。

SpreadSheetにアクセスする

捜査対象であるSpreadSheetにアクセスし、
"Googleスプレッドシートを使ってみる"をクリックします。

SpreadSheet

スプレッドシートを用意する

画面の右下にある"+"ボタンを押してスプレッドシートを用意します。

仮のデータを用意する

スプレッドシートに仮のデータを入力します。
1行目は、明示的にデータの種類(カラム)を記述しておきましょう。

ScriptEditorを起動する

上部のメニューにある、"Tools" -> "ScriptEditor"の順にクリックして"ScriptEditor"を起動します。
(2023年11月現在では、"Extensions" -> "Apps Script"で起動します)

ScriptEditorを操作する

初期状態では、"myFunction"関数が一つ用意されています。

Debug実行する

"ScriptEditor"では、コードのデバッグを簡単に行う事が出来ます。
コードを次の様に記述し、"myFunction"が選択されている事を確認ます。
メニュー上部にある"Debug"ボタンをクリックする事でデバッグをする事ができます。

Code.js
function myFunction() {
  console.log("Hello GAS!!");
}

"Executing log"に、実行結果が表示されます。(やりました!!)

インターネットからアクセスする

"SpreadSheet"にインターネットからアクセスをさせてみます。
次の手順に従って"Deploy"をしていきましょう。

doGet関数を用意する

"myFunction"関数を"doGet"関数に書き換えます。
この関数は、インターネットからアクセスされるタイミングで実行されます。
return値には実行元への出力値を指定します。(ここでは仮にJSON形式の文字列として出力します)

Code.js
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"をしてみましょう。

Code.js
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

main.js
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