Hey there stranger!

Sign up to get access.

Spreadsheet Automation 101 Lesson 1: onOpen() Trigger - Custom Menu

About this Tutorial

This lesson will get you started on your first trigger. It’s the first real spreadsheet automation you can do. Fun stuff!

Video Transcript

0:01 Welcome back. We are going to start into our first trigger, the very first real automation in spreadsheet automation that you can do.
0:09 So triggers are able to do a lot of different things. In this case, it's a simple trigger called unop Open.
0:15 We can write this inside of our app script. So let's go to our app script right here, extension app script.
0:22 And all we have to do, we can do this on a new script if we want. For the purposes here, I want to just add some lines up here and write it here.
0:32 Now, on open can be function on with lowercase o here and then an uppercase on open. And we have our function here, but I always forget how to do this and I always forget what, like what I want to do on an open.
0:47 So in this particular case, what we wanna do, little, little fun thing is we want a custom menu that every time this spreadsheet opens, it adds a custom menu here.
0:56 And we can access scripts from that menu and, and run them. Because as I sort of mentioned before, actually maybe in the pre-course information functions that we write in cript can be done here.
1:09 We can do an equal sign, type it out, put it in there. We can also run them from inside here.
1:16 But another place we can do is a custom menu and we can run it directly from this sort of area, the front end of our Google sheet.
1:27 And we can run functions directly from here. What kind of functions would we run from here that would run, you know, maybe copy paste, maybe logging, tracking any kind of sort of maybe deleting some rows in a particular way or adding some rows with some formatting.
1:42 Lots of different things we want to do. Once we have like an active range and an active spreadsheet, we're like, oh, we want to, maybe we want to duplicate this spreadsheet but we don't want to do it as this duplicate.
1:53 Maybe there's extra things we want to do, duplicate and change something. We can run that from a custom menu, but I always forget the exact syntax of custom menu.
2:04 So I literally go to Google's help here. You can type in custom menu Google sheets, and the very first link will be this custom menus in Google Workspace.
2:15 This link here, we scroll down and we have function on open. We can literally copy paste all of this. We can copy paste all of this if you want to see actually these, so let's copy the whole thing and I'm gonna paste it over this again.
2:30 We cannot have more than one unopened function here in our sheet. So I'm gonna save this and exactly as Google has this custom menu and the sort of sample size sample here.
2:43 The one thing we have to do though, to get this is we have to open the sheet, but the sheet's already open.
2:49 So we're gonna refresh the sheet. Our app script goes away. You must save the app script before you do this, but once we reopen this you'll see this custom menu pop up Here, here in the top right, the top right top in the middle sort of.
3:05 So how do we get edit that? We can go back to extensions app script. Let's look at it though. Let's see.
3:12 Custom menu. First item, let's click on it. It's runs the script. It asks us for some authorization because I think we have some spreadsheet app in there.
3:21 We always have to run this au authorization. The first time we run a script from this menu run, you click the first menu item.
3:30 So it gives us a little prompt, gives a little, okay, let's see what happens when we click the sub menu.
3:34 The second item, click the second item. So something happens that the script shows us if something is happening, there's running that script and now we have a custom menu.
3:43 This is really, really cool. We can rename this custom menu. We can say, you know, automations save, we have to save and then we have to reload it.
3:54 Right now it's says custom menu, but it only runs on open. So we'll have to refresh again and wait for it to pop.
4:02 It takes a moment or two to pop up. And here's our automations. It's renamed. So we have to open our app script again.
4:11 Let's see. And this on, on open. We can also edit how many items are here, which items are where they go.
4:18 We can name something so it shows up one name and use the function name that it will actually run here as the second item.
4:28 So if we have, like, if these were actually functions we wanna run without knowing, you know, an active range, we could put that, get a one from sheet here, <affirmative> here.
4:41 So we can sort of write another one function show a one. Let's do that. And what we need to do is we need to get all of this value stuff from a one, but we don't wanna return it.
4:54 We wanna give get, get this ui. Same thing here. We'll just copy paste that. So what this is is spreadsheet app dot get ui.
5:05 And instead of any text here, we'll write the value. We can actually string together some information here a one colon space and then plus the value.
5:19 So let's see what that does. We have show a one. We need to add an item so we can add item.
5:28 The first one will be whatever the text we want to see. So show space a one. Then we wanna do a comma.
5:37 And here we need the exact function name here with no spaces, the same exact name. So let's save that again.
5:46 We can hit save project as well. Up here we are going to, we, we don't have a second item here, so we need to refresh it needs to run that again on open, Wait a moment or two.
6:01 And here's our automations. Show a one sheet is not defined. Let's go back and see what our error is. Dismiss extension, app script.
6:12 We probably have the word sheet, not sheet one, maybe I think show a one. Yeah, we have the word sheet.
6:19 And here we need to write capital sheet one. We can also do get active, but ah, for now, show a one.
6:28 And I don't think, because we didn't change the name of the function and we didn't change the custom menu here, the unop unopened.
6:37 We don't have to refresh the page, we don't have to reopen the page. So let's see if that works. There we go.
6:43 The stuff in a one. That's literally it. Here. Hello, world Automation Show a one, it's gonna grab that. Hello world.
6:53 There we go. Perfect. So we have worked with spreadsheet app. We've gotten, we've figured out what the difference between active spreadsheet get at Get Sheet.
7:05 We've created our first trigger, which is an unopened trigger that opens a custom menu. Every time that we open the sheet, we are rocking and rolling.
7:14 Really excited about the next. I hope you are excited about the next parts and we're gonna go off and we're gonna get more than one value in the next session.
7:23 This was get value. Now let's get values.


