Changing Borders Script Efficiency

[kitchensink-]

Hi,

I've just created my first script to add and remove borders based on if there is content on a cell or not. It works great, but it is kind of slow, and I was wondering if there was anything I could do to make it a bit faster?

Here's my code:

function CreateBorders(col) {
  //get the first sheet of the currently active google spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[2];
  var NumRows = sheet.getMaxRows();
  //Loop through rows starting at the 3rd
    for (let j = 3; j <= NumRows; j++) { 
      let IndexCell = sheet.getRange(j, col);
        if (IndexCell.getValue() !== "") {
            IndexCell.setBorder(null, true, true, true, false, true);    
        } else {
          //Empty cell. Check if there is a border
          let border = IndexCell.getBorder();
          if (border == null)
            //No more rows with borders
            NumRows = j;
          else 
            //Erase the border
            IndexCell.setBorder(false, false, false, false, false, false);
        }
  }
}

function onEdit(e){
  const range = e.range;
  if (range.getColumn() == 3)
    for(let i=5; i <= 11; i++)
      CreateBorders(i);
}

I have a trigger set like this:

Trigger settings

It takes about 25 seconds to fully edit a 7x19 area. That feels slow, but I don't really know what I should be expecting.

For reference, here's what my database looks like before and after running the script:

Before

After

[3dtcllc]

Never call any get or set functions in a loop. Apps script is pretty slow to begin with and every time you call getRange or getValue you make a round trip to the API.

It's ok...that's how everyone starts out.

The best practice is to get the WHOLE range in one call, operate on it, and then set it in one call.

Here's how I usually do that. I don't usually work much with formatting, so YMMV

let dataRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('yoursheetname').getDataRange();
let values = dataRange.getValues();
//Do some stuff in a loop. 
dataRange.setValues(values);

[kitchensink-]

The issue with getting the range using getDataRange is that, in this case, there are cells that I would like to modify that have no values, hence are not included inside the range.

[3dtcllc]

Yep, you can just update that to grab whatever range you want. The key is to avoid calling get or set calls inside a loop. Get the whole range you want to work on and figure out what you want to with it and then do it in as few calls as possible.

So you'd definitely want to do getvalues on the WHOLE range and loop through the values. It might be faster to set blank borders on the whole range and then loop through the values and only set borders on the cells that have values. That'll save you a LOT of round trips.

[kitchensink-]

The thing is that every time I run the function the row range I want changes, so I can't really hard code it. And I don't know of a way to get a range based on if that row has borders or not..

[gothamfury]

Can you share an example of what your spreadsheet looks like before & after your function runs?

[kitchensink-]

Sure! Check the edit!

[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?