GoogleAppsScriptを使ってテスト項目書の体裁を一発で整える

こんにちは、テストオペレーショングループのりきおです。

本記事は、スプレッドシートで作成したテスト項目書に対してGAS（GoogleAppsScript）を使って体裁修正する方法についてご紹介していきたいと思います。テスト項目書はもちろん、あらゆるドキュメント作成の際に少しでもご参考にして頂けると幸いです。

※なお、本記事では「デザイン」「見た目」等は、まとめて「体裁」と総称します。

はじめに

いきなりですが、下図のように同一の項目内容で体裁のみが異なるテスト項目書があったとします。

それぞれを見比べたとき、どちらがより見やすいと感じますか？

あるいは、どちらがより円滑にテストを実行できそうだと感じますか？

パターンA

パターンB

・

・

・

恐らく、多くの人はパターンBを選ぶのではないでしょうか。

パターンBは、項目の所属がカテゴライズ化されたような体裁となっているため、「どの画面（大項目）」の「どの場所（中項目）」の「どの機能（小項目）」を指しているのかが、パターンAよりも識別しやすくなっています。上図の例では項目数が20個と少ないため、両者の視認レベルにそこまで大きな差異はありませんが、例えば、項目数が数百～数千項目に及んでいたり、項目内で似たような文言が散見されるような場合だと、より顕著な結果となるのではないでしょうか。

しかし、上記で挙げたように数百～数千項目が記載されたテスト項目書を手動で体裁修正した場合、それなりに作業工数がかかってしまったり、また、コピペで修正した場合には修正対象や記載内容の誤植といったヒューマンエラーが起きる可能性があることも事実です。

そのような事態を踏まえて、今回はパターンBを例にスプレッドシートで作成したテスト項目書に対してGASを使って一発で体裁修正したときの方法について、次章以降より解説していきたいと思います！

要件定義

それでは、コーディングに入る前にパターンBがどのように体裁修正されているかを一度整理しておきましょう。

1.基本要件

はじめに、体裁修正を行う要件を確認していきます。修正内容をまとめると、以下の通りとなっていることが分かります。

・同列の上下で同じ値が連続する場合、下のセルの文字色をグレーに変更する

・同列で上下で同じ値が連続する場合、下のセル上部の罫線を非表示とする

・「中項目」は「大項目＋中項目」、「小項目」は「大項目＋中項目＋小項目」でそれぞれ比較する

2.機能要件

続いて、上節で整理した内容に基づき、機能要件を手順に沿って定義していきましょう。

基本機能（判定・描画処理）

1. 制御処理：実行回数（N回目）を確認し、状態に応じて以下の通り制御する

　1-A：指定した回数に到達した場合

　　　　・体裁修正処理を終了する

　1-B：指定した回数に未達の場合

　　　　・「2. 判定処理」以降の処理を行う

2. 判定処理：各列の項目間の上下行（N行とN＋1行）の値を比較する

　※上下行の値が「同じ」と判定される条件は以下の通り

　・大項目：「大項目」の上下行の値が同じ

　・中項目：「大項目」「中項目」の上下行の各値がそれぞれ同じ

　・小項目：「大項目」「中項目」「小項目」の上下行の各値がそれぞれ同じ

3. 修正処理：「2. 判定処理」の比較結果に応じて以下の通り体裁修正する

　3-A：同じ値だった場合、下行（N＋1行）の項目を以下に体裁修正する

　　　　・文字色設定：グレー（#cccccc）

　　　　・上罫線描画：FALSE

　3-B：異なる値だった場合、下行（N＋1行）の項目を以下に体裁修正する

　　　　・文字色設定：そのまま（何もしない）

　　　　・上罫線描画：TRUE

4. 実行回数や比較対象の行数にて共通で使用しているインデックス（N）をインクリメントし、「1. 制御処理」に戻る

フローチャート：

実際のコーディング処理とは少し異なりますが、イメージとしては下図のような感じとなります。

補足：

・初回実行時、上行（項番#1）の項目は修正対象とならないため、以降の処理でも常に「下行（N＋1行）」の項目を修正対象とします。

