##発生した問題
仕事でよくスプレッドシートを利用していて、データの分析を行うときにふと、いくつかのキーワードを自動でハイライトしたいと考え、プログラムを書いていたところGASではエクセルのVBAのように簡単にセル内の文字列に対して色を指定できるわけではなさそうだと気付きました。
Googleスプレッドシートにはエクセルのようにセルに入力された文字列のうち特定部分をマクロ機能を用いて自動で色付けする方法がありません。
例えば、セル内に「今日は晴れていて、空が青い。」という文字列があったときに、GASを用いて「空」を水色にして、「青い」を濃い青に着色しようとするとセル内の文字はプロパティを有していないため、一つ一つの文字に対して自動で別の色をつけるということが簡単にはできないのです。
これらは海外の掲示板でも同様の議論がされてはいるものの、解決策があまり知られていません。
Script to change individual text color within a cell - Stack Exchange
そこで今回は独自の方法を用いて、セル内の特定文字列に対して色付けを自動で行うプログラムを作成していきます。
(2020年2月7日現在、日本語でこの問題を解決する文献は確認できなかったので、参考程度に手法とプログラムをこの記事に記録します。）
##問題の背景
Google App Scriptではセルのフォーマットや文字サイズ、色を決める際に以下のようなコードでセル(Range)に対してスタイルを指定することができます。
function cell_style_function() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1");
range.setBackground("#FFFF00");
range.setFontColor("red");
}
しかし、これではセル全体としての文字色を指定できてもセルの中の文字列の一部だけを指定して色付けすることはできません。
##解決方法
ここでは上記の問題を解決するべく、独自の方法を用いて色付けを行っていきます。
今回はB3からB7のセルに色付けをしたい文字列を入力し、プログラムを実行するとD7からI12の範囲内の該当文字列がキーワードの文字色同じになるようなプログラムを作成します。
####スプレッドシートの準備
まずはスプレッドシートを作成し下記のスクリーンショットのようにセルと範囲を設定しましょう。
シート名はdata_sheetとし、わかりやすいように罫線をつけます。
##コーディング
スプレッドシートの準備が終わったら、実際にプログラムを書いていきましょう。
今回作成するプログラムはメイン関数となるcolor_main関数、色の変更を実際に行うcell_selector関数、各セルの色付けを行う命令文を自動生成するauto_instruction関数によって構成されています。
####color_main関数
この関数はメイン関数(メインルーチン)であり、ほかのでは主に対象セルの指定と対象範囲の色変更を行うcell_selector関数(サブルーチン)を実行します。
//© 2020 Kiyokazu Tanaka
function color_main(){
//着色を行いたいシートの範囲(D7からI12)
const coloring_range = "D7:I12";
//キーワードの範囲(B3からB7) この際、2次元配列にするとエラーになるため、必ず一列となるように定義を行う
const keyword_range = "B3:B7";
//カラーリングを実行
cell_selector(coloring_range,keyword_range);
}
####cell_selector関数
color_main関数に呼び出されるこの関数は主に指定範囲の色付けを順番に実行するループを作成します。
文字列探索には.indexOfを利用し、単純な線形探索を行っています。(Knuth–Morris–Pratt Algorithmやほかのアルゴリズムを組み合わせて高速化できる可能性があります。）
//© 2020 Kiyokazu Tanaka
//カラーリングを実際に行う関数
function cell_selector(INPUT_RANGE,KEYWORD_RANGE){
//シートの定義とパラメータの読み取り
var book = SpreadsheetApp.getActiveSpreadsheet();
//シート「data_sheet」を指定
var sheet_data = book.getSheetByName("data_sheet");
//色付けする範囲を定義
var inputRange = sheet_data.getRange(INPUT_RANGE);
//色付けするキーワードを定義
var keywords = sheet_data.getRange(KEYWORD_RANGE).getValues();
var key_last_row = sheet_data.getRange(KEYWORD_RANGE).getLastRow();
var key_first_row = sheet_data.getRange(KEYWORD_RANGE).getRow();
var key_first_col = sheet_data.getRange(KEYWORD_RANGE).getColumn();
var kw_amount = key_last_row-key_first_row;
var lead_row = inputRange.getRow();
var lead_col = inputRange.getColumn();
var last_row = inputRange.getLastRow();
var last_col = inputRange.getLastColumn();
//範囲内を巡回
for(var i=0; i < last_row+1 - lead_row; i++){
for(var j=0; j < last_col+1 - lead_col; j++){
//変更対象のセル、値を変数に格納し初期化する
var cur_row = i+lead_row;
var cur_col = j+lead_col;
var active_cell = sheet_data.getRange(cur_row ,cur_col);
var original_value = active_cell.getValue();
//色の変更処理の対象となるセルをアクティブにする
sheet_data.getRange(cur_row,cur_col).activate();
//探索用配列とカラー保持用を作成(文字列の検索とカラーリングに使用する)
var search_word_start_index = [];
var search_word_length_index = [];
var search_word_colorparam_index = [];
for(var kw_count = 0; kw_count < kw_amount; kw_count++){
//検索キーワードを設定
var searching_keyword = keywords[kw_count];
//色付け対象
var target_st = searching_keyword.toString();
var st_length = target_st.length+1;
//文章中の文字列を探索するためのポインタ(active_seach_index)を作成し、セル内で文字列探索を行う
for(var active_search_index = 0; active_search_index <= original_value.length;){
var search_result = original_value.indexOf(searching_keyword, active_search_index);
//キーワードが範囲内にあった場合
if(search_result != -1){
//文章内のキーワード開始位置を配列に格納
search_word_start_index.push(search_result);
//現在検索しているキーワードの長さを配列に格納
search_word_length_index.push(st_length);
//検索キーワードのフォントカラーを配列に格納
search_word_colorparam_index.push(sheet_data.getRange(key_first_row+kw_count,key_first_col ).getTextStyle().getForegroundColor());
//検索用ポインタをキーワードの長さだけ進める
active_search_index = search_result + st_length;
}else{
break;
}
}
//セル内の文字列の最後まで到達した場合
if(search_word_start_index.length >= 1){
//セル内で見つかった各キーワードの開始位置と色などが格納された配列をauto_instruction関数に渡し、命令文(スタイル定義文)を自動生成
var instruction = auto_instruction(search_word_start_index,search_word_length_index,search_word_colorparam_index);
//現在捜査しているセルのスタイルをコピー
var current_cell_rv = sheet_data.getCurrentCell().getRichTextValue().copy();
//自動生成された各セルのスタイルの定義命令文を実行
eval(instruction);
}
}
}
}
}
####auto_instruction関数
cell_selector関数によって呼び出される、各セルのスタイル（文字色等）を定義するための命令文を自動生成する関数です。
JavaScriptには動的に作成した文字列をプログラムの一部として実行できるeval()関数があるので、今回はこれを用いて複数の文字列に対して色付けを同時に行う命令文を実行します。
この関数(auto_instruction関数)では与えられたセルの情報とキーワードの情報を元にセル内の何文字目を着色するかを決定し、実行可能な命令文を返します。
その戻り値を呼び出し元であるcell_selector関数内のeval()にて実行することで着色を実行します。
//© 2020 Kiyokazu Tanaka
//セルのスタイル定義を自動生成
function auto_instruction(start_index_array,char_length_array,color_array){
//定義文の初期化
var back_text = "sheet_data.getCurrentCell()";
//定義文の接頭辞と接尾辞は固定で、中身を自動生成する
const back_prefix = "sheet_data.getCurrentCell().setRichTextValue(current_cell_rv.setText(original_value)";
var back_body = "";
const back_suffix = ".build());";
//見つかったキーワードの数だけループ
for(var i=0;i < start_index_array.length;i++){
//見つかったキーワードの開始位置と長さから終了位置を算出
var end_point = start_index_array[i] + char_length_array[i]-1;
//フォントカラーを取り出す
var color = color_array[i];
//定義文のボディを自動生成(appendするような形で)
back_body += ".setTextStyle(" + start_index_array[i] + "," + end_point + ", SpreadsheetApp.newTextStyle().setForegroundColor('"+color+"').build())";
}
//定義命令文を接頭辞と接尾辞とともにまとめる
back_text = back_prefix + back_body + back_suffix;
//返り値として定義文を実行可能形式で返す
return back_text;
}
GASではこの同時にスタイルを定義するといった考えが非常に重要です。
スプレッドシートにおいてはあるセルの「1文字目から3文字目は青色」というようにスタイルを定義して適用し、その後「4文字目から8文字目を赤色」というようにスタイルを追加適用しようすると、「1文字目から3文字目は青色」とした最初のスタイルがリセットされ、結果として「4文字目から8文字目を赤色」というスタイルしかセルに適用されません。（つまり、1文字目から3文字目が青色にならない）
今回のプログラムではそれを克服するためにauto_instruction関数を用いて、スタイルの定義文を各セルごとに自動生成し実行させることで、同時に複数の色を着色することが可能になっています。
##実行結果
####プログラム実行前
範囲内のセルにランダムに文字列が入力されている状態です。
####プログラム実行後
実行すると、キーワードの色に合わせて範囲内の文字も色が変更されています。
##まとめと改善点
Googleスプレッドシートはエクセルに比べ、処理が基本的にサーバーで行われていることから比較的クライアントのCPU処理速度に依存せずに、大きなデータを扱うことができます。
しかしながらスプレッドシートはブラウザアプリケーションであり、非同期通信を用いたものである以上、エクセルのようなネイティブアプリケーションで設定されているプロパティの細かさを実現できない時もあります。
今回のプログラムはそのギャップを少しでも埋めるために開発しました。
膨大なデータから特定の文字列をハイライトする機能は少なからず役に立つと思われます。
今後は文字列探索アルゴリズムを変更すればそれなりにスピードも出ると考えられるので、改良をしていきます。また、思い付きで書いたものなので改善点やほかの方法で改善されているようでしたら教えてください。
このプログラムを利用する際もコメントに利用する旨を記入していただけると嬉しいです。よろしくお願いします。
最後まで読んでいただきありがとうございました。
##今回作成したプログラム
//© 2020 Kiyokazu Tanaka
function color_main(){
//着色を行いたいシートの範囲(D7からI12)
const coloring_range = "D7:I12";
//キーワードの範囲(B3からB7) この際、2次元配列にするとエラーになるため、必ず一列となるように定義を行う
const keyword_range = "B3:B7";
//カラーリングを実行
cell_selector(coloring_range,keyword_range);
}
//カラーリングを実際に行う関数
function cell_selector(INPUT_RANGE,KEYWORD_RANGE){
//シートの定義とパラメータの読み取り
var book = SpreadsheetApp.getActiveSpreadsheet();
//シート「data_sheet」を指定
var sheet_data = book.getSheetByName("data_sheet");
//色付けする範囲を定義
var inputRange = sheet_data.getRange(INPUT_RANGE);
//色付けするキーワードを定義
var keywords = sheet_data.getRange(KEYWORD_RANGE).getValues();
var key_last_row = sheet_data.getRange(KEYWORD_RANGE).getLastRow();
var key_first_row = sheet_data.getRange(KEYWORD_RANGE).getRow();
var key_first_col = sheet_data.getRange(KEYWORD_RANGE).getColumn();
var kw_amount = key_last_row-key_first_row;
var lead_row = inputRange.getRow();
var lead_col = inputRange.getColumn();
var last_row = inputRange.getLastRow();
var last_col = inputRange.getLastColumn();
//範囲内を巡回
for(var i=0; i < last_row+1 - lead_row; i++){
for(var j=0; j < last_col+1 - lead_col; j++){
//変更対象のセル、値を変数に格納し初期化する
var cur_row = i+lead_row;
var cur_col = j+lead_col;
var active_cell = sheet_data.getRange(cur_row ,cur_col);
var original_value = active_cell.getValue();
//色の変更処理の対象となるセルをアクティブにする
sheet_data.getRange(cur_row,cur_col).activate();
//探索用配列とカラー保持用を作成(文字列の検索とカラーリングに使用する)
var search_word_start_index = [];
var search_word_length_index = [];
var search_word_colorparam_index = [];
for(var kw_count = 0; kw_count < kw_amount; kw_count++){
//検索キーワードを設定
var searching_keyword = keywords[kw_count];
//色付け対象
var target_st = searching_keyword.toString();
var st_length = target_st.length+1;
//文章中の文字列を探索するためのポインタ(active_seach_index)を作成し、セル内で文字列探索を行う
for(var active_search_index = 0; active_search_index <= original_value.length;){
var search_result = original_value.indexOf(searching_keyword, active_search_index);
//キーワードが範囲内にあった場合
if(search_result != -1){
//文章内のキーワード開始位置を配列に格納
search_word_start_index.push(search_result);
//現在検索しているキーワードの長さを配列に格納
search_word_length_index.push(st_length);
//検索キーワードのフォントカラーを配列に格納
search_word_colorparam_index.push(sheet_data.getRange(key_first_row+kw_count,key_first_col ).getTextStyle().getForegroundColor());
//検索用ポインタをキーワードの長さだけ進める
active_search_index = search_result + st_length;
}else{
break;
}
}
//セル内の文字列の最後まで到達した場合
if(search_word_start_index.length >= 1){
//セル内で見つかった各キーワードの開始位置と色などが格納された配列をauto_instruction関数に渡し、命令文(スタイル定義文)を自動生成
var instruction = auto_instruction(search_word_start_index,search_word_length_index,search_word_colorparam_index);
//現在捜査しているセルのスタイルをコピー
var current_cell_rv = sheet_data.getCurrentCell().getRichTextValue().copy();
//自動生成された各セルのスタイルの定義命令文を実行
eval(instruction);
}
}
}
}
}
//セルのスタイル定義を自動生成
function auto_instruction(start_index_array,char_length_array,color_array){
//定義文の初期化
var back_text = "sheet_data.getCurrentCell()";
//定義文の接頭辞と接尾辞は固定で、中身を自動生成する
const back_prefix = "sheet_data.getCurrentCell().setRichTextValue(current_cell_rv.setText(original_value)";
var back_body = "";
const back_suffix = ".build());";
//見つかったキーワードの数だけループ
for(var i=0;i < start_index_array.length;i++){
//見つかったキーワードの開始位置と長さから終了位置を算出
var end_point = start_index_array[i] + char_length_array[i]-1;
//フォントカラーを取り出す
var color = color_array[i];
//定義文のボディを自動生成(appendするような形で)
back_body += ".setTextStyle(" + start_index_array[i] + "," + end_point + ", SpreadsheetApp.newTextStyle().setForegroundColor('"+color+"').build())";
}
//定義命令文を接頭辞と接尾辞とともにまとめる
back_text = back_prefix + back_body + back_suffix;
//返り値として定義文を実行可能形式で返す
return back_text;
}