どうでもいいプログラム研究所

とある編集者によるIT、Web、ソフトウェア、プログラミングに関する雑記と覚え書き

Google Apps Scriptで今日の日付の行に一瞬で移動する方法

Google Apps Script

f:id:tdyu5021:20190504132834p:plain

Googleスプレッドシートは前回閉じた場所を記憶できない！

スケジュール表をExcelなどのスプレッドシートを使って作るという行為は、おそらくビジネスマンなら大半の人が体験しているはずです。編集・制作の仕事をしている私も日常茶飯事です。前はExcelをよく使っていましたが、最近ではGoogle スプレッドシートを使い始めました。

ただ、すぐにこれには問題があることに気がつきました。行を使って縦方向に日付を記入するスケジュール表の場合、スケジュール期間が短ければよいのですが、「年間予定」のようなかなり期間の長いと問題が発生します。

というのも、Excelと違ってGoogleスプレッドシートはワークシート上の最後に閉じた場所を記憶するわけではないので、ファイルを開いたら必ず先頭の行列（A1）が画面の一番上に来た状態で表示されます。

ですから、確認したいスケジュールの場所がそれよりも何十行も先の行にあるとわざわざ開くたびにそこまでスクロールしなければいけません。

本日の日付が書いてある行を見つけてそこをアクティブにしてみる

そこで私が考えたのが「スプレッドシートを開いたときに今日の日付の行に自動で飛んでくれればよいのでは・・」という解決策です。

（まぁそんなことをせずとも、過ぎた日付の行を非表示にするとかいう運用をすれば完全に解決するのですけど・・）

そんなそもそも論はさておき、A列にある日付の羅列のうち、本日の日付を見つけ、そのセルをアクティブにするというスクリプトを作ってみました。

function myFunction() {
   var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
   var d = new Date();
   var today = Utilities.formatDate(d, "JST","M月dd日"); 
   var foundRow = findRow(sh,today,1);
   var todayCell = sh.getRange(foundRow,1);
   sh.setActiveRange(todayCell); 
}
function findRow(sheet,val,col){
   var dat = sheet.getDataRange().getValues();
   for(var i=1;i < dat.length;i++){
      try{var target = Utilities.formatDate(dat[i][col-1], "JST","M月dd日");
   }catch(e){}
   if(target === val){
      return i+1;
      break;
   }
}
return 0;
}

このコードだけではまだ手動実行になります。上記のコードにあるmyFunctionという関数をonOpenにすれば、スプレッドシートを開くのと同時にこれが実行されるはずです（まだ検証してません）。

上記のコードは細かく説明しませんが、

（１）本日の日付をM月dd日形式にして変数に格納

（２）A列の日付すべてを配列に格納

（３）（２）の配列の中から本日の日付に合致するものを検索する

（４）見つかった行数のセルをアクティブにする

やっているのは上記だけのことです。

ちなみに特定行の中から該当する行を取得する関数（上記コード中のfindRow）については、偉大なるタカハシノリアキ氏の以下の記事内のコードを参考にさせていただきました。

tonari-it.com

今日の日付を配列の中の日付から検索するために、一度日付を”ｍ月dd日”にしていますが、ここはお使いのスケジュールの日付欄の形式に合うように調整が必要です。

 

 

Google Apps Scriptを使ってGoogleスプレッドシートにユーザー定義関数を作る方法

Google Apps Script

エクセルの場合、VBAを使用してユーザー定義関数を作ることができます。同様に、GoogleスプレッドシートもGoogle Apps Scriptを使ってユーザー定義関数が作れます。というわけで、ここではユーザー定義関数をサンプルで作ってみようと思います。

 得点表から平均点を計算する関数を作成してみる

まず以下の表を作ってみました。 

f:id:tdyu5021:20190504033450p:plain

５人の人がいて、B列に国語の点数、C列に数学の点数が載っています。国語と数学を足した得点の6人の平均点以上であれば“合格”、それに満たなければ“不合格”という文字をD列に表示する関数を作ってみたいと思います。表は現在5人ですが、人数がどんどん増えても成り立つようにします。 

Google Apps Scriptを使ったことがある人なら解説不要かと思いますが、念のため順を追って解説します。

ユーザー定義関数の作成方法

まずは、メニューの「ツール」 > 「スクリプトエディタ」をクリックして、スクリプトエディタを開きます。

f:id:tdyu5021:20190504033453p:plain

すると次のような画面が現れます。

f:id:tdyu5021:20190504033500p:plain

デフォルトでは、プロジェクト名は「無題のプロジェクト」で、その中の関数名が「myFunction」とあります。ここは自由に変えてください。 

