I have many cells with multiple questions and answers in one cell like A1. When I run the apps script, I want the blue text formatting to be applied only to the answer line, like B1.

The algorithm I was thinking of is as follows.

1. Cut the questions and answers based on the newline character and make a list and condition processing within loop.
2. If the first character is -, it is a question, so pass or apply black format.
3. If the first character is ┗, it is an answer, so apply blue formatting.

But I'm new to apps script and google sheet api, so I don't know which way to go. Could you please write an example?

CodePudding user response：

Try on active cell for instance

function formatCell() {
  const range = SpreadsheetApp.getActiveRange()
  // const range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange() // for the whole sheet
  const spec = { regex: /┗.*/gi, textColor: 'blue' };
  const values = range.getDisplayValues();
  let match;
  const formattedText = values.map(row => row.map(value => {
    const richText = SpreadsheetApp.newRichTextValue().setText(value);
    const format = SpreadsheetApp.newTextStyle()
      .setForegroundColor(spec.textColor)
      .build();
    while (match = spec.regex.exec(value)) {
      richText.setTextStyle(match.index, match.index   match[0].length, format);
    }
    return richText.build();
  }));
  range.setRichTextValues(formattedText);
}

reference

CodePudding user response：

Use RichTextValue to set stylized text, and apply the corresponding TextStyle in lines that start with your desired character:

function changeAnswersColor() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const textStyle = SpreadsheetApp.newTextStyle().setForegroundColor("blue").build();
  const range = sheet.getRange("A1");
  const values = range.getValues();
  const richTextValues = values.map(row => {
    return row.map(value => {
      const valueLines = value.split("\n");
      let builder = SpreadsheetApp.newRichTextValue().setText(value);
      for (let i = 0; i < valueLines.length; i  ) {
        const vl = valueLines[i];
        if (vl.trim()[0] === "┗") {
          console.log(valueLines.slice(0,i).join("\n"))
          const startOffset = valueLines.slice(0,i).join("\n").length;
          const endOffset = startOffset   vl.length   1;
          builder.setTextStyle(startOffset, endOffset, textStyle);
        }
      }
      const richTextValue = builder.build();
      return richTextValue;
    });
  });
  range.offset(0,range.getNumColumns()).setRichTextValues(richTextValues);
}