Spreadsheet Automation 101: Introduction to Pre-course Videos

Breaking Through Errors In Apps Script

Think Like a Programmer: Develop The Mindset of an Apps Script Coder

Spreadsheet Automation 101: Functions

Spreadsheet Automation 101: Variables

Spreadsheet Automation 101: Dot Notation

Spreadsheet Automation 101: Camel Case

Spreadsheet Automation 101: Parentheses

Spreadsheet Automation 101 Lesson 1: GetValue - Introduction to SpreadsheetApp

Spreadsheet Automation 101 Lesson 1: Spreadsheet Taxonomy

Spreadsheet Automation 101 Lesson 1: A1 Notation vs Row,Column Syntax

Spreadsheet Automation 101 Lesson 1: getActiveSpreadsheet() vs getActiveSheet()

Spreadsheet Automation 101 Lesson 1: onOpen() Trigger - Custom Menu

This Seems Like Automation

Spreadsheet Automation 101 Lesson 2: Get Values - Introduction

Spreadsheet Automation 101 Lesson 2: Arrays

Spreadsheet Automation 101 Lesson 2: For Loop

Spreadsheet Automation 101 Lesson 2: Bracket Notation

Spreadsheet Automation 101 Lesson 2: Logger.log()

Spreadsheet Automation 101 Lesson 2: If ( ){ } and Checkboxes

Spreadsheet Automation 101 Lesson 2: onEdit() Trigger

Introduction to Spreadsheet Automation 101 Lesson 3

Spreadsheet Automation 101 Lesson 3: MailApp

Spreadsheet Automation 101 Lesson 3: Email Yourself For Loop

Spreadsheet Automation 101 Lesson 3: Send Email Every Week Trigger

Spreadsheet Automation 101 Lesson 3: Email Other People For Loop

Spreadsheet Automation 101 Lesson 4: Access APIs Introduction

Spreadsheet Automation 101 Lesson 4: UrlFetchApp

Spreadsheet Automation 101 Lesson 4: OmdbAPI get ApiKey, get Data in URL

Spreadsheet Automation 101 Lesson 4: OmdbAPI get data in Apps Script

Spreadsheet Automation 101 Lesson 4: JSON (beautifier) and OmdbAPI parameters

Spreadsheet Automation 101 Lesson 4: OmdbAPI Parameter Picker

Automatically Clear Content | Refresh Reuse Recycle Templates

Automate Google Sheets With Zero Experience

Automatically Uncheck A Daily Checklist

Activate A Certain Sheet When Opening a Spreadsheet

Scoping Functions in Apps Script