Google Apps Script Google Workspace (G Suite) Javascript

GoogleスプレッドシートをWebアプリのデータベース代わりに使う

 

データベース代わりに使うならドキュメントよりスプレッドシートの方が便利

以前、GoogleドキュメントにWebブラウザからデータをPOSTする記事を書きました。

ブラウザからGoogle DocumentにPOSTする

ブラウザ(Chrome)からAjaxでGoogle DocumentにデータをPOSTするコードです。ローカルWebアプリのデータ保存とかに使えます。

続きを見る

 

今度はGoogleスプレッドシートとJSONデータの読み書きができるようにしてみました。Webアプリのデータベース代わりに使うならスプレッドシートの方が便利でしょう。

通常、Webアプリケーションでデータを保存して後から再利用したいとなると、サーバーを立ててMySQLとかを使うのが普通だと思います。

そうすると急にハードルが上がります。

Webアプリはサクっと作れるのがいいところなので、できればサーバーもデータベースを使わずに書きたいのです。

もちろんデータ件数が万単位とか高度な検索や抽出をしたいなら別ですが。

GoogleスプレッドシートのデータをWebアプリから読み書きできれば、結構いろんな要素に使えそうです。

フロントエンドもGoogle Apps Scriptで書くという手もあるのですが、GASで凝ったことをしようとするとちょっと大変なんですよね。

そんなわけで今回のプログラムです。

 

表全体のデータを一括で読み出し、書き込み

今回はとりあえず、

  • GETリクエストで任意のシートの全データを読み込む
  • POSTリクエストで任意のシートにデータを書き込む

の2つの機能を付けました。

 

動作を動画にしてみました。

 

GETで表全体のデータを取得

GETボタンを押すとGoogleスプレッドシートの1番の表の全データが読み込まれます。

データは、最初の行をキーとしたオブジェクトの配列になっています。

例えば、以下のような表であれば、

name age
鈴木 18
佐藤 30
 json
[ { name:"鈴木", age:18} , { name:"佐藤", age:30} ]

というデータが得られます。

取得したデータはtextareaに表示されるほか、「dataObj」というグローバルオブジェクトに入るので、ブラウザ上で自由にデータを扱えます。

 

POSTでテキストエリアの内容を表に書き込み

POSTボタンを押すとテキストエリアの内容をJSON.parseしてGoogleスプレッドシートに書き込みます。

テキストエリアの内容がJSONかどうか try...catch で簡易チェックをしています。

テストでは上の動画のようにGETボタンで取得したデータを編集して書き戻すのがいいでしょう。

既存のシートの内容は全消去して上書きされます。

 

GASのレスポンスはものすごく遅いのでやりとりは最小減にする

GASはドキュメントやスプレッドシートなどのデータにアクセスするとものすごく遅くなります。一回の読み書きに秒単位で時間がかかったりします。

データの量にはあまり関係がないようです。単純に1回1回のアクセスが遅い感じです。

ですから検索とか並び替えをGoogleスプレッドシート側でやらせると、レスポンスが長くなりま、結構かったるいアプリケーションになってしまいます。

今回はローカルアプリからGASのWebアプリにリクエストをする形なのでさらに遅くなりそうです。

このため、今回のアプリケーションも、データの処理はローカルのオンメモリでやる前提にして、表のデータを一括で読み込み、一括で書き込むようにしています。

 

jQueryの$.ajaxでJSONデータをGET/POSTする

実際のコードは割とシンプルです。

まずはブラウザ用のプログラム。短いのでHTML内にscriptタグで記述しています。

お使いになるときは、END_POINTにGASでデプロイしたWebアプリケーションのアドレスを設定してください。

また、SHEET_NOは1から始まるGoogleスプレッドシートの番号です。これも任意に設定してください。

<!DOCTYPE html>
<html lang="ja">

<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Get/Post with Google SpreadSheet</title>
</head>

<body>
  <p>GET and POST <br>
    to Google SpreadSheet</p>
  <textarea id="textArea" rows="5" cols="25"></textarea>
  <br>
  <button id="postButton">POST</button>
  <button id="getButton">GET</button>
  <script src="https://code.jquery.com/jquery-3.5.1.min.js"
    integrity="sha256-9/aliU8dGd2tb6OSsuzixeV4y/faTqgFtohetphbbj0=" crossorigin="anonymous"></script>

  <script>
    //GAS WebアプリのURL
    const END_POINT = "https://script.google.com/macros/s/*****************************/exec";
    //読み書きするスプレッドシート(タブ)の番号
    const SHEET_NO = 1; 

    document.getElementById("postButton").addEventListener('click', postToGAS, false);
    document.getElementById("getButton").addEventListener('click', getFromGAS, false);
    let dataObj = {};

    function postToGAS() {
      const Content = document.getElementById("textArea").value.trim();
      console.log(Content);

      //データがJSONかどうかのチェック
      try {
        const checkJSON = JSON.parse(Content);
        if (checkJSON.length > 0 && Object.keys(checkJSON).length > 0) {
          console.log("data is OK");
        } else {
          throw "data is not array of object";
        }
      }
      catch (e) {
        alert("error:" + e);
        return;
      }

      //POST送信
      $.ajax({
        type: "POST",
        url: END_POINT,
        dataType: "json",
        data: { sheetNo: SHEET_NO, data: Content }
      })
        .then(
          (result) => { // 成功した時の処理
            console.log(JSON.stringify(result));
          },
          (error) => { // 失敗した時の処理
            alert('Error:' + JSON.stringify(error));
          }
        );
    }

    function getFromGAS() {
      $.ajax({
        type: "GET",
        url: END_POINT,
        data: { sheetNo: SHEET_NO }

      }).done((result) => {        // 成功した時の処理
        dataObj = JSON.parse(result);
        document.getElementById("textArea").value = result;
        console.log("get done:" + result);

      }).fail((error) => {  // 失敗した時の処理
        alert('Error:' + JSON.stringify(error));

      }).always((data) => {// 常にやる処理
        // do something
      });
    }
  </script>
