We have a complex suite of Google Sheets that use about 300 conditional formatting rules. When it's set up it works beautifully, BUT the users want to take shortcuts and copy and paste from various columns and that takes (and breaks) the conditional formatting rules :-(
I have been trying to develop a Google apps script that extracts the conditional formatting from a sheet in a format that can be stored and reapplied on the same sheet at a later date. I have used both Bard and ChatGPT to assist but the code they give is broken. :-( Surprisingly the code they generated was almost identical. ;-)
This is as close as I can get...
/*
To use this script, simply call the extractConditionalFormatting() function with the sheet that you want to extract the conditional formatting from as a parameter. The script will then extract the conditional formatting from the sheet and store it in a hidden range on the sheet.
To reapply the conditional formatting at a later date, simply call the reapplyConditionalFormatting() function with the sheet that you want to reapply the conditional formatting to as a parameter. The script will then read the extracted conditional formatting rules from the hidden range on the sheet and apply them to the sheet.
For example, to extract the conditional formatting from the sheet "Sheet1" and store it, you would use the following code:
extractConditionalFormatting(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"));
To reapply the conditional formatting to the sheet "Sheet1" at a later date, you would use the following code:
reapplyConditionalFormatting(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"));
You can also use these scripts to extract and reuse conditional formatting on different sheets within the same spreadsheet, or even on different spreadsheets.
*/
function extractConditionalFormatting() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1"); // change name to correct sheet
// Get the conditional formatting rules from the sheet.
var conditionalFormattingRules = sheet.getConditionalFormatRules();
// Create an empty array to store the extracted conditional formatting rules.
var extractedConditionalFormattingRules = [];
// Iterate over the conditional formatting rules and extract them.
for (var i = 0; i < conditionalFormattingRules.length; i++) {
var conditionalFormattingRule = conditionalFormattingRules[i];
// Extract the range of the conditional formatting rule.
var range = conditionalFormattingRule.getRanges().map(function(cellRange) {
return cellRange.getA1Notation();
});
// Extract the condition of the conditional formatting rule.
var condition = conditionalFormattingRule.getCondition();
// Extract the formatting of the conditional formatting rule.
var formatting = conditionalFormattingRule.getFormatting();
// Create an object to store the extracted conditional formatting rule.
var extractedRule = {
range: range,
condition: condition,
formatting: formatting
};
// Add the extracted conditional formatting rule to the array of extracted conditional formatting rules.
extractedConditionalFormattingRules.push(extractedRule);
}
// Store the extracted conditional formatting rules as JSON in a custom property of the sheet.
sheet.getRange("A1").setValue(JSON.stringify(extractedConditionalFormattingRules));
}
function applyConditionalFormatting() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1"); // Change to the name of your sheet
// Get the stored conditional formatting rules JSON.
var jsonRules = sheet.getRange("A1").getValue();
if (jsonRules) {
// Parse the JSON to retrieve the conditional formatting rules.
var conditionalFormattingRules = JSON.parse(jsonRules);
// Remove existing conditional formatting rules from the sheet.
sheet.clearConditionalFormatRules();
// Apply the extracted conditional formatting rules to the sheet.
for (var i = 0; i < conditionalFormattingRules.length; i++) {
var rule = conditionalFormattingRules[i];
var range = sheet.getRange(rule.range);
var condition = rule.condition;
var formatting = rule.formatting;
// Apply the conditional formatting rule.
var newRule = range.setConditionalFormatRules([SpreadsheetApp.newConditionalFormatRule()
.withCriteria(condition.getCriteriaType(), condition.getCriteriaValues())
.setBackground(formatting.backgroundColor)
.setFontColor(formatting.textStyle.foregroundColor)
.setRanges([range])
.build()
]);
// Apply the rule to the sheet.
var rules = sheet.getConditionalFormatRules();
rules.push(newRule);
sheet.setConditionalFormatRules(rules);
}
}
}
It breaks in the extractConditionalFormatting function at:
// Extract the condition of the conditional formatting rule.
var condition = conditionalFormattingRule.getCondition();
saying that conditionalFormattingRule.getCondition is not a function.
Any ideas on where to go from here?
I'm worried that in the current stage, SpreadsheetApp.ConditionalFormatRule has no methods of getCondition() and .getFormatting(). I think that this might be the reason for your current issue of saying that conditionalFormattingRule.getCondition is not a function..
When I saw your question, I thought that when Sheets API is used, the conditionalFormatRules might be able to be saved as JSON data, and also, the saved JSON data might be able to be simply used.
When this is reflected in your situation, how about the following sample script?
In this script, Sheets API is used. So, please enable Sheets API at Advanced Google services.
function extractConditionalFormatting() {
var sheetName = "Sheet1"; // Please set your sheet name.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var { sheets: [{ conditionalFormats }] } = Sheets.Spreadsheets.get(ss.getId(), { ranges: [sheetName], fields: "sheets(conditionalFormats)" });
ss.getSheetByName(sheetName).getRange("A1").setValue(JSON.stringify(conditionalFormats));
}
function applyConditionalFormatting() {
var sheetName = "Sheet1"; // Please set your sheet name.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
var sheetId = sheet.getSheetId();
var value = sheet.getRange("A1").getDisplayValue();
if (!value) return;
var obj = JSON.parse(value);
var requests = [
...[...Array(sheet.getConditionalFormatRules().length)].map(_ => ({ deleteConditionalFormatRule: { sheetId } })),
...obj.map((rule, index) => {
rule.ranges.forEach(o => o.sheetId = sheetId);
return { addConditionalFormatRule: { rule, index } };
})
];
if (requests.length == 0) return;
Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}
When you run extractConditionalFormatting, the conditionalFormatRules are retrieved from sheetName and the retrieved JSON data is put into "A1".
When you run applyConditionalFormatting, the JSON data is retrieved from "A1". And, the conditionalFormatRules are set to sheetName.
In this sample script, when applyConditionalFormatting is run, the existing conditionalFormatRules are removed and the conditionalFormatRules retrieved from cell "A1" are set. So, I would like to recommend testing this script using a sample Spreadsheet including the conditionalFormatRules.
About the following reply,
This is great and works on small sheets, but on my big sheet I get Error Your input contains more than the maximum of 50000 characters in a single cell.. I will have to work out how to chunk it into pieces.
In that case, as another direction, how about storing the object in a text file? When this is reflected in the above script, it becomes as follows. In this case, the text file is put into the same folder of the Spreadsheet. And, the file is retrieved using the filename.
function extractConditionalFormatting() {
var sheetName = "Sheet1"; // Please set your sheet name.
var filename = "sample.txt"; // Please set your filename of the file inlcluding the object.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var { sheets: [{ conditionalFormats }] } = Sheets.Spreadsheets.get(ssId, { ranges: [sheetName], fields: "sheets(conditionalFormats)" });
var folder = DriveApp.getFileById(ssId).getParents().next()
var files = folder.getFilesByName(filename);
var file = files.hasNext() ? files.next() : folder.createFile(filename, "");
file.setContent(JSON.stringify(conditionalFormats));
}
function applyConditionalFormatting() {
var sheetName = "Sheet1"; // Please set your sheet name.
var filename = "sample.txt"; // Please set your filename of the file inlcluding the object.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var sheet = ss.getSheetByName(sheetName);
var sheetId = sheet.getSheetId();
var value = sheet.getRange("A1").getDisplayValue();
var folder = DriveApp.getFileById(ssId).getParents().next()
var files = folder.getFilesByName(filename);
if (!files.hasNext()) return;
var value = files.next().getBlob().getDataAsString();
if (!value) return;
var obj = JSON.parse(value);
var requests = [
...[...Array(sheet.getConditionalFormatRules().length)].map(_ => ({ deleteConditionalFormatRule: { sheetId } })),
...obj.map((rule, index) => {
rule.ranges.forEach(o => o.sheetId = sheetId);
return { addConditionalFormatRule: { rule, index } };
})
];
if (requests.length == 0) return;
Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
}