Is it possible to reverse engineer Custom Formatting rules that I entered by hand and create an App Script in return?
I would like to generate something like this
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenNumberBetween(1, 10)
.setBackground("#FF0000")
.setRanges([range])
.build();
In your situation, I thought that when Sheets API is used, the script can be simply created.
Because when Sheets API is used, each setting of conditional format rules can be retrieved as an object and it can be set using the object. On the other hand, when Spreadsheet service (SpreasheetApp) is used, each method for settings is required to be prepared. In this case, I thought that the script might be a bit complexity.
So, in this answer, as one direction for achieving your goal, I would like to propose to use Sheets API with Google Apps Script.
This sample script retrieve the conditional format rules from the specific sheet in Google Spreadsheet. Before you use this script, please enable Sheets API at Advanced Google services.
function myFunction1() {
const srcSpreadsheetId = "###"; // Please set the source Spreadsheet ID.
const srcSheetId = "0"; // Please set the source sheet ID.
const obj = Sheets.Spreadsheets.get(srcSpreadsheetId, {fields: "sheets(conditionalFormats,properties)"});
const rules = obj.sheets.reduce((ar, s) => {
if (s.properties.sheetId == srcSheetId && s.conditionalFormats) ar = ar.concat(s.conditionalFormats);
return ar;
}, []);
console.log(JSON.stringify(rules)); // You can see all rules of conditional format rules in the specific sheet of the Spreadsheet.
}
This sample script set the retrieved conditional format rules by above sample script to the specific sheet in a Google Spreadsheet.
function myFunction2() {
const rules = [,,,]; // Please set the retrieved conditional format rules.
if (rules.length > 0) {
const dstSpreadsheetId = "###"; // Please set the source Spreadsheet ID.
const dstSheetId = "0"; // Please set the source sheet ID.
const requests = rules.map((rule, i) => {
rule.ranges.forEach(r => r.sheetId = dstSheetId);
return {addConditionalFormatRule: {index: i, rule: rule}};
});
Sheets.Spreadsheets.batchUpdate({requests: requests}, dstSpreadsheetId);
}
}
