将多种字体颜色应用于单个 Google 表格单元格中的文本 [英] Apply multiple font colors to the text in a single Google Sheets cell

问题描述

我正在尝试使用 Google Apps Script 中的函数将单元格格式化为具有多种字体颜色.我找不到任何关于它的文档.此外，使用 getFontColor() 不会返回任何有用的东西.

I am trying to format a cell to have multiple font colors using a function in Google Apps Script. I am unable to find any documentation on it. Also, using getFontColor() doesn't return anything useful.

有没有办法以编程方式重现此功能

Is there any way to programmatically reproduce this functionality

用户可以通过 Google 表格网络用户界面访问吗?

that is available to users via the Google Sheets web UI?

推荐答案

SheetsAPI 开始使用有点令人生畏，但允许对您的电子表格进行非常细粒度的控制.您必须启用它，因为它是高级服务".我强烈建议您查看示例代码实验室.

The Sheets API is a bit daunting to start using, but allows very fine-grained control over your spreadsheets. You'll have to enable it, as it is an "Advanced Service". I strongly recommend reviewing the Sample Codelab.

使用 Sheets API，TextFormatRun 属性可以逐个单元地进行操作.注意:

With the Sheets API, the TextFormatRun property can be manipulated on a cell-by-cell basis. Note:

应用于单元格子部分的富文本运行.运行仅对用户输入的字符串有效，对公式、布尔值或数字无效.运行从文本中的特定索引开始，一直持续到下一次运行.除非在后续运行中明确更改，否则运行的属性将继续(除非明确更改，否则第一次运行的属性将继续单元格的属性).

Runs of rich text applied to subsections of the cell. Runs are only valid on user entered strings, not formulas, bools, or numbers. Runs start at specific indexes in the text and continue until the next run. Properties of a run will continue unless explicitly changed in a subsequent run (and properties of the first run will continue the properties of the cell unless explicitly changed).

写入时，新运行将覆盖任何先前运行.写入新的 userEnteredValue 时，之前的运行将被删除.

When writing, the new runs will overwrite any prior runs. When writing a new userEnteredValue, previous runs will be erased.

本示例使用它来调整文本的绿色值，在活动单元格中的字符串长度上从 0 增加到 100%.进行调整以满足您的需求.

This example uses it to adjust the green value of text, increasing from 0 to 100% over the length of a string in the active cell. Adjust to suit your needs.

function textFormatter() {
  // Get the current cell's text.
  var wb = SpreadsheetApp.getActive(), sheet = wb.getActiveSheet();
  var cell = sheet.getActiveCell(), value = cell.getValue();
  var len = value.toString().length;
  if(len == 0) return;

  // Change the color every 2 characters.
  var newCellData = Sheets.newCellData();
  newCellData.textFormatRuns = [];
  var step = 1 / len;
  for(var c = 0; c < len; c += 2) {
    var newFmt = Sheets.newTextFormatRun();
    newFmt.startIndex = c;
    newFmt.format = Sheets.newTextFormat();
    newFmt.format.foregroundColor = Sheets.newColor();
    newFmt.format.foregroundColor.green = (c + 2) * step;
    newCellData.textFormatRuns.push(newFmt);
  }

  // Create the request object.
  var batchUpdateRQ = Sheets.newBatchUpdateSpreadsheetRequest();
  batchUpdateRQ.requests = [];
  batchUpdateRQ.requests.push(
    {
       "updateCells": {
        "rows": [ { "values": newCellData } ],
        "fields": "textFormatRuns",
        "start": {
          "sheetId": sheet.getSheetId(),
          "rowIndex": cell.getRow() - 1,
          "columnIndex": cell.getColumn() - 1
        }
      }
    }
  );
  Sheets.Spreadsheets.batchUpdate(batchUpdateRQ, wb.getId());
}

---

根据要格式化的单元格值的设置方式，可能还需要在同一请求中包含单元格的值.在问题跟踪器上查看此示例

这篇关于将多种字体颜色应用于单个 Google 表格单元格中的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！