最近流行りのIFTTTを使ってGmailとスプレッドシートを連携させてみました。
IFTTTとは
テンプレートを作って多数のWebサービスを連携させるサービスです。
例えばタイトルにもある通り、Gmailの受信メールをスプレッドシートに転記するとか。
IFTTTはここからアクセス
ちなみにイフトと読むそうです。
では今回の目玉である連携にいきましょう。
そもそもまともな状態で転記されない問題
連携するのに初端からコケてるじゃねーか！
まともではないというのは、転記時にメールの受信日付が独自のもので記入されてしまう所です。
例えば
May 14, 2016 at 05:12AM
こんな感じ。
ソートできませんよね。困りますよね。
ではこれを解決するにはどうしたら良いのか？
とりあえず思いつくのは何個かありますが、正規表現で抜き取っていくのが楽かもしれません。
ただ、この場合は書式が変わると対応に追われて死にます。
なので応急処置的なものだと考えてます。
（IFTTTとGmailやスプレッドシートを連携する方法は他で調べてね）
早速ポチポチ関数を使って引き抜いていく
想定は1行目に列名を入れて、日時はA2に格納されている状態。
E2セル =F2&"-"&G2&"-"&H2&" "&J2 //フィルタ用日時格納セル
F2セル =REGEXEXTRACT(A2,"[0-9][0-9][0-9][0-9]") //年
G2セル =VLOOKUP(REGEXEXTRACT(A2,"[A-Za-z]+"),month_table!A$1:B$12,2,FALSE)//月
H2セル =REGEXEXTRACT(A2,"[0-9]+")//日
I2セル =REGEXEXTRACT(A2,"[0-9]+:[0-9]+")//時刻（12時）
J2セル =IFERROR(IF(FIND("PM",A2,1)>0,IF(MINUTE(REGEXEXTRACT(I2,"[0-9]+:[0-9]+"))<10,IF(HOUR(I2)=12,HOUR(REGEXEXTRACT(I2,"[0-9]+:[0-9]+"))&":0"&MINUTE(REGEXEXTRACT(I2,"[0-9]+:[0-9]+")),HOUR(REGEXEXTRACT(I2,"[0-9]+:[0-9]+"))+12&":0"&MINUTE(REGEXEXTRACT(I2,"[0-9]+:[0-9]+"))),IF(HOUR(I2)=12,HOUR(REGEXEXTRACT(I2,"[0-9]+:[0-9]+"))&":"&MINUTE(REGEXEXTRACT(I2,"[0-9]+:[0-9]+")),HOUR(REGEXEXTRACT(I2,"[0-9]+:[0-9]+"))+12&":"&MINUTE(REGEXEXTRACT(I2,"[0-9]+:[0-9]+")))),""),IFERROR(IF(FIND("12:",I2,1)>0,IF(MINUTE(REGEXEXTRACT(I2,"[0-9]+:[0-9]+"))<10,"00:0"&MINUTE(REGEXEXTRACT(I2,"[0-9]+:[0-9]+")),"00:"&MINUTE(REGEXEXTRACT(I2,"[0-9]+:[0-9]+"))),""),REGEXEXTRACT(I2,"[0-9]+:[0-9]+"))) //時刻（24時）
最後だけ大変長くなっています。
これは0埋めとAM、PM変換のためです。AM12時なら12を足して0時になります。
ここまで記入していざテストする段階で気づくことがあると思います。ない？残念です。
それはIFTTTには入力された行をスキップする機能があるです。
実際めちゃくちゃ困りました。
とりあえずGAS（スクリプト）で解決を目指す
IFTTT側に設定した列を使用しないとしても、入力された行をスキップする機能を殺せないので
GAS（Google Apps Script）を使うことにします。
function SetCode() {
var ss=SpreadsheetApp.getActiveSpreadsheet(); //スプレッドシートを取得
var sheet=ss.getSheetByName("シート1"); //シートを取得
var maxRow=sheet.getDataRange().getLastRow(); //シートの使用範囲のうち最終行を取得
var code1 ='';
var code2 ='';
var code3 ='';
var code4 ='';
var code5 ='';
var code6 ='';
/* スプレッドシートの入力が完了しているかをチェック */
for(var i=2;i<=maxRow;i++){
code1 ='=F'+i+'&"-"&G'+i+'&"-"&H'+i+'&" "&J'+i+'';
code2 ='=REGEXEXTRACT(A'+i+',"[0-9][0-9][0-9][0-9]")';
code3 ='=VLOOKUP(REGEXEXTRACT(A'+i+',"[A-Za-z]+"),month_table!A$1:B$12,2,FALSE)';
code4 ='=REGEXEXTRACT(A'+i+',"[0-9]+")';
code5 ='=REGEXEXTRACT(A'+i+',"[0-9]+:[0-9]+")';
code6 ='=IFERROR(IF(FIND("PM",A'+i+',1)>0,IF(MINUTE(REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+"))<10,IF(HOUR(I'+i+')=12,HOUR(REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+"))&":0"&MINUTE(REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+")),HOUR(REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+"))+12&":0"&MINUTE(REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+"))),IF(HOUR(I'+i+')=12,HOUR(REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+"))&":"&MINUTE(REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+")),HOUR(REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+"))+12&":"&MINUTE(REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+")))),""),IFERROR(IF(FIND("12:",I'+i+',1)>0,IF(MINUTE(REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+"))<10,"00:0"&MINUTE(REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+")),"00:"&MINUTE(REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+"))),""),REGEXEXTRACT(I'+i+',"[0-9]+:[0-9]+")))';
var code = [code1,code2,code3,code4,code5,code6];
if(sheet.getRange(i,1).getValue()!=""){
if(sheet.getRange(i,5).getValue()==""){
var a=0;
for(var j=5;j<=10;j++){
sheet.getRange(i,j).setFormula(code[a]);
a++;
}
a=0;
}
}
}
}
面倒なので相当雑なプログラムになっています。
こいつをGASでポチポチっと作ってトリガーを時間単位で設定します。
編集時に動かしたかったのですが、どうしてもトリガーが動かないなら定期的に実行すればいいか
くらいの気分です。
ここで共有してます。閲覧のみなのでマイドライブに追加→コピーでもして使ってみてください。
以上！
2016/12/08追記
どうやら正規表現のミスがあったようなので修正しましたよ。
AM12時＝0時
PM12時＝12時
たまに米国の悪しき慣習に引きづられそうになりますが。
