見出し画像
Photo by noouchi

非エンジニアがスプレッドシートのデータを使ったwebページを作る中で学んだこと

はちれつ

こんにちは。最近、私の職場では、スプレッドシートでデータをまとめることが年々増えてきており、自分だけでなく、いろんな人が簡単に操作してデータを動かしていくものが欲しかったので、こんな感じのものを作りました。実際に使用するものとは少し内容変えてますが大体こんな感じになってます。

完成図(見本)

webページ
スプレッドシート

ありがちなものですね。プログラム言語「GAS」とHTMLを使用して、スプレッドシートとwebページを紐づけた発注依頼フォームを作成しました。googleフォームでよいのでは？という気持ちもあったのですが、フォームだと融通が利かないことも多かったので勉強もかねて作成しました。参考にした動画は以下のURLになります。

https://www.youtube.com/watch?v=JJs8VZ7IY0Q&t=100s

【GAS】Bootstrapを使ってフォーム画面を作る方法


スプレッドシートをデータベースの代替えとして扱うことで、データを更新するためのハードルをさげることで「誰でも扱える」という必須条件を確保しつつ、ネット検索して得られる必要最低限のプログラム知識で自動化をすることができました。なのでこの記事はプログラムのすべてを紹介するのではなく、実装にあたって使用した機能のなかで、今後も応用で使えるなと思ったものを、振り返り兼ねて紹介したいと思います。


HTMLを表示させる

まず、準備としてプログラムはスプレッドシートからメニューにある「拡張機能」→「Apps Script」をクリックしてgsファイル(GAS)、htmlファイルを作成します。

GASを使用してwebページを表示させる記述方法は二つあるのですが、今回はスプレッドシートの値(変数)をwebページ上で扱うため、おそらくcreateTemplateFromFile()関数を使用する必要がありそうでした。実際のコードは以下になります。

function doGet(e){

  const Template = HtmlService.createTemplateFromFile('無題');//htmlファイル名”index”をオブジェクト化
//htmlにクエリパラメータを添付
  Template.name = e.name;
  Template.deploy_url = ScriptApp.getService().getUrl();//最新verのデプロイURLを取得
  let Html_Output = Template.evaluate();//
  return Html_Output;//evaluateメソッドでwebページ表示

}

gsファイル上でcreateTemplateFromFile("htmlのファイル名")関数を使用しhtmlファイルををオブジェクト化、変数を追加してevaluate()メソッドでブラウザ上でwebページを表示させるといったことを行っています。
ここで初めてGASを扱う方に少し注意してもらいたいのは、htmlファイルもエディタ上に保存して置いておくこ必要があります。

GASのエディタ画面左側。この場所に使用するgsファイル、およびhtmlファイルを置いておく


そこまでの準備が出来たら、URLをクリックすることを条件(トリガー)にプログラムが働く特別な関数doGet()を使用するため、クリックするURLを作成します。
具体的な方法は「GAS デプロイ方法」で検索するとわかるとたくさん情報が落ちてると思いますのでスルーさせていただきます。
大まかな流れだけ伝えると画面右上の「デプロイ」をクリックすることでデプロイしURLを作成されるのでそのURLを使用します。※なお、デバッグモードではwebページを表示させることは出来ませんでした。


URLにデータ(クエリパラメータ)を載せる(個人的に超重要！)

GASでは、webページにスプレッドシートのセルの値を挿入することが可能で非エンジニアの私にとって、とても可能性が広がった仕様がまさにこの部分になります。方法は先ほど作成したURLに以下の文字列(セル番号)を追加してください。

="デプロイURL?変数名="&セル番号

と挿入する。

実際の例だと以下になります。

="https://script.google.com/macros/s/デプロイID/exec?name="&B5

