Close Google Form On Weekends in Google Sheets
Learn how to automatically close your Google Form on weekends using Google Sheets and Apps Script. This tutorial covers creating functions and triggers to manage form responses effectively.
You want to close your Google form on weekends. Maybe you have new leads coming in every day and on weekends, you don't want those leads or even you're taking in repairs. Say a construction company has a repair form and says, Hey, we don't take repairs on weekends because we can't fulfill those within the next 24 hours. We're only going to accept form responses. On weekdays. We want to close them on weekends. I'm going to show you the two ways. So actually it's one way, but we need to do two things. We need to create a function and a trigger , but we need to do twice because we need to open them and close them. Let's go. We have our attendees. Let's say we have a form attendees. This could be new leads. This could be anything we can say. Earl has a response here and it shows up on our sheet.
How do you code this? We're going to go over to extensions app script . Don't worry. This is a very simple code. We can write it right now in just a couple minutes. We're going to write, end up writing two functions . One, we're going to write open form, and then we're going to write function close form. They'll be very similar so we can copy paste our code. First off, we're going to need the URL of the form. So we'll say variable form URL SpreadsheetApp .getActiveSpreadsheet. That's just getting the entire workbook. GetSheetByName. This is the name of the sheet that your form is connected to. Right now it's attendees. This could be leads. I'm going to change it actually to leads. And our form title could be leads as well. But this, this tab right now is called leads. So let's call it leads here.
Leads. Get form URL . There's a little funky, uh, formula or function here that gets the URL of the form. If that sheet has a form connected to it, so it doesn't get anything else, just the form URL . Now let's apply that form URL form app. dot open by URL form URL . This doesn't actually open the form. It's just connecting this app script to the form. And the very special function we need to do is set accepting responses. And here we're going to write true, but we're going to copy paste this entire function to close. And the only thing we're going to change is true to false. That's the only difference between these. Open form means yes, we're going to accept responses.
False, closed form means we are not accepting any responses. Save, command s, click this save project or control s on windows. Anyway, you save it, save this first. We can run this if we want. Let's run closed form. We'll select closed form here. We'll run this. If you have to authorize at this moment, authorize it, allow, and let's go refresh our sheet. And now it is closed. No longer taking. Let's open and let's just test this open. Click run. It's ran. Let's refresh our Form, and now we can take in more names or attendees or leads, whatever we want, they are being accepted. So we know that these triggers or these actually functions work.
Now we need triggers. We need to set up two triggers, one for each one. Let's go over to the left side triggers on the bottom, right? We're going to click add trigger . Now we need to create two triggers. One, we're going to open our form on Mondays. So let's click open form, select event source to time driven, select type to week timer. Every Monday we're going to open it midnight to 1am. Or maybe at the start of the day, like you want to do 5 to 6am before, just before the start of the day. Let's save it. Now that we open our form on Mondays, we need to close it on Fridays. Click add trigger again, choose which function to run, close form. Select event source, time driven, week timer, day of the week, every Friday.
And we're gonna actually, at the close of the day, 4 5pm, click save. Or maybe at the beginning of the day, maybe you don't take any new leads on Friday, or half of the day on Friday, because you can't fulfill those. Either way, whatever good for your business. Now we have two functions , one that opens the form every Monday and one that closes the form every Friday. And that's how to close Google form responses on weekends. Hope you enjoyed this. If you're looking to automate your forms , Google sheets, even Google slides. Now I got some videos over there. Subscribe here to better sheets today on YouTube.