Match Function to Apps Script for Finding Closest but Less Than Date

user_16733744 0
Viewed 174

I have an array of dates. I have a specific date "Search Date". I want to find the date in the array that is closest to but less than the Search Date and return the index of that date in the array.

Example: Suppose the array of dates is [8/1/21, 8/5/22, 8/5/23], indexed 1,2,3 for sake of example. Suppose my Search date is 9/17/22. The function should find the date 8/5/22 and return the index 2.

In Google Sheets, this is simply the match function: enter image description here

How do I do this (and most efficiently) in Apps Script? The IndexOf() function seems to return only an exact match, which does not work in this case.

Thank you!

google-apps-script google-sheets javascript
1 Answers

Explanation:

The idea would be to compare each date in the array with the search date and select the absolute minimum difference which will be the closest date to the search date:

const checkDif = checkDates.map(d=>searchDate - d).filter(v=>v>0);
const minIndex = checkDif.indexOf(Math.min(...checkDif))+1;

  • map is used to calculate the difference between the dates for every date in the input array.

  • filter is used to select only the positive differences (namely the dates that are smaller than the search date).

  • Math.min is used to calculate the minimum value of the array of the positive differences, therefore the closest date that is less than the search date.

  • indexOf is used to calculate the index of that minimum value.

We do +1 because array indexes in JavaScript start from 0 but it seems you want them to start from 1.

Solution:

See comments for further explanation and in case you want to change the ranges or the name of the sheet.

function closestDate() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet1"); // name of the sheet
  const searchDate = sh.getRange("B4").getValue(); // search date
  const checkDates = sh.getRange("B1:D1").getValues().flat(); // input range
  const checkDif = checkDates.map(d=>searchDate - d).filter(v=>v>0);
  const minIndex = checkDif.indexOf(Math.min(...checkDif))+1;
  console.log(minIndex); // 2
  sh.getRange("B5").setValue(minIndex); // return the result to B5
}

Example that works with the code snippet:

enter image description here

user_112252910
Related