I'm often asked to handle routine tasks, one of the most common being the opening and closing of responses for a Google Form. There's nothing quite like sitting at your computer at 9:59, anxiously waiting to toggle the "Not accepting responses" to accepting at exactly at 10:00.
This manual process often results in a flood of emails asking why the form isn't opened yet because users are sitting on the response page, unaware that Google hasn't updated their screen.
There has to be a better way... and there is, through Google Scripts.
Here's the script that will automate the opening and closing of a Google Form that's linked to a spreadsheet:
function OpenResponses() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const formURL = ss.getFormUrl();
const form = FormApp.openByUrl(formURL);
form.setAcceptingResponses(true);
}
function CloseResponses() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const formURL = ss.getFormUrl();
const form = FormApp.openByUrl(formURL);
form.setAcceptingResponses(false);
}
1. Open/Create the Response Spreadsheet for the Form
- Ensure only one form is attached to the spreadsheet
2. Access Google Apps Script
- Go to "Extensions - Apps Script" in the top menu
3. Add the Script
- If you do not have a script, delete all existing text
- If you already have some scripts, click on the "+" and create a "Script" called "Open/Close Form"
- Copy and paste in the code above into the script editor
- Click on the "Save" icon
4. Authorize and Run the Script
- Select "OpenResponses" from the dropdown, click "Run," and follow the prompts to authorize the script
- Click "Review Permissions," choose your account, and click "Allow"
- This step sets up the security settings for the script but does not run it
5. Set Up Time-Driven Triggers
- Click on the "alarm clock" icon (triggers) on the left
- Click on the "+ Add Trigger" button
- Pick which one you want to run (i.e. OpenResponses) under the "Choose which function to run"
- Under "Select event source" choose "Time-driven"
- Under "Select type of time based trigger" choose "Specific date and time"
- Enter in the date and time noting the time zone and that the time is to be entered in the 24 format (i.e. 3:00 pm is 15:00)
- You can set the time zone back in the spreadsheet under "File - Settings"
- Click on "Save", click on your account, scroll down and click "Allow"
Editing and Deleting Triggers
1. Edit a Trigger
- Go back to the Triggers
- Open Spreadsheet, select "Extensions - App Script" then click on the "alarm clock" icon
- Find the trigger you want to change, hover over it, and click the pencil icon
- Change the information and click "Save"
2. Delete a Trigger
- Go back to the Triggers
- Open Spreadsheet, select "Extensions - App Script" then click on the "alarm clock" icon
- Find the one you want to change, hover your mouse over it, click on the three dots (snowman?) and click on "Delete trigger" then "DELETE FOREVER"
Now, you can automate the process of opening and closing your form responses, freeing you from the stress of manual toggling and ensuring a smooth user experience. Enjoy having one less thing on your "too stressful for no reason" list.
Comments ()