内容

GET/POSTで値を受信しスプレッドシートに書き込み、GETでスプレッドシートから取得した値を送信するだけの簡易データベースをGASで作ります。10分クッキングです。

材料

Googleアカウント
~~シャキシャキの~~Googleスプレッドシート
~~塩水に浸しておいた~~Google App Script（GAS）
適当なクライアント（JavaScript）

スプレッドシートの準備

Google Driveでスプレッドシートを新規作成します。スプレッドシートを開いたらエディタのURLhttps://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxx/edit
のxxxxxxxxxxxxxxxの部分をコピーしましょう。

GASの準備

スクリプトプロパティの設定

Google DriveでGoogla App Scriptを新規作成します。

左のナビゲーションバーから「プロジェクトの設定」をクリックし、一番下の「スクリプトプロパティ」に先程取得したスプレッドシートの識別子的なものを追加します。ここではプロパティはsheetとし、値をxxxxxxxxxxxxxxxとします。

ここで設定したスクリプトプロパティは、

const val = PropertiesService.getScriptProperties().getProperty("プロパティ");

で取得できます。ここではプロパティはsheetなので、getProperty("sheet")で取得できます。

取得できる値

GASでは、クライアントからのHTTPリクエストに応じてdoGet(e)もしくはdoPost(e)関数が実行されます。イベントeのフィールドは、

プロパティ	値の例(説明)
e.queryString	`name=alice&n=1&n=2`
e.parameter	`{"name": "alice", "n": "1"}`
e.parameters	`{"name": ["alice"], "n": ["1", "2"]}`
e.pathInfo	`/exec`または`/dev`の後のURLパス
e.contentLength	POSTリクエストの場合はリクエスト本文の長さ、GET リクエストの場合は`-1`
e.postData.length	〃
e.postData.type	`text/csv`などのPOST本文のMIMEタイプ
e.postData.contents	POST本文のコンテンツテキスト
e.postData.name	常に`postData`

となっています。ここでは、GETでパラメータで値の受信、POSTで値の受信をしたいので、e.parameterとe.postData.contentsを使います。

詳しくはウェブアプリ | Apps Script | Google for Developersを御覧ください。

返せる値

doGet(e)とdoPost(e)の戻り値はそれぞれ、

doGet(e){
  return ContentService.createTextOutput(JSON.stringify({'hogehoge':'hogehoge' })).setMimeType(ContentService.MimeType.JSON);
}

doPost(e){
  return ContentService.createTextOutput(JSON.stringify({result:"Ok"})).setMimeType(ContentService.MimeType.JSON);
}

のように設定します。ContentsServiceは、createTextOutputメソッドを用いてコンテンツを返します。さらにsetMimeType()メソッドを用いることで、JSON JAVASCRIPT CSVなどの返すコンテンツの形式を指定できます。（HTMLを返したい場合はHTMLServiceを用いることができます。）ここではJSON形式で戻り値を設定します。

詳しくは、Class TextOutput | Apps Script | Google for Developersや、Class ContentService | Apps Script | Google for Developersを御覧ください。

オブジェクトはJSON.stringify()で文字列に変換しないと、Access to XMLHttpRequest at 'hogehoge' from origin 'hogehoge' has been blocked by CORS policy: Response to preflight request doesn't pass access control check: No 'Access-Control-Allow-Origin' header is present on the requested resource.といったCORS(ドメイン間リソース共有)エラーを吐きます。この仕様については、今から10分ではじめる Google Apps Script(GAS) で Web API公開の記事が大変参考になりました。

コード

ひとまず、GETでは１つの値の受信(URLのパラメータ)と１つの値の送信、POSTでは１つの値の受信をするだけの簡単な仕組みを実装してみましょう。

function doGet(e){
  const sheetId = PropertiesService.getScriptProperties().getProperty("sheet");
  const spreadsheet = SpreadsheetApp.openById(sheetId);
  const sheet = spreadsheet.getSheets()[0]; //シートをインデックスで指定します。

  const parameterValue = e.parameter.value //１つの値を受信します。プロパティ"value"はあとでクライアント側で指定します。

  var cellValue = sheet.getRange("A1").getValue(); //ここではシートの左上のセルの値を取得して返します。

  return ContentService.createTextOutput(JSON.stringify({'value':cellValue})).setMimeType(ContentService.MimeType.JSON);
}

function doPost(e) {
  const sheetId = PropertiesService.getScriptProperties().getProperty("sheet");
  const spreadsheet = SpreadsheetApp.openById(sheetId);
  const sheet = spreadsheet.getSheets()[0]; //シートをインデックスで指定します。

  const postData = JSON.parse(e.postData.contents);
  const postDataValue = postData.value //１つの値を受信します。プロパティ"value"はあとでクライアント側で指定します。

  sheet.getRange("1:1").insertCells(SpreadsheetApp.Dimension.ROWS); //1行目に空の行を挿入します。
  
  sheet.getRange("A1").setValue(postDataValue);
  sheet.getRange("A2").setValue((new Date).toLocaleString('ja-JP')); //HTTPリクエストがあった時間を取得できます。

  return ContentService.createTextOutput(JSON.stringify({result:"Ok"})).setMimeType(ContentService.MimeType.JSON);
}

デプロイ

完成したGASをデプロイ（Webアプリケーションとして公開すること。）します。

