はじめに
今年もアドベントカレンダーの季節がやってまいりました。
毎年スプレッドシートに関する知見の共有を記事にしてきましたが今年はQAエンジニアの観点で、検証業務の自動化を視野に入れた文章のテンプレ運用と題して、文章をコピーして一部書き換えて使うよりも効率よく文章を作成する方法と、その方法が何故検証業務の自動化に関わるのかをお伝えしていきたいと思います。
過去に掲載した記事については下記のリンクをご参照ください。
- Google Apps Scriptを使ってみよう(前編)
- Google Apps Scriptを使ってみよう(後編)
- Google Apps Scriptを使ってみよう(おまけ編)
- カスタム関数でキャラクタースキルの詳細をチェックする
- Google スプレッドシートを使って文章を検証しよう
- コーディングとの向き合い方 Before - After
文章の作成と検証の効率化
数値は文章と比べて比較的簡単に検証を行うことができるため、データの作成や検証作業の自動化の実装で大なり小なりの課題があってもそれほど複雑にはならないため、今回は文章を対象に効率の良い作成方法と検証方法の例を挙げます。
シチュエーションとしては、ある商品の販売促進を目的としたお知らせの文章を毎月更新し、更新したお知らせに関する検証作業が行われることを想定しています。
ベースとなるお知らせ
応募期間
2023年12月1日 (金) ~ 2023年12月12日(火)
〇〇を購入してQRコードで応募すると
毎月抽選で100名様に「△△」他、豪華景品が当たる！
ベースとなるお知らせの内容の中で毎月更新する箇所は「応募期間開始・応募期間終了・対象商品名・抽選される人数・景品名」を想定しています。
運用面のみで考えればこれらの内容をそのままコピーして該当箇所を書き換えることで運用する方法も考えられますが、検証を行うことも考えると文章全てを対象に注意深く更新があった部分を確認するのは検証方法としても不安が残ります。
そこで毎月更新のある部分を機械的に置き換えられる文字列でベースとなるお知らせを書き換えます。また置き換え用として用意する文字列についても確認を容易にするために更新箇所と紐付いた名前とします。
これらの機能をスプレッドシートとGoogle Apps Scriptを使用して作成します。
作成するものは下記4シートとスクリプトを数行です。
・更新する表示内容を入力するための「表示内容マスター」
・置き換えに使用するテンプレートの文章を入力するための「テンプレリスト」
・テンプレートの文章を表示内容マスターの内容で置き換えた文章を確認するための「テンプレビューワー」
・あらかじめ想定していた表示内容と実際に入力した内容が一致するかを検証するための「検証用シート」
・置き換え用文字列と表示内容を置き換える処理(スクリプト)
上記内容を作成します。
表示内容マスター
改めてになりますが表示内容マスターでは毎月のお知らせの中で更新する必要のある内容とその更新に関わるデータの定義、それにテンプレートの文章内で置き換える対象となる文字列の定義を行います。
シート名は「表示内容マスター」です。
B,C,D列はそれぞれ表示内容の管理と識別をするためのマスターID、適用するテンプレートを定義するテンプレID、今後の運用でデータが追加されることを考え該当するレコードで使用するデータ列の範囲を定義します。
E,F,G,H,I列には更新する必要のある箇所に関連する名前とそのデータを入力します。
表示内容マスターIDはどんな値でも良いのですが、使用する年と月としました。
テンプレIDは運用でテンプレートの文章が変更されるたびに変更することになりますが、今回は初回のため1としています。
データ範囲についてはB〜I列にデータ列が定義されているため「B:I」としています。
E列以降の内容についてはそれぞれベースとなるお知らせに表記されていた内容を用意しました。
テンプレリスト
「テンプレリスト」シートにはテンプレートを識別するためのIDと文章の作成に使用するテンプレートの文章の内容を定義します。
順序が逆になりますが、テンプレIDには表示内容マスターで表示内容マスターID「202312」のレコードのテンプレIDに定義した「1」としています。
表示内容マスターで定義したテンプレIDと紐付くテンプレートを表示内容マスターに入力した各データでテンプレートの文字列を置き換えることで文章を作成します。
テンプレート内で置き換える文字列は表示内容マスターの2行目に定義したデータ名の前後を「%」で挟んだ文字列で定義しています。
この名前で定義した理由はふたつあり、ひとつ目は表示内容マスターで定義されるデータが追加されたとしても機械的に置き換えが可能な分かりやすい文字列であること、ふたつ目は文章中で自然に混入する文字列が置き換え対象の文字列と衝突することで誤って置き換えられることを防ぐことです。
テンプレビューワー
「テンプレビューワー」シートで担う役割は2つです。
ひとつ目は指定された表示内容マスターIDから該当するレコードを表示して内容が確認できること。
ふたつ目は指定された表示内容マスターIDから該当するレコードを参照してお知らせの文章を作成し確認できることです。
続いて各セルの説明をしていきます。
画像中の2行目、オレンジで塗りつぶされているセルは表示する表示内容マスターIDを入力するセルになります。このセルに入力された表示内容マスターIDをもとに該当する表示内容マスターを表示します。
また3行目のデータ範囲についても表示内容マスターIDから該当するレコードのデータ範囲を取得して表示します。
C3に入力する数式は下記の通りです。
=vlookup(C2,'表示内容マスター'!B3:D,3,false)
5行目、6行目はC2,C3のセルの情報をもとに、文章を生成するために必要な情報を表示内容マスターから取得します。
数式の細かい説明は割愛しますが、C2に入力した表示内容マスターIDとC3に表示されるデータ範囲から表示内容マスターのデータの名前の行と該当するレコードの行をデータ範囲で定義されている分だけ取得します。
B5には表示内容マスターのデータ名を取得するための数式を入力します。
B5に入力する数式は下記の通りです。
=query(indirect("'表示内容マスター'!"®EXREPLACE($C$3,"([a-zA-Z])","$1"&"2")),"select * ")
B6には表示内容マスターからC2で指定した表示内容マスターIDに該当するレコードを取得するために数式を入力します。
B6に入力する数式は下記の通りです。
=query(indirect("'表示内容マスター'!"®EXREPLACE($C$3,"([a-zA-Z])(:)","$1"&"3"&"$2")),"select * where B="&$C$2&"")
次にB9に入力する数式ですがGoogle Apps Scriptで定義した関数を数式として呼び出すことができるカスタム数式というものを使用します。
Google Apps Scriptおよびカスタム数式については過去の記事に記載しているのでそちらを参照してください。
今回作成する関数はテンプレートの文章・表示内容マスターのデータ名・表示内容マスターの該当するレコードを引数として渡すとテンプレートの文章に含まれる置き換え用の文字列を該当するデータで置き換え文章を作成する機能となります。
拡張機能からApps Scriptを選択しエディタを開き、下記のコードを入力して保存します。
//置き換え用文字列と表示内容を置き換える処理
function TRANSLATE(template, header, data_list)
{
header = header[0]; //多次元配列になっているので1行だけ取り出す
data_list = data_list[0]; //多次元配列になっているので1行だけ取り出す
var replace_list = {}; //置き換え用の連想配列 keyに置き換え用の対象の文字列、valueに置き換え用の値
for (i=0;i<header.length;i++) {
var key = '%' + header[i] + '%'; //表示内容マスターのデータ名を「%」で括る
replace_list[key] = data_list[i];
}
for (let key in replace_list) {
template = template.replace(key, replace_list[key]); //テンプレートの文字列に含まれる置き換え用の文字列を値で置き換える
}
return template;
}
続いてB9のセルに作成した関数を呼び出すための数式を入力します。
数式の引数には表示内容マスターに設定されているテンプレIDをもつテンプレートの文章、表示内容マスターに設定されているデータ名、表示内容マスターに設定されているデータ名に対応するデータです。
B9に入力する数式は下記の通りです。
=TRANSLATE(vlookup(C6,'テンプレリスト'!B3:C,2,false),indirect(REGEXREPLACE($C$3,"([a-zA-Z])","$1"&"5")), indirect(REGEXREPLACE($C$3,"([a-zA-Z])","$1"&"6")))
これで文章を作成する準備が整いました。
ここまでの手順でB9のセルにはテンプレートの文章を表示内容マスターで設定した内容で置き換えた文章が表示されているはずです。
文章の作成についてはここまでですが、今回は検証の効率面も考慮した仕組みに焦点を当てていました。
その点についても説明します。
検証用シート
検証用シートでは毎月のお知らせの内容で、更新を行う必要のある内容について検証を容易にするための機能を提供します。
検証用シートでは検証に必要な情報と検証対象となる内容を表示し、加えてテストベースとなる情報を入力し、検証結果が表示されるようにします。
まず、検証に必要な情報として検証する対象が正しいかどうかを判断するために「表示内容マスターID、テンプレID、データ範囲」加えて検証対象とするデータ名を表示します。
数式はテンプレビューワーで説明したものと似ていますが検証の際には横並びよりも縦に並んでいる方が都合が良いことが多いため、transposeを使用して縦に並ぶようにしています。
B4のセルには下記の数式を入力します。
=transpose(query(indirect("'表示内容マスター'!"®EXREPLACE('テンプレビューワー'!$C$3,"([a-zA-Z])","$1"&"2")),"select * "))
続いて検証対象となるデータを表示します。
C4のセルに下記の数式を入力します。
=transpose(query(indirect("'表示内容マスター'!"®EXREPLACE('テンプレビューワー'!$C$3,"([a-zA-Z])(:)","$1"&"3"&"$2")),"select * where B="&'テンプレビューワー'!$C$2&""))
仕様情報の列は入力用となり、お知らせに記載されることが期待される内容を入力する列になります。
表示内容マスターID、テンプレID、データ範囲についてはデータを管理するための情報となり、一般的に仕様情報として提供されることは少ないため「N/A」としています。
最後に結果を表示する列になります。
E4のセルに入力する数式は下記のようになります。
=arrayformula(IF(D4:D="","",IF(D4:D="N/A","N/A",IF(C4:C=D4:D,"OK","NG"))))
D列に入力した値をC列と比較し、同じであれば「OK」を。異なる値であれば「NG」を表示します。検証が行いやすいように、セルにコピーできる形式で仕様情報を用意するとさらに検証効率が向上します。
例えば下記のように仕様情報としてお知らせそのものが記載されている資料があるとします。
応募期間
2023年12月1日 (金) ~ 2023年12月12日(火)
〇〇を購入してQRコードで応募すると
毎月抽選で100名様に「△△」他、豪華景品が当たる！
この場合、検証業務を行う際にテスト担当者は、記載されている仕様情報の中から検証に必要な情報を抜き出し、ひとつずつ入力されているデータと仕様情報を見比べながら確認を行うほかありません。
しかし、仕様情報の資料の内容に検証対象の情報が下記のような表形式で用意されていれば、内容の列を先ほどの検証用シートのD列へそのまま入力することで検証が可能になります。
| 検証対象 | 内容 |
|---|---|
| 応募期間開始 | 2023年12月1日 (金) |
| 応募期間終了 | 2023年12月12日(火) |
| 対象商品名 | 〇〇 |
| 抽選される人数 | 100 |
| 景品名 | △△ |
ここまでで文章作成と検証の準備は終わりです。
用意した仕組みを使って実際に運用する際の手順を見ていきましょう。
運用(表示内容の追加)
ベースとなるお知らせの内容は下記の内容でした。
応募期間
2023年12月1日 (金) ~ 2023年12月12日(火)
〇〇を購入してQRコードで応募すると
毎月抽選で100名様に「△△」他、豪華景品が当たる！
ここで景品名の表記が「△△」となっているため、その他にどんな景品が当たるのかが分かりにくいという意見が上がったため、お知らせの内容に景品の記載を追加し、それぞれの景品が抽選で何名に当たるかを明記する必要が出てきたとします。
応募期間
2024年1月1日 (月) ~ 2024年1月12日(金)
〇〇を購入してQRコードで応募すると豪華景品が当たる！
抽選で20名様に「△△」をプレゼント、さらに抽選にはずれてしまった方にも
Wチャンスとして抽選で80名様に「××」をプレゼントいたします。
表示内容マスターには新たに2024年1月のお知らせの内容を定義するためレコードを追加します。
さらに景品と抽選される人数の表記が増えたためにデータ名を追加します。
テンプレートの文章もWチャンスの表記に変更され文章の細かい表記も変更になっているためテンプレリストに新しくテンプレIDを採番してテンプレートを追加します。
表示内容マスターへレコードを追加
テンプレリストへテンプレを追加
テンプレビューワーで確認
検証シートで確認
まとめ
テンプレリストに追加する置き換え元の文章の表記が少しクセのある作りになってはいますが、それ以外の箇所については比較的簡単に対応ができたのではないでしょうか？
勘の良い方は既にお気付きかもしれませんが、表示内容マスターで定義されるレコードの内容は直接の入力で用意しましたが数式を使用して別のシートや外部のシートから参照することも可能です。
この仕組みを利用して元データを数式で参照することにより、名称の表記揺れやそもそもの入力のミスを防ぐことが可能となります。
また置き換える箇所についても固有の名称が直接テンプレートへ記載するようなことがなければ検証用シートで確認ができるため、検証の作業についても効率が向上するものと考えられます。
さらに文字列を置き換える前のテンプレートを検証対象に設定し、固有の名称が記載されていないかどうかも確認を行うことで品質の担保を厚めに行うことも可能です。
文章の検証では正規表現を使うことで検証方法を実装することもあるかと思いますが、細かい表現の変更に追従するためテストの更新も頻繁に行わなければならないケースが多く運用コストもかかります。
今回紹介した仕組みを使えば更新はレコードの追加で行うことが可能になり、一度検証を行った文章であれば検証対象をレコードで追加したデータのみに絞れるようになるため、運用コストも検証にかかるコストも抑えることが可能となります。
文章の作成と運用でお悩みの方の一助になりましたら幸いです。