What is an alternative function for getBorderlines in Google App scripts?-CodePudding

I have this code running in Google App script that counts the cells that have a solid border (some cells have a dotted border) and returns that number.

I get the following error when I run the script:

TypeError: cell.getBorderlines is not a function
countSolidBorderedCells @ Code.gs:10

function countSolidBorderedCells() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  var count = 0;
  for (var i = 0; i < values.length; i  ) {
    for (var j = 0; j < values[i].length; j  ) {
      var cell = sheet.getRange(i   1, j   1);
      var borders = cell.getBorderlines();
      if (borders.length > 0) {
        for (var k = 0; k < borders.length; k  ) {
          if (borders[k].isSolid()) {
            count  ;
            break;
          }
        }
      }
    }
  }
  sheet.getRange("D22").setValue(count);
}

What should I replace that function with to make it work in Google App Script?

CodePudding user response：

There is no method getBorderLines() for Google Sheets. And unfortunately there is no method getBorders() that works for me. So you have to use getBorder() for each cell and examine it. I have a sample spreadsheet with random borders applied to different cells.

function getBorders() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Sheet1");
    let range = sheet.getDataRange();
    let rows = range.getNumRows();
    let cols = range.getNumColumns();
    for( let i=0; i<rows; i   ) {
      for( let j=0; j<cols; j   ) {
        let cell = range.offset(i,j,1,1);
        let borders = cell.getBorder();
        if( borders ) {
          let border = borders.getLeft();
          if( border ) {
            if( border.getBorderStyle() ) console.log("range: " cell.getA1Notation() " left: " border.getBorderStyle().toString());
          }
          border = borders.getRight();
          if( border ) {
            if( border.getBorderStyle() ) console.log("range: " cell.getA1Notation() " right: " border.getBorderStyle().toString());
          }
          border = borders.getTop();
          if( border ) {
            if( border.getBorderStyle() ) console.log("range: " cell.getA1Notation() " top: " border.getBorderStyle().toString());
          }
          border = borders.getBottom();
          if( border ) {
            if( border.getBorderStyle() ) console.log("range: " cell.getA1Notation() " bottom: " border.getBorderStyle().toString());
          }
        }
      }
    }
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

8:51:11 AM  Notice  Execution started
8:51:13 AM  Info    range: A2 left: SOLID
8:51:13 AM  Info    range: A2 right: SOLID
8:51:13 AM  Info    range: A2 top: SOLID
8:51:13 AM  Info    range: A2 bottom: SOLID
8:51:13 AM  Info    range: B4 left: SOLID
8:51:13 AM  Info    range: B4 right: SOLID
8:51:13 AM  Info    range: B4 top: SOLID
8:51:13 AM  Info    range: C5 right: DASHED
8:51:13 AM  Notice  Execution completed