Formatting script applicable to a range, not an entire sheet

by Vlad Pyagay   Last Updated April 15, 2019 07:03 AM - source

I have the following script, which applies formatting in row 1 to an entire spreadsheet. How can I limit the formatting application to a range (ex. A7:AN135), so that the header rows are excluded from the formatting rule and keep their colour, font, etc.

function onEdit(e){ //This is activated each time a modification happens in the sheet var tabs = [ 'G1', 'G2C', 'G2J', 'G3', 'G4', 'G5' ]; var ss = SpreadsheetApp.getActive() for (var i = 0; i < tabs.length; i++) { var sheet =ss.getActiveSheet() if(sheet.getSheetName() == tabs[i]){ var entryRange = e.range var range = sheet.getRange(1,entryRange.getColumn(),1,entryRange.getNumColumns()) //This will be you range to get the formatting from row "1" and corresponding column based on the column being edited Logger.log(entryRange.getA1Notation()) range.copyFormatToRange(sheet, entryRange.getColumn(), entryRange.getNumColumns()+entryRange.getColumn()-1, entryRange.getRow(), entryRange.getNumRows()+entryRange.getRow()-1) Logger.log(entryRange.getColumn()) if(entryRange.getColumn() == 1){ //This column value will not be allowed modified except for row 1 of that column if (entryRange.getRow() != 1){ //The columns in row "1" will be allowed to modified e.range.setValue((e.oldvalue == undefined? "": e.oldvalue)) } } } } }

Tags : formatting


Related Questions


Limit Answers on Selection

Updated July 08, 2017 11:03 AM

How to left align dollar symbol but right align number?

Updated February 16, 2018 18:03 PM