Aaron Smith
Aaron Smith

Reputation: 37

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

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
      }
  }
};

Upvotes: 1

Views: 1002

Answers (2)

NEWAZA
NEWAZA

Reputation: 1620

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()

    const numOfRows = (e.range.rowStart !== e.range.rowEnd) ? e.range.rowEnd-e.range.rowStart+1 : 1

    const wasRemoved = sheet.getRange(e.range.rowStart, 2, numOfRows, sheet.getLastColumn()-1)
                                .getValues()
                                .every(row => row.every(col => col === ``))

    if (wasRemoved) {

      const dataAbove = sheet.getRange(e.range.rowStart-1, 2, 1, sheet.getLastColumn()-1)
                             .getDisplayValues()
                             .flat()
                             .some(cell => cell !== ``)
      const dataBelow = sheet.getRange(e.range.rowEnd+1, 2, 1, sheet.getLastColumn()-1)
                             .getDisplayValues()
                             .flat()
                             .some(cell => cell !== ``)

      sheet.getRange(e.range.rowStart, 2, numOfRows, sheet.getLastColumn()-1)
           .setBorder(dataAbove, false, dataBelow, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID)
    
    } else {

      sheet.getRange(e.range.rowStart, 2, numOfRows, 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 that starts at Row 8+, as well as remove borders when deleted.

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 edited within the specified range.

Upvotes: 1

Cooper
Cooper

Reputation: 64120

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)
  }
}

Upvotes: 1

Related Questions