LoginSignup
2
2
お題は不問！Qiita Engineer Festa 2023で記事投稿！
@sea0430

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

Last updated at Posted at 2023-07-21

内容

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形式で戻り値を設定します。

オブジェクトは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分クッキングでした。

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2