Google Spreadsheet Script Missed Tasks

Google Spreadsheet Script Description

A fellow Google Docs user had the following use case: I have a spreadsheet with daily, weekly, and monthly tasks for the whole year. If the task is ‘Met’ on time the user selects ‘Yes’, but if the task is ‘Missed’ then the user selects ‘No’. When ‘No’ is selected the user should be prompted to fill out a form with the reason why. I would like to have a Google spreadsheet script that once ‘No’ is selected that the form auto pops up in the same screen & for it to be completed for each time.

As far as I know it is not possible to control a user’s browser with a script in Google Apps. Please correct me if I am wrong! Google prevents this for security reasons. After a little bit of research I found that at the time of this post it is also not possible to automatically launch sidebars or dialog boxes from within the sheet automatically also.

https://issuetracker.google.com/issues/69238694#comment7

To ensure user safety, the ability to display dialog and sidebars without explicit user action has been removed. We’re still evaluating whether or not we can support these use cases again in the future. For the time being, we suggest that scripts that display dialog or sidebars add a custom menu item (https://developers.google.com/apps-script/guides/menus) that users can select to open the dialog or sidebar.

As a work around to these restrictions I recommended using a “toast” popup when a user selects “No” to inform them to fill out the late form. The late form itself is a side dialog that is launched from an installed menu item in the Google Sheet.

Installing the Google Spreadsheet Script

Google Spreadsheet Script Editor

Before you can copy the google spreadsheet script to your script editor you will need to add an additional .html file.

Open the script editor by going to Tools -> Script editor.
Add a new .html file by going to File->New->Html file. Name it Index when prompted and click OK.

GitHub Repository

There are two script files to be installed; Code.gs and Index.html. Go to the GitHub link below to find them. Click on each file and copy the contents to your script editor. The script files are named the same as Google spreadsheet script files. Copy and paste each one accordingly.

https://github.com/EtwasShawn/GAS-Missed-Tasks-Form

Update Save Sheet

The script expects to save the values to a sheet called “Reason For Missing Deadline”. Look for line 69 and change it to the sheet name you need.


var sheetName = "Reason For Missing Deadline";

Google Spreadsheet Script Directions

The spreadsheet can script can be copied from here: Spreadsheet Link

When a task dead line is set to “No” a popup will inform the user that they need to fill out the reason for missing the deadline.

The Reason For Missing Deadline form can be located in the Task Options menu at the top.

google spreadsheet script late task launch

The spreadsheet opens as a side dialog. The Form uses data pickers for the dates, a drop down for the Media options, a text area for the reason, and text box for the email.