WEBアプリでスプレッドシートのデータを表示・抽出・CSV化【GAS・HtmlService】

GoogleスプレッドシートのデータをWEBアプリ的な感じに公開したい・・・と思い、ツールを作成してみました。

会社の全社員向けに社員名簿を公開する方法を検討しており、スプレッドシートを共有(閲覧者として)しようと思っていたのですが、スプレッドシートそのままというのも気が引けたので、Google Apps Scriptを使ってWEBアプリにしてみました。

どのようなデータでも使えるようなツールにしましたので、当記事にて公開します。

ツールの概要

スプレッドシートのデータをWEBアプリとして表示するツールです。詳細は下記のとおりです。

WEBアプリの元となるスプレッドシート
このようなスプレッドシートのデータを・・・
WEBアプリの画面
こんな感じに表示する(フィルタやCSVダウンロードも可能)

サンプルはこちら
* サンプルで表示されるデータは、テストデータ・ジェネレータを利用して生成した架空のものです。

利用方法

  1. 当サイトが公開しているスプレッドシートを開く。
  2. ファイル > コピーを作成で、スプレッドシートをコピーする。
スプレッドシートをコピーする
スプレッドシートをコピーする
  1. ツール > スクリプトエディタで、スクリプトエディタを起動する。
スクリプトエディタを起動する
  1. デプロイ > 新しいデプロイから、WEBアプリをデプロイする
WEBアプリをデプロイする その1

アクセスできるユーザーの設定をよく確認のうえデプロイ。
*Googleアカウントを持つ全員全員は、全世界にデータを公開することを意味しますので注意!

WEBアプリをデプロイする その2
WEBアプリをデプロイする その3

ダイアログが起動するので、ご自身のアカウントを選択し、このアプリケーションにデータへのアクセスを許可してください。

WEBアプリをデプロイする その4
このURLがWEBアプリのURLになります
  1. スプレッドシートにデータを入力し、URLを共有する
    スプレッドシートのdataシートに、公開したいデータを入力します。1行目は必ずヘッダー(項目名の行)にしてください。データが入力できたら、先ほどのURLを利用者に伝えてください。

参考

ソースコードは下記のとおりです。何かの参考になれば幸いです。

const ss = SpreadsheetApp.getActiveSpreadsheet();

function doGet() {
  const htmlTemplate = HtmlService.createTemplateFromFile('index');
  const title = ss.getName();
  htmlTemplate.title = title;
  return htmlTemplate.evaluate().setTitle(title);
}

function getSheetData() {
  const sh = ss.getSheetByName("data");
  const response = JSON.stringify(sh.getDataRange().getValues());
  return response
}
<!DOCTYPE html>
<html style="overflow-y:hidden">
<head>
  <base target="_top">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bulma@0.9.3/css/bulma.min.css">
  <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
  <style>
    ::-webkit-scrollbar {
      background: transparent;  
      height: 10px;
      width: 8px;
    }

    ::-webkit-scrollbar-thumb {
      border: none;
      background: #bbb;
      -webkit-border-radius: 8px;
      border-radius: 8px;
      min-height: 40px;
    }

    thead th {
      position: -webkit-sticky;
      position: sticky;
      top: -1px;
      background-color: #ddd;
    }

    .btn {
      position: fixed;
      width: 50px;
      height: 50px;
      color: white;
      border-radius: 50px;
      display: flex;
      justify-content: center;
      align-items: center;
      opacity: 0.4;
      box-shadow: 3px 3px 5px rgba(0, 0, 0, 0.3);
      transition: 200ms;
      user-select: none;
    }

    .btn:hover {
      opacity: 0.9;
    }

    .btn:active {
      opacity: 1;
    }
  </style>
