Google Sheets script - add borders across a row for multiple sheets-CodePudding

I have a series of tabs with account balances. All of them with the same 7 header rows. Starting at row 8, I want all rows with content in the "B" column (dates) to have a border. I found some helpful script that works (see link) as long as I specify the name of the sheet: https://docs.google.com/spreadsheets/d/1v5w3Sd4BWubJVliZz03oqfnYIq2UkoufD_-cDtAcWkE/edit?usp=sharing

But I now want to modify the script so that it works on every tab in the workbook (even tabs that have not been created) no matter the name. Again, in all tabs rows 1-7 will be the same.

Here is the script that corresponds to the link above:

function onEdit() {
   GroupMyData(); // trigger this function when edits are made
}

function GroupMyData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Fam Lobo Murillo'); // apply to sheet name only
  var rows = sheet.getRange('b8:J'); // range to apply formatting to
  var numRows = rows.getNumRows(); // no. of rows in the range named above
  var values = rows.getValues(); // array of values in the range named above
  var testvalues = sheet.getRange('j1:j').getValues(); // array of values to be tested (1st column of the range named above)

  rows.setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID); // remove existing borders before applying rule below
      //Logger.log(numRows);

  for (var i = 0; i <= numRows - 1; i  ) {
      var n = i   1;
      //Logger.log(n);
      //Logger.log(testvalues[i] > 0);
      //Logger.log(testvalues[i]);
      if (testvalues[i] > 0) { // test applied to array of values
        sheet.getRange('b'   n   ':j'   n).setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID); // format if true
      }
  }
};

CodePudding user response：

I think the best way to accomplish what you're trying to do is:

function onEdit(e) {

  if (e.range.rowStart >= 8 && e.range.columnStart === 2) {

    const sheet = e.source.getActiveSheet()
    sheet.getRange(e.range.rowStart, 2, 1, sheet.getLastColumn()-1)
            .setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID)

  }

}

This will replace your current provided code. This will add a border around any row, starting at Row 8, that you edit in the B column.

Currently your script works that after every edit, it removes all borders and re-adds them. The code I've provided will strictly run when something is entered in the B Column of Rows 8 .

CodePudding user response：

Try it this way:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (e.range.rowStart >= 8 && e.range.columnStart == 2) {
    sh.getRange(e.range.rowStart, 2, e.range.rowEnd - e.range.rowStart   1, sh.getLastColumn() - 1).setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID)
  }
}