Google Apps Scriptでシートを開いたとき、横方向の検索をする

2015年12月8日

この記事は最終更新日から9年経過しています。内容が古い可能性があります。

以前書いた記事「Google Apps Scriptでスプレッドシートを開いたとき今月のセルに移動」にコメントで質問をいただきました。この記事では縦方向に検索して今月を見つけに行くのですが、横方向に検索したいということでした。
ちょっと確かめればすぐわかるかなと思ったのですがいくつか落とし穴に引っかかったのでそれについて書きたいと思います。

記事をざっと見る

コメント欄からのコピーだと文字が変わっていた

いただいたコメントをコピーして貼り付けて実行しようと思ったのですが、ちょっと様子がおかしいことに気づきました。ダブルコーテーションなどが全角とかちがう文字に置き換わっています。

例えば

var currentYear = Utilities.formatDate(currenDate, "GMT+9", "yyyy");

という行はコメント欄で書き込むと

var currentYear = Utilities.formatDate(currenDate, “GMT+9”, “yyyy”);

こんな感じに、よく見るとダブルコーテーションが全角になっています。

最初このことに気づくまで、と気づいてからもいろいろな文字に変換されていたので直しきるまでちょっと時間がかかりました。

コメント欄からソースをコピーしたときは記号が変わっていることがあるようなので、実行する前に置き換えましょう！

列の名前A,B,Cだとプログラムが組みにくいため、数字でセルを特定する

「Google Apps Scriptでスプレッドシートを開いたとき今月のセルに移動」では、検索するところは

  // 最終行から1行ずつ上の行を参照
  for (var i = sheet.getLastRow(); i > 1; i--) {
    sheetValue = sheet.getRange("A" + i).getValue();
    sheetYear = Utilities.formatDate(sheetValue, "GMT+9", "yyyy");
    sheetMonth = Utilities.formatDate(sheetValue, "GMT+9", "MM");
    // 今月と一致する年月があったらアクティブにする
    if ((sheetYear == currentYear) && (sheetMonth == currentMonth)) {
      sheet.setActiveSelection("A" + i);
      break;
    }

  }

最終行から一つずつ上の行を見に行き、年と月が今現在の年と月と同じセルを探しました。

    sheetValue = sheet.getRange("A" + i).getValue();

この　“A”　というのが列の名前で　i　というのは行数を数字で指定します。この場合は行の上への移動は　i　という数字をマイナスしていくことで行えますが、横方向を探す場合は最終列が　Z　だとしたら Y、X、W…　と文字をさかのぼっていかなければいけません。Zより右に列がある場合は　AA　となったりして、16進数でもないのでこの列を1つずつマイナスしていくというのは扱いにくいと思いました。

Excelでもそうなのですが、セルの列を文字ではなくて行と同じように数字でも扱えるようになっています。

セル(Range)オブジェクトを取得する

こちらにその方法が載っていました

objRange = objSheet.getRange(“B1”) //セルB1を取得
objRange = objSheet.getRange(2, 1) //セルA2を取得

こちらにあるように列名を文字でも指定できるし、数字でも指定できるようです。

これを見て、プログラムを組み直してみたのですがどうもうまく動きません。よく見直してみると(ちゃんとみれば分かったんですが)、文字で列を表すときは　B1　のように列名が最初に来るのですが数字であらわすときは　2,1　でA2を表しているということは　列名が2番目に来る、つまり文字の時とは逆になる　ということに気づかずに逆でコーディングしてしまってました。これが2つめのはまりポイントでした(^_^;。

Rangeを文字で指定するときと、数字で指定するときは列の位置が逆なので注意！

一致したセルをアクティブにする方法が違っていました。

ということでほぼほぼ、コードができあがったのですが、最後の最後一致したセルを見つけてそのセルをアクティブにするというところでどうしても見つかったセルがアクティブになりませんでした。

アクティブにする行はもともと

 sheet.setActiveSelection("A" + i);

こういうコードで　setActiveSelection　で指定していました。Rangeは文字ではなく数字で指定しているので

 sheet.setActiveSelection(1 , i);

のように指定してみたのですがここがどうもうまくいってないようでした。

ということはもしかすると　setActiveSelection　は文字での指定しかできない？のではないかな、、、と思いました。ググってもそのような情報は見つからなかったのですが、ググって見つけたコードのサンプルはみんな文字でのrange指定の例だったので、もしかすると～と思いました。

ということでそれ以外にセルをアクティブにする方法はないかなとググって、activate()　というクラスを見つけました。

var range = sheet.getRange(1,i);
range.activate();

このように一旦、数字型でrangeというオブジェクトを作っておいてactivate()でこのセルをアクティブにします。

Rangeを数字で指定するときにセルをアクティブにするにはactivate()を使う！

できあがったコード

ということで試行錯誤してできあがったコードはこちらです。

function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var sheetValue;
var sheetYear,sheetMonth,sheetDay;
var currenDate = new Date();
var currentYear = Utilities.formatDate(currenDate, "GMT+9", "yyyy");
var currentMonth = Utilities.formatDate(currenDate, "GMT+9", "MM");
var currentDay = Utilities.formatDate(currenDate, "GMT+9", "dd");

// 最終列から1列ずつ横の列を参照
var last = sheet.getLastColumn();
for (var i = last; i > 1; i--) {
  sheetValue = sheet.getRange(1,i).getValue();
  sheetYear = Utilities.formatDate(sheetValue, "GMT+9", "yyyy");
  sheetMonth = Utilities.formatDate(sheetValue, "GMT+9", "MM");
  sheetDay = Utilities.formatDate(sheetValue, "GMT+9","dd");
      if ((sheetYear == currentYear) && (sheetMonth == currentMonth) && (sheetDay == currentDay)) {
        var range = sheet.getRange(1,i);
        range.activate();     
        break;
      }
  }
}

コメントいただいた方がやりたかったこと「1行目に年月日が横に入っていて、その中から本日の年月日に一致するセルに位置づける」ことだという理解でこのコードを書きました。ですので年月だけで良ければ　==　で年月日を判定しているところから日付を削除するなどして使ってみてください(^^)/