Monday, July 3, 2017

Google Sheet Script for border style


I have columns and rows: D69:J79 filled with data, data in rows is changing on daily basis, one day it’s D69:J79, another it’s shorter D69:J77 or D69:J75 etc, rows below are blanks accordingly, columns stay the same.

I want script to make regular square line border around this data, but with one rule - bottom line of square should be right below latest row with data. And left and right borders lenght should be fitted to rows with data only as well.

I am total noob in scripts, could somebody help me with proper script for this?
And i hope this script can be set to autorun, so i don’t have to run it each time.

Or maybe there is some other solution more simple :)


Try the following script code:


function fillBorders() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sheet1");
  var r = s.getRange("D69:J79");
  var v = r.getValues();
  var firstRow = r.getRow();
  var lastRow = r.getLastRow();
  var lastFilledRow = lastRow;
  for(var i=v.length-1;i>=0;i--) {
    var isRowBlank = true;
    for(var j=0;j<v[0].length;j++) {
      if(v[i][j]!="") {
        isRowBlank = false;
        lastFilledRow = firstRow + i;


in the above script code, change the sheet name "Sheet1" as per your need.

No comments:

Post a Comment