I need a function or a formula in a cell (example: A2) that gives the border of cell (example: A1) in a format like ####, with # being 0 or 1, depending if there is an active border in the positions LTRD (left, top, right, down).
It can be a function of a script, whatever works.
I’ve browsed through the network but didn’t get any simple solution. I’m a beginner at Google Sheets (experienced in Excel)
You can use a script like this. I used row
and col
, so you can drag the formula in case you need it:
You can use it like: =getBorderInfo(ROW(B1),COLUMN(B1))
function getBorderInfo(row, col) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get the current cell's range
var range = sheet.getRange(row, col);
var borders = [0, 0, 0, 0]; // Initial value for [left, top, right, bottom]
// Check the current cell's borders
var border = range.getBorder();
if (border) {
if (border.getLeft().getBorderStyle() !== null) borders[0] = 1;
if (border.getTop().getBorderStyle() !== null) borders[1] = 1;
if (border.getRight().getBorderStyle() !== null) borders[2] = 1;
if (border.getBottom().getBorderStyle() !== null) borders[3] = 1;
}
// Check left adjacent cell for its right border
if (col > 1) {
var leftCell = sheet.getRange(row, col - 1);
var leftBorder = leftCell.getBorder();
if (leftBorder && leftBorder.getRight().getBorderStyle() !== null) borders[0] = 1;
}
// Check top adjacent cell for its bottom border
if (row > 1) {
var topCell = sheet.getRange(row - 1, col);
var topBorder = topCell.getBorder();
if (topBorder && topBorder.getBottom().getBorderStyle() !== null) borders[1] = 1;
}
// Check right adjacent cell for its left border
if (col < sheet.getMaxColumns()) {
var rightCell = sheet.getRange(row, col + 1);
var rightBorder = rightCell.getBorder();
if (rightBorder && rightBorder.getLeft().getBorderStyle() !== null) borders[2] = 1;
}
// Check bottom adjacent cell for its top border
if (row < sheet.getMaxRows()) {
var bottomCell = sheet.getRange(row + 1, col);
var bottomBorder = bottomCell.getBorder();
if (bottomBorder && bottomBorder.getTop().getBorderStyle() !== null) borders[3] = 1;
}
return borders.join("");
}
Update Workaround:
Script-based formulas only update when the value
of a cell they use is updated. What you can do (depending on how many times you’re using it all over the spreadsheet) is to create an “updater cell” that is used only for that. Let’s say it’s Z1
– Then you use =getBorderInfo(ROW(B2),COLUMN(B2),$Z$1)
as a third argument; and an onEdit(e) function that updates Z1 with each modification of the spreadsheet:
function onEdit(e){
SpreadsheetApp.getActive().getRange("Z1").setValue(new Date())
}
Then every time a border or anything is modified, it would update Z1
which would eventually update every script-based formulas. You can think of ways to have an “updater column” and modify onEdit to update the row of the cell, or something like that
4