getBorderStyle() to setBorderStyle()
I am trying to use the .getBorderStyle() function from one cell and change the setting for another cell to the same border style. This will not work for me though. When I go to use the border style to set the border it is null. I have put two example of this below.
function borderpaste(range,destination) {
var rangenr = range.getNumRows();
var rangenc = range.getNumColumns();for(var i=0;i<rangenr;i++){
for(var j=0;j<rangenc;j++){
var sBD = range.offset(i,j).getBorder();
if(sBD!==null){
var sBDR = sBD.getRight().getBorderStyle();
if(sBDR!==null){
destination.offset(i,j,1,1).setBorder(null,null,null,true,false,false,SpreadsheetApp.BorderStyle.sBDR)}
console.log(SpreadsheetApp.BorderStyle.sBDRs);}
}
I have tried also to put this into a string and then using the number but this does not work either. This probably does not work because this method is looking for an enum and not a string.
function borderpaste(range,destination) {
var rangenr = range.getNumRows();
var rangenc = range.getNumColumns();
for(var i=0;i<rangenr;i++){
for(var j=0;j<rangenc;j++){
var sBD = range.offset(i,j).getBorder();
if(sBD!==null){
var sBDR = sBD.getRight().getBorderStyle();
if(sBDR!==null){
var sBDRs = sBDR.toString();
if(sBDRs!==null){
destination.offset(i,j,1,1).setBorder(null,null,null,true,false,false,SpreadsheetApp.BorderStyle.sBDRs)}
console.log(SpreadsheetApp.BorderStyle.sBDRs);}
}
}
For the console.log(SpreadsheetApp.BorderStyle.sBDR) and console.log(SpreadsheetApp.BorderStyle.sBDRs) they both are reading as undefined. Does anyone know what formatting needs to be change from the sBDR in this example for it to work with SpreadsheetApp.BorderStyle. to set the border of the destination cell?
Range is a named range from another sheet and destination is the range on the sheet that I will be setting this into.
To make this more clear I have created sheets to share as an example: Source sheet (https://docs.google.com/spreadsheets/d/1calbWQ3GNQMuShxI2tuqlt8yldHaBklkqpPJ1WOKvtY/edit?gid=0#gid=0) Target sheet (https://docs.google.com/spreadsheets/d/1UJsbhTynho0H6moDspg4qQfdo_YMRkHaw0VGsdHdSpQ/edit?gid=0#gid=0)
I want to make this script a dynamic script that I can put into multiple situations to copy a named ranges borders from one sheet to another below is how he entire script would work. The merging format script is also on here. I did not mention it previously since this works great.
function copyandpaste(range,destination) {
var ranger=range.getRow()
var rangec = range.getColumn()
var rangenr = range.getNumRows();
var rangenc = range.getNumColumns();
var sMG = range.getMergedRanges();
for(var k=0;k<sMG.length;k++){
var rowNum = sMG[k].getRow();
var colNum = sMG[k].getColumn();
var numrow = sMG[k].getNumRows();
var numcol = sMG[k].getNumColumns();
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).merge();
}
for(var i=0;i<rangenr;i++){
for(var j=0;j<rangenc;j++){
var targetl = destination.offset(i,j,1,1).getA1Notation();
var sBD = range.offset(i,j,1,1).getBorder();
if(sBD!==null){
var sBDL = sBD.getLeft().getBorderStyle();
if(sBDL!==null){
var sBDLs=sBDL.toString()
if(sBDLs=="SOLID"){
destination.offset(i,j,1,1).setBorder(null,true,null,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID)}
if(sBDLs=="DOTTED"){
destination.offset(i,j,1,1).setBorder(null,true,null,null,false,false,'black',SpreadsheetApp.BorderStyle.DOTTED)}
if(sBDLs=="DASHED"){
destination.offset(i,j,1,1).setBorder(null,true,null,null,false,false,'black',SpreadsheetApp.BorderStyle.DASHED)}
if(sBDLs=="SOLID_MEDIUM"){
destination.offset(i,j,1,1).setBorder(null,true,null,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM)}
if(sBDLs=="SOLID_THICK"){
destination.offset(i,j,1,1).setBorder(null,true,null,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_THICK)}
if(sBDLs=="DOUBLE"){
destination.offset(i,j,1,1).setBorder(null,true,null,null,false,false,'black',SpreadsheetApp.BorderStyle.DOUBLE)}
}
}
if(sBD!==null){
var sBDR = sBD.getRight().getBorderStyle();
if(sBDR!==null){
var sBDRs=sBDR.toString()
if(sBDRs=="SOLID"){
destination.offset(i,j,1,1).setBorder(null,null,null,true,false,false,'black',SpreadsheetApp.BorderStyle.SOLID)}
if(sBDRs=="DOTTED"){
destination.offset(i,j,1,1).setBorder(null,null,null,true,false,false,'black',SpreadsheetApp.BorderStyle.DOTTED)}
if(sBDRs=="DASHED"){
destination.offset(i,j,1,1).setBorder(null,null,null,true,false,false,'black',SpreadsheetApp.BorderStyle.DASHED)}
if(sBDRs=="SOLID_MEDIUM"){
destination.offset(i,j,1,1).setBorder(null,null,null,true,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM)}
if(sBDRs=="SOLID_THICK"){console.log(targetl)
destination.offset(i,j,1,1).setBorder(null,null,null,true,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_THICK)}
if(sBDRs=="DOUBLE"){
destination.offset(i,j,1,1).setBorder(null,null,null,true,false,false,'black',SpreadsheetApp.BorderStyle.DOUBLE)}
}
}
if(sBD!==null){
var sBDT = sBD.getTop().getBorderStyle();
if(sBDT!==null){;
var sBDTs=sBDT.toString()
if(sBDTs=="SOLID"){
destination.offset(i,j,1,1).setBorder(true,null,null,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID)}
if(sBDTs=="DOTTED"){
destination.offset(i,j,1,1).setBorder(true,null,null,null,false,false,'black',SpreadsheetApp.BorderStyle.DOTTED)}
if(sBDTs=="DASHED"){
destination.offset(i,j,1,1).setBorder(true,null,null,null,false,false,'black',SpreadsheetApp.BorderStyle.DASHED)}
if(sBDTs=="SOLID_MEDIUM"){
destination.offset(i,j,1,1).setBorder(true,null,null,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM)}
if(sBDTs=="SOLID_THICK"){
destination.offset(i,j,1,1).setBorder(true,null,null,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_THICK)}
if(sBDTs=="DOUBLE"){
destination.offset(i,j,1,1).setBorder(true,null,null,null,false,false,'black',SpreadsheetApp.BorderStyle.DOUBLE)}
}
}
if(sBD!==null){
var sBDB = sBD.getBottom().getBorderStyle();
if(sBDB!==null){;
var sBDBs=sBDB.toString()
if(sBDBs=="SOLID"){
destination.offset(i,j,1,1).setBorder(null,null,true,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID)}
if(sBDBs=="DOTTED"){
destination.offset(i,j,1,1).setBorder(null,null,true,null,false,false,'black',SpreadsheetApp.BorderStyle.DOTTED)}
if(sBDBs=="DASHED"){
destination.offset(i,j,1,1).setBorder(null,null,true,null,false,false,'black',SpreadsheetApp.BorderStyle.DASHED)}
if(sBDBs=="SOLID_MEDIUM"){
destination.offset(i,j,1,1).setBorder(null,null,true,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM)}
if(sBDBs=="SOLID_THICK"){
destination.offset(i,j,1,1).setBorder(null,null,true,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_THICK)}
if(sBDBs=="DOUBLE"){
destination.offset(i,j,1,1).setBorder(null,null,true,null,false,false,'black',SpreadsheetApp.BorderStyle.DOUBLE)}
}
}
}}
for(var k=0;k<sMG.length;k++){
var rowNum = sMG[k].getRow();
var colNum = sMG[k].getColumn();
var numrow = sMG[k].getNumRows();
var numcol = sMG[k].getNumColumns();
var sBD = range.offset(rowNum-ranger,colNum-rangec,numrow,numcol).getBorder();
if(sBD!==null){
var sBDL = sBD.getLeft().getBorderStyle();
if(sBDL!==null){
var sBDLs=sBDL.toString()
if(sBDLs=="SOLID"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,true,null,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID)}
if(sBDLs=="DOTTED"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,true,null,null,false,false,'black',SpreadsheetApp.BorderStyle.DOTTED)}
if(sBDLs=="DASHED"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,true,null,null,false,false,'black',SpreadsheetApp.BorderStyle.DASHED)}
if(sBDLs=="SOLID_MEDIUM"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,true,null,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM)}
if(sBDLs=="SOLID_THICK"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,true,null,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_THICK)}
if(sBDLs=="DOUBLE"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,true,null,null,false,false,'black',SpreadsheetApp.BorderStyle.DOUBLE)}
}
}
if(sBD!==null){
var sBDR = sBD.getRight().getBorderStyle();
if(sBDR!==null){
var sBDRs=sBDR.toString()
if(sBDRs=="SOLID"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,null,null,true,false,false,'black',SpreadsheetApp.BorderStyle.SOLID)}
if(sBDRs=="DOTTED"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,null,null,true,false,false,'black',SpreadsheetApp.BorderStyle.DOTTED)}
if(sBDRs=="DASHED"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,null,null,true,false,false,'black',SpreadsheetApp.BorderStyle.DASHED)}
if(sBDRs=="SOLID_MEDIUM"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,null,null,true,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM)}
if(sBDRs=="SOLID_THICK"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,null,null,true,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_THICK)}
if(sBDRs=="DOUBLE"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,null,null,true,false,false,'black',SpreadsheetApp.BorderStyle.DOUBLE)}
}
}
if(sBD!==null){
var sBDT = sBD.getTop().getBorderStyle();
if(sBDT!==null){;
var sBDTs=sBDT.toString()
if(sBDTs=="SOLID"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(true,null,null,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID)}
if(sBDTs=="DOTTED"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(true,null,null,null,false,false,'black',SpreadsheetApp.BorderStyle.DOTTED)}
if(sBDTs=="DASHED"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(true,null,null,null,false,false,'black',SpreadsheetApp.BorderStyle.DASHED)}
if(sBDTs=="SOLID_MEDIUM"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(true,null,null,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM)}
if(sBDTs=="SOLID_THICK"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(true,null,null,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_THICK)}
if(sBDTs=="DOUBLE"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(true,null,null,null,false,false,'black',SpreadsheetApp.BorderStyle.DOUBLE)}
}
}
if(sBD!==null){
var sBDB = sBD.getBottom().getBorderStyle();
if(sBDB!==null){;
var sBDBs=sBDB.toString()
if(sBDBs=="SOLID"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,null,true,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID)}
if(sBDBs=="DOTTED"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,null,true,null,false,false,'black',SpreadsheetApp.BorderStyle.DOTTED)}
if(sBDBs=="DASHED"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,null,true,null,false,false,'black',SpreadsheetApp.BorderStyle.DASHED)}
if(sBDBs=="SOLID_MEDIUM"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,null,true,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_MEDIUM)}
if(sBDBs=="SOLID_THICK"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,null,true,null,false,false,'black',SpreadsheetApp.BorderStyle.SOLID_THICK)}
if(sBDBs=="DOUBLE"){
destination.offset(rowNum-ranger,colNum-rangec,numrow,numcol).setBorder(null,null,true,null,false,false,'black',SpreadsheetApp.BorderStyle.DOUBLE)}
}
}
}
}
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var target = ss.getSheetByName("Sheet1");
var source = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1calbWQ3GNQMuShxI2tuqlt8yldHaBklkqpPJ1WOKvtY/edit?gid=0#gid=0");
var source1range = source.getRangeByName("Source1");
var target1destination = target.getRange("B6:Q13");
copyandpaste(source1range,target1destination);
var source2range = source.getRangeByName("Source2");
var target2destination = target.getRange("B15:Y32");
copyandpaste(source2range,target2destination);
var source3range = source.getRangeByName("Source3");
var target3destination = target.getRange("B34:Y73");
copyandpaste(source3range,target3destination);
}This is all that I have figured out so far but there must be an easier way to accomplish this and create it as a dynamic script to be used in multiple places.
Solution
You want to set the right border for a cell based on the right border of another cell.
The script is failing (or will fail) for several reasons:
getBorder()(sBD) can have a value of "null" if one or more borders aren't set and this will halt the scriptvar sBD = targetRange.getBorder()?? "No Border"is a workaround for a null value (using the Javascript Nullish coalescing operator (??) to set a default value if sBD evaluates to null
- the syntax of
setBorderdoc ref includes a "color"setBorder(top, left, bottom, right, vertical, horizontal, color, style)
- the
styleargument insetBorderis a BorderStyle and must be in the formparent class, name, and propertyFor example,SpreadsheetApp.BorderStyle.DOTTED- the
BorderStyledoes not appear to accept a variable as the property. For exampleSpreadsheetApp.BorderStyle.sBDRwill create a "solid" border regardless of the value of "sBDR" - this means that a style needs to be assigned for each possible value of "sBDR". The following script uses a series of "if/else" statements.
- the
The following script is based on the OP's script but the range and destination are declared within the function.
function borderCopy() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName("Sheet1")
var range = sheet.getRange("A2:B8")
var destination = sheet.getRange("F10")
// get the top left cell in the range
var rangeCell = sheet.getRange(range.getRow(),range.getColumn())
//Logger.log("DEBUG: the starting cell = "+rangeCell.getA1Notation())
var rangenr = range.getNumRows();
var rangenc = range.getNumColumns();
// Logger.log("DEBUG: range rows = "+rangenr+", range columns = "+rangenc)
// Loop through the range
for(var i=0;i<rangenr;i++){
for(var j=0;j<rangenc;j++){
var targetRange = rangeCell.offset(i,j)
var destinationRange = destination.offset(i,j)
// Logger.log("DEBUG: targetRange = "+targetRange.getA1Notation()+", destinationRange = "+destinationRange.getA1Notation())
// get the border for the target cell
// if result is null, then assign default value
var sBD = targetRange.getBorder()?? "No Border"
// test for null/default value and skip to next cell
if (sBD == "No Border"){
// do nothing, skip to next cell
Logger.log("DEBUG: Border for "+destinationRange.getA1Notation()+" was not set")
continue
}
// get the right border attributes
var sBDR = sBD.getRight().getBorderStyle()
// set the style based on the Border Style
if (sBDR == "DOTTED") {
var style = SpreadsheetApp.BorderStyle.DOTTED
} else if (sBDR == "DASHED") {
var style = SpreadsheetApp.BorderStyle.DASHED
} else if (sBDR == "SOLID") {
var style = SpreadsheetApp.BorderStyle.SOLID
} else if (sBDR == "SOLID_MEDIUM") {
var style = SpreadsheetApp.BorderStyle.SOLID_MEDIUM
} else if (sBDR == "SOLID_THICK") {
var style = SpreadsheetApp.BorderStyle.SOLID_THICK
} else if (sBDR == "DOUBLE") {
var style = SpreadsheetApp.BorderStyle.DOUBLE
}
//Logger.log("DEBUG: i="+i+", j="+j+", target Cell = "+targetRange.getA1Notation()+", destination Cell = "+destinationRange.getA1Notation()+" Right = "+style)
// update the right border in the destination.
destinationRange.setBorder(null, null, null, true, false, false, "red", style)
Logger.log("DEBUG: set right Border for "+destinationRange.getA1Notation()+" with "+sBDR)
}
}
}
SAMPLE - Before
SAMPLE - After
- How to create a formula that will repeat a range by splitting one column and joining with the other column?
- Google Sheets Splitting Text to Rows and Duplicate Surrounding Rows Entries
- How to transpose & split multiple columns and repeat specific cells in a column
- How do you deal with multiple values in a single cell separated by comma?
- Copy specific fields to another sheet
- Find Text and copy another cells
- Generating date range based on allowed weekdays, repeat times and times of day in Google Sheets
- How to use a query within a query?
- Find first empty row and scroll to it, Lastrow not working due to formulas
- Map a table, replacing values based on other cell
- Sum value if matching value is not blank in range leaving it blank if empty
- A Script to Simplify Creating a SO Table
- Google Sheets API v4 and valueInputOption
- Get Google Sheet by ID?
- ReferenceError: C is not defined
- How to sum up multiple VLOOKUP results?
- Conditional Formatting in Google Sheets with another table INDIRECT
- How to reference a cell to the left of active cell in Google script
- How to show instruction with image after install google workspace extension?
- How to use SpreadsheetApp.Spreadsheet.getChanges method?
- Get the user that changed specific cell
- Daytime and Nightime Occurrence Duration of an Event
- Recombining 4 pairs of numbers
- GmailApp.sendEmail: is it possible to schedule send an email?
- Why do I get the error SPLIT parameter 1 empty error when it isn't?
- Is there a faster way to sort sheets with AppScripts?
- ISO-8601 String to Date in Google Sheets cell
- How can we sort multiple values ​horizontally using VLOOKUP?
- COUNTIFS WITHIN DATE RANGE GOOGLE SHEET
- conditional formatting on a single cell where criteria is another column [solved]