Google Apps Script で 「スクリプトによるメソッド Range.getValue の使用頻度が高すぎます」って叱られたの巻

仕事はエンジニアでもデザイナーでもありません。Google Apps Script がちょっと楽しくなってきました。そういうわけで***プログラミング初心者が、Google Apps Scriptで「◯月◯日週」という出力をするためにがんばったこと***の続編的なやつです。

タイトルに特定のキーワードが含まれている場合にセルの色を変える的な処理をつくろうと思った

まー、条件付き書式でもできるんだけどね。
せっかくちょっとわかってきたので Google Apps Script でやってやろうかという気分になった。
そして、最初につくったのがこちら。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet =ss.getActiveSheet();

function search1() {
  var cols = 1;
  var startrows = 2;
  for (var i = startrows; i < sheet.getMaxRows(); i++) {
    var titles = sheet.getRange(i, cols).getValue();
    result1 = titles.indexOf('キーワード1');
    result2 = titles.indexOf('キーワード2');
    result3 = titles.indexOf('キーワード3');
    result4 = titles.indexOf('キーワード4');
    result5 = titles.indexOf('キーワード5');
    result6 = titles.indexOf('キーワード6');
    if(result1 >= 0 || result2 >= 0 || result3 >= 0 || result4 >= 0 || result5 >= 0){
      sheet.getRange(i, cols).setBackground('yellow');
    } else if (result6 >= 0) {
      sheet.getRange(i, cols).setBackground('red');
    }
  }
}

上記の処理

• 1列目、2行目から処理を開始
• スプレッドシートの最終行まで処理を行う
• それぞれの行で、タイトルを取得し、各タイトルにキーワードが含まれているか、indexOfかける（含まれていると0以上の値を返し、含まれていないと-1を返し、各resultにセットされる）
• キーワード1か、キーワード2か、キーワード3か、キーワード4か、キーワード5が含まれていたら、セルを黄色く塗る
• キーワード6が含まれていたら、セルを赤く塗る

ちゃんと動いた。
おれ、成長したかも。

しかし、叱られた

なんか言いたそうなアイコンを発見！
↓

押してみた。
↓

そう言われても…。いったいどういうことよ！？
↓

なるほど、「getValue()」ではなく、「getValues()」を使えってことっすね。

複数のセルを選択するのはどうやるのじゃ？

「こういうときは公式ドキュメントを調べるのが正しいはず」とこちらのページをざっと見てみた。

「2,1」的な単独指定か、「A1:B3」的な指定しか見つからないな！

結論、上記は勘違いというか「ドキュメントちゃんと読めよ」というかんじなのだが、ここでまず1回つまずく…。

• なんか方法があるはず！と思い
• とりあえず「(2,1,3,4)」とか入れてみて、Browser.msgBox()してみたところ、複数セルの値をgetできていることを確認する
• 数値を変えてみながらどんな数字を入れるとどのセルがgetできるか確認する

ということで、以下のようなかんじで無事、タイトルをいっぺんに取得することに成功した。

  var startrows = 2;
  var cols = 1;
  var maxrows = sheet.getMaxRows();
  var titles = sheet.getRange(startrows,cols,maxrows,1).getValues();

indexOf が効かない！

取得できたらあとは判定にかければよい。判定のところはすでにつくってある。よっしゃ！ということで、以下を動かしてみた。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet =ss.getActiveSheet();

function search2() {
  var cols = 1;
  var startrows = 2;
  var maxrows = sheet.getMaxRows();
  var titles = sheet.getRange(startrows,cols,maxrows,1).getValues();
  for (var i = 0; i < titles.length; i++) {
    checktitle = titles[i];
    result1 = checktitle.indexOf('キーワード1'); 
    result2 = checktitle.indexOf('キーワード2'); 
    result3 = checktitle.indexOf('キーワード3'); 
    result4 = checktitle.indexOf('キーワード4'); 
    result5 = checktitle.indexOf('キーワード5'); 
    result6 = checktitle.indexOf('キーワード6'); 
    if (result1 >= 0 || result2 >= 0 || result3 >= 0 || result4 >= 0 || result5 >= 0) {
      sheet.getRange(i+startrows, cols).setBackground('yellow');
     } else if (result6 >= 0) {
      sheet.getRange(i+startrows, cols).setBackground('red');
    }
  }
}

