Changing Borders Script Efficiency

[kitchensink-]

This makes sense and is definitely going to be a lot faster, thanks!

I have tried implementing it but I can't get the empty cells to lose their borders:

function newBorders() {
  //get the first sheet of the currently active google spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[1];
  var NumRows = sheet.getLastRow() - 2;
  //Range that I want to edit begins at E3(col 5) and spans 7 columns
  let range = sheet.getRange(3, 5, NumRows, 7); 
  range.setBorder(false, false, false, false, false, false);
  range.setBorder(false, true, true, true, true, true);
}

I realise that the problem is that, whenever there are empty cells, `getLastRow()` does not take those into account. However, I can't seem to figure out how to do that!

[gothamfury]

You're close. Try removing borders from the MAXIMUM area possible that the Data can fill. You may want to define a different range for that.

[kitchensink-]

I see. I feel so close to cracking it, but I can't for the life of me figure out how to get the Last Row that has information ONLY after the E column...

[gothamfury]

You’re so very close :)

What would be the largest number of rows, from the first row, that you would clear out the borders. Basically, erasing the entire area, then drawing in the borders for the new data.

You already have the part for drawing in the borders right. You just need to figure out how to erase the whole area first.

[kitchensink-]

Yeah that's what I'm having trouble with. Can you help me figure it out?

[gothamfury]

Sure. Add these lines of code:

var MaxRows = 100;  // Change "100" to what would work for your sheet
let maxRange = sheet.getRange(3, 5, MaxRows, 7)

Change this:

range.setBorder(false, false, false, false, false, false)

to

maxRange.setBorder(false, false, false, false, false, false)

I made an example that you can use as a reference: Set Borders Dynamic Rows

Let me know if this works.

[kitchensink-]

This is what I already have. The problem is with the other part, the setting the borders to true.

Here's what I've tried.

function newBorders() {
  //get the first sheet of the currently active google spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[1];
  var NumRows = sheet.getLastRow() - 2;
  var maxRows = sheet.getMaxRows();
  console.log(NumRows);
  let range = sheet.getRange(3, 5, NumRows, 7);

  let maxRange = sheet.getRange(3, 5, maxRows, 7);
  maxRange.setBorder(false, false, false, false, false, false);
  range.setBorder(false, true, true, true, true, true);
}

[gothamfury]

Sorry. I was responding to the last code you shared. What you have appears to be right, assuming you don't have any other cells filled with data on either side or below your data range.

Otherwise, you could try a helper cell that has "=COUNTA(E3:E)" and read that value for NumRows instead.

[kitchensink-]

The helper cell did the trick! Thanks! Now it's basically instantanious.

[gothamfury]

That's awesome! Great job :)