How can I dynamically construct a setRichTextValue statement?

Question

In a Google Apps Script (GAS) for Sheets, I have multiple entries in a cell. I would like to change the font to RED (defined elsewhere in the code as const red = "#db4437";) for the invalid entries. I have already captured the starting and ending character positions for the text ("txt") in question into "driveArray". "nfi" is the the last used index into driveArray. Since I could not figure out how to do it dynamically I hardcoded the first two cases with multiple 'setTextStyle' commands. I want to account for more invalid entries, do I have to hard code cases 3 & 4?

  switch (nfi) {
    case 0:
      sht.getCurrentCell()
        .setRichTextValue(SpreadsheetApp.newRichTextValue()
          .setText(txt)
          .setTextStyle(driveArray[0][0],driveArray[0][1], SpreadsheetApp.newTextStyle()
            .setForegroundColor(red)
            .build()
            )
          .build()
        );
      break;
    case 1:
      sht.getCurrentCell()
        .setRichTextValue(SpreadsheetApp.newRichTextValue()
          .setText(txt)
          .setTextStyle(driveArray[0][0],driveArray[0][1], SpreadsheetApp.newTextStyle()
            .setForegroundColor(red)
            .build()
            )
          .setTextStyle(driveArray[1][0],driveArray[1][1], SpreadsheetApp.newTextStyle()
            .setForegroundColor(red)
            .build()
            )
          .build()
        );
      break;
  }

See https://stackoverflow.com/questions/9329446/loop-over-an-array-in-javascript — TheMaster, Nov 01 '22 at 15:50

ValLeNain · Answer 1 · 2022-11-01T17:02:40.637

1

Something like that ?

I take nfi as the upper limit for a loop. For each iteration, I set the foreground red, of the appropriate part of the cell.

function colorCell(sht, nfi, txt, driveArray, red) {
  let richTextValue = SpreadsheetApp.newRichTextValue().setText(txt)
  
  for(let i = 0; i <= nfi; i++) {
    richTextValue.setTextStyle(
      driveArray[i][0],driveArray[i][1], 
      SpreadsheetApp.newTextStyle()
        .setForegroundColor(red)
        .build()
    )
  }
   
  sht.getCurrentCell().setRichTextValue(richTextValue.build());
}

(You don't have to write a separate function for that but I think it's easier to read that way)

edited Nov 01 '22 at 17:02

answered Nov 01 '22 at 16:26

ValLeNain

2,232
1
18
37

While this code snippet may be the solution, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-‌code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – TheMaster Nov 01 '22 at 16:45
1

you're right, I'll edit it – ValLeNain Nov 01 '22 at 17:00

How can I dynamically construct a setRichTextValue statement?

1 Answers1