</head>
<body>
  <div id="app">
    <div style="display: flex;">
      
      <div style="
        width: 300px;
        height: 100vh;
        background-color: #fafafa;
        padding: 0 30px;
        overflow: auto;
      ">
        <h1 class="title is-4" style="margin-top: 50px;">
          <span class="material-icons">filter_list</span> 
          フィルタ
        </h1>
        <template v-for="(key, index) in keys">
          <div style="margin-bottom: 1em;">
            <label class="label is-small">{{ key }}</label>
            <input v-model="conditions[index]" type="text" class="input is-small">
          </div>
        </template>
        <div
          @click="clearConditions()"
          class="btn"
          style="bottom: 30px; left: 220px; background-color: #e85a5a;"
        >
          <span class="material-icons">clear</span>
        </div>
      </div>

      <div style="
        max-width: calc(100% - 300px);
        height: 100vh;
        padding: 0 50px;
        overflow: auto;
      ">
        <h1 class="title is-4" style="margin-top: 50px;">
          <span class="material-icons">description</span> 
          <?= title ?>
        </h1>
        <table class="table is-striped is-hoverable" style="white-space: nowrap; position: relative;">
          <thead>
            <tr>
              <th v-for="key in keys">{{ key }}</th>
            </tr>
          </thead>
          <tbody>
            <template v-for="record in records">
            <tr v-if="checkCondition(record)">
              <td v-for="item in record">{{ item }}</td>
            </tr>
            </template>
          </tbody>      
        </table>
        <div
          @click="downloadCSV()"
          class="btn"
          style="bottom: 30px; right: 30px; background-color: #3a93e8;"
        >
          <span class="material-icons">file_download</span>
        </div>
      </div>

    </div>
  </div>
  
  <script src="https://cdn.jsdelivr.net/npm/vue@2.6.14"></script>
  <script>
    var app = new Vue({
      el: '#app',
      data: {
        keys: [],
        records: [],
        conditions: []
      },
      mounted: function(){
        google.script.run.withSuccessHandler(function(text) {
          const response = JSON.parse(text)
          app.keys = response[0];
          app.records = response.slice(1);
          app.records.splice();
        }).getSheetData();
      },
      methods: {
        checkCondition: function(record){
          for (let i = 0; i < this.keys.length; i++){
            if ( this.conditions[i] && !record[i].includes(this.conditions[i]) ) return false;
          }
          return true;
        },
        clearConditions: function(){
          this.conditions.splice(0);
        },
        downloadCSV: function(){
          const filename = "data.csv";
          let data = '\"' + this.keys.join('\",\"') + '\"\r\n';
          for (const record of this.records){
            if ( this.checkCondition(record) ){
              data += '\"' + record.join('\",\"') + '\"\r\n';
            }
          }
          const bom = new Uint8Array([0xef, 0xbb, 0xbf]);
          const blob = new Blob([bom, data], { type: "text/csv" });
          const url = (window.URL || window.webkitURL).createObjectURL(blob);
          const download = document.createElement("a");
          download.href = url;
          download.download = filename;
          download.click();
          (window.URL || window.webkitURL).revokeObjectURL(url);
        }
      }
    })
  </script>
</body>
</html>

* CSVダウンロード部分はこちらのサイトを参考にさせていただきました。