・「3-B：異なる値だった場合」の処理として、パターンAのように体裁修正前の全ての項目が「文字色：黒」かつ「横罫線：あり」となっていれば「何もしない処理」でも問題ないですが、テスターへの注記として意図的に文字色が黒以外に設定されていたり、罫線が描画されていないといった体裁上の誤植があった場合などを考慮して、今回は「文字色：そのまま」「上罫線描画：TRUE」とします。

以上が今回の要件の概要となります。

本章でまとめた要件を元に、次章でコーディングしていきましょう！

コーディング

本記事ではGASのエディタページのコーディングから解説していきます。

※GASの概要については、別記事「業務改善にはコレ！＼Google Apps Script／」でもご紹介しております。ご興味がある方、GASとはなんぞやという方は、是非読んでみて下さい。

1.スプレッドシート・項目シートの取得

はじめに、実行対象となるスプレッドシートと項目シートを取得しましょう。

関数名は「TestSheetFix」としています。

function TestSheetFix() {


const activeSheet = SpreadsheetApp.getActiveSpreadsheet();
const activeSheetName = SpreadsheetApp.getActiveSheet().getName();
const fixSheet = activeSheet.getSheetByName(activeSheetName);

今回はアクティブ状態のシート（現在開いているシート）であれば全てのシートが実行対象となるようにコーディングしているため、シート名に依存せずに実行することができます（間接参照）。

言い換えれば、実行対象以外のシートがアクティブだった場合にも体裁修正処理が走るので、扱いには少し注意が必要です。

補足

実行対象のシート数が少ない場合や特定のシートに対して実行したいといった限定的な用途であれば、以下のように実行対象のシート名を直値で指定する方法でも良さそうです（直接参照）。

function TestSheetFix() {
 
const activeSheet = SpreadsheetApp.getActiveSpreadsheet();
const fixSheet = activeSheet.getSheetByName('実行対象のシート名');

2.行・列・範囲の取得

次に、取得したシート内で実行対象となる行・列・実行範囲を定義していきましょう。

対象は以下4点です。

1)先頭行の定義

はじめに、実行範囲や修正対象となる行を指定するために、上図スプレッドシートで赤枠に該当する行位置の変数を定義しておきます。

let firstRowUpper = 4;
let firstRowLower = firstRowUpper + 1;

・firstRowUpper（先頭上行）：4行目

・firstRowLower（先頭下行）：5行目（先頭上行＋1行）

2)最終行の定義

続いて、体裁修正する範囲の末尾を決定するために、上図スプレッドシートで赤枠に該当する行位置の変数を定義しておきます。

let lastRow = 23;

・lastRow（最終行）：23行目

3)実行範囲の定義

項目の先頭行と最終行を取得した後は、それぞれを減算して体裁修正する実行範囲（実行回数）を定義しましょう。

let fixRange = lastRow - firstRowUpper;

・fixRange(19)　：lastRow(23) – firstRowUpper(4) 

※「下行」が修正対象であり一番最後に実行する行は22行目（最終行 – 1行目）のため、全項目数に対して1項目少ない値が実行範囲となります。

4)列番号の定義

最後に、比較対象や実行する列を指定するために、上図スプレッドシートで赤枠に該当する列番号を定義しておきます。

let primaryItemCol = 3;
let secondaryItemCol = primaryItemCol + 1;
let tertiaryItemCol = primaryItemCol + 2;

・primaryItemCol（大項目列）　 ：C列＝3列目

・secondaryItemCol（中項目列）：D列＝4列目（大項目列＋1列目）

・tertiaryItemCol（小項目列）　  ：E列＝5列目（大項目列＋2列目）

変数の戻り値

デバッグで各変数の戻り値を確認してみると、下図の結果となりました。

どれも適切な値が定義できてますね！

補足

行位置及び列番号は直値で指定しています。タイトルの文字列をキーとして検索する方法やgetNextDataCellメソッドなどでセル位置を取得する方法も検討しましたが、タイトル名が変更された、意図しない文字列が存在していたため想定外のセル位置が取得された、といったケースを考慮してより確実性の高いコーディングにしています。そのため、対象の行・列番号や実行範囲は任意に指定できる反面、行・列が追加・削除された場合、別シートに対して実行したい場合などは再定義する必要があります。

3.比較対象の取得

