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.
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.
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.
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.
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.
Submitting the form saves the response to the Reason for Missing Deadline sheet. The form presents the options to submit again.
Changing the HTML Elements
The Task Form can back be updated in the script editors Index.html file.