スプレッドシートでやりたいこと
A1セルからD1セルに横並びで入力されているデータを、
別シートのA1セル内に結合させて表示したい。
しかも、各セルの文字色もそのまま反映させたい。
着手にあたっての課題
リッチテキストの存在を知らなかったため、
以下の問題点を解決する術を探すところから始めました。
①getValue()とsetValue()ではセル内のテキストしかコピペできない
②setBackground()では、セル内の文字色が全て変更されてしまう
解決策
リッチテキストを使い、各セルの文字色を一文字ずつ抽出する。
文字列を結合させて、文字色を設定してからリッチテキスト形式で出力する。
※シート名は[テスト1]と[テスト2]です。
main.gs
//アクティブなスプレットシート
const SPREAD_SHEET = SpreadsheetApp.getActiveSpreadsheet();
//値を入力するシート名
const SHEET_NAME_TEST1 = "テスト1";
//値を出力するシート名
const SHEET_NAME_TEST2 = "テスト2";
// グレー(グレーは黒に変換する)
const GRAY = "#222222";
// 黒
const BLACK = "#000000";
/**
* テストシート2に、各セルの文字と色をマージした書式付き文字列を出力する
*/
function Main() {
//テスト1シートからリッチテキストを取得
var test1RichTextValues = getTest1RichTextValues();
var richTextObj = cerateRichTextObj(test1RichTextValues);
//テスト2シートに出力する
setTest2RichTextValues(richTextObj);
}
/**
* 指定のシートオブジェクトを取得します。
* @param: シート名
* return: シートオブジェクト
*/
function getSheet(sheet) {
return SPREAD_SHEET.getSheetByName(sheet);
}
/**
* テストシート1から値をリッチテキストで取得する
*/
function getTest1RichTextValues() {
//テスト1のシートオブジェクトを取得
const sheetNameTest1Obj = getSheet(SHEET_NAME_TEST1);
//A1~D1をリッチテキストで取得
return sheetNameTest1Obj.getRange(1, 1, 1, 4).getRichTextValues();
}
/**
* リッチテキストから文字列のみ取り出し、結合する。
* @param: リッチテキストの2次元配列
* return: リッチテキストオブジェクト
*/
function cerateRichTextObj(richTexts) {
//文字色を入れるリストを用意する
var colorInfoList = new Array();
var text = "";
var planeText = "";
var count = 0;
//1文字ずつ文字色をチェックして配列に格納していく
for (richText of richTexts.flat()) {
//リッチテキストからプレーンテキストを取り出す
planeText = richText.getText();
text = text + planeText;
//プレーンテキストが空の場合は実行しない(buildでエラーになる)
if (planeText != "") {
richText.getRuns().forEach(run => {
var startOffset = run.getStartIndex();
var endOffset = run.getEndIndex();
var color = richText.getTextStyle(startOffset, endOffset).getForegroundColor();
//文字色がグレーの場合、黒に変換する(不要であれば削除)
if (color == GRAY) {
color = BLACK;
}
colorInfoList.push([startOffset + count, endOffset + count, color]);
});
//次の文字色変更の開始位置調整のため、カウンターを進める
count = count + planeText.length;
}
}
//リッチテキスト作りますよ宣言
const ricthTextObj = SpreadsheetApp.newRichTextValue();
//リッチテキストオブジェクトにテキストをセットする
ricthTextObj.setText(text);
//文字色を反映させる
for (colorInfo of colorInfoList) {
// Logger.log(colorInfo)
ricthTextObj.setTextStyle(colorInfo[0], colorInfo[1], SpreadsheetApp.newTextStyle().setForegroundColor(colorInfo[2]).build());
}
return ricthTextObj.build();
}
/**
* テストシート2にリッチテキストをセットする
* @param リッチテキスト
*/
function setTest2RichTextValues(ricthTextObj) {
//テスト2のシートオブジェクトを取得
const sheetNameTest2Obj = getSheet(SHEET_NAME_TEST2);
//A1セルに出力(1つのセルに出力するのでsetRichTextValueを使用する)
sheetNameTest2Obj.getRange("A1").setRichTextValue(ricthTextObj);
}
これで、1行目に入力された文字と文字色を結合して、1つのセルに出力できました!
文字色を[開始位置,終了位置,文字色]の配列でまとめ、
ループして結合した文字列に反映させています。
業務で需要があったため作りましたが、1つのセルの文字色をそのまま他のシートに移すよりは、かなり複雑な処理になっています。
列verは作っていませんので悪しからず...