Overview

If you enjoy my Google Workspace Apps Script work, please consider buying me a cup of coffee!

---

TSCron is a Google Forms based Cron scheduler powered by Google Apps Script.

Cron scheduling is available for:

• Minutes
• Hours
• Days
• Weeks
• Months
• Years
• Custom date/time (runs once)

Potential uses for TSCron:

• Send a Google Docs newsletter to clients on the 1st of every month
• Populate a Google Sheet with Google Analytics data every 4 weeks
• Email event attendees a link to Google Slides 1 hour after the presentation
• Notify warehouse managers of new regulations every 3 months on the 1st of the month
• Send an email to a colleague every 3 days
• Send yourself a list of goals every January 1st
• Add new todos to a Trello board every Sunday at 9:00 PM
• Send invoices to customers on the "last day" of every month
• Post a message to a Slack channel daily at 8:00 AM
• Automatically create a Sales Forecast presentation with Google Slides and share it with your manager every 6 months
• Send school activity updates to parents every Monday at 8:00 AM
• Run a backend microservice every 3 hours
• ...

---

How It Works

A submission to a TSCron enabled Google Form initiates a new cron job (any previously scheduled cron job is removed).

Each form submission contains responses for both:

• pre-existing TSCron configuration form elements (i.e. cron interval configuration, start date and optional end date)

• any user defined form elements which are needed to implement the cron job

TSCron uses the configuration to "schedule" the new cron job at the selected start date/time and to "reschedule" the cron job at the desired cron interval. If an optional end date is specified, TSCron will stop the cron job at that date/time.

NOTE: Actual start, end and cron execution times may vary +/- 15 minutes from the original selected dates/times.

The form owner (or a form collaborator) implements the cron job in a provided cronJob() function using Google Apps Script. (TSCron executes the implemented cronJob() function on the start date/time and on each repeating cron interval.)

Cron scheduling occurs in the form owner's default time zone and takes into account:

• short months (e.g. Feburary, April, June, September, November)
• leap years
• daylight savings time

---

Getting Started

1) Install TSCron in Google Drive.

• Login to Google Drive.

• Access the TSCron form.

• Click the Use Template button. This will copy the form to Google Drive.

2) Add any user defined form elements needed to implement the cronJob() function

Locate and open the newly copied TSCron form and add any optional user defined form elements needed to implement the cronJob() function.

Be sure to add these optional form elements to form section(s) BEFORE the pre-existing TSCron configuration form elements.

Any form submission responses for user defined form elements will be passed to the cronJob() function as an array of ItemResponses each time the cron job executes.

Be sure to set the Continue to next section option on the TSCron form section preceding the pre-existing TSCron configuration section.

IMPORTANT:

• Do not add, delete or modify any form element or form section ON OR AFTER the pre-existing TSCron configuration form elements or the cron scheduler will cease to function properly.

• Do not add any user defined elements with the title Run TSCron.

3) Implement the cronJob() function

Open the TSCron form's script editor.

Implement the cronJob() function in the supplied Code.gs file using Google Apps Script. This file can be found within the TSCron form's Script Editor.

The cronJob() function will be called each time the cron job executes (i.e. first on the cron start date/time and then on each repeating cron interval) and will be passed two parameters:

• e - time-driven event object which contains information for the current cron execution (see 'time-driven events' in the Google Apps Script documentation for more information). The event object is represented in Coordinated Universal Time (UTC).

• params - an array of any user defined ItemResponses for the form elements defined in Step #2 (NOTE: If no user defined form elements exist this parameter will be null)

Best implementation practices:

• Test for a null value in the passed params function parameter

• Use the Lock Service to prevent any concurrent access to sections of code within the cronJob() function which might modify a shared resource. This is particularly important when implementing longer running processes on a short Minutes cron interval where multiple cronJob() executions could occur simultaneously.

• Wrap the cronJob() implementation code in a try/catch block and send an email on any error conditions

NOTE: Both the moment.js and moment-timezone.js libraries used by TSCron are available for use within the cronJob() function.

Here's an simple example implementation of a cronJob() function which is passed a user defined text form element ItemResponse which contains an email address. The cron function sends a message to the specified email address (or to the form owner if no email address is supplied through the form submission).

function cronJob(e, params) {

  try {
    var email = params ? params[0].getResponse() : Session.getEffectiveUser().getEmail();
    var emailMsg = 'Cron Job Successfully Executed On: '+ e.year + '-' + e.month + '-' +
                   e['day-of-month'] + ' At: ' + (e.hour-6) + ':' + e.minute + ':' +
                  e.second + ' with message:<br>';
    GmailApp.sendEmail(email, 'Cron Job Execution Success', '', {htmlBody: emailMsg});
  } catch(err) {
    GmailApp.sendEmail(email, 'Cron Job Execution Failure', '', {htmlBody: 'Error: ' + err.message });
  }

}

Important:

When implementing the cronJob() function:

• Do not create a "Script" PropertiesService value with the key tscron or the cron scheduler will cease to function properly.

• Do not create any form submit triggers using the existing TSCron function newTSCron or the cron scheduler will cease to function properly.

• Do not create any time-based triggers using the existing TSCron functions startTSCron, runTSCron or endTScron or the cron scheduler will cease to function properly.

