[GAS] onEditで行の背景色変更

---

開発現場で作業をしていると
よく課題管理表というものを作成します。
Excelが使われることが多いですが、
完了した課題は行の背景色を変えて
完了したことをわかりやすくすることが多いです。

Excelを使っていれば「条件付き書式」を使って
「解決日に値が入ったら行の背景色をグレーにする」
などといった設定を簡単に行う事が出来ます。

しかし、残念ながらスプレッドシートにはその機能はありません。

代わりにGoogle Apps Scriptを書く必要があります。

↓の様なスプレッドシートがあったとします。
解決日に何か入力するとその行の背景色をグレーに設定する処理を作ります。

function onEdit(param) {
  var range = param.range;
  if (range.getColumn() != 6) {
    // 解決日の列以外の場合は処理を抜ける。
    return;
  }
  var sheet = SpreadsheetApp.getActiveSheet();
  if (range.getValue() == "") {
    // 解決日が空白になった場合は背景色を白にする。
    sheet.getRange(range.getRow(), 1, 1, 6).setBackground("#FFFFFF");
  } else {
    // 解決日が空白以外になった場合は背景色をつける。
    sheet.getRange(range.getRow(), 1, 1, 6).setBackground("#C0C0C0");
  }
}

試しに№3の解決日に日付を入れてみると

こんな感じで背景色が変わります。

ただし、スクリプトが走るため背景色が変わるまで若干のタイムラグがあります。

「ん？あれ？スクリプトがおかしくなったか？」

と思うくらい遅い場合もありますのでご注意を。

---

にほんブログ村

プログラマー ブログランキングへ

[GAS] onEditでの注意点

---

今日もGoogle Apps Scriptのお話です。
onEditファンクションはスプレッドシートのセルが更新されるたびに呼ばれます。
とても便利ですが、注意すべき点があります。
例えば以下の様なスプレッドシートがあるとします。

B1列にはファイルIDが書かれています。
そのファイルIDの示すドキュメントはこちら。

このドキュメントのファイル名を取得してB2セルに書き込む処理を考えます。
普通に書くとこんな感じです。

function func01() {
  var range = SpreadsheetApp.getActiveSheet().getRange(1, 2);
  // セルに記載された文字をIDとしてドライブからファイルを読み込む。
  var file = DriveApp.getFileById(range.getValue());
  // ファイル名をB2セルへ書き込む。
  SpreadsheetApp.getActiveSheet().getRange(2, 2).setValue(file.getName());
}

結果は

こんな感じ。想定通りです。

続いて本題のonEditを使った処理です。
B1セルにファイルIDを記述した、というイベントを拾ってファイル名を取得、書き込みを実行します。

function onEdit(param) {
  // 編集されたセルの参照を取得する。
  var range = param.range;
  if (range.getRow() != 1 && range.getColumn() != 2) {
    // B1のセル以外が編集された場合は処理を抜ける。
    return;
  }
  // セルに記載された文字をIDとしてドライブからファイルを読み込む。
  var file = DriveApp.getFileById(range.getValue());
  // ファイル名をB2セルへ書き込む。
  SpreadsheetApp.getActiveSheet().getRange(2, 2).setValue(file.getName());
}

結果は

書き変わりません・・・
ロジック的には間違ってないはずですが・・・
実行ログを見てみると・・・

権限が無い、というエラーが発生しています。
これは私の憶測ですが、onEditでバンバン更新されて
バンバンGoogleドライブにアクセスされたらたまらん！
というGoogleの中の人の防御策ではないかと思っています。
私がGoogleの中の人だったら

「読めなくすりゃいんじゃね？onEditでドライブなんか読みに来んなよ。迷惑なんだよ。」

と思うと思いますので仕方ないですかね。

---

にほんブログ村

プログラマー ブログランキングへ

[GAS] スプレッドシートの値参照、値設定のパフォーマンス

---

Google Apps Scriptを使ってGoogleスプレッドシートの内容を編集する、という場面がよくあります。
しかし、ここでExcelVBAの様に素直に書くと、ちょっと痛い目を見る事があります。

例えば↓のようにスプレッドシートのA列の値とB列の値を加算してC列に結果を入れる処理を考えます。
※100行あるとします。 あ、関数書きゃいいだろ、ってのはここでは無しね（・∀・）

普通に書くとこんな感じになると思います。

function func01() {
  var sheet = SpreadsheetApp.getActiveSheet();
  for (var row = 1; row <= 100; row++) {
    // C列に設定する値を計算（A列の値+B列の値）
    var answer = sheet.getRange(row, 1).getValue() 
               + sheet.getRange(row, 2).getValue();
    // 計算結果をC列に設定
    sheet.getRange(row, 3).setValue(answer);
  }
}

これで実行すると当然結果は正しいものとなりますが・・・

たった100行処理するのに18秒以上・・・(´･ω･`)Excelﾉﾎｳｶﾞｲｲﾖﾈ･･･

原因は
・sheet.getRange(row, 1).getValue()
・sheet.getRange(row, 2).getValue()
・sheet.getRange(row, 3).setValue(answer)
をそれぞれ100回呼び出してしまっていることです。
これらは全て実行されるたびにスプレッドシートとのやり取りが発生するため、
パフォーマンスに影響が出てしまいます。

問題点を改善したソースがこちら。

function func02() {
  var sheet = SpreadsheetApp.getActiveSheet();
  // 100行2列の値を一気に取得
  var values = sheet.getRange(1, 1, 100, 2).getValues();
  // 行配列を定義
  var rows = [];
  for (var row = 0; row < 100; row++) {
    // 列配列を定義
    var cols = [];
    // C列に設定する値を計算（A列の値+B列の値）
    var answer = values[row][0] + values[row][1];
    // 列配列に計算結果を設定
    cols.push(answer);
    // 行配列に列配列を設定
    rows.push(cols);
  }
  // 計算結果をC列に設定
  sheet.getRange(1, 3, 100, 1).setValues(rows);
}

そして実行結果のログがこちら。

圧倒的じゃないか、我が軍は・・・

同じ行を処理するのにたった0.3秒ちょい。
これはスプレッドシートの参照を全値取得、全値設定の2回のみにしているからです。

スプレッドシートのGoogle Apps Scriptでパフォーマンスに悩んでいる方は
まずスプレッドシートの参照回数を見直せないかどうかを検討してみてはいかがでしょうか。

---

にほんブログ村

プログラマー ブログランキングへ