Similar to my question, I want to highlight the objects that repeat within 30 days according to current date. for eg when I bought an object on 01-01-2022, if i buy that product again after 20 days then those objects must highlight(because the range is within 30 days). then after 15 days i buy the same product again then it must highlight like which i bought second and third time ( because first buy and third buy are more than 30 days and second buy and third buy are within 30 days).

Hope you got what i need. sorry for my bad english.. here a sheet shared below. there you can see an estimated output too.

DiffInDays:

function DiffInDays(Day1,Day2) {
  if(Day1 && Day2 && (Object.prototype.toString.call(Day1) === '[object Date]') && (Object.prototype.toString.call(Day2) === '[object Date]')) {
    var day=86400000;
    var t1=new Date(Day1).valueOf();
    var t2=new Date(Day2).valueOf();
    var d=Math.abs(t2-t1);
    var days=Math.floor(d/day); 
    //Logger.log(days);
    return days;
  } else {
    throw 'Invalid Inputs';
  }
}

CodePudding user response：

Quite clumsy, but it works (I hope):

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var colors = range.setBackground('').getBackgrounds();
  var data = range.getValues().reverse();
  var today = new Date().setHours(0,0,0,0)
  var thirty_days = 1000 * 60 * 60 * 24 * 30;
  var index = 0;

  while (data.length) {
    let [date, product] = data.shift();
    let same_product_index = data.map(x => x[1]).indexOf(product);
    if (same_product_index) {
      let dates = data.map(x => x[0]);
      let product_date = new Date(date).getTime();
      let same_product_date = new Date(dates[same_product_index]).getTime();

      // conditions
      let cond1 = Math.abs(product_date - same_product_date) < thirty_days;
      let cond2 = Math.abs(same_product_date - today) < thirty_days
      let cond3 = Math.abs(product_date - today) < thirty_days
      let cond4 = colors[index][1] != 'yellow';

      if (cond1 && cond2 && cond3 && cond4) {
        colors[index][1] = 'yellow';
        colors[index   same_product_index   1][1] = 'yellow';
        }
    }
    index  ;
  }

  range.setBackgrounds(colors.reverse())
}

Output:

Note: I changed date in the row 4 to '07-Jul-2022'. Since I don't understand why in your estimated output the row 4 (03-Jul-2022 GLASS) is highlighted. There are more than 30 days between '03-Jul-2022 GLASS' and '05-Aug-2022 GLASS'.