UEI shi3zの日記

■Google Spreadsheet のスクリプト機能が便利すぎる! ガントチャートもどきの自動生成やToDoリスト管理、そしてモテプログラミングまで

　いやー、マクロを舐めてた。

　

　「マクロ?あんなもんプログラムじゃねえよ」

　と思っていたんだけど、想像以上に便利で驚愕した。

　マクロにも色々あるんだけど、特にGoogle Appsのマクロは秀逸。

　Google Apps Script(GAS)と言うらしい。

　なにがいいって、JavaScriptだってこと。VBAとか特殊な言語を勉強しなくてもマクロが使える。これは便利。

　もろちんGAS特有のクラスについては多少覚えなきゃならないこともあるけれども、それを補ってあまりある便利さ。今更だけど。気付くの遅かった。

　たとえばMicrosoft Projectみたいな高価なプロジェクト管理ツールが使えない環境では、結局 Google Spreadsheetみたいなので、プロモーションとか取材日程とか工場との折衝とかおおざっぱな作業日程を管理したりする(バグとか機能実装の詳細管理はBTS使うけどね)。

　ガントチャートは見る分には便利だけど入力するのが結構面倒(依存関係とか)なのでついつい作るのを忘れてしまったり、日付と実態がズレたりしてこれをメンテするだけで相当面倒なことになるケースも少なくない。めちゃくちゃ大規模な開発なら仕方ないけどね。

　これをGoogle Spreadsheetで共有すると、まあ10人以下の少人数チームならある程度は進捗の把握に使えなくもない。むしろこのレベルなら相当便利なのだ。

　んで、こういうゆるい工数管理をしてるときに、何がどのくらい進行しているのか、ゆるい工数表からガントチャートもどきを作りたい、と思ったら、Google Apps Scriptの出番だ。

function makeChart(e) { 

  var ss = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
  var sheet = ss.getSheetByName("task"); //シート名「task」
  var cell = sheet.getRange('a1');
  var col = 0;
  
  var offsetCol=6;
  var offsetMon=4;//4月始まり
  var daysOfMonth=[0,31,28,31,30,31,30,31,31,30,31,30,31];
  
  //白でクリア
  var area = sheet.getRange(1,5,100,sheet.getLastColumn()).setBackground("white");

  var monthOffset=0;
  for(month=offsetMon;month<=offsetMon+1;month++){
    cell.offset(0,offsetCol+monthOffset).setValue(month+"月");
    for(day=1;day<daysOfMonth[month];day++){
      if(day%5==1)
       cell.offset(1,offsetCol+day-1+monthOffset).setValue(day);
      sheet.setColumnWidth(offsetCol+day+monthOffset,15);
    }
    monthOffset+=day-1;
  }

  for(i=0;i<sheet.getLastRow();i++){
    var stDay = cell.offset(i+2,2).getValue();
    if(stDay)
    if(stDay.getDay){
      var st = stDay.getDate() +  (stDay.getMonth()-offsetMon+1)*daysOfMonth[stDay.getMonth()+1];
      var edDay = cell.offset(i+2,3).getValue();
      var ed = st+1;
      if(edDay){
        ed=edDay.getDate() +  (edDay.getMonth()-offsetMon+1)*daysOfMonth[stDay.getMonth()+1];
      }
      if(st==ed)ed=st+1;
      for(j=st;j<ed;j++)
        cell.offset(i+2,j+offsetCol-1).setBackground("red");
    }
  }
}
function setUp() {
  var ss = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
  ss.insertColumnsAfter(6,100); //表を広げる
}

　これをScriptとして設定して、まず

　setUp関数を実行すると、表が横方向に拡大される。

　それから

　makeChart関数を実行すると・・・

　おお、まるで魔法のようにガントチャートのようなものが!(依存関係が書かれてないので厳密にはガントチャートではないが)。

　毎回実行するのは面倒なので、トリガーを設定できる。

　Resources→Current Project's triggersから「表を編集すると自動的にmakeChartを実行」するように設定すると、編集するだけで自動的にガントチャートもどきが更新される!

　わお!簡単便利

　他にも表に書かれたあるメールアドレスに順番に一斉同報送信したり(迷惑メール大量に出しそうだから下手にやらないように注意)するには以下のような簡単なコードで事足りる。

