Hey there stranger!

Sign up to get access.

Spreadsheet Automation 101 Lesson 1: Spreadsheet Taxonomy

About this Tutorial

Learn about the strategy and concept of the SpreadsheetApp. Understand rows, columns, and cells better.

Video Transcript

0:00 Hi, welcome. In this video, we're gonna jump out of spreadsheets for just a moment and go into a slide and try to present to you what that spreadsheet app and all of that code that we did before.
0:10 Spreadsheet app, get active spreadsheet, get sheet by name, get range, get value. I want to show that to you instead of just typing it out.
0:17 And you d typed it out yourself and it worked. That's sort of the same as copy and pasting the code.
0:22 I wanna actually try to get across to you what this is doing visually. So I made a little bit of a slide here.
0:30 Let me move my face around. All right. What makes a sheet? Let's find out. Right? And what that is is it's just rose and columns and cells.
0:40 The code app script that we actually program is integrated inside of the spreadsheet file. So all of the code that we do in app script, especially when you start into spreadsheet and you go to extension app script, that's all code inside the spreadsheet file.
0:57 That spreadsheet file includes all of the sheets. It includes all the tabs, all of the cells, all of the rows, all of the columns, everything in that entire spreadsheet file.
1:07 We can access every sheet. It has some rows, every sheet has some columns. Now A is equal to one, the first column B is equal to two.
1:17 The le number two, we'll get into that in the next video actually a little bit more. And each cell ha it ha sorry, every sheet has cells and can be referenced two ways in code.
1:28 You have a one, A two, B one, you also have 1, 1, 1, 2 again, we'll, we'll jump into that in the next code cuz that's a little bit it's hard to tell you.
1:39 It's easier to show you. All right, we have, we already went over notation and that's what we're gonna use right now to go from spreadsheet app down to the cell.
1:48 So spreadsheet app, the pink, the pink part here, right? This is not black, it doesn't have parentheses, it is spreadsheet app.
1:57 It is a whole set of functions pre-built into Google Sheets that allows us to access a lot of cool stuff.
2:03 So that spreadsheet app means the entire file, everything, all the sheets. If we do dot get active spreadsheet, we're getting the spreadsheet that we are on right now.
2:14 That is still all of the sheets, all of the tabs, everything. But it's just saying we want the spreadsheet part of that.
2:21 Now if we do dot get sheet by name, we are selecting one of the, one of the tabs by its name and we have to put in quotes exactly the name and spelled correctly and the correct capitalization.
2:37 Sheet one, sheet one or sheet two. Sheet two. We have to put in exactly the name of the sheet in quotes inside that.
2:44 But now see we're going a little bit deeper each time, every time we add a dot dot notation and a new function, we're going a little bit deeper down, down, down into the rabbit hole.
2:55 The next thing we could do is get range from that tap. So we are here on, on cheat one. We have a range, any types of ranges, rows, columns ranges one <laugh>, one dimensional arrays, two dimensional arrays.
3:09 We have all types of stuff here we can get, but we can access that, that range with a one notation, meaning we can literally type in quotes a one.
3:19 We can also do one on one. We'll get into that later. Once we have the range, the next thing we do is we get the value of that range.
3:27 So we usually, at least at this moment, we're getting one cell. So when we do dot get value, we're getting the value inside that cell.
3:40 So back to our function, we just went all the way through this right spreadsheet app without any parenthesis, dot get active spreadsheet.
3:48 And we can sort of look at it this way, right? We can, this is all one line, but we can also put it on separate lines if we want.
3:55 We do believe and we can see how visually, right from the entire spreadsheet we go to, what is the active spreadsheet?
4:04 What is the, the spreadsheet file that we have open right now? What is the sheet inside of that file and what is the range inside of that sheet?
4:13 And then what is the value inside of that cell or that range right now we can change the sheet name, we can change it to sheet two.
4:20 We can change a one to B one. These are all fungible, flexible we can do all sorts of things with this, right?
4:27 We can also, you mentioned in the pre-course material, we can have like a range here and we can put range right here.
4:37 We can also have a sheet, perhaps sheet, and we can say in the function on the sheet, we can say whatever the sheet is and we'll have that variable pass through all the way here.
4:48 So this allows us, this function allows us to do lots of things. It's very flexible. It allows us to get all the way down to the exact value that we want, where we want it.
4:59 In the next couple of videos, I'm gonna go deeper into a couple parts of this particular script and we'll see how that goes.

Courses

Spreadsheet Automation 101: Introduction to Pre-course Videos

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