UPDATE: Solution is to simply create it in EXCEL (making sure all formulas are compatible with Sheets), then upload and convert it from xls to a sheet format. https://docs.google.com/spreadsheets/d/1TI8qkTvnanNW1IwxAYbkPISmwMLOOGu5Go0WQVBDAXQ/edit?usp=sharing
I have a timetable in Google Sheets that needs to have the borders change depending on if a class occurs during that time.
It needs to be triggered onEdit so that as the spreadsheet changes, the borders update. I will need to remove borders at the beginning of each trigger, and I have this code from another answered question:
function RemoveBorders() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange("C5:G29");
cell.setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.solid);
}
An example of this can be found at this link I need each class to be boxed in (and any cells not involved in a class need to retain their borders.
The conditional formatting for the background was done using this formula and it works very well.
=SUMPRODUCT(($B5>=($L$5:$L)),(C$4=($K$5:$K)),($B5<($M$5:$M)))>=1
I have the code that works to edit borders in Excel via conditional formatting. But since I'm new to Google scripts and Java, this is really overwhelming of how to convert the concept
I have three conditional rules
Rule 1: For the first cell of a class, add borders to the left, right, and top of the cell, if the cell is in a timetable column that has a class on that day AND if the cell's time period is equal to a value in the Start Time column
=SUMPRODUCT((C$4=($K$5:$K)),($B5=($L$5:$L)))>=1
Rule 2: For the "middle cells" of a class, add borders to the left and right side, if the cell is in a timetable column that has a class on that day AND if the cell's time period (9:00-9:30) is greater or equal to Class Start time values, AND THE cell's time period is less than Class End time values.
=SUMPRODUCT((C$4=($K$5:$K)),($B5>=($L$5:$L)),($B5<($M$5:$M)))>=1
Rule 3: Add a border to the top of the cell (for cells right after the end of a class) if the cell is in a timetable column that has a class on that day, AND if the cell's time period is in the Class End time columns.
=SUMPRODUCT((C$4=($K$5:$K)),($B5=($M$5:$M)))>=1
This is my very last step in finishing a whole dynamic data import and manipulation that dynamically updates an individual teacher's timetable. I have searched and searched and looked up so much stuff but I keep getting stumped on how to achieve this in script form. Any help would be really appreciated.
EDIT: As a complete newbie to all of this, I have just been trying to get things to work. In the process, I have stumbled upon a few other answers to questions and have hashed together this code which at least slightly delineates the classes, although is not the perfect solution
function onEdit() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("C5:g30");
var adress= range.getA1Notation();
Logger.log("changed cell %s", adress);
sheet.getRange('F2').setValue(adress);
// Remove all borders
var range = sheet.getRange("c5:g30");
range.setBorder(true, true, true, true, true, true,"#D3D3D3",SpreadsheetApp.BorderStyle.SOLID);
//addtop rows
var numRows = range.getNumRows();
var numCols = range.getNumColumns();
for (var i = 1; i <= numRows; i++) {
for (var j = 1; j <= numCols; j++) {
var currentValue = range.getCell(i,j).getValue();
Logger.log("changed cell %s", range.getCell(i,j).getA1Notation() );
if(currentValue != "") {
Logger.log("positive on %s", range.getCell(i,j).getA1Notation());
range.getCell(i,j).setBorder(true, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}
}
}
}
