0

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))
    }
  }
  }
}
}
  • Have you made a start at this? It seems to me that the range that's changed is already determined by some simple math in the "range.copyFormatToRange(…" parameters. Where would your example "A7:AN135" range come from? Couldn't you add it to the math or hard-code it? Per SE guidelines, do please let us know what problems you encountered in your efforts. – Joel Reid Apr 18 at 17:19
  • Hi Joel, unfortunately I couldn't make it work, so, I am hoping brighter minds here could help me. The script applies to an entire spreadsheet except row1. I wanted it to apply to a range of data excluding the header rows (from 1-6, incl. the example format row1). So, ranges "A7:AN135" would contain only data to which the script would apply and all data would be formatted the same. I tried to add the range after "range.copyFormatToRange", it didn't work. I also tried to change +entryRange.getRow()-1) to "-6" for the script to exclude 6 rows, but id didn't work. I'd really appreciate any help. – Vlad Pyagay Apr 21 at 8:27
  • A note on SE usage: rather than cramped comments, please always add everything you tried, and details on what happened / why they didn't work, as edits to your question. – Joel Reid Apr 22 at 16:09
0

You need to add testing and logic to your script to match your requirements. Try the code below.

I wrote everything "the long way" to make it easier to follow—note that it could be heavily condensed. Also that I chose variable names (and even changed some of your variables' names), for clarity.

function onEdit(e) {

  var tabs = [ 'G1', 'G2C', 'G2J', 'G3', 'G4', 'G5' ],
    watchRowMin = 7, watchRowMax = 135, watchColMin = 1, watchColMax = 40,
    editRange = e.range,
    editSheet = e.range.getSheet();

  if ( !(tabs.indexOf(editSheet.getName())>-1)) {
    Logger.log("Edit was in tab ("+editSheet.getName()+") that is not in watched tab list.");
  } else {
    Logger.log("Edit was in a watched tab ("+editSheet.getName()+").");

    var editLeft = editRange.getColumn();
    var editRight = editRange.getColumn()+editRange.getNumColumns()-1;
    var editTop = editRange.getRow();
    var editBottom = editRange.getRow()+editRange.getNumRows()-1;

    if ( editBottom<watchRowMin || editTop>watchRowMax || editRight<watchColMin || editLeft>watchColMax ) {
      Logger.log("Edited range outside target range. editRange was: "+editRange.getA1Notation());
    } else {
      Logger.log("Edited range intersects target range. editRange is: "+editRange.getA1Notation());

      var targetLeft = Math.max(editLeft, watchColMin),
        targetRight = Math.min(editRight, watchColMax);
      var targetNumCols = targetRight-targetLeft+1;

      var targetTop = Math.max(editTop, watchRowMin),
        targetBottom = Math.min(editBottom, watchRowMax);
      var targetNumRows = targetBottom-targetTop+1;

      Logger.log("Target:  ⇤"+targetLeft+"   ⇥"+targetRight+"   ↔"+targetNumCols+"   ⤒"+targetTop+"   ⤓"+targetBottom+"   ↕"+targetNumRows);

      var targetRange = editSheet.getRange(targetTop, targetLeft, targetNumRows, targetNumCols);

      Logger.log("targetRange is: "+targetRange.getA1Notation());

      var formattingSourceRange = editSheet.getRange(1, targetLeft, 1, targetNumCols);
      Logger.log("formattingSourceRange is: "+formattingSourceRange.getA1Notation());

      formattingSourceRange.copyTo(targetRange, {formatOnly:true});

      Logger.log("Checking for disallowed edit anywhere in A2:A. Edit's first column: "+editRange.getColumn());
      if(editRange.getColumn() == 1){      //Col A edits prohibited except A1
        if (editRange.getRow() != 1 || editRange.getNumRows() != 1){      //A1 Edits allowed. Disallow not-row-1 or multi-row edits.
          e.range.setValue((e.oldvalue == undefined? "": e.oldvalue))
        }
      }
    }
  }
}

In English:

  • Define variables for watched tabs, the watched range, and the user edit which triggered the function.
  • If the edited sheet's name cannot be found in the tab list, stop.
  • Do your width/height math getting the bounds of the edit range.
  • If the nearest edit bounds are outside the watched range bounds, stop.
  • Create variables for the target range; the same as the edit range but constrained to the watched area, A7:AN135. For example, if the user clears AL20:AS20, AS is right of AN, so when it sets the right boundry of the target range, it finds the lower value of 45 and 40 to be 40 (column AN), so that's our new right edge.
    • Since we're using range boundaries, also do "the opposite of your math" to get back from range beginning and end, to having a target range width and height.
  • Assemble the variables into an actual range.
  • Set the range from which to pull formatting, using the target columns rather than edit columns in case trimming occurred.
  • Finally ready, do the formatting-only copy. I switched from using copyFormatToRange() to copyTo() since it accepts a simple range object parameter.
  • Lastly, do your "first column locked except first row" testing and value reversion. Note I added another test since your if conditions wouldn't catch an edit starting in the allowed A1, but extending to not-allowed lower rows.
  • A further note on SE usage (How to Ask): I answered, but frankly I only did so because you're new here & it's a fun problem. Web Apps SE (and stackoverflow) are naturally not coding services, and the right answer could have been "You have rules where the script should operate. Test user edits using if conditions to match your rules." If this was a one-off problem, all the best! But if you will be coding frequently, you'll need to get accustomed to clearly defining your requirements and making whatever effort or research is needed to achieve it, first—then coming here if you have problems. Cheers. – Joel Reid Apr 23 at 17:16
  • You are spot on, Joel. I'm very new to this and am just discovering this exciting field of scripts. Well noted all your comments and appreciate your help, advice and a very detailed explanation. Will read up on the rules for how this site works. Hopefully, one day I will be able to return the favour and help someone else. Cheers. – Vlad Pyagay Apr 23 at 17:38
  • Again, I hope you can read from it that I wrote it all in good faith. It all just works better for everyone if used that way. Good luck with your project. ••• OH! A final note on the script: scripts can account for when the user does an edit to multiple ranges (hold ctrl on Windows, command on Mac, and click multiple cells/ranges) . To see the above script fail in that regard, select 2+ ranges inside A7:AN135 and hit delete. They're all cleared, but only the first contiguous range gets the formatting copied. The fix, rangeList, is broken. – Joel Reid Apr 23 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.