GASとスプレッドシートで簡易データベースを作る【１０分クッキング】

# 内容

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

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

# スプレッドシートの準備

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

# GASの準備

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

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

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

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

```js
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`を使います。

:::note info
詳しくは[ ウェブアプリ |  Apps Script  |  Google for Developers](https://developers.google.com/apps-script/guides/web?hl=ja)を御覧ください。
:::

##### 返せる値

`doGet(e)`と`doPost(e)`の戻り値はそれぞれ、
```js
doGet(e){
  return ContentService.createTextOutput(JSON.stringify({'hogehoge':'hogehoge' })).setMimeType(ContentService.MimeType.JSON);
}
```
```js
doPost(e){
  return ContentService.createTextOutput(JSON.stringify({result:"Ok"})).setMimeType(ContentService.MimeType.JSON);
}
```
のように設定します。`ContentsService`は、`createTextOutput`メソッドを用いてコンテンツを返します。さらに`setMimeType()`メソッドを用いることで、`JSON` `JAVASCRIPT` `CSV`などの返すコンテンツの形式を指定できます。（HTMLを返したい場合は`HTMLService`を用いることができます。）ここではJSON形式で戻り値を設定します。

:::note info
詳しくは、[Class TextOutput  |  Apps Script  |  Google for Developers](https://developers.google.com/apps-script/reference/content/text-output?hl=ja)や、[Class ContentService  |  Apps Script  |  Google for Developers](https://developers.google.com/apps-script/reference/content/content-service?hl=ja)を御覧ください。
:::

:::note warn
オブジェクトは`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公開](https://qiita.com/riversun/items/c924cfe70e16ee3fe3ba)の記事が大変参考になりました。
:::

##### コード

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

```js
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アプリケーションとして公開すること。）します。

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

# 送受信

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

```js
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)にソートできます。

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

# ちょい足し味変レシピ２

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

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

```js
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);
}
```

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

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

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

# 終わりに

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