CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Latent-code
Latent-code

Reputation: 83

OnOpen() change border color of column

I have a calendar that has dates horizontally. I want to check for the cell with todays date and then format the side borders of all the cells in that column red.

I dont really know scripting so any and all help is greatly appreciated. The script underneith is the one i was going to work something out from, but i have not gotten anywhere..

function checkRange(ss) {
    var ss = ss || SpreadsheetApp.getActiveSheet();
    var range = ss.getRange('A6:NA6');
    range.setBorder(false, false, false, false, false, false);
    var values = range.getValues();
    for (var i = 0; i < values.length; i++) {
        for (var j = 0; j < values[i].length; j++) {
            if (values[i][j] !== "") {
                range.getCell(i + 1, j + 1).setBorder(true, true, true, true, true, true)
                    .setBackground('white');
            }
        }
    }
}

This script is a little misleading. It does not work, but i tried editing to make it work. It has some of the features i need, but i dont know any coding im afraid.. I need a push in the right direction when it comes to searching for dates and selecting the column with that cell in it.

for (var i = 0; i < values.length; i++) This is greek to me im afraid.

Upvotes: 1

Views: 82

Answers (1)

Mikah
Mikah

Reputation: 75

function checkRange(ss) {
  var ss = ss || SpreadsheetApp.getActiveSpreadsheet();
  var as=ss.getActiveSheet();
  var timeZone=ss.getSpreadsheetTimeZone();
  var range = as.getRange('A1:I10');
  var now=new Date();
  var nowOnlyDay=Utilities.formatDate(now,timeZone,"dd/MM/YYYY");
  Logger.log(nowOnlyDay);
  range.setBorder(false, false, false, false, false, false);
  var values = range.getValues();
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (values[i][j]!="") {
        var dateCell=Utilities.formatDate(values[i][j],timeZone,"dd/MM/YYYY");
        Logger.log(dateCell);
        if(dateCell===nowOnlyDay) {
          Logger.log(values[i][j]);
          range.getCell(i + 1, j + 1).setBorder(true, true, true, true, true, true)
          .setBackground('white');
        }
      }
    }
  }
}

Upvotes: 1

Related Questions