00:00 Alright, hello. So, in this video, in real time, we are going to create an internal Google Sheet add-on. What this means is that we'll, ah, I won't show you the coding, I'll actually show you the code 100%.
00:13 We'll show you the code, but I won't do the coding. I already did. I did this, but we're going to do it all again because it's pretty complicated.
00:19 A lot of different steps, and I want to show you all the steps in order to create an internal Google Sheets add-on.
00:25 This is, and I'll show you how this works, is this add-on that we made today is here So, this is a really easy thing to set up, an easy automation.
00:48 to set up to, for your sheets that you generally want to do, like some actions you want to take when you first set up a sheet, or whenever you're formatting a sheet to your liking, uh, bolding the header, freezing a certain number of rows, all of these steps you can do easily.
01:08 You can do this through, uh, absolutely you can do this through just Extensions, Apps Script, which I'll show you the code, but what the add-on does is makes it available to other sheets or even to even everyone on your domain.
01:21 So if you're an admin in a domain or even if you just work in a domain where you're like, uhm, creating your own Apps Scripts but you work in a company which has their own workspace domain, now if I go to sheet.new, it's a brand new sheet, uh, I have, I have the extension available to me so I can set
01:40 up the sheet, uh, just like I would do ever anytime. So here's some text, it's already bold. See, it was a brand new sheet, absolutely new so it was completely defaulted and in one click I have this set up done.
01:52 Uhm, this is the code for it, we're doing essentially three things. We are freezing the top row so spreadsheet app.getActiveSheet.setFrozenRows, only one.
02:02 We're getting the maximum number of columns just so that we know the number of columns that we have to set the background to which we're doing right now to row one from row one column one until the end which is max columns.
02:14 We're setting the background to this hex code and then we're gonna bold the first row. This was a little tricky if you're looking to bold a row.
02:21 Spreadsheet app.getActiveSheet.setFrozenRows, getRange, get the range, the same range, this is just the header row here, one, one, one, and then max columns.
02:30 .setFontWeightBold, so this is something that is a little unintuitive. You have to set the font weight bold if you want to bold something.
02:39 I thought it was like Format, I thought it was format style, text style, it's setFontWeightBold, that's about it. And so I'm going to show you all of these, these other two steps, but what's really cool again is that you can write the script, right?
02:56 week. I created this function set up, and normally I would say, hey, just create an unopened menu that when you open the sheet, you have this one click available, this extension, actually it would be in this custom menu.
03:08 But instead, what we're going to do is we're going to create an add-on menu. Thank you. And then we're going to go through the whole process of creating an add-on which you will see it is quite complicated, but I think it's really awesome if you are a domain master or creating in scripts and you want
03:24 to give this kind of uh automations to your uh. Entire domain. I think it's worth it, I think. Uh, and I think it's a great addition to the course, uh, publisher and goosheet add-on because that entire course, uh, which this video will be added to, is all about external add-ons.
03:43 And you will see it is- is very much the exact same process except no verification, no approval process. We're gonna get it all done today.
03:50 Uh, we're gonna create a new one, so I'm actually gonna go and do sheet.new. Again, I'm gonna do this all over again from, nothing.
04:00 So we're gonna set up our sheet, set up our sheet a bit differently. But we are going to have a sheet set up.
04:08 So we're gonna go to extensions app script. We will have nothing. The only thing I'm gonna copy is this set up function, and I will write the other stuff.
04:16 Two. So again, if we have this function set up, we're just freezing the top row. We're actually gonna freeze the top two rows.
04:23 We're gonna change this color to uhh, I think sort of a gray 2222. So it's six twos. Yeah, we're gonna, Set that background to gray.
04:34 I think also what we can do is set the text color. Let's see. Set. Text. I think it's style. Actually it's set font.
04:55 There it is, set font and color. Okay. And this instead of uh black we want white. So we can actually type in white I think.
05:04 And we are going to also bold the first row. So I'm going to save this. And if we run this again this is some text here.
05:14 Let's say we have a little bit of text text. This looks like it's all normal formatting. If we set up first two rows, let's say.
05:26 If we hit run in here, it's going to ask for our permissions of course. The very first time we run it.
05:33 Let's go through it. Allow. See if it worked. It worked. We have the first two rows frozen. We have a bold text and we have it also black.
05:46 Uh, or bit black background. It is a custom color here. Okay, it- this- It works, but now we want to give it to everyone else in the- in the entire domain.
05:57 How do we do that? Okay, we are going to copy this function on open. So what this is creating is on open.
06:04 We're creating a spreadsheet dot get UI. We're creating an add-on-ment- new setup sheet setup. So we're going to create this setup to- we're going to rename this setup to.
06:17 We're going to save this. Once we save it, we're going to close out of the app script and we're going to refresh our sheet.
06:24 Because that on open is only going to happen on open. We just want to double check that it is absolutely correct.
06:29 It is working. So we go to extensions, setup first two rows, setup sheet. So actually we can, let's say we freeze more rows.
06:38 Now let's see what happens if we go to extensions. Set up first two rows, I have to actually click it.
06:43 Set up first two rows, setup sheet. We'll run it. Great! It works! Again, this all, in our own sheet we just want to make sure it works.
06:53 And now we are going to do something special a few, a few things special in order to get this as an add-on.
07:00 We need to create a function on install. Put e here. Uh, and then just run the on open the e.
07:13 On open. See. And that's it. We just need to run the on open once it's installed so that it actually adds it to that uh create that add-on menu.
07:22 This is a little different than I've ever shown you before. This create add-on menu normally looks like create. Create menu, but we do have to have that add-on menu.
07:33 Ah here in order to use this. So we're gonna save this. Now we're gonna go over to our project settings.
07:40 We need a few things. Let's show Apps Script.Manifest. We also need to do this cloud platform. So we're gonna go to our console.cloud.
07:53 We're gonna sign in with our user. Make sure it's a correct user. And if you see this, I already have a Google Cloud function here umm or project actually.
08:09 So what I need to do is go up to this bar here and create new project. So we're gonna create a new cloud project.
08:14 I'm gonna call it setup to, setup sheet two. Because again, I've done this before. Just wanna have it something differentiator, some name here.
08:23 We're gonna create it. It's gonna take a moment here to create. We're gonna see this loading bar. And we need to actually wait until it's created until we umm.
08:32 Select that project. So make sure you select that project. Make sure the name is here, setup two. Right now we just need this project number.
08:39 For now, we'll come back to this in a second. We wanna change the project. When we add this, watch what happens.
08:46 Is it will say in order to- Change your project. You need to configure the OAuth consent screen. So let's go do that.
08:53 Here we will go to dashboard I think. Think here we will be able to actually up in the navigation. APIs and services OAuth consent screen.
09:04 . . That's where it is. APIs, services, OAuth consent screen. We need to create this. We will create an internal type. So if you saw the publish a Google Sheet add-on course uh we will always .
09:20 But in this particular case we are doing internal and this will only you won't be able to change this later.
09:29 But you can absolutely do this entire process again if you just copy the app script and do another sheet. You can absolutely do it again.
09:36 So we are going to set up sheet 2. We going to call it. We are going to use our own email as our user support email.
09:44 We do need a logo here. I'm going to use my uhh company logo. Better sheet logo. We need a PNG I think.
09:54 Yeah. The app domain. I'm just going to use my own uhh domain. And for all of these the policy in terms of service.
10:05 And this is an internal tool so there's no verification. I'm in this but I just use my own domain just in case somebody is looking at this.
10:12 I need to add the domain which is our authorized domain which we are a domain uhh user of and and admin so I'll add that actually we will.
10:23 So, two. htb. must not specify so we just need better sheet of echo. Developer we will add our, add email address again save and continue.
10:35 The scopes we are going to use this might sound tricky. For this particular one we are only going to need one which is spreadsheets and I always have to look at oauthscopes spreadsheet only.
10:49 I always have to like google this and figure out what the URL is because it's not umm. Here. I think it's spreadsheets only.
11:01 This one. Uh cause it is not here. We always have to manually add it to the table now it is checked off.
11:09 Again it's it's weird. It's not found a- but you just add it, copy paste it. We add it. It is a sensitive scope but in internal apps it does not matter.
11:19 We will not be going through the verification process. So uhh it says OAuth consent screen. Green. To dashboard. We do not need to make this internal.
11:34 Let's see. I think that's it. I think we have to do something else but we will come back to it if we need to do it.
11:39 We will set the project. Great. Worked. So now, what's the next step? We need to get this actually onto the Google workspace marketplace.
11:57 Again, just like we do with the- the external one. We just search for marketplace SDK. And it is right here.
12:06 Google workspace marketplace SDK. We will need to enable this. And you will see we will go through almost the entire process of- love.
12:16 Creating an add-on as if we were gonna need to get it approved. Put it on the marketplace. But you will see the last step.
12:22 It will be automatic. Mmm. Okay. We are now in Google workspace marketplace SDK. We need to do two things. We need to a app configuration and store listing.
12:35 So app configuration. Let's click on that. We do not need to make it public. We actually need to make it private so that it is only available to users in our domain.
12:46 Uh you can- can make it unlisted I believe. I'm gonna make it private in this particular case. We will make it in visual and admin.
12:56 Install the app integration down here will be only sheets add on. Okay here we need a google- sheets add on project script ID.
13:06 Where do we get that from? So this is the app script project. But what I need to do is click on deploy new deployment.
13:16 I'm going to select the type add on and this is just set up. To deploy we need a version number two and that's why we need to deploy this.
13:27 So when we deploy it it will have a deployment ID. Right there. And we can also click copy. This is version one.
13:34 So if you ever make a additional versions you will have to come in here and changes. But add on project script ID script version one.
13:43 Scroll down. We want to add just to make sure we have this. We do. We want to make sure we have this OAuth scopes.
13:55 So I'm actually going to copy these OAuth scopes and put it into our manifest. Just in case we need this and we might have to umm just once.
14:08 I have sheets here. Do not want this second part. Just in case we need it. We may have to deploy this again if we need these OAuth scopes.
14:18 But I also just want these so I can copy it here. Copy this. Go to. Add scope. Add scope. Scroll down.
14:27 Develop. We do have to put these names in. Again this is all internal so you can really do whatever not necessarily every anything you want but you do need to make it clear.
14:39 here. It's just your own stuff application. It's just going to be HTTPS. You may want to actually create a website for your application here for internal use only.
14:50 You may want to do exactly the same thing as I did with the external one. Create a card. C A R R R D.
14:55 Uh. If. If you wish, you may want to do that. Um. Make sure all the fields are correct. What did I miss?
15:02 Project key is not associated. Add on Project Script ID. Let's go back and look at this. Actually Project Settings. Did I get the wrong one?
15:18 I think maybe this is it. Actually IDs are unique. This is Script ID. That's what we need. My mistake. Not the deployment ID.
15:25 Ok that should work now. Let's click Save. See if we have any more errors. Great. We are safe. Ah this one.
15:35 This is the one that can't be. So the app visibility setting cannot be changed once it's safe. You can do this entire process again.
15:41 But for our purposes here it is going to be private. No matter what. Okay, we saved. Now what? What's the next step?
15:48 We will go back to the marketplace SDK. And we will do uh store listing. So we did our app conf- figuration here, right?
16:03 Done and done was there go to store listing now. This is where you're gonna have to just put the sort of customer facing information.
16:09 But, again uhh uhh again our customers are going to be either ourselves or the people on our domain. So you have to make it just enough information for them uhh sets uhh for them to know what it does.
16:28 Sets up the new spreadsheet. Easily. Uhh the longer description we will adhere easily with font bold and black background. And freezes to rose.
16:46 And we're just making a longer This is for people who may be on your domain but they might not know what is doing.
16:53 Free of charge, category. For this we are just gonna do umm task management probably. Or utilities. Could do. May wanna do task management.
17:02 But I'll put utilities. Graphic. Ac-ass-ass-asset. This can literally be the exact same image. This is something that uhh I sort of found out umm not by mistake but by other people we only need to put in the uhh umm ones that are required.
17:19 Special. So, basically for one that we're making an internal we just want to get through this sort of as quickly as we want because we just want users to use this, right?
17:27 They know that it's coming. We know it's coming. We need a screenshot. Let's go back to our umm sheet. Let's take a really- Quick screenshot here.
17:38 Close that. Brows. We can drag a photo. There we go. There's a screenshot. Show them what we're working with. Support links.
17:51 We will need a- We'll you in the next one. Support URL. Again, just your own domain. Wherever that is. And you may actually want to publish these.
18:00 We want it all regions. Save. Saved. And I'm looking through just checking again that we have everything. Done. Publish. Approved.
18:14 See, this is the process in which it is so fast. Externally what happened is we umm. So if you need to do external you need to go get the OAuth screen, OAuth consent screen approved first, and then you need to get this store listing approved.
18:33 But in this case we have now approved it. But the cool thing about this is it's not available to anyone, not in our domain.
18:40 The bad thing is this app URL doesn't exist. It literally does not exist. If you click on it, it will take you to a 400 error.
18:48 If you try to rename it app name, the app name we put in, it does not work. I'll show you how we get this.
18:54 So let's see if it is now in our, uh, extensions add-ons. We're gonna go to get add-ons. And if all is correct, we now have this button here called internal apps.
19:06 And it says, no matching results. We did have this, before. Umm, might take a little bit of time to get here.
19:18 But this internal apps button shows up and it should have something. Did have something. There it is. I'm funny I had to actually search for it.
19:27 But- Look, there's two because I had created this one before my own sheet setup as a test of this. Now we have setup two, the one we just created.
19:35 Uh, we're gonna do an individual install. Again you can do an admin install if you wish. So that everybody has it available.
19:43 We're going to- Yeah, allow. And let's see if on this brand new sheet we have this app script available to us.
19:53 Tells us where to go. Can we go up to extensions? Setup two, setup sheet. Done and done. We have bolded white text on black background.
20:05 We have two uh rows frozen. We have done it in twenty minutes. It's been twenty minutes and we have an internal app completely.
20:15 Uh an internal add-on completely created and published so that people on our domain can use it. Again, it is a little funky to go to add-ons, get add-ons funny enough before I was just clicking on internal apps and it was there.
20:30 Cause we have to search for it as well. Done and done and installed. But it is available there. Ah. And yeah, avoid this URL.
20:40 It doesn't work. It's so weird that that wouldn't work. Umm some key things, the script IDs, the Google Cloud Project.
20:48 It's probably one of the most convoluted things you may have to do. You have to go to console.google.com, create a new Google Cloud platform.
20:56 Uh and you do have to do the OAuth consent screen and do the appr- uh store listing. Uh app listing and store listing.
21:06 App configurations and store listing. So that's all the steps. And I hope that was really helpful to you to create an internal Google sheet add-on.