Close Form After Certain Number of Responses

Intermediate

About this Tutorial

Create automatic triggers that close a form after a certain number of responses.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>0:00 Here's how to close a form after a certain number of responses when the form is attached to a Google Sheet, and we're going to use Apps Script here. 0:08 So, first off, we need a sheet and a form. Let's create the form. This is when they're attached and there's only one form for the sheet. 0:17 So we're going to create a form here. We're just going to ask name. And we're going to publish .

Now, this is really good for things that are like attendees or a certain number of people have to respond and then no more responses. 0:37 Maybe signing up for a coupon code or something like that. It's a publish and so we have a publish sheet where accepting responses right now we have a link. 0:48 Here, let's copy that link and put in some responses. And let's just check that the form works totally fine. We have our form responses here. 0:59 We get an attendee, let's submit another response. Click submit and everything is working fine. It is accepting responses.

Let's close it after a certain amount. 1:12 So we'll have to write some App Script . That's over in extensions App Script . That'll bring up this menu. Uh, the first thing we're gonna write is, let's just check how many responses we have. 1:27 And then once we have our limit , we're going to close it. So let's call this. Let's check form. We'll have an event because we'll have the form response. 1:42 Actually, we don't even, I don't even think we have to have that. Um, event.

Uh, first off, Let's just get the form URL . 1:57 Variable form URL equals, Let's spreadsheetapp .getactofspreadsheetgetformURL. This will get us the URL of this form here, so we don't have to keep, uh, we don't have to go and copy-paste it anywhere. 2:15 Let's actually get the form, uh, through formapp. So variable form equals, formapp.open... a URL, and we have our form URL right there. 2:27 And let's see. We're going to use the Boolean , um, is accepting responses and get responses linked, so let's, logar.log. 2:41 Form.is accepting responses. This will give us a true or false.

Uh, logar.log. Form.get responses dot length. 2:55 Okay, let's check this. And actually, let's have this, um, actually, we're running right now just to check it and see what occurs. 3:08 Just make sure we're getting the right thing. It is true, it's accepting responses, and we have two right. Now, so we now can get the number of responses we have, and no, is it accepting responses or not. 3:19 So, if, and let's get this length out of the log and as a variable variable response count equals this. So, if, response- response count is greater than 50.

Actually, let's do it less. 3:42 Let's, uhh, you can put any number here. Uhh, greater than three. Then what are we going to do? We're going to close this form. Form dot set accepting responses and we'll check, change. 4:01 This to false. And then just to log at logger dot log, we'll put in quotes accepting and put a response here, this one. 4:21 Form is accepting responses. Is it accepting responses or not? Cool. So, if we run this right now, nothing will happen because this response count is not greater than 3.

But we want to run this every time that a response is sent. 4:40 So we do need to do one more step. Let's go over to triggers. And on the bottom right, add trigger . 4:48 We're gonna choose- Switch function to run. If you have multiple functions , choose which one it is here. Check form. We're going to select event type on form submit and click save. 5:00 So now the very first one we put in here, once we have this, will not close the form because we'd have exactly 3. But what- it should close the form once we have- Have a good day. 5:13 3. So let's say Carl signs up.

And we can go back to our app script and look at our executions and see is it executing correctly? 5:23 It's completed. Maybe there's a log here. There, it's true, it's accepting. And let's now submit another response. Dave. Is kinda come to. 5:40 And let's see, is this going to close the form? It says completed. This one. Open. Fire off. And now we go back to our form actually before we looked at those executions. 6:07 Now this form isn't accepting. So let's submit another response. And now the form is no longer accepting responses.

So this totally works. 6:17 Let's refresh this last one. Just double check what this log says. Yup. Accepting full. Great. So now we can come- back to our form and we can manage it and- and choose to turn it back on. 6:30 Not accepting or accepting responses. But now the cool thing is that it will close it automatically after a certain number of responses. 6:43 That's pretty awesome. So if it's greater than three, it closed. closed.

So it didn't close it on the third one. 6:51 So if you wanted it closed on the third one, you just say greater than or equal to three, in your app script . 6:59 Here, and, yeah, that's it. So going over this, we have two things we need to do, right? We need to create this function and put it as a trigger for on form submit . 7:10 This works if you have one form in your- sheet. If you have multiple forms , you just need to get the URL of each one and then choose which one you want to open by URL here. 7:20 You can put in a text here of your URL, form URL . Totally fine.

I've find it a little more compact and a little more interesting to get the form URL automatically here without having to copy-paste the string here. 7:33 But up to you how you want to do it. And we're just using this if, uh, I have for counting the response count and saying, you know, we can do greater than or equal to if we want to here, just to make sure we have a very specific amount. 7:48 Thanks for watching.</div>