Uncheck checkboxes by multiple timestamps in different categories

[Csanád Fazekas]

Hello everyone,

I am seeking your advice in a months long issue I have with my script.

Our company deals with mobile carwashing service, and one of our B2B partners has a gigantic fleet with two bundles - 30 and 60 days ordering cycle.

Therefore I should make the data to uncheck itself either after 30 or 60 days (Base or Optimum - specified in a column for each car).

I am currently at this phase:

function unCheck(){

  var d = new Date();

  var props = PropertiesService.getScriptProperties();

  var day60 = 5184000000; //milliseconds for 60 full day 

  var day30 = 2592000000; //milliseconds for 30 full day

  var keys = props.getKeys();

  for(var i in keys){

    var t = parseInt(keys[i]);

    if(d.getTime() - t > day30){

      SpreadsheetApp.getActiveSheet().getRange(props.getProperty(keys[i])).setValue(false);

      props.deleteProperty(keys[i]);

    }

  }

}

How shall I approach this?

Appreciate your help,

Csanád

[Clark Lind]

I would start by getting the column values that specify Base or Optimum.  If Col "C" then:
//var types = SpreadsheetApp.getActiveSheet().getRange("C2:C").getValues()  //assuming C1 is a heading. start at the first data row

Then, I'm guessing you have a column that shows the date the last time each car was washed? That would be better than keeping data in property service for 60+ days.

//var dates =  SpreadsheetApp.getActiveSheet().getRange("D2:D").getValues()  //assuming D1 is a heading. start at the first data row

Then, let's assume the checkboxes are in Col E. 

-------------------

So first, add this function at the top of your code, outside of any function. It takes in a 2D array, and returns a 1D array. They are just easier to work with.

const flat = (arr) => arr.reduce((a, b) => (Array.isArray(b) ? [...a, ...flat(b)] : [...a, b]), []);

Then define the sheet and hold it in a variable:
var sheet = SpreadsheetApp.getActiveSheet();

//flatten both arrays

var types = sheet.getRange("C2:C").getValues()  //assuming C1 is a heading. start at the first data row

types = flat(types)

var dates =  sheet.getRange("D2:D").getValues()

dates = flat(dates)

Define today:
var d= new Date();

Now iterate through the dates (also keeping track of the array index)
dates.forEach( (date, index) => {

    //do your date comparison however you want
    if ( today - new Date(date) >= 30 && types[index] === 'Base' ) {
        sheet.getRange("E" + index +2).setValue(false)

    } else if ( today - new Date(date) >= 60 && types[index] === 'Optimum' ) {

        sheet.getRange("E" + index +2).setValue(false)

    }    

})

Essentially, I'm doing the same as you are with your code, just more explicitly.

Putting it all together:  (untested, may not work as is)

const flat = (arr) => arr.reduce((a, b) => (Array.isArray(b) ? [...a, ...flat(b)] : [...a, b]), []);

function unCheck(){

  var sheet = SpreadsheetApp.getActiveSheet();

  var d= new Date();

  var types = flat(sheet.getRange("C2:C").getValues())

  var dates =  flat(sheet.getRange("D2:D").getValues());

  dates.forEach( (date, index) => {

    if (  (d.getTime() - new Date(date).getTime()) / (1000 * 3600 * 24) >= 30 && types[index] === 'Base' ) {

        sheet.getRange("E" + index +2).setValue(false);         //adjust what you add to index based on how many rows you skip + 1 since rows start at 1 and index starts at 0

    } else if (  (d.getTime() - new Date(date).getTime()) / (1000 * 3600 * 24)  >= 60 && types[index] === 'Optimum' ) {

        sheet.getRange("E" + index +2).setValue(false);         //adjust what you add to index based on how many rows you skip + 1 since rows start at 1 and index starts at 0

       }    

    })

}

[Csanád Fazekas]

Thank you very much, it was very educating!

I have been a bit absent-minded, I have a column describing the number of days since the last wash, how would the script look like with such an option? I have tried to make a comparison instead of the method with dates above - what do you think?

Csanád

[Clark Lind]

Then it should be much simpler since the calculations are already done :)
(Change these to the appropriate columns):

const flat = (arr) => arr.reduce((a, b) => (Array.isArray(b) ? [...a, ...flat(b)] : [...a, b]), []);

function unCheck(){
  var sheet = SpreadsheetApp.getActiveSheet();

  var daysSinceWash = flat(sheet.getRange("E2:E").getValues()); //column containing number of days since wash
  var types = flat(sheet.getRange("C2:C").getValues());  //column containing wash type

//Iterate over wash type, and if # days meets criteria, uncheck the box (change the column from "F" to your column)
  types.forEach( (type, index) => {
    if ( type === 'Base' && daysSinceWash >= 30 ) {
       sheet.getRange("F" + index +2).setValue(false);
    }
    else if ( type === 'Optimum' && daysSinceWash >= 60 ) {
       sheet.getRange("F" + index +2).setValue(false);

[Clark Lind]

And if it were me, I would also sort the sheet by type and # of days so those needing washing are at the top. You could even add some conditional formatting to row: if overdue, Red, if within 3 days, Yellow, etc etc.  :)

[Clark Lind]

Heck, you could even have it send you a daily email with the cars due or coming due. :)  OK, I'll stop now.

[Csanád Fazekas]

Unfortunately, it is not doing anything really, does not uncheck the boxes.

I am out of ideas...

[Витя Коледа]

пн, 6 июн. 2022 г., 02:14 Csanád Fazekas <csanad....@cleango.hu>:

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ebdf3d76-f457-4ebd-9584-1dce023816f2n%40googlegroups.com.