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を利用者に伝えてください。

参考

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

コード.gs

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 = sh.getDataRange().getValues();
  return response
}

index.html

<!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(response) {
          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ダウンロード部分はこちらのサイトを参考にさせていただきました。

コメント

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