function sendMail(){
  var ss = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
  var sheet = ss.getSheetByName("emails"); //シートの名前
  var cell = sheet.getRange('a1');
  
  for(i=0;i<sheet.getLastRow();i++){
    MailApp.sendEmail(cell.offset(i,0).getValue(),"こんにちは!","こんにちはこんにちは");//はまちちゃん風
  }

　これ下手に送ったらただの迷惑メールなので気を付けよう。

　しかし、ここまで簡単に迷惑メールが出せてしまうと迷惑メールシステムで何百万も取ってた業者のことを考えるといろいろ考えさせられる(もちろん、実際の迷惑メールというかメール一斉送信の仕組みを構築するにはここには話題にしていないもっと複雑かつ大規模な問題に対処する必要がある)。

　Google Appsは、使えるデータの大きさや複雑さに制限があるものの、複数のユーザーが同時にひとつのドキュメントを編集したり、そうして編集されたデータに対してリアルタイムに反応するスクリプトをクラウド側に持たせたりすることができるので、アイデア次第でなんでもできそう(まあこのアイデア次第でなんでも・・・という台詞ほど胡散臭いものはないのだが)。

　でもちょっと表計算ソフトのマクロ見直した。

　特にGoogle Spreadsheetは複数人が同時に編集できるので、以前はD2C主催のアプリ甲子園の審査に使ったりしてた(セルでチャットみたいなこともできるし)んだけど、もっと面白いことが出来そうだね

　他にも、WebのフォームからGoogle App Scriptを呼び出したりもできるので、簡単なお店の予約システムだったらサーバーをまったく立てなくてもGoogle Appsだけで無料のシステムを構築可能だ!Yeah!

　これは便利すぎて反則というレベル。

　しかもJavaScriptだし。

追記

　ついでにToDoリストも管理してみるか、と思った。

　なんかここ数年、僕はToDoを自分で管理してないことに気付いた。こりゃダメだ。堕落しとる。

　ToDoで管理するほどの仕事がなくなったとも言えるが、最近は月刊連載が二本始まったり、ゲンロンカフェのイベントやったり、まあもちろん忘れちゃいけないenchantMOONがあったりと忙しい。こんなの管理しなきゃやってられん。

　ToDoリストの管理ツールは世の中に死ぬほどあるが、決定版、みたいなものはまだないような気がする。iOSのリマインダーもイマイチだしね。

　こんな感じのToDo管理表を自分でつくる。

　「これスクリプトいらねえじゃん。こんなのなら俺もやっとるわ!」

　と思うだろう。違うのだ。

　そもそもToDoの管理ってのは、大きく分けて三つある。

　ひとつは「やること」の管理、次に「やる順番」の管理、そして「やったことの管理」だ。

　「やること」はまあ別に説明するまでもない。「やる順番」は優先度順だ・・・と簡単に言えればいいが、実際はそうでもない。

　優先度は高いが締め切りが遠い場合、締め切りが近いものからやる必要がある。

　優先度が低くても締め切りが明日なら、今からやらないといけないし。

　さらに入力。みんなToDoの項目を入力するときに「これは明日までにやらないとだから、えーと、今日は17日だから明日は18日か・・・」なんて無駄なことに頭を使ってないだろうか。これははっきり言って無駄な時間だ。一秒でも貴重な時間を、そんな日付の足し算みたいなくだらないことに浪費すべきではない。

　そこでshi3z式ToDo管理表では「明日」と書くことにする。

　するとなんということでしょう・・・・

　自動的に4/18の日付が入る。しかも「明日」の予定だから、強調するために赤くハイライトされるのだ。

　同様に

　「今週中」と入力すると・・・

　ああなんということでしょう。

　4月19日の金曜日がデッドラインに設定されちゃう。

　同様に来月中、今月中、なんていうのも対応してる。わー便利。世界にたったひとつ、俺の俺による俺のためのToDo管理リストのできあがりだっ!

function Autocomp() {
  var ss = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
  var sheet = ss.getSheetByName("Sheet1"); 
  var cell = sheet.getRange('a1');
  var col = 0;
  var daysOfMonth=[0,31,28,31,30,31,30,31,31,30,31,30,31];
  
  var today = new Date();

  
  for(var i=0;i<sheet.getLastRow();i++){
    for(var j=0;j<sheet.getLastColumn();j++){
      var c = cell.offset(i,j).getValue();
      
      if(c=="今から"){
        cell.offset(i,j).setValue(today);
      }
      if(c=="今日中"){
        var deadline =new Date(today.getFullYear(),today.getMonth(),today.getDate(),23,59,59);
        cell.offset(i,j).setValue(deadline);
      }
      if(c=="明日"){
        var deadline =new Date(today.getTime() + 24*60*60*1000);
        cell.offset(i,j).setValue(deadline);
      }
      
      if(c=="今月中"){
        var until = (daysOfMonth[today.getMonth()+1] - today.getDate());
        var deadline =new Date(today.getTime() + until*24*60*60*1000);
        cell.offset(i,j).setValue(deadline);
      }

      if(c=="今週中"){
        var until = (7+5 - today.getDay())%7;
        var deadline =new Date(today.getTime() + until*24*60*60*1000);
        cell.offset(i,j).setValue(deadline);
      }

      if(c=="来週中"){
        var until = (7+5 - today.getDay())%7+7;
        var deadline =new Date(today.getTime() + until*24*60*60*1000);
        cell.offset(i,j).setValue(deadline);
      }

      if(c=="再来週"){
        var until = (7+5 - today.getDay())%7+14;
        var deadline =new Date(today.getTime() + until*24*60*60*1000);
        cell.offset(i,j).setValue(deadline);
      }
      
      c = cell.offset(i,j).getValue();
      if(c.getTime){
        if(((c.getTime()/1000/24/60/60)-(today.getTime()/1000/24/60/60)) <= 30){
          cell.offset(i,j).setBackground("#aaaaff");
          if(((c.getTime()/1000/24/60/60)-(today.getTime()/1000/24/60/60)) <= 14){
            cell.offset(i,j).setBackground("#aaffaa");
            
            if(((c.getTime()/1000/24/60/60)-(today.getTime()/1000/24/60/60)) <= 7){
              cell.offset(i,j).setBackground("#ffffaa");
              
              
              if(((c.getTime()/1000/60/60)-(today.getTime()/1000/60/60)) <= 30){
                  cell.offset(i,j).setBackground("#ff6666");
              }
              if((today.getFullYear() == c.getFullYear())&&
                (today.getMonth() == c.getMonth())&&
                  (today.getDate() == c.getDate()))
                  cell.offset(i,j).setBackground("#ffaaaa");
            }
          }
        }else
            cell.offset(i,j).setBackground("#ffffff");
      }
      
      
    }
  }
}

　これもまた、「編集」をトリガーにしてるので、リターンキーを押すと勝手に入れ替わる。

　さてさて、では「順番」の管理はどうするか。

　面倒なので他のシートを作ってそこをソートすることにした。

　「Priority(優先度順)」と「Deadline(期限順)」の二つのシートをつくり、こちらに自動的にコピーしてソートするようになっている。

function copyAndSort(){ 
  
  //期限が近い順にソート
  var ss = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
  var sheet = ss.getSheetByName("Sheet1"); 
  var sheet2 = ss.getSheetByName("Deadline");
  sheet.getRange("A:G").copyTo(sheet2.getRange("a1"));
  sheet2.getRange("A2:G100").sort([5]);

  //優先度が高い順にソート
  var ss = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
  var sheet3 = ss.getSheetByName("Priority");
  sheet.getRange("A:G").copyTo(sheet3.getRange("a1"));
  sheet3.getRange("A2:G100").sort([3]);
}

　わーお。やったー

　うーん。ここまで作るとですね。

　もう一歩って感じしない?

　何がもう一歩なのかというと、ここまでハイパーナイスなToDo管理を実現してるわけですが、いかんせんスマートフォンでToDoを確認できないじゃないですか。僕たちユビキ達社会において、スマホで使えないとかあり得なくないっスカ?

　まあスマホ用のフォームとか用意すりゃいいんだろうけど、それも面倒なので、とりあえず毎日期限の近いToDo5件を自分にメールするようにしてみることにする。

　まずコードを書いてみよう

function mail(){
  copyAndSort();

  var ss = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
  var sheet = ss.getSheetByName("Deadline"); 
  var cell = sheet.getRange('a1');
  
  var message="本日のToDo\n------------------------\n";
  for(var i=1;i<=5;i++){
    message+=cell.offset(i,0).getValue()+"\n"+
      "   優先度:"+cell.offset(i,2).getValue()+"\n"+
      "   期限:"+cell.offset(i,4).getValue()+"\n"+
      "   ステータス:"+cell.offset(i,3).getValue()+"\n";      
  }
  message+="------------------------\n";
  message+="しっかり働けよ!\n";
  
  MailApp.sendEmail("ore@shi3z.net", "本日のToDo", message);
 
}

　こういうコードを書いて実行すると・・・

　こんなメールが送られて来る。

　これが毎朝くれば、朝起きる度に「ああ、今日もこんなに仕事がたまってるのか・・・」とさわやかな目覚めを体験できることは必定!

　こんな感じで時間をトリガーに設定しておけば、ToDoリストを確認し忘れることもない。これは便利!

　うーん、これは素晴らしいぞ。

　

　さあここでお便りの紹介だ。

拝啓 shi3z様

　こんにちは。いつも楽しくブログを拝見させていただいています。

　電脳空間カウボーイズを聞いていたおかげで会社では出世し、ついに彼女ができたのですが、そこで思わぬ罠にひっかかってしまいました。

　出世したせいで仕事が忙しく、なかなか彼女と会う時間がとれません。

　それどころか、彼女にメールすることさえ忘れてしまって、せっかく彼女ができたのに早くも別れを切り出されそうな状態です。

　なにかプログラミングで彼女の気を引くいい方法はないでしょぅか。

世田谷区　係長A

　なるほど。そういうことなら、やりましょう!(孫正義)

　モテるためのプログラミング。テストに出るよ!

　まず、愛の言葉をひたすら、スプレッドシートにダーっと書きます。

　挨拶、話題、愛の言葉、署名、ラッキーアイテムを同じ数だけ書いて下さい(ここポイント)

　ここが一番クリエイティビティ要求されるところだけどガンバって!彼女への愛でなんとか!

　さあ、そしてまたいつものように「Script Editor」を起動し、以下のスクリプトを打ち込む!!

function rand(max){
  return ~~(Math.random()*max);
}
function loveMail() {
  var ss = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
  var sheet = ss.getSheetByName("Sheet1"); 
  var cell = sheet.getRange('a1'); 
  var max = ss.getLastRow()-1;
  
  var greeting = cell.offset(rand(max)+1,0).getValue(); //挨拶
  var topic = cell.offset(rand(max)+1,1).getValue();    //話題
  var love = cell.offset(rand(max)+1,2).getValue();     //愛の言葉
  var sign = cell.offset(rand(max)+1,3).getValue();     //署名
  var item = cell.offset(rand(max)+1,4).getValue();     //ラッキーアイテム
  
  MailApp.sendEmail("kanozyo@onnnabakkari.com",greeting,
                   greeting+"\n"+topic+"\n"+love+"\n\n"+sign+"より\n　今日のラッキーアイテムは"+item+"だよ!");
  
}

　これを毎朝実行するようにすれば、彼女のメールボックスに・・・

　毎朝こんなイカしたラブメールが自動送信される!!

　これは便利だ!どうだ!

　しかも、これを複数の相手に送るには

  MailApp.sendEmail("kanozyoA@onnnabakkari.com",greeting,
                   greeting+"\n"+topic+"\n"+love+"\n\n"+sign+"より\n　今日のラッキーアイテムは"+item+"だよ!");
  MailApp.sendEmail("kanozyoB@onnnabakkari.com",greeting,
                   greeting+"\n"+topic+"\n"+love+"\n\n"+sign+"より\n　今日のラッキーアイテムは"+item+"だよ!");
  MailApp.sendEmail("kanozyoC@onnnabakkari.com",greeting,
                   greeting+"\n"+topic+"\n"+love+"\n\n"+sign+"より\n　今日のラッキーアイテムは"+item+"だよ!");
  MailApp.sendEmail("kanozyoD@onnnabakkari.com",greeting,
                   greeting+"\n"+topic+"\n"+love+"\n\n"+sign+"より\n　今日のラッキーアイテムは"+item+"だよ!");

　

　こんな感じで複数カノジョモテにも対応

　みんなもGoogle Apps Scriptを活用して、教えてくれよな!

↓このエントリーの続編もあるよ

Google Spreadsheetのスクリプト機能で秒速でWebアプリを作ってみる - UEI shi3zの日記

Permalink | 11:17