何も起こらない...

データ型が object ではダメなのだ

すぐに「indexOfが効いていないのだろう」ということまではわかった。
ただ、Browser.msgBox()にかけても見た目は同じ。あと考えられるのはデータ型の違いくらいかなと思った。

というわけで以下を実行してみた。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet =ss.getActiveSheet();

function searchtest() {
  var titles = sheet.getRange(2, 1).getValues();
  var title = sheet.getRange(2, 1).getValue();
  var result1 = typeof(titles[0]);
  var result2 = typeof(title);
  Browser.msgBox('getValues：' + result1);
  Browser.msgBox('getValue：'  + result2);
}

以下の結果がかえってきた。

getValues：object

getValue：string

なるほど。
function search1 では、getValue だったので string だったけど、function search2 では、getValues だったので object だったのね。object に対しては、indexOf は効かず一律「-1」を返してしまうのね。

string に変換してあげたらめでたく動いた

というわけで、最終的には以下で着地した。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet =ss.getActiveSheet();

function search2() {
  var startrows = 2;
  var cols = 1;
  var maxrows = sheet.getMaxRows();
  var titles = sheet.getRange(startrows,cols,maxrows,1).getValues();
  for (var i = 0; i < titles.length; i++) {
    checktitle = new String(titles[i]);
    result1 = checktitle.indexOf('キーワード1'); 
    result2 = checktitle.indexOf('キーワード2'); 
    result3 = checktitle.indexOf('キーワード3'); 
    result4 = checktitle.indexOf('キーワード4'); 
    result5 = checktitle.indexOf('キーワード5'); 
    result6 = checktitle.indexOf('キーワード6'); 
    if (result1 >= 0 || result2 >= 0 || result3 >= 0 || result4 >= 0 || result5 >= 0) {
      sheet.getRange(i+startrows, cols).setBackground('yellow');
     } else if (result6 >= 0) {
      sheet.getRange(i+startrows, cols).setBackground('red');
    }
  }
}

ちなみに、function search1 に比べると、function search2 のほうが、処理速度も圧倒的に速くなった。

初心者はこんなところでつまずくものなのだよ

このつまずきがどれくらい「あるある」なのかはよくわからないけど、個人的な備忘録として残してみました。

12/2追記）間違いを見つけた！

こっちのエントリーと同様に、getMaxRows()ではなくgetLastRow()が正しいようなので修正します。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet =ss.getActiveSheet();

function search2() {
  var startrows = 2;
  var cols = 1;
  var maxrows = sheet.getLastRow();
  var titles = sheet.getRange(startrows,cols,maxrows,1).getValues();
  for (var i = 0; i <= titles.length-startrows; i++) {
    checktitle = new String(titles[i]);
    result1 = checktitle.indexOf('キーワード1'); 
    result2 = checktitle.indexOf('キーワード2'); 
    result3 = checktitle.indexOf('キーワード3'); 
    result4 = checktitle.indexOf('キーワード4'); 
    result5 = checktitle.indexOf('キーワード5'); 
    result6 = checktitle.indexOf('キーワード6'); 
    if (result1 >= 0 || result2 >= 0 || result3 >= 0 || result4 >= 0 || result5 >= 0) {
      sheet.getRange(i+startrows, cols).setBackground('yellow');
     } else if (result6 >= 0) {
      sheet.getRange(i+startrows, cols).setBackground('red');
    }
  }
}

ちなみにforの終了条件も以下のとおり少し変えてあります。

前バージョン　　→　i <= titles.length-startrows
修正バージョン　→　i < titles.length

最終行の行番号をとってくるので、ここは開始行文の値をマイナスしておかないといけなかったのでした。