Zenn
👋

Googleスプレッドシートでセルに進捗表示

2023/07/02に公開
GAS
tech

概要

Googleスプレッドシートでセル内にApps Script処理の進捗表示を行う。
「処理済件数/総件数」の文字列表示、「SPARKLINE関数」によるグラフ表示を行う。

簡単な実装方法

ループ処理前に処理済み0件で表示し、ループ処理毎に処理済件数を表示する。

const textCell = SpreadsheetApp.getActiveSheet().getRange("テキスト表示");
const graphCell = SpreadsheetApp.getActiveSheet().getRange("バー表示");
// 中略
textCell.setValue(`0/${list.size}`);
graphCell.setValue(`=SPARKLINE(0, {"charttype","bar";"max",${list.size}})`);
for (let i = 0; i < list.size; i++) {
  textCell.setValue(`${i + 1}/${list.size}`);
  graphCell.setValue(`=SPARKLINE(${i + 1}, {"charttype","bar";"max",${list.size}})`);
}

画面が更新されない

これで問題なさそうに見えるが、実は画面更新がされない。
「SpreadsheetApp.flush()」を実行することで画面更新をさせることができる。
が、画面更新が負荷になって、処理が重くなり過ぎる。
10%進む毎に画面更新をする、というような対策を入れることで使い物になるレベルにできるが、そんな処理を入れてしまうと本来の処理を汚し過ぎてしまう。

クラスにしてしまう

進捗表示を行う処理をクラスにする。
表示に使うセル、処理総件数、処理済件数をクラスに渡すことで、表示処理をすべて任せてしまう。

クラスの使い方

進捗表示クラス（Progress）をインスタンス化、セルを設定、処理総件数(list.size）を渡し、ループ内で処理済件数（i + 1）を設定する。
画面更新処理などは進捗表示クラスで行うため、呼び出し元は初期化と値を設定し続けるのみで良い。
※処理済件数なので（i + 1）となるのがミソ

const textCell = SpreadsheetApp.getActiveSheet().getRange("テキスト表示");
const graphCell = SpreadsheetApp.getActiveSheet().getRange("バー表示");

const progress = new Progress();
progress.setProgressTextCell(textCell);
progress.setProgressGraphCell(graphCell);
// 中略
progress.setEndCnt(list.size);
for (let i = 0; i < list.size; i++) {
  // 中略
  progress.setCnt(i + 1);
}

クラスの実装

画面更新処理を実装する。

「SpreadsheetApp.flush()」による更新を進捗10%毎に実施するための基準値を、処理総件数（endCount）が設定されたときに「_calcFlashCount」で算出する。

処理済件数をこの値で除算し、余りが0の時に画面更新（preadsheetApp.flush()）を実施する。
更新基準が端数となった場合の更新漏れを防ぐ為に、処理済件数が0と総件数（endCount）の場合も画面更新を実行する。

内部的にはsetCountの度に「_flashCell()」で画面表示値を更新している。
この関数内で、更新基準値と比較して画面更新の実施を行うかを判定している。

class Progress {
  constructor() {
    this._textCell = null;
    this._graphCell = null;
    this._endCount = 0;
    this._nowCount = 0;
    this._flashCount = 1;
  }

  setTextCell(cellRange) {
    this._textCell = cellRange;
    if (cellRange) { cellRange.clearContent(); }
  }

  setGraphCell(cellRange) {
    this._graphCell = cellRange;
    if (cellRange) { cellRange.clearContent(); }
  }

  setEndCount(endCount) {
    this._endCount = endCount;
    this._calcFlashCount();
    this._flashCell();
  }

  setNowCount(nowCount) {
    this._nowCount = nowCount;
    this._flashCell();
  }

  _calcFlashCount() {
    this._flashCount = Math.round(this._endCount / 10);
    if (this._flashCount == 0) { this._flashCount = 1 }
  }

  _flashCell() {
    if (this._textCell) {
      this._textCell.setValue(`${this._nowCount}/${this._endCount}`);
    }
    if (this._graphCell) {
      this._graphCell.setValue(`=SPARKLINE(${this._nowCount},
        {"charttype","bar";"max",${this._endCount}})`);
    }
    if (this._nowCount == 0
      || this._nowCount == this._endCount
      || this._nowCount % this._flashCount == 0) {
      SpreadsheetApp.flush();
    }
  }
}

ライブラリ化

GASはライブラリ化をして使いまわすことができる。

このやり方については、いろいろなところで書かれているので触れない。

注意点は、ライブラリのclassを直接インスタンス化できない点。
インスタンスを返すグローバル関数を作る必要がある。

function getInstance() {
  return new Progress();
}

シングルトンではないので、別の関数名の方が良いだろうか？

newでインスタンス化できないが、getInstanceで取得したクラスのメソッドは使える。
ただし、入力保管はされない、JsDOC書いても説明が表示されない、など、不便な点はある。
が、毎回コピーして新規実装するよりはマシだと思う。

デプロイでバージョン付けて、既にライブラリを使っているスプレッドシートへの影響を抑える事もできるし。（HEADで使われてたらどうしようもないけども）

なぜ作ったか

ただの趣味。
進捗表示させてみようと思って調べてみたが、気軽に実装できなさそうなのが多かったので。

SPARKLINE関数が有るのを知った時、これを使えないかと思った。
画面更新が追い付かない問題が出たが、SpreadsheetApp.flush()で解決できると知ったので。
1ループごとに更新するとものすごく遅くなるのを見た時は笑った。

まあまあな仕上がりになったので、個人的には満足。

Discussion