I was trying to get the information about the borders of a range, doing so I tried the getBorders() function, but received the following error:
Exception: Unexpected error while getting the method or property getBorders on object SpreadsheetApp.Range.
Here is the code below:
function test(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var pg = ss.getActiveSheet();
var range = pg.getRange(1,1,10,5);
var border = range.getBorders();
}
For a workaround I needed to do a getBorder() for each cell on the range:
function getBorders(range){
var pg = range.getSheet();
var fr = range.getRow();
var fc = range.getColumn();
var lr = range.getLastRow();
var lc = range.getLastColumn();
var row = []
var finalArray = []
for(var r = fr;r<=lr;r++){
for(var c = fc;c<=lc;c++){
var exactRange = pg.getRange(r,c);
if(exactRange.isPartOfMerge()){
//For some reason I found that trying to get the border of an Merged cell is impossible
var hexTop = "#000000";
var hexLeft = "#000000";
var hexRight = "#000000";
var hexBottom = "#000000";
} else {
var bottomColor = exactRange.getBorder().getBottom().getColor();
var topColor = exactRange.getBorder().getTop().getColor();
var leftColor = exactRange.getBorder().getLeft().getColor();
var rightColor = exactRange.getBorder().getRight().getColor();
var bcolorType = bottomColor.getColorType();
var tcolorType = topColor.getColorType();
var rcolorType = rightColor.getColorType();
var lcolorType = leftColor.getColorType();
const getHexColor= (color,type) => {
switch(type){
case SpreadsheetApp.ColorType.RGB:
var hex = color.asRgbColor().asHexString();
break
default:
var hex = "#000000"
break
}
return hex;
}
var hexTop = getHexColor(topColor , tcolorType)
var hexLeft = getHexColor(leftColor , lcolorType)
var hexRight = getHexColor(rightColor , rcolorType)
var hexBottom = getHexColor(bottomColor , bcolorType)
}
row.push([[hexTop],[hexLeft],[hexRight],[hexBottom]]);
}
finalArray = finalArray.concat([linha]);
row= []
}
return finalArray;
}
I was expecting the getBorders() function to return to me an array of the function for each cell just like I did in the workaround, obviously not with the separation for the color values for top, left, right and bottom, which I would do where I would use this array
Despite the Google Apps Scripts Editor autocomplete showing getBorder and getBorders for the Class SpreadsheetApp.Range these methods aren't included in https://developers.google.com/apps-script/reference/spreadsheet/range, the official reference page for this class.
I understand that, as of August 8th, 2024, getBorder works, which leads us to expect that getBorders works too, but as they aren't included in the official references, "complaining" about this will not help in the short term.
Instead of these methods, use the Advanced Spreadsheet Service to play on the safe side. Below is a question about using this service to get the borders
Note:
I just tested the sample code from Tanike's answer to the above question. It works.
function getBorderAdvanced(){
const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
const res = Sheets.Spreadsheets.get(spreadsheetId, {ranges: "Sheet1!A1", fields: "sheets/data/rowData/values/userEnteredFormat/borders"});
Logger.log(res)
}