はじめに
GoogleスプレッドシートをGAS（GooglAppsScript)を使って
Webアプリを作成しデータベース代わりに使えるよう考えてみた。
インターフェースはHTMLベースだが、これもGASにて提供されているので、
自分でサーバを用意する必要はない。
自分用の住所録にするもいいと思うし、
ブログするのは嫌な人は、趣味のことを日記風にまとめてもいいんじゃないかと思う。
また、会社での案件管理をエクセルで行って行っているのであれば
こちらに移行するのも手ではないかと思う。
以下お読みになっていただければと思う。
セキュリティ範囲は自己責任でお願いしたく。
第２弾はこちら、スプレッドシートをDB代わりにGASのWebアプリを作成しデータ更新させてみた2
ここが長めなので早めに告知(^^A
用意するもの
・Googleアカウント（スプレッドシートが使えること）
ここに記載しないこと
・GASからHTMLを表示する際に初めに知っておかなければならないこと
こちらを参考にしていただきたく：Google Apps Script でHTMLファイルを作って表示
・排他制御の説明
こちらを参考にしていただきたく：排他制御でGoogle Apps Scriptを安全に実行
・ソートの説明：
こちらを参考にしていただきたく：Google Apps Script試行錯誤Blog
・CSS周りは考慮しない。（見た目は気にしない）（気が向いたら次にやる）
ゴール
・データベース内容は吉野家の(一部の)店舗一覧とする。
→理由：日々お世話になっているから。
→データの内容については、検証のために入力している内容であって実際とはなんの関係もございません。
・一覧表示ができること
→ただし、全件出てきても意味がないので、最新更新の１０件を表示するものとする。
→全項目を表示すると、項目が増えた時に見た目が悪いので、スプレッドシートの左から６列を表示するものとする。
一覧画面はこんな感じ↓
・検索できること（すべてのデータから文言検索）
・新規登録ができること（「新規はこちら」から遷移）
・編集登録ができること（「編集」から遷移）
・上記画面を遷移できること
その他考慮したこと
・項目が増えた時の対応
→項目を追加したいことはよくあることで、スプレッドシートに追加し、コード.gsにも例にならって追加すれば対応できるようにはした。（※スプレッドシートに記載すれば増えていくようにしたかったが、力が足らず実現できなかった。）
やってはいけないこと
・スプレッドシートの行削除
→いらないからといって行を削除してしまうとうまく動作しなかった。
行に書き込むと、そこまで書かれていたことを記憶しているようで
見た目のMAX行と「MAX行教えて～」と問い合わせた時の行が異なる。
削除したいなら、異なるデータで上書き保存すること。
データ
どうやって記載したらいいのかわからないので、とりあえずcsv形式で張っておきます。
どうにかしてスプレッドシートに張り付けてください。
私は下記をコピーして、スプレッドシートのA1を選択、右クリックして、特殊貼り付け、値のみでできました。
タイムスタンプの表示がおかしくなるので、B列を選択、メニューバーの「123▼」をクリックし、日付を選択してできました。
そして、シートの名前を「database」にしてください。（2020/7/16 追記）
データ
リンク,タイムスタンプ,店舗名,郵便番号,県,住所,電話番号,駐車場,駐輪場,感想
2,2020/02/05 14:57:55,吉野家 市ヶ谷１号店,102-00763,東京都,千代田区神田1丁目,,あるかも,,うまかった6
3,2020/02/06 14:57:55,吉野家 秋葉原１号店,101-0021,東京都,千代田区外神田1丁目,,あるような,,おいしかった
4,2020/02/07 14:57:55,吉野家 有楽町１号店,100-0006,東京都,千代田区有楽町2丁目,,,,
5,2020/02/08 14:57:55,吉野家 有楽町２号店,100-0006,東京都,千代田区有楽町2丁目,,,,
6,2020/02/09 14:57:55,吉野家 永田町１号店,100-0014,東京都,千代田区永田町1丁目,,,,
7,2020/02/10 14:57:55,吉野家 水道橋１号店,101-0061,東京都,千代田区神田三崎町2丁目,,,,
8,2020/02/11 14:57:55,吉野家 小川町１号店,101-0052,東京都,千代田区神田小川町2丁目,,,,
9,2020/02/12 14:57:55,吉野家 秋葉原１号店,101-0023,東京都,千代田区神田松永町,,,,
10,2020/02/13 14:57:55,吉野家 神保町１号店,101-0051,東京都,千代田区神田神保町2丁目,,,,
11,2020/02/14 14:57:55,吉野家 淡路町１号店,101-0041,東京都,千代田区神田須田町1丁目,,,,
12,2020/02/15 14:57:55,吉野家 お茶の水１号店,101-0062,東京都,千代田区神田駿河台2丁目,,,,
13,2020/02/16 14:57:55,吉野家 神田１号店,101-0044,東京都,千代田区鍛冶町2丁目,,,,
14,2020/02/17 14:57:55,吉野家 霞ヶ関１号店,100-8918,東京都,千代田区霞が関,,,,
15,2020/02/18 14:57:55,吉野家 霞が関２号店,100-0013,東京都,千代田区霞が関,,,,
16,2020/02/19 14:57:55,吉野家 帯広１号店,080-0011,北海道,帯広市西1条南,,,,
17,2020/02/20 14:57:55,吉野家 旭川１号店,078-8231,北海道,旭川市豊岡1条,,,,
18,2020/02/21 14:57:55,吉野家 旭川２号店,070-0034,北海道,旭川市4条通2丁目,,,,
19,2020/02/22 14:57:55,吉野家 岩見沢１号店,068-0825,北海道,岩見沢市日の出町,,,,
20,2020/02/23 14:57:55,吉野家 新千歳１号店,006-0012,北海道,千歳市美々 新千歳空港,,,,
21,2020/02/24 14:57:55,吉野家 千歳２号店,066-0036,北海道,千歳市北栄2丁目,,,,
22,2020/02/25 14:57:55,吉野家 1厚別東１号店,004-0004,北海道,札幌市厚別区厚別東4条3丁目,,,,
23,2020/02/26 14:57:55,吉野家 苫小牧新開町店,053-0052,北海道,苫小牧市新開町2丁目,,,,
24,2020/02/27 14:57:55,吉野家 羊ヶ丘通清田店,004-0842,北海道,札幌市清田区清田2条3丁目,,,,
25,2020/02/28 14:57:55,吉野家 札幌伏古店,007-0871,北海道,札幌市東区伏古11条4丁目,,,,
26,2020/02/29 14:57:55,吉野家 南郷通6丁目店,003-0023,北海道,札幌市白石区南郷通6丁目北,,,,
27,2020/03/01 14:57:55,吉野家 環状通美園店,062-0004,北海道,札幌市豊平区美園4条6丁目,,,,
28,2020/03/02 14:57:55,吉野家 36号線苫小牧店,053-0021,北海道,苫小牧市若草町3丁目,,,,
29,2020/03/03 14:57:55,吉野家 環状通東店,065-0015,北海道,札幌市東区北15条東17丁目,,,,
30,2020/03/04 14:57:55,吉野家 231号線篠路店,002-8022,北海道,札幌市北区篠路2条1丁目,,,,
31,2020/03/05 14:57:55,吉野家 札幌エスタ店,060-0005,北海道,札幌市中央区北5条西2丁目,,,,
32,2020/03/06 14:57:55,吉野家 札幌狸小路店,060-0063,北海道,札幌市中央区南3条西4丁目,,,,
33,2020/03/07 14:57:55,吉野家 札幌駅ＡＰＩＡ店,060-0005,北海道,札幌市中央区北5条西4丁目,,,,
34,2020/03/08 14:57:55,吉野家 札幌麻生店,001-0040,北海道,札幌市北区北40条西5丁目,,,,
35,2020/03/09 14:57:55,吉野家 大通西10丁目店,060-0042,北海道,札幌市中央区大通西10丁目,,,,
36,2020/04/03 16:44:33,吉野家 春吉店,810-0003,福岡県,福岡市中央区春吉3丁目,,,,神奈川と姉妹店？
37,2020/04/03 13:27:54,吉野家 天神サザン通り店,810-0001,福岡県,福岡市中央区天神2丁目,,,,サザンだぜ
38,2020/03/12 14:57:55,吉野家 博多祇園店,812-0038,福岡県,福岡市博多区祇園町,,,,
39,2020/03/13 14:57:55,吉野家 築港本町店,812-0022,福岡県,福岡市博多区神屋町,,,,
40,2020/03/14 14:57:55,吉野家 博多駅地下街店,812-0012,福岡県,福岡市博多区博多駅中央街,,,,
41,2020/03/15 14:57:55,吉野家 博多デイトスアネックス店,812-0012,福岡県,福岡市博多区博多駅中央街,,,,
42,2020/03/16 14:57:55,吉野家 港町店,810-0075,福岡県,福岡市中央区港2丁目,,,,
43,2020/03/17 14:57:55,吉野家 東光寺店,812-0896,福岡県,福岡市博多区東光寺町1丁目,,,,
44,2020/03/18 14:57:55,吉野家 3号線箱崎店,812-0053,福岡県,福岡市東区箱崎2丁目,,,,
45,2020/04/03 13:28:29,吉野家 福岡空港国際線ターミナル店,812-0851,福岡県,福岡市博多区青木739 福岡空港,,,,空港だってうまいぜ
46,2020/03/20 14:57:55,吉野家 大橋店,815-0033,福岡県,福岡市南区大橋1丁目,,,,
47,2020/03/21 14:57:55,吉野家 早良街道西新店,814-0004,福岡県,福岡市早良区曙1丁目,,,,
48,2020/03/22 14:57:55,吉野家 大池通り長丘店,815-0075,福岡県,福岡市南区長丘5丁目,,,,
49,2020/03/23 14:57:55,吉野家 福岡原田店,812-0063,福岡県,福岡市東区原田3丁目,,,,
50,2020/04/03 13:55:45,吉野家 粕屋仲原店,811-2304,福岡県,糟屋郡 粕屋町仲原,,,,やすい
51,2020/04/03 13:48:19,吉野家 202号線バイパス原店,814-0022,福岡県,福岡市早良区原4丁目,,,,バイパス横も最高
52,2020/04/03 13:16:15,吉野家 千早東店,813-0036,福岡県,福岡市東区若宮5丁目,,,,ここもうまい
53,2020/03/27 14:57:55,吉野家 385号線那珂川店,811-1204,福岡県,那珂川市片縄東1丁目,,,,福岡はいい！
54,2020/04/03 16:05:57,吉野家 宇都宮細谷町店,320-0074,栃木県,宇都宮市細谷町,99999999999,,,いいよ
55,2020/04/03 14:06:45,吉野家 宇都宮競輪場通り店,320-0013,栃木県,宇都宮市上大曽町,,,,gggg
56,2020/04/03 14:09:36,吉野家 宇都宮城東店,321-0935,栃木県,宇都宮市城東1,,,,hhhhh
57,2020/04/06 13:56:41,吉野家 広島本通店,730-0035,広島県,広島市 中区 本通8,1234567890,,,うまい
58,2020/04/06 14:15:03,吉野家 カインズホーム木更津店,292-0008,千葉県,木更津市金田東３丁目,,,,
59,2020/04/03 17:20:54,吉野家 厚木関口店,243-0804,神奈川県,厚木市関口,111111111,あり,,
60,2020/04/06 14:08:28,吉野家 松山竹原店,790-0053,愛媛県,松山市竹原２丁目,222334455,●かな,●かな,うまい
61,2020/04/06 13:59:59,吉野家 広島宇品店,734-0014,広島県,広島市南区宇品西３丁目,12345678,あったかな,あると思う,うまーい
62,2020/04/06 13:57:54,吉野家 広島吉島店,730-0823,広島県,広島市中区吉島西２丁目,,,,
63,2020/04/06 14:13:03,そば処吉野家 弘前中央通り店,036-8012,青森県,弘前市北瓦ヶ町７－１,,,,そばもあるの
それではコード
お恥ずかしいコードですが、公開します。
全部で４ファイルです。
コード.gs ：スプレッドシートからスクリプトエディタを選択した時にできるファイル
index.html ：一覧表示、検索一覧表示
result.html ：編集画面
newPage.html ：新規登録画面
コード.gs
//スプレッドシートの項目が増えた場合、以下の関数を修正してください。
function getParamet(ary1, e) {
ary1.push(e.parameters.d_item2);
ary1.push(e.parameters.d_item3);
ary1.push(e.parameters.d_item4);
ary1.push(e.parameters.d_item5);
ary1.push(e.parameters.d_item6); //G列
// -----------------------------------------------------------------------
//項目が増えたら増やす
ary1.push(e.parameters.d_item7); //H列
ary1.push(e.parameters.d_item8); //I列
ary1.push(e.parameters.d_item9); //J列
// -----------------------------------------------------------------------
return ary1;
}
function doGet(e) {
if (e.parameter.mode == 'list' || e.parameter.mode == null) {
var template = HtmlService.createTemplateFromFile('index');
var ItemNameList = getItemNameList(6);
Logger.log(ItemNameList);
template.itemName = ItemNameList;
tmp1 = getNewDataList();
//Logger.log(tmp1);
var res3 = [];
for(var i = 0; i < tmp1.length; i++){
res3.push(getCellValue(tmp1[i][0], 6));
}
template.res = res3;
} else if (e.parameter.mode == 'edit') {
var template = HtmlService.createTemplateFromFile('result');
var ItemNameList = getItemNameList(-1);
template.itemName = ItemNameList;
var editRow = e.parameter.row;
template.row = editRow;
template.res = getCellValue(editRow, -1);
Logger.log(editRow);
}
if (e.parameter.mode == 'new') {
var template = HtmlService.createTemplateFromFile('newPage');
var ItemNameList = getItemNameList(-1);
template.itemName = ItemNameList;
}
return template.evaluate();
}
function doPost(e) {
Logger.log('ここから');
Logger.log(e.parameter.mode);
Logger.log('ここまで');
var spread = SpreadsheetApp.getActiveSpreadsheet() ;
var sheet = spread.getSheetByName('database');
var date = new Date();
// 今日の日付を表示
date1 = Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');
var template = HtmlService.createTemplateFromFile('index');
var ItemNameList = getItemNameList(6);
template.itemName = ItemNameList;
if (e.parameter.mode == 'edit') {
//編集時
var ary1 = [];
var d_row = e.parameters.d_item0;
ary1.push(d_row);
ary1.push(date1);
ary1 = getParamet(ary1, e);
var ary2 = [];
ary2.push(ary1);
dataNewAdd(sheet, d_row,1,1,ary2[0].length, ary2);
//sheet.getRange(d_row,1,1,ary2[0].length).setValues(ary2);
tmp1 = getNewDataList();
var res3 = [];
for(var i = 0; i < tmp1.length; i++){
res3.push(getCellValue(tmp1[i][0], 6));
}
template.res = res3;
}else if (e.parameter.mode == 'new') {
//新規作成時
var ary1 = [];
ary1.push(sheet.getLastRow()+1);
ary1.push(date1);
ary1 = getParamet(ary1, e);
var ary2 = [];
ary2.push(ary1);
dataNewAdd(sheet, sheet.getLastRow()+1, 1, 1, ary2[0].length, ary2);
//sheet.getRange(sheet.getLastRow()+1,1,1,ary2[0].length).setValues(ary2);
tmp1 = getNewDataList();
var res3 = [];
for(var i = 0; i < tmp1.length; i++){
res3.push(getCellValue(tmp1[i][0], 6));
}
template.res = res3;
} else {
//検索時
var res2 = rowSearch(e.parameter.search);
var res3 = [];
for(var i = 0; i < res2.length; i++){
res3.push(getCellValue(res2[i], 6));
}
//<?= res ?> がHTMLにあること
template.res = res3;
}
return template.evaluate();
}
//更新や新規登録を行う
//排他制御を掛けるので更新処理を一か所にまとめた
function dataNewAdd(sheet, row1, col1, row2, col2, data){
//UIを取得する
//var ui = SpreadsheetApp.getUi();
var msg = "";
//ドキュメントロックを使用する
var lock = LockService.getDocumentLock();
//30秒間のロックを取得
try {
//ロックを実施する
lock.waitLock(30000);
//ここにメインルーチンを記述する
sheet.getRange(row1, col1, row2, col2).setValues(data);
//メッセージを格納
msg = "保存完了";
} catch (e) {
//ロック取得できなかった時の処理等を記述する
var checkword = "ロックのタイムアウト: 別のプロセスがロックを保持している時間が長すぎました。";
//通常のエラーとロックエラーを区別する
if(e.message == checkword){
//ロックエラーの場合
msg = "更新処理中でした";
}else{
//ソレ以外のエラーの場合
msg = e.message;
}
} finally {
//ロックを開放する
lock.releaseLock();
//メッセージを表示する
//ui.alert(msg);
}
}
//エラーメッセージを保存
//項目名称を取得する
function getItemNameList(col){
var res = [];
var spread = SpreadsheetApp.getActiveSpreadsheet() ;
var sheet = spread.getSheetByName('database');
if (col == -1) {
var values = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
} else {
var values = sheet.getRange(1, 1, 1, col).getValues();
}
Logger.log('values getItemNameList');
Logger.log(values);
return values;
}
//タイムスタンプの新しい１０件を取得する。[行番号,日付]の二次元配列で時間で降順
//スプレッドシートの左から６列までとする
function getNewDataList(){
var res = [];
var spread = SpreadsheetApp.getActiveSpreadsheet() ;
var sheet = spread.getSheetByName('database');
var values = sheet.getRange(2, 1, sheet.getLastRow()-1, 2).getValues();
//Logger.log('values before');
//Logger.log(values);
//ソート sorting_asc sorting_desc
values.sort(sorting_desc);
Logger.log('values after');
Logger.log(values);
for (var i = 0; i < 10; i++){
res.push(values[i]);
}
return res;
}
//スプレッドシート内を文言で検索し行番号を返す（同じ行内に複数出てくると抽出結果も重複する
function rowSearch(str){
var res = [];
var spread = SpreadsheetApp.getActiveSpreadsheet() ;
var sheet = spread.getSheetByName('database');
var textFinder = sheet.createTextFinder(str);
var ranges = textFinder.findAll();
for(var i = 0; i < ranges.length; i++){
var range = sheet.getRange(ranges[i].getA1Notation());
res.push(range.getRow());
}
var res2 = uniqueArray(res);
return res2;
}
//行番号からセル値を取得
function getCellValue(row, col){
// 現在アクティブなスプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('database');
// そのシートにある (1, 1) のセルから3行目までのセル範囲を取得
if (col == -1 ) {
var range = sheet.getRange(row, 1, 1, sheet.getLastColumn());
} else {
var range = sheet.getRange(row, 1, 1, col);
}
// そのセル範囲の値を取得
var values = range.getValues();
values[0][0] = row;
values[0][1] = Utilities.formatDate( values[0][1], 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');
return values[0];
}
//一次配列から重複を排除する
function uniqueArray(ary){
var res = [];
res = ary.filter(function(value, index, self){
return self.indexOf(value) === index;
});
return res;
}
//ソート昇順
function sorting_asc(a, b){
if(a[1] < b[1]){
return -1;
}else if(a[1] > b[1] ){
return 1;
}else{
return 0;
}
}
//ソート降順
function sorting_desc(a, b){
if(a[1] > b[1]){
return -1;
}else if(a[1] < b[1] ){
return 1;
}else{
return 0;
}
}
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form method="post" action='https://script.google.com/macros/ここに公開時のURLを記載/exec'>
<input type=hidden name=mode value='list' />mode: list
<h2>吉野家一覧画面</h2>
初期表示は、スプレッドシートの２列目のタイムスタンプで最新の１０行、6列を表示します。<br>
そのため、一覧表示したいものは左から６項目内にするようにします。<br>
ただし、左の２列はそのままにしてください。よって、残りの４項目になります。（※ソースを変更すれば項目数は調整できます）<br>
検索時は、スプレッドシート全体から対象文言を検索し、ヒットした行すべてを表示します。<br>
スプレッドシートの項目を追加（１行目の項目名を追加）する場合は、「コード.gs」のgetParamet関数の<br>
「//項目が増えたら増やす」の下にある書き方にならって増やしてください。<br>
<br>
新規は<a href='https://script.google.com/macros/ここに公開時のURLを記載/exec?mode=new'>こちら</a><br>
検索：<input type='text' name=search />
<input type='submit' value='検索' /><br><br>
<table border=1>
<!-- 項目名を取得し、表示する -->
<? for (var g = 0; g < itemName.length; g++) { ?>
<tr>
<? for (var h = 0; h < itemName[g].length; h++) { ?>
<td><?= itemName[g][h] ?></td>
<? } ?>
</tr>
<? } ?>
<!-- データを取得し、表示する -->
<? for (var i = 0; i < res.length; i++) { ?>
<tr>
<? for (var j = 0; j < res[i].length; j++) { ?>
<? if (j == 0) { ?>
<td><a href='https://script.google.com/macros/ここに公開時のURLを記載/exec?mode=edit&row=<?= res[i][j] ?>'>編集</a></td>
<? } else { ?>
<td><?= res[i][j] ?></td>
<? } ?>
<? } ?>
</tr>
<? } ?>
</table>
</form>
</body>
</html>
更新画面↓
result.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form method="post" action='https://script.google.com/macros/ここに公開時のURLを記載/exec'>
<input type=hidden name=mode value='edit' />mode: edit
<h2>吉野家 編集画面</h2>
<br>
<table border=1>
<!-- 項目名を取得し、表示する -->
<? for (var h = 0; h < itemName[0].length; h++) { ?>
<tr>
<td><?= itemName[0][h] ?></td>
<? if ( h == 0 ) { ?>
<td><input type=hidden name=d_item<?= h ?> value='<?= res[h] ?>' /><?= res[h] ?></td>
<? } else if (h == 1) { ?>
<td><input type=hidden name=d_item<?= h ?> /><?= res[h] ?></td>
<? } else { ?>
<td><input type=text name=d_item<?= h ?> value='<?= res[h] ?>' /></td>
<? } ?>
</tr>
<? } ?>
</table>
<input type='submit' value='更新' /><br>
<a href='https://script.google.com/macros/ここに公開時のURLを記載/exec?mode=list'>検索ページへ</a>
</form>
</body>
</html>
新規登録画面↓
newPage.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form method="post" action='https://script.google.com/macros/ここに公開時のURLを記載/exec'>
<input type=hidden name=mode value='new' />mode: new<br>
<h2>吉野家 登録画面</h2>
<br>
<table border=1>
<!-- 項目名を取得し、表示する -->
<? for (var h = 0; h < itemName[0].length; h++) { ?>
<tr>
<td><?= itemName[0][h] ?></td>
<? if ( h == 0 ) { ?>
<td><input type=hidden name=d_item<?= h ?> /></td>
<? } else if (h == 1) { ?>
<td><input type=hidden name=d_item<?= h ?> /></td>
<? } else { ?>
<td><input type=text name=d_item<?= h ?> /></td>
<? } ?>
</tr>
<? } ?>
</table>
<input type='submit' value='登録' /><br>
<a href='https://script.google.com/macros/ここに公開時のURLを記載/exec?mode=list'>検索ページへ</a>
</form>
</body>
</html>
以上、誰かのお役に立てれば幸いです。
でわでわ。