ここにGoogle Apps Scriptを記述していきます。

f:id:tdyu5021:20190504033507p:plain 記述したらスクリプトエディタのメニューの「ファイル」 > 「保存」をクリックしてできあがりです。

これを最初のスプレッドシートで利用するのは簡単です。エクセルのように、作った関数名をセルの中に=をつけて記入し、引数にB列の数字とC列の数字をいれるだけです。 

f:id:tdyu5021:20190504033514p:plain

というように、ただスクリプトエディタに関数をつくって、それをスプレッドシートのセルの中に記入しただけです。なのでいちいち解説するほどでもないことなのですが、Google Apps Scriptでユーザー定義関数のことに触れている情報があまりなかったので整理してみました。

最後に今回作った関数のコードを載せておきます。

ソースコード

function myFunction(a,b) {
  var bk = SpreadsheetApp.getActiveSpreadsheet();
  objSheet = bk.getActiveSheet();
  var objCell = objSheet.getActiveCell();
  var finalRow = 100;
  
  //リストの最終行を取得
  for(var i = 2; i <=finalRow ; i++){
     var rng = objSheet.getRange(i,1);
       if(rng.getValue() == ""){
          rowEnd = i-1;
　　　break;
　　}
  }

  //国語と数学の合計を求める  
  var kokugoSum = calcSum(2);  
  var mathSum = calcSum(3);
  //国語と数学の合計の平均を求める
  var avg = (kokugoSum + mathSum) /(rowEnd-1);

  //合格か不合格かを判定
  var sm = a + b;
  if(sm >= avg){
  return "合格";
  }
  else{
  return "不合格";
  }
}

//合計を求める
calcSum =function(x){
  for(var i = 2, sum =0; i <=rowEnd ; i++){
  var rng = objSheet.getRange(i,x);
  var valu = rng.getValue();
  sum += valu;
  }
  return sum;
};

今回作ったユーザー定義関数は上記のように記述しました（Google Apps Scriptというか、JavaScript自体あまり知識ないので、これが良いプログラムかはわかりませんが・・・）

JavaScriptで重複しない乱数を生成する

JavaScript

f:id:tdyu5021:20190504020858p:plain

プログラミングでよく必要になるのが、重複しないランダムな数の生成です。出典は忘れてしまったのですが、以前どこかのサイトで非常にスマートな記述方法が載っており感動したので、備忘録としてメモします。JavaScriptでの例です。

 

重複しない乱数生成のJavaScriptのコード

早速ですが、コードは以下です。下記の例では配列numArrに1から5までの数字をランダムに１つずつ格納するコードです。 

function randomizing(){
   var arr = [];
   numArr = []; 
   for(var i=0; i < 5; i++){
      arr[i]=i+1;
   }

   for(var j = 0, var len = arr.length; j < 5; j++, len--) {
      rndNum = Math.floor(Math.random()*len);
      numArr.push(arr[rndNum]);
      arr[rndNum] = arr[len-1];
   }
}

重複しない乱数を生成するロジック

では、上記のコードでなぜ重複しない乱数ができるのかを見ていきましょう。

  1. 生成した乱数を格納するnumArrという配列をつくる
  2. 上記とは別に配列（arr）を作り、ランダムに生成したい値（今回は1,2,3,4,5）を順にいれておく
  3. 乱数を生成（Math.floor(Math.random()*len)）する・・・①とする
  4. 配列numArrの中に、配列arrの[①で生成した乱数]番目の数をpushで追加する
  5. 配列arrの中で、[①で生成した乱数]番目のところに、[arrの配列の要素数-1]番目の数字を埋めていく
  6. 乱数を生成したい数だけforループで繰り返す（上記の例では5回）。その際、arrの配列の要素数の数字を１つずつ減らしていく

最初見たときこのコードが理解できませんでした。なぜ上記のステップ5でarrの[乱数]番目のところに[arr配列の要素数]番目の数字をいれるのでしょうか。順番に見ていきます。

まず、１～６行目のコードが実行されると配列arrには以下の要素が入っていることになります。

  • arr[0] =1
  • arr[1] =2
  • arr[2]= 3
  • arr[3]= 4
  • arr[4] =5

まず乱数の生成で2の数字が出たとしましょう。arr[2]は3ですから、numArrに3の値をpushして次のようになります。 

  • numArr[0] =3
  • numArr[1] = ""
  • numArr[2] = ""
  • numArr[3] = ""
  • numArr[4] = ""

 

