Google Sheets, I need to force a date to the nearest Friday in my function, and detect if the date is Friday beforehand for a popup box

Question

Really simple, I have a big function that does a bunch of really cool stuff, I want to add to it two big things.

1. I'd like to check a very specific cell for the nearest Friday, and if the date is accurate (implying the function has run before) make a popup box or something arrive to inform the user and give the option to cancel the function or continue.
2. I'd like a very specific cell to be rewritten with the nearest friday.

That's it. I know how to rewrite a cell but I cannot figure out the nearest friday part. And I don't know the first thing about popup notifications and stuff. Gonna be real, I could probably figure out everything if I just knew the friday part. Or even how to just subtract dates. This code is always going to be used on Saturday, I need it to go back one day.

Answer 1

You want to get a value from a cell and determine the "nearest Friday". "Nearest Friday" is an ambiguous term, so this answer finds the previous Friday as well as the "next" Friday.

The answer is an implementation of How to get the date of every upcoming Friday in mm.dd.yy format in Google App Script? by @Brenden

---

function findFriday() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
  var range = sheet.getRange("A2")
  var cellValue = range.getValue()
  var dayNumber = new Date(cellValue).getDay()
  
  // object list of the days of the week and respective day numbers
  const days = {
    sunday: 0,
    monday: 1,
    tuesday: 2,
    wednesday: 3,
    thursday: 4,
    friday: 5,
    saturday: 6
  };

  // get the day of the cell value
  // h/t so_9907419
  var key = Object.keys(days).find(k=>days[k]===dayNumber);
  // Logger.log("DEBUG: Cell date: "+new Date(cellValue)+", day number = "+dayNumber+", day of the week = "+key)

  // h/t so_63854571
  // find the date of next Friday
  const nextFriday = (cellValue) => findNextFriday(cellValue, days.friday);

  var nextValues = nextFriday(cellValue)
  var nextTime = nextValues[0]
  var nextDaysOffset = nextValues[1]
  // Logger.log("DEBUG: next Friday: time = "+nextValues[0]+", days offset = "+nextDaysOffset)

  // convert time to date
  // h/t so_53833942
  var dateObj = new Date(nextTime);
  var tz = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
  var nextFriFormattedDate = Utilities.formatDate(dateObj, tz, "dd-MM-yyyy");
  // Logger.log("DEBUG: Date NEXT friday = "+nextFriFormattedDate)

  // find the date for last Friday
  // adapted from so_53833942
  const prevFriday = (cellValue) => findPrevFriday(cellValue, days.friday);

  var prevValues = prevFriday(cellValue)
  var prevTime = prevValues[0]
  var prevDaysOffset = prevValues[1]
  // Logger.log("DEBUG: previous Friday: time = "+prevValues[0]+", days offset = "+prevDaysOffset)

  var dateObj = new Date(prevTime);
  var prevFriFormattedDate = Utilities.formatDate(dateObj, tz, "dd-MM-yyyy");
  // Logger.log("DEBUG: Date LAST friday = "+prevFriFormattedDate)

  // update spreadsheet values
  var saveValues = []
  saveValues.push([dayNumber,key,nextDaysOffset,nextFriFormattedDate,prevDaysOffset,prevFriFormattedDate])
  sheet.getRange(2,3,saveValues.length,saveValues[0].length).setValues(saveValues)
}


// h/t so_63854571
// forumla to find "next Friday"
function findNextFriday(date,day) {

  // h/t so_4467539
  // Javascript modulo formula
  Number.prototype.mod = function(n) {
    return ((this%n)+n)%n;
  }

  const nextResult = new Date(date);
  // Logger.log("DEBUG: nextDay: result = "+nextResult)
  const nextOffset = (((day + 6) - date.getDay()).mod(7))+1
  // Logger.log("DEBUG: nextDay: offset = "+nextOffset)
  var nextOutcome = nextResult.setDate(date.getDate() + nextOffset);
  // Logger.log("DEBUG: nextDay: outcome = "+nextOutcome)
  var next = []
  next.push(nextOutcome,nextOffset)
  return next;
}

function findPrevFriday(date,day) {

  Number.prototype.mod = function(n) {
  return ((this%n)+n)%n;
  }

  const prevResult = new Date(date);
  // Logger.log("DEBUG: prevDay: result = "+prevResult)
  const prevOffset = ((date.getDay()-day).mod(7))
  // Logger.log("DEBUG: prevDay: offset = "+prevOffset)
  const prevOutcome = prevResult.setDate(date.getDate() - prevOffset);
  // Logger.log("DEBUG: prevDay: outcome = "+prevOutcome)
  var prev = []
  prev.push(prevOutcome,prevOffset)
  return prev;
}

---

---

Sample