Automate copying a template for new form submissions with user data inserted into the new sheet.
Academy ↓
Hey there stranger!
Create New Worksheet When Google Form Submitted
About this Tutorial
Sheet Resources
Video Transcript
00:00 So this video is great for people who are creating a new worksheet, like an entire spreadsheet file anytime someone is submitting a form.
00:09 So we are going to do this automatically and why would you do this? Maybe you have a consultancy or some kind of service you provide where everybody who either signs up or even a lead has a customized sheet from a template.
00:25 So what we need to do is we need to create a sheet that is going to automatically create a new sheet.
00:30 But it's going to take an existing sheet and copy it. So we will need two sheets. One sheet to do the app script and a second sheet that is going to be the template that we will copy.
00:40 But the interesting part about this, not just that we will create the new sheet every time a form is submitted, what's going to happen is any of the information on Google Forms that we get, we can put that into the new sheet.
00:57 So you might find yourself creating a new worksheet from a template. And paste, copying and pasting that information from form responses into your sheet.
01:07 So that's what we're going to do. I'm going to show you a really quick example of this here. And we're going to recreate this.
01:11 We're going to do this step by step. I'm going to show you the trigger you have to use. I'm going to show you the app script.
01:15 And you're going to be able to download this if you are a BetterSheets member right now. Down below here at bettersheets.co, if you're watching this video on bettersheets.co, you could be getting this exact sheet and the code in it.
01:26 So let's do this. So we have a form here. we have a name. Andrew. Some notes or information. I'm a Google Sheets wizard.
01:43 And you can put an email. This is going to be interesting. We, if we have some time at the end, we'll automatically send an email.
01:51 Or actually not even send an email. We will send an email, but we will add them as a editor of the sheet or a viewer of the sheet.
01:58 I will show you how to do that so we'll do Andrew at bettersheet.co. Submit. That is submitted to this form.
02:06 Ah, form responses here. But also it's creating a line here and it is creating a brand new worksheet and it's telling me the URL.
02:16 So I can go to that right now and I can see here it is. And that information that I just entered on the form is already here.
02:22 Contact information, the email, the ah information I put in that I'm a Google Sheets wizard, my name, it's already here.
02:29 Maybe Maybe this is like a delivery. Deliverable list and says, hey, you're starting our consultancy or our service and here's all of the things we're going to do.
02:38 Maybe milestones or goals or things that need timestamps or dates or approvals. Maybe you have a client portal that you say, hey, we're going to create a worksheet for you and we're going to present that data and then you just approve all that stuff that happens, right?
02:52 This is fantastic for those kinds of examples, right? Okay. I will show you what we need. First thing we need, is we need a template.
03:02 This is a brand new, different Google Sheet. This is called, I'm calling it consulting template. You can call it anything you want.
03:10 What we need here is the URL. We're going to go back to our sheet where you create the form. We've created a form already and we have those form responses.
03:19 Now you already know this or if you don't, you can just create a new form in your Google Sheet. It will be automatically connected to your Google Sheet, but it's by form responses.
03:28 We don't have this new URL, so we're going to have to do that with, extensions app script. We open it up and this is what I've already done for you.
03:36 It's only about 30 lines. It's actually less than 30 lines of code because there's a bunch of spaces here. And I'm going to walk through all of this because we're going to act like this doesn't exist yet.
03:47 So we're going to call function make a new sheet. We're going to add the parentheses and some curly brackets to make sure that we're going to have a function to operate.
04:01 Then we put this in a trigger. We're going to put this in a trigger called on form submit. It's going to have some data for us.
04:07 We can use e here. A lot of ah developers do that in app script just to make it easier. We can also call it event or form or submission.
04:17 This can be any text we want. We were, we want to get all of the information that's built into that submission, right?
04:25 Whenever a form is submitted there's data in there and we want to access it. So we're going to create a variable called form.
04:32 Values. And we're going to say equals submission. That is the package of data that is the event that's happening. The submission of the form.
04:42 And we're going to use named values. Now what this is is a special sort of section of the data that gives us a key which is the name item and the answer.
04:57 And so we're going to go through this, and we've done it here before. We're going to have a for loop for key in form value.
05:06 So inside of that is two pieces of data for every single uhm form item. And that's what we're going to get here.
05:16 We're going to say variable key equals key dot to string. So that it actually gives us the data that's involved there.
05:29 We are also going to use, get the data. So we need to do that with a bracket notation of the key.
05:38 And we're also going to change that to a string. So that we actually can read the data. If we don't do the to string, it'll be like an object and it'll just say literally object.
05:48 That's what it'll say. Now we know our item names. We know the key. The key is, let's submit another response, is this name, information and email.
05:58 And if we edit this, cause we can edit. This form, right? If we edit it, let's see. We can edit this to be your name, right?
06:09 This is the key. Whatever you're naming each of the items. And the response is going to be that data. So we name this if the key is equal to, we need two equal signs, name.
06:29 Then we can assign some variables. Variable name of client equals whatever this data is, which is the key here. We need to put this in brackets, actually.
06:49 And then if, we're going to keep going with each of these items is equal to information. Again, that's just the, whatever name you put here for this form item.
07:02 put curly brackets, variable, let's call it information of client, is going to be assigned whatever that data is. Again, we're getting the data for each of these based on its key.
07:22 If key is equal to email, that again is just the name here. What the, you can put email as address, you can put contact, anything you want.
07:38 So we're going to say variable email address equals data. Now what are we going to do with this? One thing we want to do and we did here is we want to create the new sheet.
07:50 We need to first get a variable of a new sheet, you go to SpreadsheetApp.open by URL. And here we're going to enter a URL.
08:05 I will get the URL soon of the template. And this is going to be, I can make a note here, if you're looking at this later, copy the template sheet.
08:18 And we want to get URL of this. That's what we're doing here. Oh, we also have to actually copy it.
08:31 Almost forgot. .copy We're not just getting the URL, we're actually making a copy of it. We can name this copy.
08:40 Uhm, we want to maybe name it for the name of the client. So we'll just put, not in quotes, but name of client.
08:53 That could be name of client, maybe R plus, our service sheet. Whatever name you want to give it. And now, Oh, we want to get the URL.
09:05 Get URL. And then over here on the new tab, so we can name this tab anything we want. In my case, I named it new.
09:16 We're going to add this information. So we want to get, where is it? Here we go. So we want to get a spreadsheet app, dot get active spreadsheet, get sheet by name.
09:31 We're going to get new. Let me move it up above my head. We're going to then ah append row. Now what append row does is we need to give it um an array of information.
09:45 So that's why we need curly brackets here. And the information we're going to give it is first we're going to give it the new sheet.
09:53 We could actually, instead of get URL here, could actually do this. Variable new sheet URL, be a little more specific.
10:01 New sheet. Get URL. So we'll change this to get sheet URL. We will do a comma. And then we want, we want all of this piece, this information on the sheet.
10:13 So we're going to do name of client, information of client, and email address. Cool. So this is a array of information that we're going to put in across the row.
10:28 We did it in this way, URL, name, information, contact. That's what we're going to do. So that's what this line does.
10:35 Just puts that information there. So over on our template, there is a tab called start. And on the new sheet, so not on this sheet, but when we make a copy of this sheet, this new sheet, we want to insert the name, the information, and contact here in B1, B2, and B3.
10:56 So let's do that now. We're going to take the URL that we have up here. We're going to open it.
11:02 Edit this. So again, we're, we're in an app script of one sheet, and we're taking another sheet that we've just created, and we're editing information there.
11:10 I think this is pretty cool. So we're doing spreadsheet app dot get active. Actually open by URL. The URL is going to be that new sheet URL that we just created up there.
11:22 We are going to get sheet by name. Let's move this. Up a little so you can see it. Get sheet by name.
11:35 We're going to do start. We know the tab name because we've named it. Get, get range. We want to edit B1.
11:45 Set value is going to be the first thing here. Name of client. Now we're going to copy this whole row, and we're going to paste it, and instead of B1 we're going to do and information of client.
12:03 Same thing, we're going to copy it again, paste it, B3 is going to be email. So now what we're doing is we are creating a brand new sheet based on the template, copying it.
12:17 Then we're going to take the form information that we've gotten, and we're going to set the values of each one in this, not in the template, but in the new sheet we create.
12:26 This is going to be really cool. There is one thing we need to do is we have to trigger this.
12:30 Based on the form submitted, We need to actually make sure that this function, make a new sheet, actually happens. So I'm going to show you how to do that.
12:39 We're going to save our Apps Script here, and over on the left side we're going to go to triggers. We already have a trigger set up for the other one, so we can always delete this.
12:49 But over here on the right side, if you can see this, just made it easier to see, add trigger. This big blue button right here next to my face, we're going to add a trigger.
12:59 We're going to choose which function to run, which is make a new sheet. Maybe it's the only one in Europe.
13:03 Select event source must be from spreadsheet, and the event type must be on form submit. That's all we need to do.
13:15 Now save it. So now every time a form is submitted, that trigger will happen. If you want to delete old triggers, we can go over here.
13:23 This was the create a new sheet we did before. We can delete that trigger, and if you ever need to delete the trigger, you can just go here to this three dots, click delete.
13:33 Now every time the form is submitted, it should do what we ask it to do. Let's see if we get any errors.
13:39 We're going to do Carl Forestry and Carl at Google.com. Submit. Let's see if we get any errors. Let's see if it works.
13:57 We may get an error, or it may take a little bit to do. Let's look at our execution, see if we have a failure.
14:07 Yep, failed. Let's see what happened. This is really cool. Invalid argument URL at code 22. So line 22, we have to make a change for some reason.
14:18 22. Oh, we need the URL of this template. So let's go copy it, put the URL there. I didn't come back and do this.
14:28 I think I said earlier in the video that I would, and I didn't. Okay, we just have to save that.
14:32 that we don't have. We have to redo the trigger. It's going to work. Let's submit another response. Let's do Carl with a K, with a K, and email carl at google.com.
14:44 I don't know if that's a real email address. It doesn't matter now. Let's go here, see if it works. Great!
14:51 We have a lot of stuff happening. I just realized what we did wrong is we have, let me move my face over here.
14:59 In the for loop we sort of formatted this a little. So inside of our for loop we're looking at all of the values and we're copying, we're making a new sheet every single time.
15:09 So we need to take all of this out there and put it inside or outside of our uhm for loop.
15:23 So there we go. So now the for loop is only happening once. It's going and getting all of our variables and then it's stopping and And then we'll create the new shape.
15:33 Alright, let's do that. So clean that up a little bit. We are going to submit a new response. So we have Frank here.
15:40 We're going to submit this information. We're going to look at our cleaned up sheet from before. See if it comes in.
15:47 All of the data comes in only once. And now we have a sheet for Frank with all of his information.
15:53 This is again a new sheet, not the template that is being filled, but a new sheet that has been built.
15:59 So, I want to give you one extra thing for making it all the way through this video. I want to show you.
16:03 I show you how you can give Frank access to this sheet. So at the end, let's go back to at the end.
16:10 What we want to do is we're setting this value, but let's grab this URL. It's going to be very simple to do this.
16:21 We're going to grab this URL and the sheet and we're going to add a viewer. And we're going to use the email address that we've gotten from that form submit.
16:31 We're going to add them as a viewer. You can add them as a editor if you wish. Uh, there are some things that happen with this that are like happen like 5 to 10% of the time, which is someone might give you an email address like a hotmail yahoo account.
16:45 They don't have a Gmail account, so this will error out. I recommend you do this at the end of the app script, not at the top, because if there is an error, you want to know about it and you may get alerted like there was an error.
16:58 You can add to this some logging to see like, okay, maybe it was the email address. Or, uhm, you can get emails based on those errors, uh, if you wish to add like an email to yourself here and be like, uh, basically you do try, try this, and then you can catch it.
17:18 Uh, let's add this here. Try this. And then we can catch with an error message here and we can email ourselves, mailapp.c.
17:33 Send email to someone subject and the body. We can say, hey, just, this didn't work. So variable two can be, I think it's session.getActiveUser, that's just the email address of whoever's running this.
17:54 Variable subject equals error adding email. And the body, variable body could be. The email address, maybe simply. So this is just a very simple version of this, but we're going to do that just in case this email address doesn't work.
18:14 Let's go back to our form. We don't have to redo any of the triggers, but I want to show you like using a different email of mine.
18:22 So AndrewsOtherEmail, email address added as viewer and I'm going to use camphy at gmail.com that is just a personal name email address I have.
18:35 I've submitted it. Let's see if it goes into our sheet. Yes, now we have another sheet. So let's open this and see who was the viewer.
18:45 So if we go here, this is private to only me, the frank one, the one we did just before. There is no other viewer here, right?
18:54 And let's see, this is shared with one person and that one person is canva.gmail.com. So this is really good if you're adding these, clients manually and you don't want to do that anymore.
19:09 You want to email them or add their email address immediately to the sheet that you've created. Template, you started work with them, here you go.
19:18 It's all automated and all done and we all we had to do was add this one line. But of course there is some errors 10 to 20% of the time you're gonna get some errors.
19:26 So like let's create another your email here, I'm going to undo this so you see the kind of error we get.
19:34 It doesn't matter Andy, doesn't matter here and we're gonna come just do a completely this is not a, this is a gobbly goop right.
19:46 So everything happens totally fine cause the script still runs, we get our sheet that's been created and if we go here it is still private only to me.
19:57 So what happened? Let's go look at our email and see. So actually the The real error was that I don't, I can't send an email this way.
20:04 Let's just put in our email address here instead of getting the active user, we'll just do andreabettersheet.co and try this again.
20:15 So we will get an error because the email address doesn't work, not because our own email address doesn't work. Let's see.
20:22 I think actually I just need to authorize myself at, so we just hit run in here, it's not gonna run anything cause there's no form values here, or it's going to get an error.
20:32 So let's try that. Let's try this again. Now that we authorized our work, carl, information carls stuff, and again I'm just gonna use gobbledygook just to get an error that this email doesn't work.
20:46 So finally yes, so it's that authorization that actually was the error. So now we got an email and it says error, add an email.
20:53 Look, this email is just, doesn't work, right? We can add more language to this, this email if we want to type out a nice email that says Hey, we tried to add you guys here.
21:03 That we tried adding. Here's the client that we tried adding. We can make this a lot prettier with a little bit more time, just a few more minutes, right?
21:11 Just over here in the editor write a better subject, email address, maybe input the form values, input the URL of the sheet.
21:22 You can make this a lot nicer, but this is really cool that we are able to take in a form, right, just a form into a sheet, put that information into a sheet.
21:34 Copy another template, another sheet, create that and then give access to the person that we fill in their email address here.
21:43 So again, great for leads, great for consulting clients, great for service providers. This is going to be really cool to automate these kinds of things that you keep on doing every single time you have a new client.
21:54 And you can actually focus on providing the services for that client and not having to set up this infrastructure each and every time.
22:00 So that's really cool. Thanks for watching this whole video, hope you got something out of it. Again, if you are a BetterSheets member, you have access to this exact Google Sheet and you can get it accessed below at bettersheets.co.
00:09 So we are going to do this automatically and why would you do this? Maybe you have a consultancy or some kind of service you provide where everybody who either signs up or even a lead has a customized sheet from a template.
00:25 So what we need to do is we need to create a sheet that is going to automatically create a new sheet.
00:30 But it's going to take an existing sheet and copy it. So we will need two sheets. One sheet to do the app script and a second sheet that is going to be the template that we will copy.
00:40 But the interesting part about this, not just that we will create the new sheet every time a form is submitted, what's going to happen is any of the information on Google Forms that we get, we can put that into the new sheet.
00:57 So you might find yourself creating a new worksheet from a template. And paste, copying and pasting that information from form responses into your sheet.
01:07 So that's what we're going to do. I'm going to show you a really quick example of this here. And we're going to recreate this.
01:11 We're going to do this step by step. I'm going to show you the trigger you have to use. I'm going to show you the app script.
01:15 And you're going to be able to download this if you are a BetterSheets member right now. Down below here at bettersheets.co, if you're watching this video on bettersheets.co, you could be getting this exact sheet and the code in it.
01:26 So let's do this. So we have a form here. we have a name. Andrew. Some notes or information. I'm a Google Sheets wizard.
01:43 And you can put an email. This is going to be interesting. We, if we have some time at the end, we'll automatically send an email.
01:51 Or actually not even send an email. We will send an email, but we will add them as a editor of the sheet or a viewer of the sheet.
01:58 I will show you how to do that so we'll do Andrew at bettersheet.co. Submit. That is submitted to this form.
02:06 Ah, form responses here. But also it's creating a line here and it is creating a brand new worksheet and it's telling me the URL.
02:16 So I can go to that right now and I can see here it is. And that information that I just entered on the form is already here.
02:22 Contact information, the email, the ah information I put in that I'm a Google Sheets wizard, my name, it's already here.
02:29 Maybe Maybe this is like a delivery. Deliverable list and says, hey, you're starting our consultancy or our service and here's all of the things we're going to do.
02:38 Maybe milestones or goals or things that need timestamps or dates or approvals. Maybe you have a client portal that you say, hey, we're going to create a worksheet for you and we're going to present that data and then you just approve all that stuff that happens, right?
02:52 This is fantastic for those kinds of examples, right? Okay. I will show you what we need. First thing we need, is we need a template.
03:02 This is a brand new, different Google Sheet. This is called, I'm calling it consulting template. You can call it anything you want.
03:10 What we need here is the URL. We're going to go back to our sheet where you create the form. We've created a form already and we have those form responses.
03:19 Now you already know this or if you don't, you can just create a new form in your Google Sheet. It will be automatically connected to your Google Sheet, but it's by form responses.
03:28 We don't have this new URL, so we're going to have to do that with, extensions app script. We open it up and this is what I've already done for you.
03:36 It's only about 30 lines. It's actually less than 30 lines of code because there's a bunch of spaces here. And I'm going to walk through all of this because we're going to act like this doesn't exist yet.
03:47 So we're going to call function make a new sheet. We're going to add the parentheses and some curly brackets to make sure that we're going to have a function to operate.
04:01 Then we put this in a trigger. We're going to put this in a trigger called on form submit. It's going to have some data for us.
04:07 We can use e here. A lot of ah developers do that in app script just to make it easier. We can also call it event or form or submission.
04:17 This can be any text we want. We were, we want to get all of the information that's built into that submission, right?
04:25 Whenever a form is submitted there's data in there and we want to access it. So we're going to create a variable called form.
04:32 Values. And we're going to say equals submission. That is the package of data that is the event that's happening. The submission of the form.
04:42 And we're going to use named values. Now what this is is a special sort of section of the data that gives us a key which is the name item and the answer.
04:57 And so we're going to go through this, and we've done it here before. We're going to have a for loop for key in form value.
05:06 So inside of that is two pieces of data for every single uhm form item. And that's what we're going to get here.
05:16 We're going to say variable key equals key dot to string. So that it actually gives us the data that's involved there.
05:29 We are also going to use, get the data. So we need to do that with a bracket notation of the key.
05:38 And we're also going to change that to a string. So that we actually can read the data. If we don't do the to string, it'll be like an object and it'll just say literally object.
05:48 That's what it'll say. Now we know our item names. We know the key. The key is, let's submit another response, is this name, information and email.
05:58 And if we edit this, cause we can edit. This form, right? If we edit it, let's see. We can edit this to be your name, right?
06:09 This is the key. Whatever you're naming each of the items. And the response is going to be that data. So we name this if the key is equal to, we need two equal signs, name.
06:29 Then we can assign some variables. Variable name of client equals whatever this data is, which is the key here. We need to put this in brackets, actually.
06:49 And then if, we're going to keep going with each of these items is equal to information. Again, that's just the, whatever name you put here for this form item.
07:02 put curly brackets, variable, let's call it information of client, is going to be assigned whatever that data is. Again, we're getting the data for each of these based on its key.
07:22 If key is equal to email, that again is just the name here. What the, you can put email as address, you can put contact, anything you want.
07:38 So we're going to say variable email address equals data. Now what are we going to do with this? One thing we want to do and we did here is we want to create the new sheet.
07:50 We need to first get a variable of a new sheet, you go to SpreadsheetApp.open by URL. And here we're going to enter a URL.
08:05 I will get the URL soon of the template. And this is going to be, I can make a note here, if you're looking at this later, copy the template sheet.
08:18 And we want to get URL of this. That's what we're doing here. Oh, we also have to actually copy it.
08:31 Almost forgot. .copy We're not just getting the URL, we're actually making a copy of it. We can name this copy.
08:40 Uhm, we want to maybe name it for the name of the client. So we'll just put, not in quotes, but name of client.
08:53 That could be name of client, maybe R plus, our service sheet. Whatever name you want to give it. And now, Oh, we want to get the URL.
09:05 Get URL. And then over here on the new tab, so we can name this tab anything we want. In my case, I named it new.
09:16 We're going to add this information. So we want to get, where is it? Here we go. So we want to get a spreadsheet app, dot get active spreadsheet, get sheet by name.
09:31 We're going to get new. Let me move it up above my head. We're going to then ah append row. Now what append row does is we need to give it um an array of information.
09:45 So that's why we need curly brackets here. And the information we're going to give it is first we're going to give it the new sheet.
09:53 We could actually, instead of get URL here, could actually do this. Variable new sheet URL, be a little more specific.
10:01 New sheet. Get URL. So we'll change this to get sheet URL. We will do a comma. And then we want, we want all of this piece, this information on the sheet.
10:13 So we're going to do name of client, information of client, and email address. Cool. So this is a array of information that we're going to put in across the row.
10:28 We did it in this way, URL, name, information, contact. That's what we're going to do. So that's what this line does.
10:35 Just puts that information there. So over on our template, there is a tab called start. And on the new sheet, so not on this sheet, but when we make a copy of this sheet, this new sheet, we want to insert the name, the information, and contact here in B1, B2, and B3.
10:56 So let's do that now. We're going to take the URL that we have up here. We're going to open it.
11:02 Edit this. So again, we're, we're in an app script of one sheet, and we're taking another sheet that we've just created, and we're editing information there.
11:10 I think this is pretty cool. So we're doing spreadsheet app dot get active. Actually open by URL. The URL is going to be that new sheet URL that we just created up there.
11:22 We are going to get sheet by name. Let's move this. Up a little so you can see it. Get sheet by name.
11:35 We're going to do start. We know the tab name because we've named it. Get, get range. We want to edit B1.
11:45 Set value is going to be the first thing here. Name of client. Now we're going to copy this whole row, and we're going to paste it, and instead of B1 we're going to do and information of client.
12:03 Same thing, we're going to copy it again, paste it, B3 is going to be email. So now what we're doing is we are creating a brand new sheet based on the template, copying it.
12:17 Then we're going to take the form information that we've gotten, and we're going to set the values of each one in this, not in the template, but in the new sheet we create.
12:26 This is going to be really cool. There is one thing we need to do is we have to trigger this.
12:30 Based on the form submitted, We need to actually make sure that this function, make a new sheet, actually happens. So I'm going to show you how to do that.
12:39 We're going to save our Apps Script here, and over on the left side we're going to go to triggers. We already have a trigger set up for the other one, so we can always delete this.
12:49 But over here on the right side, if you can see this, just made it easier to see, add trigger. This big blue button right here next to my face, we're going to add a trigger.
12:59 We're going to choose which function to run, which is make a new sheet. Maybe it's the only one in Europe.
13:03 Select event source must be from spreadsheet, and the event type must be on form submit. That's all we need to do.
13:15 Now save it. So now every time a form is submitted, that trigger will happen. If you want to delete old triggers, we can go over here.
13:23 This was the create a new sheet we did before. We can delete that trigger, and if you ever need to delete the trigger, you can just go here to this three dots, click delete.
13:33 Now every time the form is submitted, it should do what we ask it to do. Let's see if we get any errors.
13:39 We're going to do Carl Forestry and Carl at Google.com. Submit. Let's see if we get any errors. Let's see if it works.
13:57 We may get an error, or it may take a little bit to do. Let's look at our execution, see if we have a failure.
14:07 Yep, failed. Let's see what happened. This is really cool. Invalid argument URL at code 22. So line 22, we have to make a change for some reason.
14:18 22. Oh, we need the URL of this template. So let's go copy it, put the URL there. I didn't come back and do this.
14:28 I think I said earlier in the video that I would, and I didn't. Okay, we just have to save that.
14:32 that we don't have. We have to redo the trigger. It's going to work. Let's submit another response. Let's do Carl with a K, with a K, and email carl at google.com.
14:44 I don't know if that's a real email address. It doesn't matter now. Let's go here, see if it works. Great!
14:51 We have a lot of stuff happening. I just realized what we did wrong is we have, let me move my face over here.
14:59 In the for loop we sort of formatted this a little. So inside of our for loop we're looking at all of the values and we're copying, we're making a new sheet every single time.
15:09 So we need to take all of this out there and put it inside or outside of our uhm for loop.
15:23 So there we go. So now the for loop is only happening once. It's going and getting all of our variables and then it's stopping and And then we'll create the new shape.
15:33 Alright, let's do that. So clean that up a little bit. We are going to submit a new response. So we have Frank here.
15:40 We're going to submit this information. We're going to look at our cleaned up sheet from before. See if it comes in.
15:47 All of the data comes in only once. And now we have a sheet for Frank with all of his information.
15:53 This is again a new sheet, not the template that is being filled, but a new sheet that has been built.
15:59 So, I want to give you one extra thing for making it all the way through this video. I want to show you.
16:03 I show you how you can give Frank access to this sheet. So at the end, let's go back to at the end.
16:10 What we want to do is we're setting this value, but let's grab this URL. It's going to be very simple to do this.
16:21 We're going to grab this URL and the sheet and we're going to add a viewer. And we're going to use the email address that we've gotten from that form submit.
16:31 We're going to add them as a viewer. You can add them as a editor if you wish. Uh, there are some things that happen with this that are like happen like 5 to 10% of the time, which is someone might give you an email address like a hotmail yahoo account.
16:45 They don't have a Gmail account, so this will error out. I recommend you do this at the end of the app script, not at the top, because if there is an error, you want to know about it and you may get alerted like there was an error.
16:58 You can add to this some logging to see like, okay, maybe it was the email address. Or, uhm, you can get emails based on those errors, uh, if you wish to add like an email to yourself here and be like, uh, basically you do try, try this, and then you can catch it.
17:18 Uh, let's add this here. Try this. And then we can catch with an error message here and we can email ourselves, mailapp.c.
17:33 Send email to someone subject and the body. We can say, hey, just, this didn't work. So variable two can be, I think it's session.getActiveUser, that's just the email address of whoever's running this.
17:54 Variable subject equals error adding email. And the body, variable body could be. The email address, maybe simply. So this is just a very simple version of this, but we're going to do that just in case this email address doesn't work.
18:14 Let's go back to our form. We don't have to redo any of the triggers, but I want to show you like using a different email of mine.
18:22 So AndrewsOtherEmail, email address added as viewer and I'm going to use camphy at gmail.com that is just a personal name email address I have.
18:35 I've submitted it. Let's see if it goes into our sheet. Yes, now we have another sheet. So let's open this and see who was the viewer.
18:45 So if we go here, this is private to only me, the frank one, the one we did just before. There is no other viewer here, right?
18:54 And let's see, this is shared with one person and that one person is canva.gmail.com. So this is really good if you're adding these, clients manually and you don't want to do that anymore.
19:09 You want to email them or add their email address immediately to the sheet that you've created. Template, you started work with them, here you go.
19:18 It's all automated and all done and we all we had to do was add this one line. But of course there is some errors 10 to 20% of the time you're gonna get some errors.
19:26 So like let's create another your email here, I'm going to undo this so you see the kind of error we get.
19:34 It doesn't matter Andy, doesn't matter here and we're gonna come just do a completely this is not a, this is a gobbly goop right.
19:46 So everything happens totally fine cause the script still runs, we get our sheet that's been created and if we go here it is still private only to me.
19:57 So what happened? Let's go look at our email and see. So actually the The real error was that I don't, I can't send an email this way.
20:04 Let's just put in our email address here instead of getting the active user, we'll just do andreabettersheet.co and try this again.
20:15 So we will get an error because the email address doesn't work, not because our own email address doesn't work. Let's see.
20:22 I think actually I just need to authorize myself at, so we just hit run in here, it's not gonna run anything cause there's no form values here, or it's going to get an error.
20:32 So let's try that. Let's try this again. Now that we authorized our work, carl, information carls stuff, and again I'm just gonna use gobbledygook just to get an error that this email doesn't work.
20:46 So finally yes, so it's that authorization that actually was the error. So now we got an email and it says error, add an email.
20:53 Look, this email is just, doesn't work, right? We can add more language to this, this email if we want to type out a nice email that says Hey, we tried to add you guys here.
21:03 That we tried adding. Here's the client that we tried adding. We can make this a lot prettier with a little bit more time, just a few more minutes, right?
21:11 Just over here in the editor write a better subject, email address, maybe input the form values, input the URL of the sheet.
21:22 You can make this a lot nicer, but this is really cool that we are able to take in a form, right, just a form into a sheet, put that information into a sheet.
21:34 Copy another template, another sheet, create that and then give access to the person that we fill in their email address here.
21:43 So again, great for leads, great for consulting clients, great for service providers. This is going to be really cool to automate these kinds of things that you keep on doing every single time you have a new client.
21:54 And you can actually focus on providing the services for that client and not having to set up this infrastructure each and every time.
22:00 So that's really cool. Thanks for watching this whole video, hope you got something out of it. Again, if you are a BetterSheets member, you have access to this exact Google Sheet and you can get it accessed below at bettersheets.co.