Google Spread Sheetのセル内文字色を一括で置換。
はじめに
Google Spread Sheetは様々な使い方がありますが、タスク管理などで、一覧表の各行内の一部を更新し、更新箇所を赤字にして差分を示すことがあると思います。
次の改訂タイミングでは、その赤字を黒字にして、さらに改訂した部分を赤字にして・・・等とやる訳ですが、個人的なステータス管理表では、
青字で追記
⇒ 次週は赤字で追記
⇒ 青字を黒字にして、新規追加分を青字で追記
⇒ 赤字を黒字にして、新規追加分を赤字で追記
⇒ 青字を黒字にして、新規追加分を青字で追記
⇒ 赤字を・・・
という具合に青・赤で、先週の追加分・今週の追加分の2色で差分を表現していました。
課題点
すぐに分かると思いますが、改訂するときに前々週の色を黒字にしていくのですが、セルの一部の色を変更するので、結構な手間がかかっていました。
これをラクにできないか・・・と考えていました。
改善方法
GoogleSpreadSheetでは、Google Apps Scriptの利用が出来るので、これで何とか出来ないか？と考え、以下のような実装でやりたいことが実装できました。
・Google Apps Scriptを使い、指定した範囲の内容を加工
・「RichTextVaule」を使用
・スプレッドシート側にメニューを付けて、そこから色変更を実行
[参考]
class RichTextValue
https://developers.google.com/apps-script/reference/spreadsheet/rich-text-value
※作成したのは数年前ですが、まだ使っているので動作上は問題ないはずです。
実装していないこと(手抜き・・・)
・選択範囲の途中に文字列を含まない、空のセルが存在すると、例外処理の不足によりスクリプトエラーとなる。
⇒例外パターンへの対処が必要
・色以外のTextStyleはオリジナルから引き継ぐ必要があるが、ベタ書きで引継ぎ・・・。
スタイルの項目が増えると改修が必要なので、個別にsetせず、全て引き継ぐ方式が望ましい
実装したスクリプト
// 選択範囲のセルに含まれる色つき文字列を別の色に変更するスクリプト
// ・対象Sheetで変更対象のセル範囲を選択
// ・UIのメニューを追加し、アイテム選択で実行
// シートへのメニュー追加
function onOpen() {
var ui = SpreadsheetApp.getUi();
var menu = ui.createMenu('文字色変更');
menu.addItem('青⇒黒', 'changeBlueToBlack');
menu.addItem('赤⇒黒', 'changeRedToBlack');
menu.addToUi();
}
function changeBlueToBlack(){
changeTextColor( '#0000ff', '#000000');
}
function changeRedToBlack(){
changeTextColor( '#ff0000', '#000000');
}
function changeTextColor(orgColor, destColor) {
// アクティブレンジの取得
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
var range = sheet.getActiveRange();
// RichTextValue[][] を取得
var richText = range.getRichTextValues();
Logger.log( 'Sheet = [' + sheet.getSheetName() + ']');
// 1セル分ずつRunsを分解
for (var i = 0; i < richText.length; i++) {
for (var j = 0; j < richText[i].length; j++) {
// Runsを取り出し
var runs = richText[i][j].getRuns();
var runsCount = runs.length;
var nRichText = SpreadsheetApp.newRichTextValue();
if( runsCount == 0 ){
break;
}
Logger.log( 'runsCount=[' + runsCount +']\n' );
// rebuild用テキスト
var cText = '';
// 個々のRunよりText, Colorなどを取得
for( var k = 0; k<runsCount; k++){
// Textをrunsより取り出して連結
cText = cText + runs[k].getText();
}
nRichText.setText(cText);
// 個々のRunよりText, Colorなどを取得
for( var k = 0; k<runsCount; k++){
// 該当のrunが変更前の色(orgColor)だった場合、変更後の色(destColor)に変更
if( runs[k].getTextStyle().getForegroundColor() == orgColor ){
nRichText.setTextStyle( runs[k].getStartIndex(), runs[k].getStartIndex()+runs[k].getText().length,
SpreadsheetApp.newTextStyle()
.setForegroundColor(destColor)
.build()
);
}
else {
// 対象の色(orgColor以外)は元の色
nRichText.setTextStyle( runs[k].getStartIndex(), runs[k].getStartIndex()+runs[k].getText().length,
SpreadsheetApp.newTextStyle()
.setForegroundColor(runs[k].getTextStyle().getForegroundColor())
.build()
);
}
// その他のTextStyleはオリジナルから引き継ぐ
// TODO: スタイルの項目が増えると改修が必要なので、個別にsetせず、全て引き継ぐ方式が望ましい
nRichText.setTextStyle( runs[k].getStartIndex(), runs[k].getStartIndex()+runs[k].getText().length,
SpreadsheetApp.newTextStyle()
.setFontFamily(runs[k].getTextStyle().getFontFamily())
.setFontSize(runs[k].getTextStyle().getFontSize())
.setBold(runs[k].getTextStyle().isBold())
.setItalic(runs[k].getTextStyle().isItalic())
.setStrikethrough(runs[k].getTextStyle().isStrikethrough())
.setUnderline(runs[k].getTextStyle().isUnderline())
.build()
);
// debug用のログ
Logger.log( 'cell['+i+':'+j+'->Runs['+k+']] >> '+'text[' + runs[k].getText() +
'] index['+ runs[k].getStartIndex() +
'] length['+ runs[k].getText().length +
'] color['+ runs[k].getTextStyle().getForegroundColor() +']');
}
//RichTexValueをRangeの相対位置で取得したCellへ設定する
range.getCell(i+1,j+1).setRichTextValue(nRichText.build());
}
}
return;
}
// 選択範囲のセルに含まれる色つき文字列を別の色に変更するスクリプト
// ・対象Sheetで変更対象のセル範囲を選択
// ・UIのメニューを追加し、アイテム選択で実行
// シートへのメニュー追加
function onOpen() {
var ui = SpreadsheetApp.getUi();
var menu = ui.createMenu('文字色変更');
menu.addItem('青⇒黒', 'changeBlueToBlack');
menu.addItem('赤⇒黒', 'changeRedToBlack');
menu.addToUi();
}
function changeBlueToBlack(){
changeTextColor( '#0000ff', '#000000');
}
function changeRedToBlack(){
changeTextColor( '#ff0000', '#000000');
}
function changeTextColor(orgColor, destColor) {
// アクティブレンジの取得
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
var range = sheet.getActiveRange();
// RichTextValue[][] を取得
var richText = range.getRichTextValues();
Logger.log( 'Sheet = [' + sheet.getSheetName() + ']');
// 1セル分ずつRunsを分解
for (var i = 0; i < richText.length; i++) {
for (var j = 0; j < richText[i].length; j++) {
// Runsを取り出し
var runs = richText[i][j].getRuns();
var runsCount = runs.length;
var nRichText = SpreadsheetApp.newRichTextValue();
if( runsCount == 0 ){
break;
}
Logger.log( 'runsCount=[' + runsCount +']\n' );
// rebuild用テキスト
var cText = '';
// 個々のRunよりText, Colorなどを取得
for( var k = 0; k<runsCount; k++){
// Textをrunsより取り出して連結
cText = cText + runs[k].getText();
}
nRichText.setText(cText);
// 個々のRunよりText, Colorなどを取得
for( var k = 0; k<runsCount; k++){
// 該当のrunが変更前の色(orgColor)だった場合、変更後の色(destColor)に変更
if( runs[k].getTextStyle().getForegroundColor() == orgColor ){
nRichText.setTextStyle( runs[k].getStartIndex(), runs[k].getStartIndex()+runs[k].getText().length,
SpreadsheetApp.newTextStyle()
.setForegroundColor(destColor)
.build()
);
}
else {
// 対象の色(orgColor以外)は元の色
nRichText.setTextStyle( runs[k].getStartIndex(), runs[k].getStartIndex()+runs[k].getText().length,
SpreadsheetApp.newTextStyle()
.setForegroundColor(runs[k].getTextStyle().getForegroundColor())
.build()
);
}
// その他のTextStyleはオリジナルから引き継ぐ
// TODO: スタイルの項目が増えると改修が必要なので、個別にsetせず、全て引き継ぐ方式が望ましい
nRichText.setTextStyle( runs[k].getStartIndex(), runs[k].getStartIndex()+runs[k].getText().length,
SpreadsheetApp.newTextStyle()
.setFontFamily(runs[k].getTextStyle().getFontFamily())
.setFontSize(runs[k].getTextStyle().getFontSize())
.setBold(runs[k].getTextStyle().isBold())
.setItalic(runs[k].getTextStyle().isItalic())
.setStrikethrough(runs[k].getTextStyle().isStrikethrough())
.setUnderline(runs[k].getTextStyle().isUnderline())
.build()
);
// debug用のログ
Logger.log( 'cell['+i+':'+j+'->Runs['+k+']] >> '+'text[' + runs[k].getText() +
'] index['+ runs[k].getStartIndex() +
'] length['+ runs[k].getText().length +
'] color['+ runs[k].getTextStyle().getForegroundColor() +']');
}
//RichTexValueをRangeの相対位置で取得したCellへ設定する
range.getCell(i+1,j+1).setRichTextValue(nRichText.build());
}
}
return;
}
Author And Source
この問題について(Google Spread Sheetのセル内文字色を一括で置換。), 我々は、より多くの情報をここで見つけました https://qiita.com/silv224/items/2e568429886cfc2fa78d著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .