GoogleスプレッドシートのデータをWEBアプリ的な感じに公開したい・・・と思い、ツールを作成してみました。
会社の全社員向けに社員名簿を公開する方法を検討しており、スプレッドシートを共有（閲覧者として）しようと思っていたのですが、スプレッドシートそのままというのも気が引けたので、Google Apps Scriptを使ってWEBアプリにしてみました。
どのようなデータでも使えるようなツールにしましたので、当記事にて公開します。
ツールの概要
スプレッドシートのデータをWEBアプリとして表示するツールです。詳細は下記のとおりです。
サンプルはこちら
* サンプルで表示されるデータは、テストデータ・ジェネレータを利用して生成した架空のものです。
利用方法
- 当サイトが公開しているスプレッドシートを開く。
- ファイル > コピーを作成で、スプレッドシートをコピーする。
- ツール > スクリプトエディタで、スクリプトエディタを起動する。
- デプロイ > 新しいデプロイから、WEBアプリをデプロイする
アクセスできるユーザーの設定をよく確認のうえデプロイ。
*Googleアカウントを持つ全員や全員は、全世界にデータを公開することを意味しますので注意！
ダイアログが起動するので、ご自身のアカウントを選択し、このアプリケーションにデータへのアクセスを許可してください。
- スプレッドシートにデータを入力し、URLを共有する
スプレッドシートのdataシートに、公開したいデータを入力します。１行目は必ずヘッダー（項目名の行）にしてください。データが入力できたら、先ほどのURLを利用者に伝えてください。
フィルタ機能について
コメント欄にて要望をいただきましたので、フィルタ機能を少しカスタマイズできるようにしました。
- とある項目をフィルタに表示したくない場合
スプレッドシート1行目の、項目名の末尾に:hiddenを付与してください。
例：生年月日をフィルタに表示したくない場合 → 項目名を生年月日:hiddenにする - プルダウン形式にしたい場合
スプレッドシート1行目の、項目名の末尾に:select["選択肢1","選択肢2","選択肢3"]を付与してください。（選択肢はいくつでも設定できます）
例：性別をプルダウンにしたい場合 → 項目名を性別:select["男","女","その他"]にする等 - ラジオボタン形式にしたい場合
スプレッドシート1行目の、項目名の末尾に:radio["選択肢1","選択肢2","選択肢3"]を付与してください。（選択肢はいくつでも設定できます）
例：性別をラジオボタンにしたい場合 → 項目名を性別:radio["男","女","その他"]にする等 - チェックボックス形式にしたい場合
スプレッドシート1行目の、項目名の末尾に:checkbox["選択肢1","選択肢2","選択肢3"]を付与してください。（選択肢はいくつでも設定できます）
例：性別をチェックボックスにしたい場合 → 項目名を性別:checkbox["男","女","その他"]にする等
参考
ソースコードは下記のとおりです。何かの参考になれば幸いです。
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 v-if="!isHidden(key)" style="margin-bottom: 1em;">
<!-- 項目名 -->
<label class="label is-small">{{ key.replace(/:.*$/,"") }}</label>
<!-- プルダウン（:select["A","B"...] が項目名末尾に付与されていた場合） -->
<template v-if="isSelect(key)">
<select v-model="conditions[index]" class="select is-small">
<option value="">選択なし</option>
<option v-for="option in getOptions(key)" :value="option">{{ option }}</option>
</select>
</template>
<!-- ラジオボタン（:radio["A","B"...] が項目名末尾に付与されていた場合） -->
<template v-else-if="isRadio(key)">
<label for="radio-none">
<input type="radio" id="radio-none" value="" v-model="conditions[index]" />
選択なし
</label>
<label v-for="(option, radioIndex) in getOptions(key)" :for="'radio-' + radioIndex">
<input type="radio" :id="'radio-' + radioIndex" :value="option" v-model="conditions[index]" />
{{ option }}
</label>
</template>
<!-- チェックボックス（:checkbox["A","B"...] が項目名末尾に付与されていた場合） -->
<template v-else-if="isCheckbox(key)">
<label v-for="(option, checkboxIndex) in getOptions(key)" :for="'checkbox-' + checkboxIndex">
<input type="checkbox" :id="'checkbox-' + checkboxIndex" :value="option" v-model="conditions[index]" />
{{ option }}
</label>
</template>
<!-- テキストボックス（指定なしの場合） -->
<input v-else v-model="conditions[index]" type="text" class="input is-small">
</div>
</template>
<!-- フィルタクリアボタン -->
<div
@click="initConditions()"
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.replace(/:.*$/,"") }}</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>
<!-- Vue.js -->
<script src="https://cdn.jsdelivr.net/npm/vue@2.6.14"></script>
<script>
var app = new Vue({
el: '#app',
data: {
keys: [], // シートの1行目
records: [], // シートの2行目以降
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();
app.initConditions();
}).getSheetData();
},
methods: {
// フィルタ条件を初期化する
initConditions: function(){
this.conditions = app.keys.map((key) => {
return app.isCheckbox(key)
? []
: ""
})
},
// レコードが全ての絞込条件に合致するかチェックする
checkCondition: function(record){
for (let i = 0; i < this.keys.length; i++){
if ( this.isCheckbox(this.keys[i]) ){
if (this.conditions[i].length && !this.conditions[i].includes(record[i])) return false;
} else {
if ( this.conditions[i] && !record[i].includes(this.conditions[i])) return false;
}
}
return true;
},
// CSVファイルをダウンロードする
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);
},
// フォームタイプをチェックする
isHidden: function(key){ return /^.*:hidden/.test(key) },
isSelect: function(key){ return /^.*:select\[.*\]$/.test(key) },
isRadio: function(key){ return /^.*:radio\[.*\]$/.test(key) },
isCheckbox: function(key){ return /^.*:checkbox\[.*\]$/.test(key) },
// シートの項目名末尾に付与した選択肢の配列を返す
getOptions: function(key){
const param = /:(select|radio|checkbox)\[.*\]$/.exec(key) // param[0] => ':select["A","B","C"]'
const optionsString = /\[.*\]/.exec(param[0]) // optionsString[0] => '["A","B","C"]'
return JSON.parse(optionsString[0]) // return => ["A","B","C"]
}
}
})
</script>
</body>
</html>* CSVダウンロード部分はこちらのサイトを参考にさせていただきました。
コメント
自分が今求めていたものだったので使わせていただきたいです！
自分の環境で動かしてみたら下記のエラーが出てフィルタがうまく動かないようです・・・
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)
色々試行錯誤したところ、セルが数値だと動かないんですね。文字列（書式なしテキスト）にしたら動きました！！！
何度もすいません。
使う時に色を変えたり、アイコンを変更したりするのは大丈夫でしょうか？
コメントありがとうございます。
数値だとエラーになる件、知りませんでした;
教えてくださりありがとうございます。
アイコンや色等をはじめ、すべてお好きに変えていただいて問題ありません。
回答ありがとうございます！
使わせていただきます！！感謝感謝ですm(__)m
喉から手が出るくらい欲しかった情報です！
使わせていただきます！
もしよろしければ、簡単でもよいのでコードの解説をしていただけますと嬉しいです
ご検討ください
ご丁寧な掲載ありがとうございます。
当方、ミャンマーにてネットを頼りにGASでのUIや閲覧WEBAppを作る必要に駆られ目下いろんなサイトを読み漁っておりました。
サンプルまで載せていただいていて、ありがたく試させていただいております。
ところで、スプシのデータ部分が多くなると表示への反映がなくなってしまうのですが、
データ内容の最大記入済みセル数等ありますでしょうか？
いかんせん、こちらのネット環境の脆弱性もあるので、どこが原因なのか測りかねております。
なにかしら、コメントいただけると幸いです。
行数の制限等はないと思います。（とんでもなく多い場合はどうなるかわかりませんが・・・）
もしかすると、スプレッドシートのデータ内に日付形式のデータを使われているのが原因かもしれません。
以前、セルの書式をすべて「書式無しテキスト」にしたら直ったというコメントを頂いたことがあります。
一応、コードの方もJSONでデータを受渡しするよう変更してみましたが、
日付形式のデータは、このままのコードだと意図通りの形で表示されないと思いますので、テキストにしていただくのが一番簡単かな？と思います。
ご確認いただきありがとうございます。
今、すべての数字に関して書式なしテキストにしたところ、AからAH列・925行びっしりデータの詰まったものでも表示ができました。
確かに上記の過去コメントに解決法がありましたね…
落ち着いて書き込む前に、過去コメントを試すべきでした。
申し訳ありませんでした。
ありがとうございました。
こんにちは。私用で簡易的なデータベースを作成するのに大いに参考にさせて頂きました。
当方、ITスキルが殆どないため無知な質問かもしれませんが、可能であれば以下教えて頂けませんでしょうか？
例えば大元のスプレッドシートC列にURLを記載したハイパーリンクが記入されている場合でも、アプリ上では文字列としか認識されません。
これをアプリ上でハイパーリンクとして表示される事は可能でしょうか？
無知ながら色々調べてみるとそもそもJSONが文字列を扱うため出来ないのではと推察していますが、ご教示頂けますと幸いです。
スクリプトエディタでindex.htmlのtbodyタグ部分を下記のように変更すると、http://またはhttps://で始まる文字列を、リンク（aタグ）にできました。
この変更を行った後でデプロイをしてください。
ichi3270さん
ご丁寧にご返信ありがとうございます。ハイパーリンクを無事に拾う事が出来ました。
Vueには不慣れですが色々触って覚えたいと思います。
お世話になっております。
可視化を作成するのに大いに参考にさせて頂きました。
勉強中のため、無知な質問で申し訳ありませんが、下記2点について教えていただきたいです。
①右側のフィルタのフリーテキスト以外に、プルダウンでの選択したい
例）「性別」カラムにあらかじめ「男」「女」をプルダウンで表示
②右側の一覧表示に出ている項目＝フィルタ項目となっているが、フィルタを限定したい
例）「生年月日」は右の一覧には表示するが、左のフィルタには不要
以上、お手数おかけしますが、よろしくお願い申し上げます。
sdgsさん、yuu17さんから同じ質問をいただきましたので、ツールに機能追加しました。
詳細は yuu17 さんへの返信をご確認ください。
こんにちは。社内のデータ運用ツールを作成するにあたり、大いに参考にさせて頂きました。
私もsdgsさんのコメント同様の内容で悪戦苦闘しております。
①指定したフィルタをプルダウンで選択したい
②表示したいフィルタと表示させないフィルタがあり、表示を限定したい。
お忙しい所大変恐縮ではありますがご教示頂けますと幸いです。
sdgsさんとyuu17さんのお二人から同じ質問をいただきましたので、ツールに機能追加しました。
ご利用になる場合は、再度、この記事の手順（ツールをコピーして、デプロイ）を実施してお試しください。
①プルダウンにしたい場合
スプレッドシートの項目名の末尾に:select["選択肢1","選択肢2"]を追記してください。
例：
②非表示にしたい場合
スプレッドシートの項目名の末尾に:hiddenを追記してください。
例：
③その他
ついでなので、ラジオボタンとチェックボックスにも対応しました。
ラジオボタンの例：
チェックボックスの例：
ichi3270さん。お忙しい中、迅速な対応して頂きまして大変ありがとうございました。
感謝感激です。
さっそく参考にさせて頂きます。
誠にありがとうございました。
お忙しい中失礼します。
日付をスプレッドシートに入力したところ、webの方では（2022-10-03T15:00:00.000Z）このように表示されてしまいます。これをMM月dd日のような形で表示させるにはどのようにすればいいのでしょうか？
とりあえずの対処ですみませんが、スクリプトエディタのコード.gsの
getSheetData()を書き換えるのが一番単純です。上記は、G列をyyyy/MM/dd HH:mm:ss形式で表示したい場合の例です。
・row[6]の6の部分は、日付形式にしたい列の列番号 -1 です。G列は7列目ですので、7-1 で 6 になります。
・yyyy/MM/dd HH:mm:ss以外の形式にしたい場合は、formatDate関数の最後の引数を変更してください。
(こちらのサイト様の記事がわかりやすいです。）
ご返信ありがとうございます。
B列の日付形式を変えたく、コードを書き換えたのですがうまく反映されておらず、（2022-10-03T15:00:00.000Z）
このような表示のままです。
書き換えたコード
function getSheetData() {
const sh = ss.getSheetByName(“data”);
const table = sh.getDataRange().getValues();
const rows = table.slice(1)
for (const row of rows) {
row[1] = Utilities.formatDate(row[1], “Asia/Tokyo”, “yyyy/MM/dd”)
}
const response = JSON.stringify(table);
return response
}
どこか間違っているところがありますでしょうか？
コードは問題なさそうです。
先のコメントでお伝え漏れていたのですが、コードを修正した後は、デプロイをやり直す必要があります。
当記事の説明どおり、「新しいデプロイ」をしていただければ大丈夫ですが、WebアプリのURLが新しくなりますので注意してください。
もし、URLを変えたくない場合は、下記の手順で実施してください。
「デプロイ」ボタン→「デプロイを管理」→右上の鉛筆マーク→バージョンを「新バージョン」にする→デプロイボタンを押す
ご返信ありがとうございます。
新しいデプトロイをした後、新しいURLでアクセスしていなかったことが原因でした。ご丁寧にご回答いただきありがとうございます。
繰り返しで申し訳ないのですが、B列の日付形式を変えることはできたのですが、B列に一つでも空白や日付の形式ではない言葉が入っているとgetSheetData()でエラーが出てしまいwebに何も表示されないという状態になってしまいます。空白や日付の形式ではない言葉が入っていてもしっかり表示されるようにできますでしょうか？
・日付型だった場合はyyyy/MM/dd形式に変換
・日付型でなかった場合はそのまま出力
上記のようにしたい場合のコードです。先日のコメント同様、row[6]の6は、列番号から1引いた値です。（例：3列目ならrow[2]とする）
なお、日付型の列が複数列ある場合は、下記のコードで対応できると思います。
dateColumns = [6, 7] の部分に、日付型の列番号-1の数字をカンマ区切りで入力してください。
（この例は[6, 7]なので、7列目と8列目が日付型の場合の例です）
お世話になります。
すごく便利なので使いたいのですが、レコードの中身が長文になると、
横に長く（スクロールしないといけない）状態になります。
横にはスクロールせずに見渡せる状態にしたいのですが、
長文の折り返しなどの調整する場合は、どのあたりをいじればよいでしょうか、、、
styleをいじればできそうな感じですが、よくわからず。
お手数をおかけしますが、よろしくお願いします。
index.htmlのtableタグのstyleを変更すれば、画面内に収まるようになりました。
（white-space: nowrap; を削除）
変更前
変更後
コード変更後は、再度デプロイしてください。
ありがとうございます！！
抽出結果(フィルタ)後はブラウザの幅に合わせて表示されるようになりました。
ちなみにデフォルト(フィルタ なし)だとブラウザの幅に合わせて表示されません、、、
この場合はどのあたりをいじればよいでしょうか。。。
何度も申し訳ございません。。
検証が足りてませんでした、こちらでいかがでしょうか。
tableタグのスタイルから
お手間を取らせてしまい申し訳ありません。
早速、ありがとうございます！！！
ご教示頂いた方法でできました！助かりました！！
お世話になります。
こちらのツール非常に便利で社内用にカスタマイズして作成を進めております。
誠にありがとうございます。
2点ご質問がございまして、最初の画面（フォームに入力していない状態）では検索結果が表示されないにはどのようにしたら良いでしょうか
文字列を何か入力しますと結果として関係ないデータが確認できるということは重々承知しておりますが、当社画面で全部表示されることは避けられればと思っております。
また、上記に派生してですが、検索結果をコピペ不可の表記にすることは可能でしょうか。
ご質問ばかりで恐縮ですがご確認頂けますと幸甚に存じます。
当社画面で全部表示されること
→ 当初画面で全部表示されること
誤字がありました。申し訳ありません。
まず、mountedとmethodsの間に、下記のcomputedのコードを追加します。
次にtbodyの部分を下記のように書き換えます。
これでいかがでしょうか。お試しください。
他の方へのコメントにも記載しておりますが、コードの修正後、デプロイをやり直す必要がありますので、デプロイ忘れにご注意ください。
こちら、早々にありがとうございます！
無事、意図した動作にすることができました。
ご丁寧な対応そしてわかりやすい指示をしてくださりありがとうございます。とても助かりました。
以前質問させて頂いた者です。別の質問になりますが解決策がございましたらご教示頂きたくご連絡いたしました。
現在のフィルタ機能はスプレッドシートの各列毎に設定されると思いますが、それとは別に一つの検索ボックスで全ての列を検索し該当するアイテムを表示させるような事は可能でしょうか？
よろしくお願い致します。
全列をチェックする方法を考えてみました。
まず、左側のフィルタエリアの先頭に、全列検索用の入力欄を追加します。
vue.jsのdataに、全文検索フィールドへ入力した条件を格納する変数を追加します。
絞り込み条件のチェック処理に、全文検索の判定を追加します。
早速の回答ありがとうございます。
試したところ検索機能としては上手く作動しました！ありがとうございます。
一方、フィルタクリアボタンを押しても全検索のコラムだけは入力した文字がリセットされない状況が発生しています。ここは自力で解決しようと色々試したのですが、うまくいかず・・・
もし支障なければこの点もアドバイス頂けますと幸いです。
よろしくお願いいたします。
フィルタクリアの処理の修正が必要でしたね。
methods内のinitConditions関数に
を追加いただければ大丈夫かと思います。
ご教示頂きありがとうございます。フィルタクリアも問題なく動作しました！
これを使わさせてもらって、手動更新のチャットみたいなものを作らせてもらいました。本当にありがとうございました！
お世話になっております。
投稿いただいているツールですが、非常に喉から手が出るほど
求めていたもので、大変ありがたいです。
カスタマイズさせていただきたいのですが、
ITスキルがほぼないため以下２点について、お力添えいただきたいです。
①一覧画面には表示せず、検索条件には表示させることは可能でしょうか？
②画面レイアウト（項目それぞれの表示幅）を自由に変えるようにすることは可能でしょうか？
上記コメントを参照し、画面内に収まる方法を試しましたが、
長文の項目はきれいに収まりますが、隣の項目の短い文字列は縦に長くなってしまいます。
①②どちらかまたは両方できると大変見やすくなると思うのですが、
対応方法ご教授いただけますと幸いです。
よろしくお願いします。
theadタグ前に、colgroupを設定するのが最も簡単かと思います。
最大幅(max-width)の設定は、少し上のコメントにある「white-space」の設定も影響すると思いますので、色々お試しいただければと思います。
早速のご回答ありがとうございます。
調整できるようになったおかげで、とても見やすいものを作ることができました。
今後ともよろしくお願いいたします。
お世話になります。
活用させていただいております。
スプレッドシート上にファイルのURLを書き出して短縮して表示をしているんですが、スプレッドシートの方には元のURLと短縮後のURLが表示されています。Web上にアプリとしてアップしたときに一部の列を(元のURL)非表示にすることは可能でしょうか？何卒よろしくお願いいたします。
table部分にcolgroupを設定すれば、列を非表示にできます。
ひとつ上のEbinnu様への返信を参考にしてみてください。
お世話になっております。
立て続けにご質問すみません。
検索条件①（プルダウン）に入力した内容で
次の検索条件②（プルダウン）で選択できる内容を絞り込みたいときに
どのように組むのがいいでしょうか？
プルダウンで選択できる内容は
スプレッドシート上で式を組み合わせて、可変で表示されるようにはなっています。
（UNIQUE関数、文字列結合）
これももっといい方法があれば、、
お手すきの際にご確認いただければ幸いです。
お力になれず申し訳ありませんが、汎用的に作ろうとするとなかなか難しそうです・・・
項目や選択肢が決まっていて、滅多に変更しないということであれば、74 – 80行目の中に分岐を作ってOptionタグを出しわければ可能だとは思います。
このあたりは、Vue.jsのv-if, v-for およびHTMLについて調べていただくと良いかもしれません。
ご確認いただきありがとうございます。
やはりそうですよね。
こちらでもう少し使い方を検討したいと思います。
ありがとうございます。
無事に非表示にできました。
ありがとうございました。
お世話になっております。
メールアドレスもリンクできるようにしたいのですが、どのようにすればよろしいでしょうか？
お手数ですがよろしくお願いいたします