So…I’ve built a custom function that processes values from a Gsheet and return them in a single cell. In brief, the source sheet has one column containing places, one containing services rendered at these places and another one containing dates of conclusion. The custom function gathers all the services rendered at the same place on a determined date. It is already working and almost giving me the expected return.
Returned string:
-> Place1:
– Service1;
– Service2.
-> Place2:
– Service3;
– Service4.
Desired return:
-> Place1:
– Service1;
– Service2.
-> Place2:
– Service3;
– Service4.
I’ve already searched for RichTextValue usage, but I don’t know if it is because I’m using as a return of a custom function or it is me that does not know how to use it correctly, but it’s not working. Here’s a sample code that I’m using to test the function:
function test(){
var bold=SpreadsheetApp.newTextStyle().setBold(true).build();
var test=SpreadsheetApp.newRichTextValue().setText("test").setTextStyle(0,2,bold).build().getText();
return test;
}
It’s kind of obvious, one would say “getText()” returns the string contained in the RichTextValue, so it won’t return a RichText, I know. So I tried “getRuns().join()” (joined because getRuns returns array) after “build()” and I get this result: “com.google.apps.maestro.server.beans.trix.impl.RichTextValueApiAdapter@2d54d6e,com.google.apps.maestro.server.beans.trix.impl.RichTextValueApiAdapter@4c67374a”.
So my question is, Is there a “getRichText()” as a return for my custom function or any other way to do this?
Thanks in advance.
1
It is actually NOT possible to return formatted data on custom functions, only raw values.
You can work with conditional formatting or make a custom menu or button so ou can run a function that builds the values and its formats.
Something like this:
// on its startup (Simple trigger) it runs the onOpen function that creates a custom menu and adds a menu item that when clicked runs the function "appendValuestoSheet"
function onOpen(){
SpreadsheetApp.getUi()
.createMenu("My Custom Menu")
.addItem("Calculate Values", "appendValuestoSheet")
.addToUi();
}
function appendValuestoSheet(){
var ss = SpreadsheetApp.getActiveSpreadsheet(); // get the current open sheet
var fontWeights = []; // empty array to be filled with formatting strings (Its size will be based on the newData array size
var newData = [
["Place1", "Service1", "Service2"],
["Place1", "Service1", "Service2"]
];
// iterate the new values to add to sheet
newData.forEach(function(row,rowIndex){
var newFontWeightsRow, fontWeight;
// Set bold format only to the first row
if(rowIndex === 0){
fontWeight = "bold";
} else {
fontWeight = "normal";
}
// buids a new row with the formats, in this case only the first row will be bold;
newFontWeightsRow = row.map(function(){return fontWeight});
// push the new formats row to the array;
fontWeights.push(newFontWeightsRow);
});
// "fontWeights" array has the same size as "newData" array
ss.getSheetByName("newDataTab")
.getRange(1, 1, newData.length, newData[0].length) // adds data starting on the first row and col;
.setFontWeights(fontWeights) // sets the new formats to the range
.setValues(newData); // sets the new data to the range
}
2