Matching a date on a calendar to another field

Question

Ok, I have a calendar on my page and each day is a formatted version of that date, eg.

Jan 2021
1  2  3  4
5  6  7  8
etc..

so the 1 cell will contain 1/1/2021 (but formatted to just show '1' [d]) I also have a cell (K5) on that page for a user to enter a date eg:[1/1/2021]

What i'd like is a script that changes the border colour of the day in the calendar if it matches the user entry cell.

The code:

function onEdit(e) {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();
var date = sheet.getRange("K5").getValue();
if (e.range.getValue()== date) {
e.range.setBorder(true, true, true, true, true, true, "red", SpreadsheetApp.BorderStyle.solid); }
};

This doesn't work and I can't think of a way of having the code work for every cell of the calendar (there are 2 years worth so over 1000 cells). It doesn't need to be onEdit, i was just testing to see if the actual setBorder function worked (which it does) Also, I cant use conditional formatting as I've already used that to change to bg and font colours for something else (and there's no option to change boarder colour anyway)

I've made a mini version with just Jan if you'd like to have a look and see what you can do: https://docs.google.com/spreadsheets/d/1oV4lE8cQB-e2bVc_HgiGM31ivk3uHxPcqsSdLdCxsmQ/edit?usp=sharing

Answer 1

You could do the following:

• Get all values in your range, using getValues().
• Iterate through all values in the resulting 2D array.
• For each value, check if (1) the value is a Date (for example, using instanceof) and whether the two dates are from the same day (for example, doing this).
• If both conditions are met, set the border.

Code snippet:

function setBorders() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var date = sheet.getRange("K5").getValue();
  var firstRow = 7;
  var firstCol = 5;
  var data = sheet.getRange(firstRow, firstCol, sheet.getLastRow() - firstRow + 1, sheet.getLastColumn() - firstCol + 1).getValues();  
  data.forEach((row, i) => {
    row.forEach((value, j) => {
      if (value instanceof Date && sameDay(date, value)) {
        sheet.getRange(i + firstRow, j + firstCol).setBorder(true, true, true, true, true, true, "red", SpreadsheetApp.BorderStyle.solid);
      }
    });
  })
}

function sameDay(date1, date2) {
  return date1.getFullYear() === date2.getFullYear() &&
  date1.getMonth() === date2.getMonth() &&
  date1.getDate() === date2.getDate();
}

Answer 2

One alternative to looking for all the dates would be to use a formula to show the dates and their addresses in a different sheet. This formula will output a series of dates and their addresses for your holidays. It can then be read with an onEdit() script to put borders on the correct addresses:

Formula:

=ARRAYFORMULA(IFERROR(QUERY(SPLIT(FLATTEN(N(Calendar!E9:AY)&"|"&ROW(Calendar!E9:AY)&"|"&COLUMN(Calendar!E9:AY)&"|"&LEN(TO_TEXT(Calendar!E9:AY))),"|",0,0),"select Col1,Col2,Col3 where Col4<3 and ("&TEXTJOIN(" or",TRUE," Col1="&FILTER(Calendar!BE28:BE100,Calendar!BE28:BE100<>""))&")")))

Script:

function onEdit(e){
  holidayBorders(e);
}

function holidayBorders(e){
  var sheet = e.range.getSheet();
  if(e.value && sheet.getName()=='Calendar' && e.range.getColumn()==57 && e.range.getRow() >= 28){
    SpreadsheetApp.flush();
    Utilities.sleep(10000);
    var datasheet = e.source.getSheetByName('DATA');
    var rcs = datasheet.getRange('AQ3:AR').getValues().filter(e=> e[0]);

    for (let i=0;i<rcs.length;i++){
      sheet.getRange(rcs[i][0],rcs[i][1]).setBorder(true, true, true, true, true, true, "red", SpreadsheetApp.BorderStyle.solid);
    }
  }
}