注意点として、先頭に＝を付けること、文字列は""で囲み、セル番号を結合する際は&で結合してください。また、複数のパラメータをURLに載せた場合、連想配列として取得されるようです。(うまく使えば複数の変数を載せることが可能かも？https://qiita.com/www-tacos/items/98fd380acf3fbfbec908 , https://breezegroup.co.jp/201906/gas-get/)

その後、doGet()関数の中で先ほどURLに追加したパラメータをhtmlオブジェクトに加えることで、URLに追加したセルの値をGAS経由でhtmlに渡すことが出来るようになります。

function doGet(e){

  const Template = HtmlService.createTemplateFromFile('無題');//htmlファイル名”index”をオブジェクト化

//htmlにクエリパラメータを添付
  Template.name = e.parameter.name;

  let Html_Output = Template.evaluate();
  return Html_Output;//evaluateメソッドでwebページ表示
}

※「e.parameter.変数名」がURLに加えるものになります。


スプレッドシートのデータをHTMLに表示させる

webページの表示内容はhtmlファイルに記述することになります。

今回のサンプルページは無題.htmlファイルに以下のコードを書いています。初めてhtmlを書いたのでhtmlのことはほとんどわからないまま取り合えず頑張りました。

<!DOCTYPE html>
<html>
  <head>
    <title>発注依頼フォーム</title>
    <base target="_top">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
  </head>
  <body style="font-family: arial black, sans-serif;">
    <div class="container-fluid bg-success-subtle">
      <p class="text-center fs-1 fw-bold mt-4 p-4 text-white">発注依頼フォーム</p>
      <div class="container-fluid" style="max-width: 750px;">
        <!-- <form  method="POST" action=<?= deploy_url ?>> -->
        <div>
          <div class="col-12 justify-content-center align-items-center min-vh-100">
            <div class="mx-auto p-5 border-light rounded shadow" style="min-width: 280px; max-width: 750px; width: 100%;">
              <h1 class="text-center fs-1 fw-bold text-success mb-4"><?= name ?></h1>
              <p class="text-center fs-2 mb-3 text-dark">発注する本数を選んで「決定」ボタンを押してください</p>
<!-- ドロップボックス -->
              <div class="row">
                <div class="offset-md-3 col-6">
                  <select name="quantity" class="form-select my-3">
                    <? for (let i = 1; i <= 3; i++) { ?>
                      <option value=<?= i * stock ?>><?= i * stock ?></option>
                    <? } ?>
                  </select>
                </div>
                <label for="inputEmail3" class="fw-bold col-sm-2 my-3 col-form-label">本</label>
              </div>
<!-- Button trigger modal決定ボタン -->
              <div class="text-center">
              <button  class="btn btn-success btn-lg" data-bs-toggle="modal" data-bs-target="#exampleModal">決定</button>
              </div>
  <!-- Modalポップアップ -->
              <form  method="POST" action=<?= deploy_url ?>>
                <div class="modal fade" id="exampleModal" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
                  <div class="modal-dialog">
                    <div class="modal-content">
                      <div class="modal-header">
                        <h1 class="modal-title fs-5" id="exampleModalLabel">Modal title</h1>
                        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                      </div>
                      <div class="modal-body">
                        ...
                      </div>
                      <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">キャンセル</button>
                        <button type="submit" class="btn btn-primary">発注依頼</button>
                      </div>
                    </div>
                  </div>
                </div>
              </form>
            </div>
          </div>
        </div>
        <!-- </form> -->
      </div>
    </div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js" integrity="sha384-geWF76RCwLtnZ8qwWowPQNguL3RmwHVBC9FhGdlKrxdiJJigb/j/68SIy3Te4Bkz" crossorigin="anonymous"></script>

  </body>
</html>

クエリパラメータをGASファイル上で適切に追加できていれば<?= 変数名 ?>と書くことで変数の値が表示されます。

実際の例は以下の通りです。

<h1 class="text-center fs-1 fw-bold text-success mb-4"><?= name ?></h1>

変数名とすることでスプレッドシートの内容を書き換えることでwebページの内容も自動的に変更することが出来ます。


CSSとJavaScriptをGASでも使用する(Bootstrap)

webページといえばレイアウトを整えるのにCSSファイル、ボタンやタブ機能を使用するのにjsファイルを使用するのですが、GASではそのファイル形式が使えない？っぽく、一工夫必要そうだったため、私はBootstrapというフレームワークを使用することでレイアウトを最低限見れる程度に整えました。

Bootstrapは公式サイトでどんなことが出来るかビジュアル付きのサンプルでわかるものも多かったのですが、使用するための準備として、以下のコードが必要になります。

<head>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
　　<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js" integrity="sha384-geWF76RCwLtnZ8qwWowPQNguL3RmwHVBC9FhGdlKrxdiJJigb/j/68SIy3Te4Bkz" crossorigin="anonymous">
</script>

CSSを使用する場合は<head>タグに、JavaScriptを使用する場合は<script>タグにそれぞれを記述する必要があります。私はCSS用のタグしか入れていなかったのでボタン操作や、口述しますポップアップが動作せず、余計な時間をかけてしまいました。

ポップアップ(modal)を表示させる

最終確認をwebページの「決定」ボタンを押したとき、ポップアップで表現しました。動作としてはよく見るものです。



Bootstrapの「modal」機能からサンプルコードを使って実装しました。

<!-- Button trigger modal決定ボタン -->
              <div class="text-center">
              <button  class="btn btn-success btn-lg" data-bs-toggle="modal" data-bs-target="#exampleModal">決定</button>
              </div>
  <!-- Modalポップアップ -->
              <form  method="POST" action="デプロイURLを記述">
                <div class="modal fade" id="exampleModal" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
                  <div class="modal-dialog">
                    <div class="modal-content">
                      <div class="modal-header">
                        <h1 class="modal-title fs-5" id="exampleModalLabel">Modal title</h1>
                        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                      </div>
                      <div class="modal-body">
                        ...
                      </div>
                      <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">キャンセル</button>
                        <button type="submit" class="btn btn-primary">発注依頼</button>
                      </div>
                    </div>
                  </div>
                </div>
              </form>

ここで重要なことは「form」タグの中にあるbutton type="submit"が設定されてるボタンがクリックされるとdoPost()関数が実行され、次の新しいwebページに遷移するということです。なので今回は、Bootstrapのサンプルコードから、「type="button"」→「type="submit"」と変更して、webページのボタンはポップアップを呼び出す「data-bs-toggle="modal" data-bs-target="#exampleModal"」を記述しました。

Webページ(html)を遷移させる

新しいwebページに遷移させるプログラム上の処理はdoPost()にdoGet()関数で記述したものと同じ要領で新しいhtmlファイルを読み込ませて表示させることで実現してます。

function doPost(e){

  const Template = HtmlService.createTemplateFromFile('無題2');//htmlファイル名”index”をオブジェクト化 
  let Html_Output = Template.evaluate();
  return Html_Output;//evaluateメソッドでwebページ表示

}

ページ数が増えると条件分岐が増えたりして複雑になりますが今回は発注完了画面を表示するだけということなのでただhtmlを読み込むだけです。「無題２.html」ファイルを新たに作成し、発注完了とだけ追加したwebページが今回は表示されるようにしています。


webページ遷移後のブラウザ画面


以上の内容を実装することで無事webページを表示させることが出来ました。本来の完成品はさらにgsファイルにデータを送信→実際の発注品情報を自動でスプレッドシートに入力→指定の担当者にメールの下書き作成までを行っています。


やりたかったけど出来なかったこと（おまけ）

初めてwebページをちゃんと他人様に見せられるクオリティを目標にしていたのでスマートフォンから閲覧した場合も想定した違和感のないレイアウトを意識したのですが、縦方向のいい配置がうまく出来ませんでした。


スマホだとかなり上よりに表示される


Bootstrapでレイアウトを整えているためcontainerクラスにしてb-flexタグからこねこねするのかなというイメージまではありますが、毎月成果物を資料付きで出すという目標を優先し、今回のクオリティをたたき台として次のタイミングで煮詰めたいと考えてます。


以上、備忘録として個人的な要所にフォーカスを当てて同じレベルの人のとっかかりになればと書き起こした次第なのでもっといい方法があったよや、この部分もうちょっと知りたいなどあればコメントしていただけると嬉しいです。

もし依頼事項がありましたら、サポートとともに以来内容をコメントしてください。