先ほど言及した、”ステップ5でarrの[乱数]番目のところに[arrの配列の要素数-1]番目の数字を入れる（arr[rndNum] = arr[len-1]）を実行すると以下になります。

  • arr[0] =1
  • arr[1] =2
  • arr[2]= 5
  • arr[3]= 4
  • arr[4] =5

ここが最大のポイントです。この配列を見るとarr[2]も5だしarr[4]も5なので、一瞬それでいいの？と思ってしまいます。

しかし、この処理が終わった後forループがはじめに戻るわけですが、初期値が5の変数lenは1つ値が減るためMath.floor(Math.random()*len)で生成されるrndNumの値は0～3に限られます。

つまり次に配列arrから値を持ってくるときは、もう二度とarr[4]が選ばれることはないのです（arr[0]、arr[1]、arr[2]、arr[3]しか選ばれない）。

試しにこの状態でもう一度乱数を生成してみましょう。次の乱数に1が生成されたとしたら、arr[1]は2が格納されているので、numArrにプッシュすると以下のようになります。 

  • numArr[0] =3
  • numArr[1] =2
  • numArr[2] =""
  • numArr[3] =""
  • numArr[4] =""

arr[1]にはarrの「arrの要素数-1」番目にあった”4”をいれるので以下のようになります。

  • arr[0] =1
  • arr[1] =4
  • arr[2]= 5
  • arr[3]= 4
  • arr[4] =5

しつこいですが、さらに繰り返してみましょう。forループで、乱数の2が出現したとすると以下のようになります。

  • numArr[0] =3
  • numArr[1] = 2
  • numArr[2] =5
  • numArr[3] = ""
  • numArr[4] = ""
  • arr[0]=1
  • arr[1]=4
  • arr[2]=3
  • arr[3]=4
  • arr[4]=5 

繰り返しにはありますが、このプログラムでは乱数の生成で以下の青の数字が生成されると、そのループ時点での[arr配列arrの要素数-1]番目の値が右辺に代入されます。 

  • arr[0]=XXX
  • arr[1]=XXX
  • arr[2]=XXX
  • arr[3]=XXX
  • arr[4]=XXX

Math.random()*lenのうち変数lenが１つずつどんどん減っていくため、発生する乱数の範囲が縮まり、得られる数字が限られてしまうのですが、その限られたarr[X]には、[すでに発生しない乱数]番のarrの値をいれておくことで、必要なものが得られるという仕組みなっているのです。

こうすることでたとえ乱数がどんな順番でどんな数がでてきても必ず重複しない乱数が無駄なループなしに得られるのです。試しに、乱数の０が５回連続で出たとしても、1～5までの数が得られます。 

私が最初にこの仕組みを知ったときとても感動しました。一体どうやればこんなコードを思いつくのだろうか想像も付きませんが、真のプログラマーやアルゴリズムを考えられる方は本当に尊敬します。

Excel VBAでセルや文字の色のRGB値を調べる

Excel VBA

Excel VBAを使っていると、ColorIndexプロパティやRGB関数を使ってセルや文字の色を指定するシーンは多いと思います。でも、逆にセルや文字から色を調べたいときはどうすればよいでしょうか。

ColorIndexプロパティを使えば色番号は簡単にわかりますが、一方でRGB値をサクッと調べられるプロパティはありません。 そこで、RGB値を調べる方法を以下に記します。

塗りつぶしメニューの色を調べてみる

以下のキャプチャのように、A1セルに「フォント」→「塗りつぶし」のメニュー内で左から5番目にある青色を塗ってみました。試しにこの色のRGB値を調べてみたいと思います。

f:id:tdyu5021:20190504001214j:plain

RGB値をメッセージボックスで表示してみる

早速ですが、以下がこの色のRGBそれぞれの値をMsgboxで表示するコードです（私が考えたものでなく拾い物です）。
（※追記：よりシンプルな方法があるとコメントで指摘されたのでその方法を最後に記します。

Sub Test()

Dim R As Integer
Dim G As Integer
Dim B As Integer
Dim MyColor As Long

MyColor = Range("a1").Interior.Color

R = Val("&H" & Left(Hex(MyColor And 255), 2))　'・・・・・・・①'
G = Val("&H" & Left(Hex(MyColor And (256 ^ 2 - 256 ^ 1)), 2))'・・②'
B = Val("&H" & Left(Hex(MyColor And (256 ^ 3 - 256 ^ 2)), 2))'・・③'
MsgBox "R:" & R & vbCrLf & "G:" & G & vbCrLf & "B:" & B
End Sub

上記を実行すると、

R:68
G:114
B:196 

とメッセージが表示されます。
つまり、このA1のセルの色は、Rが68、Gが114、Bが196ということがわかりました。

