I have multiple columns - G, L, Q, V, AA, AF - to which I want to apply a conditional format rule in Google sheets. Starting in row 2 - so that I don't include the header row - I want any given cell in the specified column to .setBackgroundColor ("orange") if any text or data is found to the right of that cell. For example, I want cell G2 to be orange if cell H2 has anything entered inside of it, and L17 to be orange if S17 has data, and AA5 to be orange if AD5 is not blank.
My experience with apps script is very primative. I can only successfully write very few lines of code, so my dilemma is past my ability. I understand it is possible to apply conditional formatting using sheets' built in conditional formatting tab, but it will not work for my project seeing as I am gathering data with a Google form, and with every response I receive from the form, the sheet creates a new line for the submission that retains none of the formatting from the rest of the sheet. My plan would be to add a form submission trigger to the code so that the conditional formatting in the columns updates regularly with the sheet.
I have been looking around for some time online and have not found a solution to my problem. Any help would be greatly appreciated! Thank you!
The following example shows the process of creating a new conditional format rule, applying it to the sheet. Then copying and pasting the format of a range to a target range. You can find the references for the methods used in the references section at the end of this answer.
function myFunction() {
// The range where the formatting will be applied.
var range = 'G2:G'
// Get the array containing the conditional formatting rules for the sheet
var spreadsheet = SpreadsheetApp.getActive();
var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
// Build the new conditional formatting rule and push it to the array
conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule()
.setRanges([spreadsheet.getRange(range)]) // The range to apply to
.whenFormulaSatisfied('=H2<>""') // The formula to check
.setBackground('orange') // The format to apply
.build());
// Set the conditional format rules for the sheet with the updated array
spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);
// Paste the format of the range into the next desired range
spreadsheet.getRange(range).copyTo(spreadsheet.getRange('L2:L'), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
/* repeat the previous instructions for all your target ranges replacing "L2:L" with the target range */
};