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
that is available to users via the Google Sheets web UI?
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.
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).
When writing, the new runs will overwrite any prior runs. When writing a new userEnteredValue, previous runs will be erased.
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());
}
Edit: Depending on how the value of the cells to be formatted are set, including the value of the cell in the same request may be necessary as well. See this example on the issue tracker
updateCells request that supplies the textFormatRun information (as is done in the linked example). –
Gearldinegearshift As on July 2018, Apps-Script support changing individual text colors and other font related styles. Two methods are added to SpreadsheetApp. newTextStyle() and newRichTextValue(). The following apps-script changes such fontstyles in A1. For best effects, Use a lengthy string(30 characters or more).
function rainbow(){
var rng = SpreadsheetApp.getActiveSheet().getRange("A1");
var val = rng.getValue().toString();
var len = val.length; // length of string in A1
var rich = SpreadsheetApp.newRichTextValue(); //new RichText
rich.setText(val); //Set Text value in A1 to RichText as base
for (var i=0;i<len;i++){ //Loop through each character
var style = SpreadsheetApp.newTextStyle(); // Create a new text style for each character
var red= ("0"+Math.round((1/len)*(i)*255).toString(16)).substr(-2,2); //📈
var green= ("0"+Math.round((1/len)*Math.min(i*2,len-Math.abs(i*2-len))*255).toString(16)).substr(-2,2); //📈📉
var blue= ("0"+Math.round((1/len)*(len-i)*255).toString(16)).substr(-2,2);//📉
style.setForegroundColor("#"+red+green+blue); // hexcode
style.setFontSize(Math.max(Math.abs(len/2-i),8)); //Use a lengthy string
var buildStyle = style.build();
rich.setTextStyle(i,i+1,buildStyle); // set this text style to the current character and save it to Rich text
}
var format = rich.build()
rng.setRichTextValue(format); //Set the final RichTextValue to A1
}
Documentation is not published yet. Methods are subject to change
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.
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).
When writing, the new runs will overwrite any prior runs. When writing a new userEnteredValue, previous runs will be erased.
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());
}
Edit: Depending on how the value of the cells to be formatted are set, including the value of the cell in the same request may be necessary as well. See this example on the issue tracker
updateCells request that supplies the textFormatRun information (as is done in the linked example). –
Gearldinegearshift The function will generate text and then goes through all of the cells highlight the chosen words. So you can just run it on a blank sheet to figure how it works. It also handles multiple colors.
function highlightword() {
const red = SpreadsheetApp.newTextStyle().setForegroundColor('red').build();
const org = SpreadsheetApp.newTextStyle().setForegroundColor('orange').build();
const blu = SpreadsheetApp.newTextStyle().setForegroundColor('blue').build();
const cA = [red,org,blu];//colors array
const sA = ['Mimi id sweet litter wiener dog', 'Cooper died and we both miss him', 'Vony died to and I really miss her.', 'Someday fairly soon I will probably die.'];
const wordA = ['sweet', 'dog', 'died', 'fairly', 'little', 'and','Mimi','Cooper'];
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet0');
const rg = sh.getRange(1, 1, 10, 5);
let vs = rg.getValues();
rg.clearContent();
const dA = vs.map((r, i) => {
let row = [...Array(rg.getWidth()).keys()].map(i => sA[Math.floor(Math.random() * sA.length)]);
return row.slice();
});
rg.setValues(dA);
//End of sample text generation
SpreadsheetApp.flush();
dA.forEach((r, i) => {
r.forEach((c, j) => {
let idxObj = { pA: [] };
wordA.forEach(w => {
let idx = c.indexOf(w);
if (~idx) {
idxObj[w] = idx;
idxObj.pA.push(w);
}
});
if (idxObj.pA.length > 0) {
let cell = sh.getRange(i + 1, j + 1);
let val = SpreadsheetApp.newRichTextValue().setText(c);
idxObj.pA.forEach((p,k) => {
val.setTextStyle(idxObj[p], idxObj[p] + p.length, cA[k % cA.length]);
});
cell.setRichTextValue(val.build());
}
});
});
}
requests = [
object = {
"updateCells": {
"range": {
"sheetId": sheetId,
"startRowIndex":startRowIndex,
"endRowIndex": endRowIndex,
"startColumnIndex": startColumnIndex,
"endColumnIndex": endColumnIndex
}
"rows": [{
"values": [{
"textFormatRuns": [
{"format": {
"foregroundColor": {
"red": 0.0,
"green": 255.0,
"blue": 31.0
},
},"startIndex": 0
},
]
}
]
}]
"fields": "textFormatRuns(format)"
}
}
]
try:
result = service.spreadsheets().batchUpdate(spreadsheetId=internamiento_id,
body={'requests': requests}).execute()
print('{0} celdas actualizadas'.format(result.get('totalUpdatedCells')))
except Exception as e:
print(e)
json_salida["error"] = "Ocurrio un error "
return json_salida, 400
© 2022 - 2024 — McMap. All rights reserved.
TextFormatRunmeets your needs – Gearldinegearshift