結局、RGB値を調べているのは、上記のコードの①～③の部分だけです。
このコードは拾い物なので、私は①～③の中にある数式(?)でなぜRGBのそれぞれの値が導き出せるのかまでは理解していません。

理屈はわからないけど、とりあえずこのコードをコピペすれば使えるので、知らなくても実用には問題はないでしょう。

上記のコードの中の、Hexは10進数を16進数に変換する関数、Valは、引数に文字列をとり、その文字列の中から数値として抜き出せる部分を返すという関数です。いずれも自分はあまり使わない関数で、このコードを見たときに初めて知りました。
この関数ももし機会があれば調べてみたいと思います。

追記：
コメントで指摘がありましたが上記のコードのうち、R、G、Bに値を入れるコードは以下で大丈夫だそうです。

R = MyColor Mod 256
G = Int(MyColor / 256) Mod 256
B = Int(MyColor / 256 / 256)

めちゃくちゃシンプルですね！これでよかったのか・・

Excel VBAで重複しないランダムな数字を生成するマクロ

Excel VBA

f:id:tdyu5021:20190505211955p:plain

趣味でエクセルVBAを使ってあるツールを作ってたとき、重複しないランダムな数（乱数）を生成する必要があり、他のWebサイトを見ながら調べました。それと同じものですが、備忘録がわりに以下にコードを載せます。

早速、コードは以下です。

Sub Randomizing()
   Dim MaxNum As Long
   Dim flg() As Boolean
   Dim num As Long
   Dim i As Long
   Dim Number() As Long

   MaxNum = 10　'←生成したい最大値をここに記入

   ReDim Number(1 To MaxNum) As Long
   ReDim flg(1 To MaxNum) As Boolean

   Randomize

   For i = 1 To MaxNum

      Do
         num = Int(Rnd * MaxNum) + 1
         If flg(num) = False Then
            flg(num) = True
            Number(i) = num
            Exit Do
         End If
      Loop

   Next i
　'//ここからは、生成した乱数(Number())をセルに書きだす処理'
   For j = 1 To MaxNum
      Cells(j, 1).Value = Number(j)
   Next

End Sub

このコードは数字の１から指定した任意の数まで、1回ずつランダムに数字が生成されます。生成したい数の最大値は、変数MaxNumで指定します（たとえば、１から１０までの乱数を生成したいなら“MaxNum = 10”とする）

このコードを丸ごとコピペして、標準モジュールに張り付けて実行すればセルA1からA10までランダムに1から10までの数字が1回ずつ入力されます。

5つ乱数がほしい場合は、"For i = 1 To MaxNum"の部分の”1 To MaxNum”を”1 to 5”とすればOKです。

 簡単な解説

おそらくほかの方の記事で解説があるかと思いますし、解説するほどでもないのですが、念のため触れておきます。

配列の要素数を再定義するReDim

最初に以下の記述があると思います。

ReDim Number(1 To MaxNum) As Long
ReDim flg(1 To MaxNum) As Boolean

Number()という配列は、生成したランダムな数字を格納していく配列です。ReDimは最初の決めた配列の要素数を再定義するための宣言です。もちろんこの要素数を最初のDimの段階で定義してももちろん動作しますが、プログラムの途中で要素数を変更する必要もあるかもしれませんし、この記述をしたほうが他のプログラムなどへの移植性や柔軟性を考えた際に便利です。

乱数の生成

ランダムな数の生成を行っているのは“num = Int(Rnd * MaxNum) + 1”の部分です。MaxNumを10とすると、1から10までのランダムな数値が変数numに格納されます。

単にランダムな数字がほしいのであればこの１行でOKです。ただ、これだと重複が発生するかもしれません。そこで必要になるのが次の記述です。

重複を避けるための処理

If flg(num) = False Then
   flg(num) = True
   Number(i) = num
   Exit Do
End If

前の項でランダムな数字numが出現したら、配列変数のflg()の[num]番をフラグオンにしておき、「その[num]番はすでに使いましたよ」という目印を立てておくのです。

ランダムな数字はNumber()という配列変数にどんどん格納してきます。ただし、上記の5行のコードをみてわかるように、それは flg()がフラグオフ（＝まだ一度もその数字が出現していない）でなければ実行されません。Do Loopをかけているので、無事にNumberに格納されない限り繰り返されます。

以上が簡単な解説です。ランダムな数字の生成は、業務で使用するツールにはまず出てくることはないでしょうけど、私のようにExcelでの簡単なゲームとかくだらないツール作るときには頻出なので備忘録としてここに記しました。