以前「Google Apps ScriptでGoogleスプレッドシートを開いたときに利用するセルに移動」という記事で初めてGoogle Apps Scriptを使ってみました。そのときは単純にシートの一番下に移動するだけだったのですが、今回はセルの日付を見て、今月の行に移動するというスクリプトを書いてみました。
新しくスプレッドシートを作ってデータをコピーする
Googleドライブで
新規 をクリックして Googleスプレッドシート をクリック
新しいスプレッドシートが開くので適当に名前を付けます。
ここに前回使ったシートの未来分もある方のシートをコピーします。
ここの
シート2にデータがあるので、
全てを選択して、
コピーして、
新しく作ったスプレッドシートに貼り付けます。
貼り付けたデータの一番下を見てみると2015/12までデータが入っています。
このスプレッドシートを開いたときに今月の行に移動しているようにしてみたいと思います。
セルの値を表示するスクリプトを書いてみる
ツール スクリプトエディタをクリック。
このようにスクリプトエディタが開きます。
無題のプロジェクト のあたりをクリックして適当な名前を付けます。
最初に書いてあるコードは消してしまって、
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
Browser.msgBox(sheet.getRange("A2").getValue());
}
こんな感じでコードを書いてみます。このコードは onOpen() でシートが読み込まれたときに動く関数を指定しています。現在のシートの A2 の値を表示するコードです。
保存 をクリックして、
シートに戻り、再読込をします。
このように
A2の値(の英語版)がポップアップで表示されました。これでセルを参照して、日付が取れることが分かりました。
今月のセルに移動するスクリプトを書いてみる
今月のセルに移動するスクリプトを書いたときに色々紆余曲折があったのですが(^_^;、細かく書くととっても行数がかかってしまうのでまずはできあがったスクリプトを書いてみます。
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var sheetValue;
var sheetYear,sheetMonth;
var currenDate = new Date();
var currentYear = Utilities.formatDate(currenDate, "GMT+9", "yyyy");
var currentMonth = Utilities.formatDate(currenDate, "GMT+9", "MM");
// 開始した時間をD1に表示
sheet.getRange('D1').setValue( Utilities.formatDate(currenDate, "GMT+9", "HH:mm:ss")).setBackgroundColor('#eee');
// 最終行から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;
}
}
// 終了した時間をE1に表示
currenDate = new Date();
sheet.getRange('E1').setValue( Utilities.formatDate(currenDate, "GMT+9", "HH:mm:ss")).setBackgroundColor('#eee');
Browser.msgBox('終了');
}
こんな感じです。少し長くなりました。この状態でスプレッドシートを再読込すると、
このように終了時にメッセージがポップアップされて、
今月の行に移動しています。
一番最初の行を見ると開始時間と終了時間がわかります。大体1～2秒で処理は終了したようです。
Google Apps Scriptではまったところ
いくつか紆余曲折があったので、それらについてちょっと書きます。
10/1のデータが9月と判定された
日にちの判定でなぜか2015/10/1のデータが2015年9月と判断されてしまいました。そのときのコードは、
sheetYear = Utilities.formatDate(sheetValue, "GMT", "yyyy"); sheetMonth = Utilities.formatDate(sheetValue, "GMT", "MM");
でした。上手く行かなかったときに、うーんなぜ9月？あれ？日本のGMTって+9だよね？ と
sheetYear = Utilities.formatDate(sheetValue, "GMT+9", "yyyy"); sheetMonth = Utilities.formatDate(sheetValue, "GMT+9", "MM");
にしたところ正しく判断できました。日本の場合はここは GMT+9 としないと正しく判定できないようです。
頭からループしたら止まってしまった
このデータは3514行あるのですが、最初は
for (var i = 2; i <= sheet.getLastRow(); i++) {
こんな感じに頭からループしていました。しかし、実行しても全然結果が返ってこない、、時間がかかるのかなと10分ぐらい待ってみたのですが返ってきませんでした。たぶん実行時間が長すぎて処理を打ち切られたのではないかな？と思いました。
試しにデータ量を少なくして同じコードで実行したらちゃんと終わったのでやはりデータ量の問題のようでした。
このことでループは最後の行から逆に行うように変更しました。
if文で AND は使えなかった
if文で、
if ((sheetYear == currentYear) AND (sheetMonth == currentMonth)) {
とコーディングして保存したら
このようにエラーが出てしまいました。AND は && 、 OR は || と書かないと通らないようです。javascriptと同じということですね。
おまけ:処理時間がかかるので、それを前提にコードを書く
Google Apps ScriptはVBAと較べると処理時間がかかる気がします。ということでVBAなら処理時間をあまり考えずにコーディングしてしまうところも時間を短縮できるようにコーディングする必要があると思いました。
また、今回のコードは「今月に該当する行がある」ことを前提として作っています。どのような場合でも問題なく動くためには判定のあたりで 今月と年月が一致 とともに 今月より年月が小さい という条件を付ける必要があると思います。今回はif文やループを使ってみたかったのでラフにコーディングしてありますので、その辺については実際に作る場合お気を付けください。
[amazon_searchlink search=”Google Apps Script”]
コメント
コメント一覧 （3件）
返信を追加
こんにちは。
初心者で変な質問かもしれないですが、よろしくお願いいたします。
株式会社RYUSさまのblog記事を参考に以下のようなSpreadSheet及びスクリプトを書いていました。
スクリプトでは、参照サイトと同様、スプレッドシートを開いた際に、今日の日付に移動するという機能を実装したいと考えています。ただ、参照サイトが行に日付を入力していたのに対し、横列に移動したいと考えています。
ところが最終列から1列ずつ横の列を参照することがわかりません。
よろしければ教えて頂きたいです。
よろしくお願いします。
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列ずつ横の列を参照
for (var i = sheet.getLastColumn(); i > 1; i–) {
//エラー↓↓↓↓↓↓↓↓↓↓
sheetValue = sheet.getRange(“3Column” + i).getValue();
sheetYear = Utilities.formatDate(sheetValue, “GMT+9”, “yyyy”);
sheetMonth = Utilities.formatDate(sheetValue, “GMT+9”, “MM”);
sheetDay = Utilities.formatDate(currentDate, “GMT+9″,”dd”);
}
}
ハムカツさん、こんにちは。
ハムカツさんのコードをコピーして試しました。コメントを表示する時にダブルコーテーションが2バイト文字になるなどするため(たぶんWordPressの機能)、その辺を直したりしました。sheetValueを取得するところで ”3Column” としてあるところもよく分からなかったのでとりあえず1行目の見出しを最終列から見ていくのかなと思ってコードを書いていろいろためしました。
最終的にうまく動いたコードは
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();
//var test = sheet.getRange(1, 14).getValue();
// sheet.setActiveSelection("N1");
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();
// sheet.setActiveSelection(1,i);
break;
}
}
}
こちらです。列は A～ のように文字で表されていますが、文字で表すと限界があるため数字でrangeを取るようにしました。
あと、数jだと sheet.setActiveSelection がエラーになるため、別の方法でセルに位置づけるようにしました。
いろいろ学ぶところもあったのでblogにも書こうかな～と思ってます(^^)/。おかげで知ることができるいろいろなことがありました。コメントありがとうございました。
兎本さま
こんにちわ。
「メソッド formatDate(string,string,string) が見つかりません。（行 16、ファイル「コード」）」
というメッセージが出てしまいます。
すみません。
なにが問題でしょうか？