Conditional Formatting using script

Question

My problem with google spreadsheet conditional formatting is the border. I tried writing a script on this but I am stuck

In my first screenshot you can see the data, in row 7 and column A I have added a trigger that should tell the script whether it needs to do the formatting. where there is a 1 there should be borders when 0 or blank it should not.

The Idea is that I get for the grid D12 to J32 borders however the rows 18,27 and 32 should have no borders due to the 0. Also the cells D12 to J32 cannot be hard coded as they are variable, 1 time it may be until row 32 the other till 55 same with the columns it can go from D untill L but also from D untill E. This might all change based on the template called on by change cell B9.

An Example how it looks like after border would be added

The script I have written so far take into account the onedit based on cell B9 and removes all borders from D12 untill the bottom and the side but it leave the border to the top and left intact. The problem I am unsure how to write a script that adds the borders with a variable range. I am guessing the most easy way to do it is to have it write first 1 larger table of borders and then remove the vertical borders on some rows again.

Would appreciate help with this.

function onEdit(e) { var sheet = e.source.getActiveSheet() if (sheet.getName() !== 'View' || e.range.getA1Notation() !== 'B9') return; var range = SpreadsheetApp.getActiveSheet().getRange(12,4,100,13); range.setBorder(false, false, false, false, false, false); }

Harold · Accepted Answer · 2016-09-09 17:14:04Z

I did a function that do the job but in reverse, first it delete all the border and then it add border where it's needed. However I didn't understood your story with template in B9 etc... so what I did is if there is in the cell B9 the word Update then it will do the job and change the content of cell B9 for Done. For what I understood (or not) I changed the border accordingly to what is found on range A
Here is my code:

function onEdit(e) { var start = 12; var sheet = e.source.getActiveSheet() if (sheet.getName() == 'Sheet1' && e.range.getA1Notation() == 'B9' && e.range.getValue() == "Update") { var index = SpreadsheetApp.getActiveSheet().getRange("A12:A").getValues(); SpreadsheetApp.getActiveSheet().getRange("D12:J").setBorder(false, false, false, false, false, false); for(var i in index) { var range = SpreadsheetApp.getActiveSheet().getRange("D"+(Number(i)+start)+":J"+(Number(i)+start)); if (index[i][0] == 1) { range.setBorder(true, true, true, true, true, true); } } sheet.getRange("B9").setValue("Done"); } }

Hi Harold, With a little bit of tweaking I managed to get exactly what I needed based off the code you provided me. Thank you. Will post later the final version of my code for reference to other people in the future

Collectives™ on Stack Overflow

Conditional Formatting using script

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related