</body>

</html>

なお、備忘録代わりにPOSTとGETでajaxの成功時、失敗時の無名関数の書き方を変えています。最近はsuccessとかいう書き方はしないそうです。

 

Googleスプレッドシート側のプログラム

Googleスプレッドシート側のプログラムです。

スクリプト単体ではなく、スプレッドシートに付属のスクリプトとして設定します。

GASのお約束通り、doPost関数でPOSTアクセスを処理し、doGet関数でGETアクセスを処理します。

sheetNoのパラメータを省略した場合は1とみなします。

スプレッドシートへの書き込みには2次元配列が必要なので、POST時にはJSONを2次元配列に変換します。

GETの場合はシートから取得した2次元配列から1行目をキーにしたオブジェクトの配列を作ります。

1行目が見出し(キー)である、というルールで表を作ればプログラムを変更せずに対応できます。

見出しに空白文字が混じるとエラーになるので、実用化するにはアンダースコアに置換するなどの処理が必要ですね。

return ContentService.createTextOutputで返す値をJSONにしないとブラウザ側のエラーになります。

var ss = SpreadsheetApp.getActiveSpreadsheet();

function doGet(e) {
  const reqParam = e.parameter;
  const sheetNo = parseInt(reqParam.sheetNo) || 1; //シート番号のパラメーターがなければ 1 にする
  sheet = ss.getSheets()[sheetNo - 1];
  const data = sheet.getDataRange().getValues();
  const keys = data[0];
  data.shift();

  const dataObj = data.map((array) => {
    const temp = {};
    keys.map((key, index) => {
      temp[key] = array[index];
    });
    return temp;
  });

  const dataObj2 = {};
  keys.forEach((key, index) => {
    dataObj[key] = data.map((arr) => {
      return arr[index];
    });
  });

  return ContentService.createTextOutput(JSON.stringify(dataObj));
}

function doPost(e) {
  const reqParam = e.parameter;
  const sheetNo = parseInt(reqParam.sheetNo) || 1;
  sheet = ss.getSheets()[sheetNo - 1];

  var data = JSON.parse(reqParam.data);

  postJsonToSpreadSheet(data);

  return ContentService.createTextOutput(JSON.stringify({ result: "post done" })); //JSONを返すとエラーにならない? 
}



function postJsonToSpreadSheet(arrObj) {
  //受け付けるJSONは、[{key1:data1, key2:data2,....},...]、オブジェクトが配列になっている形式
  //オブジェクトのキーがスプレッドシートの項目名として1行目に入力される

  sheet.clear();
  //タイトル行を書き込み
  const keys = [Object.keys(arrObj[0])];//setValuesには必ず2次元配列を渡すので [ ] で囲んで2次元配列にする
  sheet.getRange(1, 1, 1, keys[0].length).setValues(keys);//項目名を書き込み

  //オブジェクトからデータ書き込み用の2次元配列を作成
  const arrToWrite = arrObj.map((obj) => {
    const arr = [];
    for (const key of keys[0]) { arr.push(obj[key]); }
    return arr;
  });

  const lastColumn = arrToWrite[0].length; //1個め配列の長さ=カラムの数を取得する
  const lastRow = arrToWrite.length;   //行の数を取得する
  sheet.getRange(2, 1, lastRow, lastColumn).setValues(arrToWrite);
}

 

作ってる時はつまらないところでたくさん躓きましたが、なんとか上手くいきました。

そのうち語学学習用の短文帳でも作ってみようかと思います。

【追記】その後、もうちょっと高機能なヤツを作りました

GoogleスプレッドシートをWebアプリのデータベース代わりに使う-その2【シート選択、配列orオブジェクト指定を追加】

Googleスプレッドシートのデータをブラウザから読み書きするアプリにシート選択やデータ形式の選択(配列/オブジェクト)機能を加えました

続きを見る

 

-Google Apps Script, Google Workspace (G Suite), Javascript

© 2021 make it easy Powered by AFFINGER5