続いて、体裁修正時に比較する項目の値を取得していきます。

今回は、スプレッドシートから値を直接取得するのではなく、あらかじめ全ての値を配列に格納し、その各要素を比較するようにコーディングしています。

※理由は別章「実行結果」に記載。

let primaryItemAry = fixSheet.getRange(firstRowUpper, primaryItemCol, fixRange + 1, 1).getValues().flat();
let secondaryItemAry = fixSheet.getRange(firstRowUpper, secondaryItemCol, fixRange + 1, 1).getValues().flat();
let tertiaryItemAry = fixSheet.getRange(firstRowUpper, tertiaryItemCol, fixRange + 1, 1).getValues().flat();

※「fixRange」は、「全体の項目数 – 1項目」となっているので、ここでは不足している「1」を加算しておきます。

変数の戻り値

「大項目（primaryItemAry）」を例に配列の中身を確認してみると、下図の結果となりました。

「中項目」「小項目」でも同様に、該当する全ての項目が格納されています。

次節「4.体裁修正処理」では、これらの要素を上から順に2つずつ参照していくことで、スプレッドシート上の上下列も間接的に比較できるようになっています。

補足

今回は、「大項目」「中項目」「小項目」の各列ごとに一次元配列に格納するようにコーディングしていますが、列数が多くなる場合などは以下のようにまとめて同じ配列に格納する方法でも良さそうです。

その際、要素指定には注意が必要です。

let itemAry = fixSheet.getRange(firstRowUpper, primaryItemCol, fixRange + 1, 3).getValues();

4.体裁修正処理

修正時に必要となる各値を定義・取得できたところで、体裁修正処理に入ります。

制御処理

別章「要件定義」で整理した内容に基づき、for文によるループ実行で各行・列の体裁を修正していきます。

ループ数の条件は「fixRange（実行範囲）」を指定します。