4) Run Configure option from the TSCron menu

Run the TSCron > Configure option from the form's add-on menu.

The first time the Configure option is run, the script will prompt for authorization. Complete the authorization process by following the Google authorization prompts.

Important:

• Be sure to run the Configure option BEFORE submitting to the form or the cron scheduler will not work.

• If code requiring additional user authorization is added to the cronJob() function, the authorization process must be repeated for TSCron to continue to operate correctly. Re-run the Configure menu option to re-authorize.

5) Start cron scheduler

Start the cron scheduler by submitting a response to the form.

Special Notes About "Month" and "Year" Cron Scheduling

Month Scheduling:

If a cron execution is scheduled in a month (e.g. February, April, June, September, November) which does not contain the original schedule date of the month (e.g. 29th, 30th, 31st - determined from the cron start date), the cron will execute on the "last day" of that short month near the selected time.

For months which contain the original schedule date, the cron will execute on that date of the month near the selected time.

Example: A cron job scheduled every 1 months starting on January 31st will next execute on February 28th (or February 29th if a leap year) and then again on March 31st, etc.

If the cron job will be started on the "last day" of a short month (e.g. February, April, June, September, November) and you would like it to run on the "last day" of subsequent longer months (e.g. January, March, May, July, August, October, December) select Yes on the Short Months? configuration element. For all other cases select No.

Year Scheduling:

If the cron start date is February 29th during a leap year, the cron job will be scheduled to run on February 28th in non-leap years.

If the cron job is started on February 28th of a non-leap year and you would like it to run on February 29th in subsequent leap years, select Yes on the Leap Years? configuration element. For all other cases select No.

Important:

• When scheduling a new cron the start date/time must be at least 15+ minutes in the future of the form submission and the optional end date/time must be scheduled at least 1 hour after the start date/time. NOTE: The cron job will not be scheduled and an error message email will be sent to the form owner if these conditions are not met.

• Actual cron execution times may vary +/- 15 minutes from the original schedule dates/times.

• If a failure occurs during the initial or subsequent cron scheduling, TSCron will be disabled and an error message email will be sent to the form owner. Restart the cron job by submitting another form request.

• Cron scheduling uses the Google Form owner's default time zone (determined by the form's associated Google Apps Script project properties - see File -> Project properties -> Time zone in the form's Script Editor).

• If the TSCron default time zone is modified to a new time zone the cron schedule will remain on the previous time zone. You must submit a new response to the form to get an updated cron schedule in the new time zone.

• Do not delete form responses while there is a running cron job or the cron scheduler will cease to function properly.

• Do not delete any TSCron related triggers from the form while there is a running cron job or the cron scheduler will cease to function properly.

• Because cron schedule times can vary +/- 15 minutes, the cron job could potentially execute after the original end date/time but before the cron job is stopped.

---

Additional Options

Stop Cron Scheduler

To stop the cron scheduler, run the TSCron > Stop option from the form's add-on menu. (This will remove all cron scheduling configuration for the current cron job)

Re-Start Cron Scheduler

To re-start the cron scheduler after an error condition or manual stop, submit a new response to the form.

View Cron Status

To view the cron scheduler status, select the TSCron > Status option from the form's add-on menu.

The TSCron status sidebar will open.

---

FAQ

Will the status sidebar automatically refresh?

No. Reopen the sidebar to see any changes to the cron status.

I submitted a form response but TSCron did not start. What should I do?

If TSCron is not working properly, perform the following steps:

• Check email for an error message (form owner).


• Run the TSCron > Configure option and re-authorize the script in the event any new authorization scopes were added to the cronJob() function.


• Ensure the TSCron configuration start and end dates are set to properly allow the cron interval to run. Start date should be 15+ minutes in future of the form submission and optional end date should be 1+ hours after the start date.

Is TSCron Internationalized?

No. TSCron currently exists in English only.

Why am I receiving a quota error when the cronJob() function runs?

Google Apps Script is subject to daily quotas based upon the type of account accessing and running the script. See the "Quota Limits" tab on the Google Apps Script Dashboard for more information.

How do I ask general questions about TSCron?

For general questions, submit an issue through the issue tracker.

How do I submit bug reports for TSCron?

For bug reports, fork this repository, create a test which demonstrates the problem following the procedures outlined in the "Tests" section below and submit a pull request. If possible, please submit a solution along with the pull request.

---

Tests

TSCron unit tests can be found in test.gs.

To perform TSCron unit tests:

• Add the test.gs file to the TSCron script editor.

• Install QUnit for Google Apps Script by adding the project library (see the project documentation for proper install instructions).

• Deploy the script as a web app (execute script as self).

• Set the desired unit test configuration entry in the testConfig object to true (see the test.gs file for information on where to modify these values to enable tests). NOTE: Some tests can take longer to run so best to run them individually.

• Access the deployed web app for test results.

---

License

TSCron License

© Laura Taylor (github.com/techstreams). Licensed under an MIT license.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

3rd Party Licenses

File	From	Copyright	License
moment.gs	Github	Copyright (c) JS Foundation and other contributors	MIT License
moment-timezone.gs	Github	Copyright (c) JS Foundation and other contributors	MIT License