コメント

  1. chocozukushi より:

    自分が今求めていたものだったので使わせていただきたいです!
    自分の環境で動かしてみたら下記のエラーが出てフィルタがうまく動かないようです・・・

    TypeError: record[i].includes is not a function
    at Cn.checkCondition (userCodeAppPanel:19)
    at eval (eval at Qa (vue@2.6.14:1), :3:1327)
    at Cn.mt [as _l] (vue@2.6.14:6)
    at Cn.eval (eval at Qa (vue@2.6.14:1), :3:1288)
    at Cn.e._render (vue@2.6.14:6)
    at Cn.r (vue@2.6.14:6)
    at pn.get (vue@2.6.14:6)
    at pn.run (vue@2.6.14:6)
    at ln (vue@2.6.14:6)
    at Array. (vue@2.6.14:6)

    • chocozukushi より:

      色々試行錯誤したところ、セルが数値だと動かないんですね。文字列(書式なしテキスト)にしたら動きました!!!

      • chocozukushi より:

        何度もすいません。
        使う時に色を変えたり、アイコンを変更したりするのは大丈夫でしょうか?

        • ichi3270 ichi3270 より:

          コメントありがとうございます。
          数値だとエラーになる件、知りませんでした;
          教えてくださりありがとうございます。

          アイコンや色等をはじめ、すべてお好きに変えていただいて問題ありません。

          • chocozukushi より:

            回答ありがとうございます!

            使わせていただきます!!感謝感謝ですm(__)m

  2. mazda より:

    喉から手が出るくらい欲しかった情報です!
    使わせていただきます!

    もしよろしければ、簡単でもよいのでコードの解説をしていただけますと嬉しいです
    ご検討ください

  3. Nemo より:

    ご丁寧な掲載ありがとうございます。

    当方、ミャンマーにてネットを頼りにGASでのUIや閲覧WEBAppを作る必要に駆られ目下いろんなサイトを読み漁っておりました。
    サンプルまで載せていただいていて、ありがたく試させていただいております。

    ところで、スプシのデータ部分が多くなると表示への反映がなくなってしまうのですが、
    データ内容の最大記入済みセル数等ありますでしょうか?

    いかんせん、こちらのネット環境の脆弱性もあるので、どこが原因なのか測りかねております。
    なにかしら、コメントいただけると幸いです。

    • ichi3270 ichi3270 より:

      行数の制限等はないと思います。(とんでもなく多い場合はどうなるかわかりませんが・・・)
      もしかすると、スプレッドシートのデータ内に日付形式のデータを使われているのが原因かもしれません。
      以前、セルの書式をすべて「書式無しテキスト」にしたら直ったというコメントを頂いたことがあります。

      一応、コードの方もJSONでデータを受渡しするよう変更してみましたが、
      日付形式のデータは、このままのコードだと意図通りの形で表示されないと思いますので、テキストにしていただくのが一番簡単かな?と思います。

  4. Nemo より:

    ご確認いただきありがとうございます。

    今、すべての数字に関して書式なしテキストにしたところ、AからAH列・925行びっしりデータの詰まったものでも表示ができました。

    確かに上記の過去コメントに解決法がありましたね… 
    落ち着いて書き込む前に、過去コメントを試すべきでした。
    申し訳ありませんでした。

    ありがとうございました。

  5. IT1010 より:

    こんにちは。私用で簡易的なデータベースを作成するのに大いに参考にさせて頂きました。
    当方、ITスキルが殆どないため無知な質問かもしれませんが、可能であれば以下教えて頂けませんでしょうか?

    例えば大元のスプレッドシートC列にURLを記載したハイパーリンクが記入されている場合でも、アプリ上では文字列としか認識されません。
    これをアプリ上でハイパーリンクとして表示される事は可能でしょうか?
    無知ながら色々調べてみるとそもそもJSONが文字列を扱うため出来ないのではと推察していますが、ご教示頂けますと幸いです。

    • ichi3270 ichi3270 より:

      スクリプトエディタでindex.htmltbodyタグ部分を下記のように変更すると、http://またはhttps://で始まる文字列を、リンク(aタグ)にできました。

      <tbody>
        <template v-for="record in records">
        <tr v-if="checkCondition(record)">
          <td v-for="item in record">
            <template v-if="item.match(/^https?:\/\//)">
              <a :href="item" target="_blank">{{ item }}</a>
            </template>
            <template v-else>
              {{ item }}
            </template>
          </td>
        </tr>
        </template>
      </tbody>

      この変更を行った後でデプロイをしてください。

      • IT1010 より:

        ichi3270さん
        ご丁寧にご返信ありがとうございます。ハイパーリンクを無事に拾う事が出来ました。
        Vueには不慣れですが色々触って覚えたいと思います。

  6. sdgs より:

    お世話になっております。
    可視化を作成するのに大いに参考にさせて頂きました。

    勉強中のため、無知な質問で申し訳ありませんが、下記2点について教えていただきたいです。

    ①右側のフィルタのフリーテキスト以外に、プルダウンでの選択したい
    例)「性別」カラムにあらかじめ「男」「女」をプルダウンで表示

    ②右側の一覧表示に出ている項目=フィルタ項目となっているが、フィルタを限定したい
    例)「生年月日」は右の一覧には表示するが、左のフィルタには不要

    以上、お手数おかけしますが、よろしくお願い申し上げます。

タイトルとURLをコピーしました