for (let i = 0; i < fixRange; i++) {

ここで定義した変数「i」は、以降の体裁修正処理でも使用します。比較対象となる配列要素や修正対象となる行位置に「i」を指定、または加算することで、ループ時のインクリメントに追従して修正処理の対象が変化する制御となります。

「大項目」の体裁修正

if文で「大項目」の上下行の値を比較し、その判定結果を元に体裁修正する内容を分岐させます。

if (primaryItemAry[i] === primaryItemAry[i + 1]) {
  fixSheet.getRange(firstRowLower + i, primaryItemCol).setFontColor('#cccccc')
  fixSheet.getRange(firstRowLower + i, primaryItemCol).setBorder(false, true, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
 
} else {
  fixSheet.getRange(firstRowLower + i, primaryItemCol).setBorder(true, true, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
};

同じ値の場合： if 大項目[ 上行 ] ＝ 大項目[ 下行 ] 

・文字色設定：setFontColor

　　文字色：#cccccc（グレー）

・罫線設定：setBorder

　　上部：FALSE（描画しない）　※上部罫線が引かれていた場合は削除

　　左部：TRUE（描画する）　※左部罫線が引かれていなかった場合を想定

　　下部：null（何もしない）

　　右部：null（何もしない）

　　水平：null（何もしない）

　　垂直：null（何もしない）

　　罫線色：#000000（黒）

　　罫線スタイル：SOLID（細い実線）

同じ値以外の場合：else 

・文字色設定：setFontColor　

　　→　コード記述なし（何もしない）

・罫線設定：setBorder

　　上部：TRUE（描画する）　※上部罫線が引かれていた場合は上書き

　　左部：TRUE（描画する）　※左部罫線が引かれていなかった場合を想定

　　下部：null（何もしない）

　　右部：null（何もしない）

　　水平：null（何もしない）

　　垂直：null（何もしない）

　　罫線色：#000000（黒）

　　罫線スタイル：SOLID（細い実線）

「中項目」の体裁修正

体裁修正する内容は「大項目」と同じですが、判定条件に「大項目同士が同じ場合（primaryItemAry[i] === primaryItemAry[i + 1] && ）」を含めています。

if (primaryItemAry[i] === primaryItemAry[i + 1] && secondaryItemAry[i] === secondaryItemAry[i + 1]) {
  fixSheet.getRange(firstRowLower + i, secondaryItemCol).setFontColor('#cccccc')
  fixSheet.getRange(firstRowLower + i, secondaryItemCol).setBorder(false, true, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
 
} else {
  fixSheet.getRange(firstRowLower + i, secondaryItemCol).setBorder(true, true, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
};

「小項目」の体裁修正

こちらも体裁修正する内容は「大項目（中項目）」と同じですが、判定条件に「大項目同士かつ中項目同士が同じ場合（primaryItemAry[i] === primaryItemAry[i + 1] && secondaryItemAry[i] === secondaryItemAry[i + 1] && ）」を含めています。

if (primaryItemAry[i] === primaryItemAry[i + 1] && secondaryItemAry[i] === secondaryItemAry[i + 1] && tertiaryItemAry[i] === tertiaryItemAry[i + 1]) {
  fixSheet.getRange(firstRowLower + i, tertiaryItemCol).setFontColor('#cccccc')
  fixSheet.getRange(firstRowLower + i, tertiaryItemCol).setBorder(false, true, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
 
} else {
  fixSheet.getRange(firstRowLower + i, tertiaryItemCol).setBorder(true, true, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
};

以上が各コーディング内容となります。下記にまとめとして、全体のコーディング＋スクリプト実行時間を測定する処理を含めたソースコードを記載しています。少しでもご活用いただけたらと思います。

5.ソースコード

/**
 * 関数名  :TestSheetFix
 * 内容    :スプレッドシートで作成したテスト項目書の体裁を修正する
 * 修正対象：C列[4行目～最終行]：大項目（primaryItem）
 * 　　     D列[4行目～最終行]：中項目（secondaryItem）
 * 　　     E列[4行目～最終行]：小項目（tertiaryItem）
 * 
 * ※ 流用する際は、テスト項目書に応じて「先頭行の定義」「最終行の定義」「各列の定義」を再定義して下さい
 */
 
function TestSheetFix() {
 
  //実行時間計測用：開始
  const startTime = new Date();
 
  //スプレッドシート・項目シートの取得
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  const activeSheetName = SpreadsheetApp.getActiveSheet().getName();
  const fixSheet = activeSheet.getSheetByName(activeSheetName);
 
  //先頭行の定義
  let firstRowUpper = 4;
  let firstRowLower = firstRowUpper + 1;
  //最終行の定義
  let lastRow = 23;
  //実行範囲の定義
  let fixRange = lastRow - firstRowUpper;
  //列番号の定義
  let primaryItemCol = 3;
  let secondaryItemCol = primaryItemCol + 1;
  let tertiaryItemCol = primaryItemCol + 2;
 
  //比較対象の取得
  let primaryItemAry = fixSheet.getRange(firstRowUpper, primaryItemCol, fixRange + 1, 1).getValues().flat();
  let secondaryItemAry = fixSheet.getRange(firstRowUpper, secondaryItemCol, fixRange + 1, 1).getValues().flat();
  let tertiaryItemAry = fixSheet.getRange(firstRowUpper, tertiaryItemCol, fixRange + 1, 1).getValues().flat();
 
  //体裁修正処理
  for (let i = 0; i < fixRange; i++) {
 
    //「大項目」の体裁修正
    if (primaryItemAry[i] === primaryItemAry[i + 1]) {
      fixSheet.getRange(firstRowLower + i, primaryItemCol).setFontColor('#cccccc')
      fixSheet.getRange(firstRowLower + i, primaryItemCol).setBorder(false, true, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
    } else {
      fixSheet.getRange(firstRowLower + i, primaryItemCol).setBorder(true, true, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
    };
 
    //「中項目」の体裁修正
    if (primaryItemAry[i] === primaryItemAry[i + 1] && secondaryItemAry[i] === secondaryItemAry[i + 1]) {
      fixSheet.getRange(firstRowLower + i, secondaryItemCol).setFontColor('#cccccc')
      fixSheet.getRange(firstRowLower + i, secondaryItemCol).setBorder(false, true, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
    } else {
      fixSheet.getRange(firstRowLower + i, secondaryItemCol).setBorder(true, true, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
    };
 
    //「小項目」の体裁修正
    if (primaryItemAry[i] === primaryItemAry[i + 1] && secondaryItemAry[i] === secondaryItemAry[i + 1] && tertiaryItemAry[i] === tertiaryItemAry[i + 1]) {
      fixSheet.getRange(firstRowLower + i, tertiaryItemCol).setFontColor('#cccccc')
      fixSheet.getRange(firstRowLower + i, tertiaryItemCol).setBorder(false, true, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
    } else {
      fixSheet.getRange(firstRowLower + i, tertiaryItemCol).setBorder(true, true, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
    };
    
  };
 
  //実行時間計測用：終了
  const endTime = new Date();
  //実行時間(s)の算出
  const runTime = (endTime - startTime) / 1000;
  //ログ出力
  console.log(runTime);
 
};

実行結果

1.実行結果

それでは、以下のテスト項目書に対してコードを実行し、結果を確認してみましょう！

実行前のテスト項目書

※項目内容は別章「はじめに」のパターンA・Bと同じですが、下記の通り少し細工をしています。

罫線

　・#6：「大項目」の左部に罫線が引かれていない

　・#7（#8）：「中項目」の下部（上部）に罫線が引かれていない

　・#12（#13）：「中項目」の下部（上部）に太字の罫線が引かれている

　・#18：「小項目」の左部に赤い罫線が引かれている

　・先頭行上部：項目を区別するために太字の罫線となっている

　・最終行下部：項目を区別するために太字の罫線となっている

文字色

　・#16：「小項目」が赤字になっている

補足

上記の「罫線」は体裁上で特に意味をなしておらずテスト設計者による誤記だと想定し、修正対象としています。ただし、先頭行上部と最終行下部の太字の罫線については意図した描画であるため、こちらは修正対象としません。また「文字色」については、テスト設計者が項目内容に対する注記やテスターへの補足といった意図した設定の可能性があるため、こちらも修正対象としていません。

果たして、結果は如何に・・・？

実行後のテスト項目書

期待通りの体裁となりました！

罫線及び文字色が各条件に従って修正されており、今回の要件通りとなっています。

実行時間

20項目程度だと約0.6秒で実行できました。はやい！

補足

GAS内で一発で実行できたため、実行後のUndo(Ctrl + Z)・Redo(Ctrl + Y)も一発で対応可能でした。修正結果を戻したい場合や意図しない修正を入れてしまった場合などでも、一発で手戻りできます。

2.ハマりポイントと改善策

最後に、実装時にハマったポイントとその改善策を記載しておきます。

当初は、体裁修正処理で比較対象となる上下の値をスプレッドシートから直接取得する方法で実装していたのですが、どうしても実行速度が遅くなってしまう問題が起きていました。

調べたところ、関数やメソッドの実行対象にスプレッドシートなどGAS以外のサービスを指定した場合、実行時にAPI連携が発生するようです。そのため、上述のようにfor文中にgetValueメソッドを記述し、かつ取得元にスプレッドシートを指定すると、ループするたびにリクエスト回数が増加してしまい、結果としてスクリプト実行時間が伸びてしまっていたことが遅延の原因でした（当然と言えば当然でした…）。

なので、事前に全ての比較対象となる値を一括で配列に格納し、体裁修正処理でこれらの要素を参照することで、比較時の処理をGAS内で完結できるように改善しました。

参考までに、改善前後のイメージ図と500項目に対して体裁修正したときの実行時間を以下に記載しています。

実行イメージ

実行時間

　改善前

　419秒＝約7分で完了しました。手動作業よりは多少早いくらいでしょうか…？

　改善後

　約7.6秒で完了しました！かなり良化しました◎

おわりに

今回ご紹介した方法であれば、500項目でも7.6秒程度で体裁修正できたため、作業コストが大幅に削減できるのではないでしょうか（項目数が多ければ多いほど効果的！）。

ただし、本記事ではあくまでも仮のテスト項目書に対するサンプルコードを解説しているため、ご使用いただく際は対象ドキュメントの目的や用途に応じて適宜カスタマイズして頂ければと思います。

また、こうした「GAS × スプレッドシート」といったサービスを上手く組み合わせて業務を適切に自動化・効率化し、作業をドンドン捗らせちゃいましょう！