右上の「デプロイ」をクリックし、「新しいデプロイ」をクリック。「アクセスできるユーザー」を「全員」に設定して、「デプロイ」をクリックしてデプロイ完了です。生成されたURLhttps://script.google.com/macros/s/xxxxxxxxxxxxxxx/execをコピーしましょう。

送受信

ここでは、WebサイトのJavaScriptを介してXMLHttpRequestで送受信します。（今どきPromiseが使えるFetchですよね。わかってはいるんですよ。でも慣れ親しんだXHRを中々変えられないんです。~~Blenderだって2.79ですもん。~~まだXMLHttpRequestを使ってるの？　fetchのすすめの記事が大変参考になりました。

function get(value){
  var requestUrl = "https://script.google.com/macros/s/xxxxxxxxxxxxxxx/exec" + `?value=${value}`; //デプロイで取得したURLを設定します。また受信用のパラメータを付加します。
  var xhr = new XMLHttpRequest();
  xhr.open("GET",requestUrl); //リクエスト形式を指定します。
  xhr.send(); //リクエストを実行します。
  xhr.onreadystatechange = function(){ //ステータス変更時に呼び出されます。
    if(xhr.readyState == 4){ //ステータス4はDONEを表します。
      var responseText = JSON.parse(xhr.responseText); //GASからの送信内容を取得します。送信されたJSONはテキストに変換されているためJSONに変換します。
      alert(`GETが完了しました：${xhr.responseText}`);
      //戻り値を設定すると恐らくundefinedになります。意地を張らないで大人しくFetchで非同期処理を書いたほうが本当はいいです。
    }
  }
}

function post(value){
  var requestUrl = "https://script.google.com/macros/s/xxxxxxxxxxxxxxx/exec"; //デプロイで取得したURLを設定します。
  var xhr = new XMLHttpRequest();
  xhr.open("POST",requestUrl); //リクエスト形式を指定します。
  xhr.send(`{"value":"${value}"`); //リクエストを実行します。
  xhr.onreadystatechange = function(){ //ステータス変更時に呼び出されます。
    if(xhr.readyState == 4){ //ステータス4はDONEを表します。
      alert("POSTが完了しました。");
    }
  }
}

適当なボタンのonClickに設定したりして送受信をして、スプレッドシートに値が記録、また値を取得できていることを確認してみましょう。初めて完成したときはこんなに簡単に簡易データベースができたことにテンションが気分上々↑↑になりました。

ちょい足し味変レシピ１

GASでスプレッドシートに対して使える便利なメソッドを紹介します。（追記していきます。）

列をソート

特定の範囲を、特定の列の値によって昇順/降順にソートできます。このコードでは、1行目と1列目から値がある最後のセルまでの範囲を、１列目の値を基準にして昇順(ascending)にソートできます。

sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).sort({column: 1, ascending: true});

ちょい足し味変レシピ２

Webサイトから送受信をするとき、JavaScriptのコードから不特定多数にGASの実行URLを見られます。かといっていちいちURLを入力させるのも不便なので、暗号を用いて簡単なパスワードで送受信できるようにしましょう。

CryptoJSというライブラリを用いると簡単に（文字コードの変換などで手こずるっちゃ手こずります。数時間格闘した思い出があります。）暗号化/復号を実装できます。
salt iv KeySize iterationsに設定する値2^nのnは好みに設定しましょう。またsalt ivは、暗号文に付加することで復号のときに取得しやすくなります。
以下はサンプルコードを参考にしたコードです。

function encrypt(message,password){
  var salt = CryptoJS.lib.WordArray.random(128/8);
  var key = CryptoJS.PBKDF2(pass, salt, {
      keySize: 128/32,
      iterations: 100
  });
  var iv = CryptoJS.lib.WordArray.random(128/8);
  var encrypted = CryptoJS.AES.encrypt(message, key, { 
    iv: iv, 
    padding: CryptoJS.pad.Pkcs7,
    mode: CryptoJS.mode.CBC
  });
  return `${salt.toString()}${iv.toString()}${encrypted.toString()}`;
}
  
function decrypt(message,password){
  var salt = CryptoJS.enc.Hex.parse(message.substr(0, 32));
  var iv = CryptoJS.enc.Hex.parse(message.substr(32, 32))
  var encrypted = message.substring(64);
  var key = CryptoJS.PBKDF2(pass, salt, {
      keySize: 128/32,
      iterations: 100
    });
  var decrypted = CryptoJS.AES.decrypt(encrypted, key, { 
    iv: iv, 
    padding: CryptoJS.pad.Pkcs7,
    mode: CryptoJS.mode.CBC
  })
  return decrypted.toString(CryptoJS.enc.Utf8);
}

あとはデプロイで取得したURLhttps://script.google.com/macros/s/xxxxxxxxxxxxxxx/execを適当なパスワードを設定してencrypt()で暗号化します。暗号文をコピーして、クライアント側のコードのrequestUrlに復号する処理を設定します。

var requestUrl = decrypt(コピーした暗号文,prompt("パスワードを入力してください。"));

これで、URLを秘匿に保ちつつパスワードを入力するだけで送受信ができます。

終わりに

以上GET/POSTで値を受信しスプレッドシートに書き込み、GETでスプレッドシートから取得した値を送信するだけの簡易データベースをGASで実装する10分クッキングでした。