Google App Script(GAS)を用いて、Googleスプレッドシート上のセル内の一部のみの文字色変える方法

Question

###前提・実現したいこと

上の画像のように、パセリだったら緑、トマトだったら赤、バナナだったら黄のようにセル内の一部の色を入力規則で変えたいです。
リストを作成して、参照したとしても実際は画像右のように色を参照することができません。

そこでGoogle App Scriptで”パセリ”がセル内にあったら、4文字目を緑（JISコード）にするというスクリプトを作成しようという考えにいたったのですが、上手くいかなかったので質問させていただきます。
入力規則のドロップダウン内では色がついてなくてもいいので、選んでセルに入力されたら該当部に色が付けばいいなと思ったのですが、叶わずでした...
どうか有識者のお力をお借りしたいです。m(_ _)m

###該当のソースコード

Sub mojiiro()
Dim myRng As Range
Dim myStr As String
Dim i As Integer
For Each myRng In Selection
With myRng.Font
.ColorIndex = xlColorIndexAutomatic
.Size = 11
End With
’----セルの文字列を1文字ずつ順番に調べる
For i = 1 To Len(myRng)
myStr = Mid(myRng.Value, i, 1)
'----数字があったら書式を変更する
If myStr Like "[0-9]" Then
With myRng.Characters(Start:=i, Length:=1).Font
.ColorIndex = 3
.Size = 14
End With
End If
Next i
Next myRng
End Sub

Sub test01()
Dim cl As Range
For Each cl In Range("a1:c6")
r = InStr(cl, "eee")
If r <> 0 Then
cl.Characters(r, 3).Font.Color = vbRed
End If
Next
End Sub
###試したこと

EXCELのほうだとVBAで似たようなことができることは分かったのですが、Googleスプレッドシートではなかなか調べても出てきませんでした(T_T)
上記のコードは調べてていて使えそうだったものを載せているだけなのであまり気にしないでください...

Answer 1

GoogleAppsScript で文字に色を付けるメソッドは Range.setFontColor ですが、セル単位での色変更となるため、セル内の一部文字色変更はできません。

そこで、値を入力したタイミングで「入力範囲に指定しているセル」を「入力中のセル」に上書きする、という方法で実現してみました。
私は添付画像のように A1～A3 を入力規則リストに、B列を入力列としてますので、質問者さんの環境に応じて、サンプルコードの頭の方にある const の箇所を書き換えて試してみてください。

function onEdit(event) {
  const INPUT_SHEET_NAME = "シート1"  // 入力シート名
//  const INPUT_COLUMN = 2;       // 入力セルの列位置
  const INPUT_COLUMNS = [2,3];       // 入力セルの列位置（複数列）
  const INPUT_RANGE = "A1:A3";       // 入力規則にしている範囲
  
  // 処理中のシート、範囲、値を取得
  var editSheet = event.source.getActiveSheet();
  // Event.range をそのまま上書きしようとすると実行時エラーとなるあるため再取得
  var editRange = editSheet.getRange(event.range.getRow(), event.range.getColumn());
  var newValue  = editRange.getValue();
  
  // onEdit イベントは全シート全セルの編集で起動されるため、
  //「シート1」「入力可能列」かつ「入力値あり」の場合のみ後続処理
  if ( editSheet.getName() != INPUT_SHEET_NAME ) return;
//  if ( editRange.getColumn() != INPUT_COLUMN ) return;
  if ( INPUT_COLUMNS.indexOf(editRange.getColumn()) == -1) return;  // 入力可能列の複数に対応
  if ( newValue == "") return;
  
  // 入力値に対応した一部文字色付きセルで入力セルを上書きする
  var inputRange = editSheet.getRange(INPUT_RANGE);
  loop1:
  for (var row = 1; row <= inputRange.getNumRows(); row++) {
    for (var col = 1; col <= inputRange.getNumColumns(); col++) {
      var cell = inputRange.getCell(row, col);
      // 入力セルを上書きしてループ終了
      if (cell.getValue() == newValue) {        
        cell.copyTo(editRange);
        break loop1;
      }
    }
  }

  // 上書きにより入力規則が消えてしまうため、入力規則を再作成する
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(inputRange).build();
  editRange.setDataValidation(rule);     
  
}

