I am trying to use getFontColor() in a conditional concatenate formula, but google sheets keeps throwing the error Unknown function: 'getFontColor'. How can this be? The function is in google's documentation.
here is the full formula:
=IF(getFontColor('DRAFT Compliance Spreadsheet'!B2)="#cc0000", CONCATENATE('DRAFT Compliance Spreadsheet'!B$1, ", "))
Paste this in your script editor and save:
function getFontColor(cell) {
var rangeName = cell.split("!");
var sheet = rangeName[0].replace(/[^a-zA-Z0-9 ]/g, "");
var range = rangeName[1].replace(/[^a-zA-Z0-9 ]/g, "");
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheet);
var fontColor = sheet.getRange(range).getFontColor();
return fontColor;
}
Then replace your formula with this:
=IF(getFontColor(CELL("address", 'DRAFT Compliance Spreadsheet'!B2))="#cc0000", CONCATENATE('DRAFT Compliance Spreadsheet'!B$1, ", "))
Using getFontColor custom function:
Using your formula:
Draft Compliance Spreadsheet sample data:
