Form Automations: Close Multiple Forms When Over Limit

Advanced

About this Tutorial

Adding to the Form Automations:
Allow User to Reopen form without Apps Script access
Set Limit in a Sheet, not in Apps Script
Set Limits for different Forms

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>00:00 So, in a previous video, we've created a automation in forms which closes the form when you have a certain number of responses. 00:12 And I want to add some more advanced features, such as, if we, as a creator of a sheet, create this automation, and there's another user, the user of the sheet, or even ourselves later on, we don't have, we don't want to re-open Apps Script to turn on the form again, to allow more people. 00:35 Say, you have a certain number of attendees, but you want to, uh, open it again for one or two more. 00:42 Also, if we're building this automation, we don't want to have to go into the Apps Script again.

To have this number change. 00:50 So, I'm going to put a limit somewhere in the sheet, and then, if I ever want to change that limit , I do not have to go into the Apps Script and change it. 00:57 Also, we're going to create multiple forms that link to the same sheet. How do we set limits for those different forms ? 01:05 So, that's what I'm going to go over in this video and work through. So, let's create the re-open the form, or re-open the, uh, allow responses. 01:16 So, what we're going to do is we need a unopen, so we're going to, unopen menu.

So, we're going to go to bettersheets.co slash snippets and get that code. 01:24 It's a very simple code here. Function on open. We're going to go add that at the top. And the menu We're going to call, Automations. Or actually, Apps Script menu. 01:40 You can type anything you want here. This text is going to be a different menu. And we're going to create a function , open responses. 01:50 And this will take this form that we have. So, we're just going to get the same form URL . We're going to get that form. 02:00 Form.setAcceptingResponses to true. That's it.

It's just going to open responses. Let's format document, save. 02:12 This open responses is going to be here. And we're going to say re-openResponses. Save. We don't need a second item. Save it again. 02:29 Now this on open will happen when we close our Apps Script and we re-open the sheet itself. So let's do that. 02:36 And next to help we'll have a menu. Here we can actually make it, there, there it is. Apps Script menu, re-openResponses. EditForm. I think it's closed right now. 02:52 This form is not accepting responses.

And let's go up to the Apps Script menu and re-openResponses. We do have to authorize it the first time we run it, probably because we added FormApp, I think. Allow. Let's go and just re-open again and see. 03:20 I think it's accepting responses, yeah. So now if we fill one out, let's go and fill one out. One more name, and I think it will close. 03:37 Let's close again after one. Let's refresh it, and let's see if it closed. Might have, oh, it only has one response, so it actually cleared out the past responses from that form. 04:01 Let's add another one.

One more, and I think we need to submit one more. A third? Or more. More than three. 04:11 Submit one more. Is this it? Let's go see if that, uh, maybe the trigger is not created in this one. 04:32 Oh, I don't think we have the trigger in this sheet yet. Yeah, so we've had to add this trigger . That's what we're missing in this, oh, uh, check form. 04:45 In the past video we ended on this, that we already had the trigger done. So, we need to create that trigger again. 04:52 Now the trigger is there, so let's get one more name with trigger . Yay! Let's see how fast that happens.

Is it executing? 05:05 If we want to submit another response. This form is no longer accepting responses. Great! Now let's go check reopen responses. 05:13 Let's refresh. Now we can take more responses. Great! 05:25 So, that is the first one is now we have a, the user is able to open access again. Let's set the limit in the sheet, not in a, in the Apps Script . 05:37 So, if we have this sheet that we're on called Settings, let's say, and we say, Limit Form Responses, put a number here, let's say 2.

In this cell, B10, we need to get the number from there. 05:55 So, let's go to our editor and change that. Right now, our response count and I'll see you in the editor , is greater than or greater than 3. So, we'll call this Limit . 06:06 We'll actually say greater than or equal to Limit . So, it's not more. Variable Limit equals SpreadsheetApp .get getactivespreadsheet.getSheetByName, the name is settings, getRange is b10. Let's double check that that's correct, b10, yes, onSettings, and getValue. 06:31 getValue means we'll get the value inside that cell. Let's save it.

And now our limit is 2. I think our form is open, so we need to put in two names. 06:48 Norm. Submit another response. Franco. Submit. And now we're no longer accepting responses. Cool. All that has happened. We have now set a limit in the sheet. 07:02 But let's say we want to set limits for different forms . Let's go create a new form and connect it to our sheet, form.new. Second event. 07:21 Just want a name. In our responses, we'll link to sheets. We'll select an existing sheet. I think it'll be this one. 07:35 Let's go back to our sheet. There it is.

So we have Form Responses 1, Form Responses 2. So our first one is Form Responses 1. Our second one is Form Responses 2. And when sheets are coming, uh, when form is being submitted, we're using this checkForm function . 07:55 We're gonna add an e. This e gives us really interesting information. It's an event. We'll say variable URL equals e. range .getSheet I think that's it.

So this URL, logger.log URL, is different for these two different sheets. 08:35 So let's go and get those URLs, and then we will set ifs, because we need to get the value from a different place. 08:48 So let's go to our settings and see. Let's say form 1 and form 2, we want, let's say, a limit of 4 on form 2, and a limit of 2 on form 1, or 20, or 50, or 100, doesn't matter the number here. 09:02 But form 1 is going to be 10, and form 2 is going to be 11. So we'll log the URL. 09:12 that URL will probably be somewhere.

It's similar to something like this up here, but let's just double-check. I think this is our second event. 09:22 Yep, publish it. Then you want the link. Done. Again, let's copy this link. And say, Carl, submit. 09:44 And in our executions, this is our latest one, the URL. False. 09:56 There is our URL. So this is 1GARV, and actually, it is very different from this form URL . 10:13 Oh, this is it. 1GAR. So, the URL is not the shared URL you're sharing with people, but it's a URL of the form itself. 10:24 Right here. Let's double check that that is correct.

That is just with view form on the end. So we'll take that URL. 10:37 And now we can, so, check here, let's say, we'll put this in here, we'll say, variable form 1 or 2. 2, actually, equals this URL, and variable form 1 equals, we need to go back and get this, instead of closed form, view form. 11:06 So now we have our two separate URLs. So if URL is equal to form to we'll do something here, this limit , and we're going to do something different if the form is 1. So actually, I think 1 is B10 and 2 is B11.

There we go. 11:35 So now we have our form checker is getting the URL of the submitted form, checking which one, looking at the limit , and then saying if that limit is, if our response count is greater than or equal to the limit , let's close those. 11:52 And let's also change this open responses so that we open our different forms .

So we'll say open responses, open responses form one, and we'll create a whole nother function for open responses two. 12:08 So form two is this, so instead of URL getting it automatically, we're going to select it very specifically. 12:21 And there we go. We have Form 1 and Form 2. And we can add another item up here for our menu. 12:31 Reopen Form 2. Reopen Form 1. Let me just make sure we're calling the correct form number here. 12:47 Great. Let's close this. Refresh our sheet. And now, next to Help, we have two items. 12:59 Let's reopen both.

Oh, the ID is going to be different, I think. Let's look back at our extension Apps Script and see what happened. 13:10 We might, I don't think we need this View Form. 13:25 Let's try that. So what the issue was, was this, I needed the edit URL, so just went to the sheet itself, or sorry, the form itself to edit and got the edit URL. 13:56 We're going to turn on, actually we don't need to do that. We can go to item. Our sheet and now try it. 14:08 So we finished script. It has reopened the form. We can go to tools, manage form, edit form and see if it is taking. 14:21 Perfect.

It's taking responses now. So now let's check if it's actually checking the correct one. If it's looking at this limit here, form one, it's going to be here. 14:36 Let's go and manage form. Go to live form. Let's fill in a couple. Andrew. Carl. It's continuing to let us set, but our form one was supposed to be. 15:03 Oh, I think our Apps Script then needs fixing here. Right, so we need to make sure that we're doing it to the right form. 15:24 So let's grab these. And instead of just the limit , we need to actually be setting the correct form.

I think it's this edit URL instead of this view form. 15:53 So form 1. This is form 1. And this is form 2. There we go. 16:13 So inside of our if function , we're getting the correct edit URL, and then we're editing the form responses, is accepting to false. 16:25 So let's do that again. Think 1, 2, once we put in one more Carl2, Frank again. 16:44 It's giving us an invalid argument URL. Let's Let's look at that on line 43. Seems like we just have the wrong URL here. 16:55 So let's put that in.

And we will try again. 17:08 And it worked. 17:20 It just took a minute before it showed up. I was clicking too fast. So now the form is closing correctly for the first one. 17:28 Let's look at the second one. Submit another response. This one has a limit . Let's change this to 2. And put in Andrew. 17:39 Let's see if that closed. And that already closed. Cool. So these are working now correctly. And here's a code. 17:54 Click down below for the code of these forms . It is a little tricky dealing with form URLs. And edit versus view form. 18:03 Bye.

But hopefully you can go and view this code and see what we're doing here so that we can set limits for different forms .</div>