Comments

[nikukyu]

返事が大変遅れて申し訳ありません...
解答ありがとうございますm(_ _)m
まさに求めていたものです！！
本当にありがとうございます。
そのような見方もできるのですね！奥が深いです！！

// 処理中のシート、範囲、値を取得
var editSheet = event.source.getActiveSheet();
ここの部分で実行エラー
TypeError: undefined からプロパティ「source」を読み取れません。（行 7、ファイル「コード」）
が出てしまうのですが、なぜでしょうか...
コピペして同条件下で行ったのですが、入力リストも表示されませんでした(T_T)

[rokuni]

おそらくですが、onEdit() を直接実行しているのではないでしょうか？
onEdit() は直接実行するものではなく、「値を編集した時に呼び出されるメソッド」です。
私の例で言うとB列を編集した時に自動的に動作しますので、その手順で動作確認してみてください。

[nikukyu]

してました....
ありがとうございますm(_ _)m
動作確認できました！

また機会がありましたらお願い致します。

[nikukyu]

すみません、入力セルの行位置を複数にしたいのですが、どうすればいいか教えていただきたいです。
新たに質問を作ったほうがいいのでしたら、そちらのほうで回答リクエストさせていただきますm(_ _)m
先日教えていただいたもので、自分の環境のものに合わせてみたのですが、複数行で上記の機能を反映させれず、つまずいています...

function onEdit(event) {
const INPUT_SHEET_NAME = "シート1" // 入力シート名
const INPUT_ROW = 2; // 入力セルの行位置
const INPUT_RANGE = "A1:H1"; // 入力規則にしている範囲

// 処理中のシート、範囲、値を取得
var editSheet = event.source.getActiveSheet();
// Event.range をそのまま上書きしようとすると実行時エラーとなるあるため再取得
var editRange = editSheet.getRange(event.range.getRow(), event.range.getColumn());
var newValue = editRange.getValue();

// onEdit イベントは全シート全セルの編集で起動されるため、
//「シート1」「２行」かつ「入力値あり」の場合のみ後続処理
if ( editSheet.getName() != INPUT_SHEET_NAME ) return;
if ( editRange.getRow() != INPUT_ROW ) return;
if ( newValue == "") return;

// 入力値に対応した一部文字色付きセルで入力セルを上書きする
var inputRange = editSheet.getRange(INPUT_RANGE);
loop1:
for (var row = 1; row <= inputRange.getNumRows(); row++) {
for (var col = 1; col <= inputRange.getNumColumns(); col++) {
var cell = inputRange.getCell(row, col);
// 入力セルを上書きしてループ終了
if (cell.getValue() == newValue) {
cell.copyTo(editRange);
break loop1;
}
}
}

// 上書きにより入力規則が消えてしまうため、入力規則を再作成する
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(inputRange).build();
editRange.setDataValidation(rule);

}

[rokuni]

入力可能列を複数列にできるよう、プログラムを修正しました。
INPUT_COLUMN ⇒ INPUT_COLUMNS に変更し、条件判定時に、単純な == ではなく、 indexOf で INPUT_COLUMNS に存在しているかどうか判定するようにしました。
（修正箇所は、私の回答の「編集」から確認できます）

[nikukyu]

すみません、通知がこなかったため確認が遅れましたm(_ _)m
修正ありがとうございました！
無事作りたかったものができました(*^^*)
本当に助かりました。

Answer 2

GASを使わなくても「条件付き書式」というものを使えばできます。

【条件付き書式｜Googleスプレッドシート入門】
http://excel-ubara.com/spreadsheet1/spreadsheet008.html

Comments

[nikukyu]

セル内すべての文字ではなく、セル内の一部の文字のみ変えたいという質問になりますm(_ _)m
分かりづらくて申し訳ありません...

[kei344]

質問文を読みとれていませんでした、すみません。