Need Script to run 2x a month on schedule

[Lori Nedd]

Need to run this script which updates a date field on a spreadsheet which is then emailed as a pdf to serve as a completed timesheet record for salaried employees automatically. Their pay date is 2x a month the 15th and the end of the month. So the pay periods would be the 1st to 14th and the 15th through the 31st of each month.

function ChangeDate() {

  const ss = SpreadsheetApp.getActive();

  const timezone = ss.getSpreadsheetTimeZone();

  const sh = ss.getSheetByName('Employee');

  const date = Utilities.formatDate(new Date(), timezone, "MM/dd/yyyy");

  sh.getRange('A7').setValue(date);

I read that if I use the built in triggers it will not run if there is no 31st of the month. I have looked at several scripts that include search for last day of month but they are very complicated and do not pertain solely to this type of simple script. 

Currently, I have settled on just using a trigger to run on the 16th and 28th day of the month to be safe since these are just for receipts anyways, but I would like to get it to run on the correct days. 

I have an actual sheet that lists all of the pay dates and periods through the end of 2023 if I could get a script to check each date and only run if the current date matches one of the dates on the list. But again all of the ones I found are so complicated and do not pertain to this simple type function of just changing the date field to the current date that the script is ran and I am le beginner... 

Not sure which is the best way to go at this point.

Thanks in advance!

Lori

[Jon Couch]

Lori, 

It's likely there is a more elegant solution than this suggestion, but this is how we implement a payroll reminder. We have a spreadsheet that has 3 columns. One column contains dates for the 15th and the last day of the month. The logic of the last day of the month is in the sheet, not the script. The sheet has a helper column that sets a flag, I think it says "Ready" if it is after 8:00 AM on the day in the adjacent column AND there is not a timestamp in the corresponding column for that date. A time trigger script checks the spreadsheet periodically and if it finds a "Ready" it sends the reminder email and records a timestamp in the timestamp column. Every subsequent firing of the trigger results in no action until a "Ready" shows up on the last day of the next pay period. 

=if(iseven(row()),eomonth(A50,0),edate(indirect("A"& row()-2),1))

=if(and(now()>A94+8/24,C94=""),"Ready","")

You could probably build this logic into your script, I've just never taken the time to try.

I hope this helps, 

Jon

--
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/a54895ea-accb-4574-b5ed-48f09deada46n%40googlegroups.com.

[Clark Lind]

It may seem complicated, but scripts are only as smart as the instructions they get :)   The simplest things can tend to be the toughest to code, and date/times are one of those. Consider leap years in what you are trying to do... lol

But you have made it easy for yourself by already including your paydates.  What I would do is grab the column of paydates, put them in an array, and then just see if the current date is in the array. If yes, change the date. Then I would just have a daily trigger to run the code and not worry about installing triggers based on date and all that.

So say you put your paydates just by themselves on another tab called "Paydates". Make sure they are in the same format as "MM/dd/yyyy".

Something like this set to run daily should do the trick. (My changes to your code are highlighted)

//passing in the date from below function

function ChangeDate(date) {

  const ss = SpreadsheetApp.getActive();

  const timezone = ss.getSpreadsheetTimeZone();

  const sh = ss.getSheetByName('Employee');

 // const date = Utilities.formatDate(new Date(), timezone, "MM/dd/yyyy"); can be removed since date is being passed in

  sh.getRange('A7').setValue(date);
}

//this "flat" is a helper function to flatten a 2-dimension array into a 1-dimension array
//it converts this:   [ [date1], [date2], [date3] ]

//to this (which is much easier to work with):  [ date1, date2, date3 ] 
const flat = (arr) => arr.reduce((a, b) => (Array.isArray(b) ? [...a, ...flat(b)] : [...a, b]), []);

function checkDate() {

//Get the paydates and convert to a 1-dimension array

const ss = SpreadsheetApp.getActive();

const sh = ss.getSheetByName('Paydates');

const range = sh.getRange("A1:A");

const data = flat(range.getDisplayValues()); //assuming the formats are correct on the sheet, just grab the existing (display) values
//or condensed as  const data = flat( SpreadsheetApp.getActive().getSheetByName('Paydates').getRange("A1:A").getDisplayValues() )

//get today's date

const date = Utilities.formatDate(new Date(), timezone, "MM/dd/yyyy");

//check if today's date exists in the array. If yes, run the ChangeDate function
//if the date is not in the array, it returns "-1", otherwise, it returns the location the within the array where the date matches (e.g., the index), which may be "0", the first array element

   if (data.indexOf(date) > -1) {

     ChangeDate(date)

   }

}

Doing it this way means you don't have to loop through all paydates to find a match, it is either in the array or not. If yes, run the changeDate function.