To Doを管理する時にグーグルサイトのリストを使って管理しています。
このリストは非常に便利なのですが、金額を合計できなかったり、特定の項目の数の合計をカウントしたりが出来ません。
そこで、グーグルのスプレッドシートにリストのデータをインポートさせて、リストの作成はグーグルサイト、編集したり、自分の好みに変更したりはスプレッドシートで管理する方法を試行錯誤してようやく完成しました。
ざっくりと手順は下記二点です。
備忘記録として残しておきます。
まずはグーグルサイトでリストを作成する
これはグーグル先生に聞けば腐るほど情報が出てくるので割愛しますが、テストで作成したサイトが下記になります。
https://sites.google.com/site/todolistofclt/list
次にスプレッドシートを作成してエディタにスクリプトを記載する。
これもやり方は割愛します。
スクリプトは下記の通り
——————————————————————————-
function onOpen(){
var mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var myMenuEntries = [];
myMenuEntries.push({name: “Update”, functionName: “getPageItem”});
mySpreadsheet.addMenu(“Action”, myMenuEntries);
}
function getPageItem(){
var sheet = SpreadsheetApp.getActiveSheet();
var site = SitesApp.getSiteByUrl(“https://sites.google.com/site/todolistofclt/list”);
var page = site.getChildByName(“list”)
var cols = page.getColumns();
var items = page.getListItems();
var last = sheet.getLastRow();
try{
var sheet = SpreadsheetApp.getActiveSheet();
sheet.clearContents();
}catch(e){
Browser.msgBox(e);
}
for(var i = 0;i < cols.length;i++){
Logger.log(cols[i].getName());
sheet.getRange(1, i + 1).setValue(cols[i].getName());
for (var j = 0;j < items.length;j++){
Logger.log(items[j].getValueByIndex(i));
sheet.getRange(j + 2, i + 1).setValue(items[j].getValueByIndex(i));
}
}
}
——————————————————————————-
作成したスプレッドシートがこれ
https://docs.google.com/spreadsheets/d/1DeW-PbFet-upR2KxonKKLRmdDh9S1-nsR9TZgcwHi2Y/edit#gid=0
このままスクリプトを実行してもリストで作成したItemを取り込んでくれません。
そこでTasks APIを有効にして実行します。
….と思ってたのですが、特に有効にしなくてもItemの取り込みが実行されました。
まぁ、もし出来ない、もっと詳細を知りたい方はお問い合わせください。
ワードプレスのプラグインで受け付けた予約を、自分のグーグルカレンダーに自動で登録出来れば便利なのになぁ‥というのは前から思ってました。
しかし、ワードプレスとグーグルカレンダーは全く違うシステムなので、そう簡単には連動させてくれません。
もちろん、ワードプレスには便利なプラグインが沢山ありますし、予約システムもプラグインをインストールするだけで構築出来てしまいます。
が、しかし、グーグルカレンダーで予定を管理してるのに別のシステムで管理するのは面倒だし分かりにくい。
どうせならグーグルカレンダーで一元管理させてしまおうではないかと思い試行錯誤してみました。
この辺はいくらでも情報があるので割愛しますが、まずは反映させるグーグルカレンダーの設定を一般公開しておきます。
今回私が使ったのはRestaurant Reservationsというプラグインです。
他にも色々ありますが、シンプルに利用するにはこれが最適ではないかと。
(どのプラグインも大体同じですが)このプラグインも予約を受け付けた時に登録してあるメールアドレス宛に情報が送られてきます。
その送られてきた情報を利用します。
何はともあれまずはワードプレスにあるデータを取り出さないと始まりません。
データベースからエクスポートする方法もあるようですが私には高度過ぎるのでもっと簡単な方法で取り出します。
スプレッドシートを用意してツールのスクリプトエディタより下記のコードを記載します。
for(var n in threads){
var thd = threads[n];
var msgs = thd.getMessages();
for(m in msgs){
var msg = msgs[m];
var body = msg.getBody()
sheet.getRange(row+1, 1).setValue(body);
edit();
row++
}
Utilities.sleep(1000);
}
}
New Booking Requestというタイトルのメールの本文をA列にエクスポートしていきます。
検索対象は1日にして時間主導型(1日単位)のトリガーを設定します。
これでその日に受け取ったメールがある場合は最終行に追加されていきます。
下記コードをさらに追加
for(var i=2; i< =activeSheet.getLastRow(); i++){ var currentValue = activeSheet.getRange('A'+i).getValue(); currentValue = currentValue.replace(/(^\s+)|(\s+$)/g, ""); currentValue = currentValue.replace(/[\n\r]/g,""); currentValue = currentValue.replace(/amp;/g,""); activeSheet.getRange('A'+i).setValue(currentValue); var string = activeSheet.getRange("A" + i).getValue(); var last = string.length; var people = string.indexOf("people") var name = string.indexOf("<br>") var firstname = string.indexOf("</p>") var time = string.indexOf("M") var pending = string.indexOf("View pending bookings") var confirm = string.indexOf("Confirm this booking") var reject = string.indexOf("Reject this booking") var length1 = string.slice(people+10,time+1); activeSheet.getRange(i,2).setValue(length1); var length2 = string.slice(name+4,people); activeSheet.getRange(i,4).setValue(length2); var length3 = string.slice(firstname+7,name); activeSheet.getRange(i,5).setValue(length3); var length4 = string.slice(pending+38,confirm-18); activeSheet.getRange(i,7).setValue(length4); var length5 = string.slice(confirm+37,reject-18); activeSheet.getRange(i,8).setValue(length5); activeSheet.getRange(i,3).setFormula("=B"+i+"+time(0,60,0)") } }
メールの本文をエクスポート?した場合何故かメールによって空白があったり、改行されていたりと形式が違ってました。
そのため、最初に空白と改行を削除しました。
次にamp;というのを空白に置換えてますが、amp;が入っているとワードプレスのプラグイン上でconfirmやrejectの処理が行えないので削除することにより処理を行えるようにしています。
後は特定の文字列までの文字数を検索してそれに合わせて予約時間、予約相手などのデータを取り出してます。
こんな感じになると思います。
スプレッドシートの記載
ここまで出来たら後はカレンダーに登録するスクリプトを実行するだけです。
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var row = sheet.getLastRow();
var cal = CalendarApp.getDefaultCalendar();
for(var i=2; i< =row; i++){ if(sheet.getRange(i,6).getValue() == ""){ sheet.getRange(i, 6).setValue("done"); var cname = sheet.getRange(i,5).getValue(); var d1 = sheet.getRange(i,2).getValue(); var d2 = sheet.getRange(i,3).getValue(); var d3 = sheet.getRange(i,4).getValue(); cal.createEvent(cname, d1, d2, {description: d3+'人', location: '事務所'}); } } }
スプレッドシートに実行ボタンを追加
function onOpen(e){
var arr = [
{name: “メール取得”, functionName: “getMail”},
{name: “カレンダー登録”, functionName: “makeCalEvent”}];
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
spreadsheet.addMenu(“機能”, arr);}
最初から方向性はわかってました。
予約データをスプレッドシートにエクスポートさせて、そのデータを元にカレンダーに登録させる。
エクスポートしたデータをカレンダーに登録させるような形式に変換するところが一苦労でした。
この連動を使うと、予約したと同時にカレンダーに登録されるわけではなく、自分で確認した予定だけを登録することが出来ます。
もちろん、予約と同時にカレンダーに登録させたい場合はトリガーを設定すればいいだけです。
また、確認後7列目のリンクに飛べばプラグインにも反映させるので、確認のメールが相手に送られます。
私が作ったデータはこちらからご確認下さい。
もちろん予定を登録して頂いてもほとんどRejectすると思うのでカレンダーへの反映は確認できないかもしれませんが…
スプレッドシートを使っていて、こんな時はどういった数式が使えるのか?ということをよく思います。
そのたびに調べたりしていて、結局以前調べた繰り返しになることもよくあります。
(あまり使わない数式はすぐ忘れてしまいます。)
ということで、備忘録として記録しておこうと思います。
行数を指定してセルのアルファベットを表示
=SUBSTITUTE(ADDRESS(1,L1,4),1,)
L1に入力されている番号の列に対応する(アルファベット)を表示
(アルファベットを表示させて何をやりたいかというと、GASで代入するアルファベットを取得したいからです。)
日付の書式を設定したい
例:2014/6/30と入力したら2014年06月と表示させ下にドロップすると2014年07月と月単位で変更
(しかも入力値は月の末日)
A4には2014/6/30と入力
A5=EOMONTH(A4,1)
B5=text(A5,”yyyy年mm月”)
縦と横を入れ替える
=TRANSPOSE(‘test’!C1:PC6)
入れ替えたいセルを指定
他のスプレッドシートを参照する
=ImportRange(“スプレッドシートkey”,”test!I4:I100″)