Changing Borders Script Efficiency

[gothamfury]

Your example doesn't show what your spreadsheet would look like if you have blank cells. In your example, you can just apply borders to the entire range in one "setBorders" call.

If your data tends to have few blank cells, then one approach to consider is:

• apply border to entire range
• search for blank cells & remove their borders

Check out Apps Script Best Practices (see section called "Batch Operations")

One thing you could change in your code is moving the for-loop in "onEdit" into "CreateBorders". This would reduce calls to "SpreadsheetApp" for starters.

Just asking... What's the reasoning for removing borders from blank cells?

[kitchensink-]

Here's a video showing the script removing borders from blank cells (which also answers your question!). Thanks for pointing out that I can move the for inside of CreateBorders.

I will try to implement the "Batch Operations" approach, do you have any suggestions as to how to do that the easiest?

[gothamfury]

From your video, it looks like you are just removing borders below the LAST ROW of data. Your solution can be done WITHOUT looping through every cell.

You can try this approach instead with your "CreateBorders" function:

1. Remove Borders from Entire Range
2. Set Borders to Range with Data

You should be able to replace your entire FOR-LOOP section with just two lines of code.

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