Hey there stranger!

Sign up to get access.

Spreadsheet Automation 101 Lesson 2: Arrays

About this Tutorial

Learn the basics of Arrays – how to create one, how to reference rows and columns of data in a different way, and how to get an entire column or row in Apps Script.

Video Transcript

0:00 All right, this is going over the topic of a array in app script. In a in app script, we can reference rows and columns of data in a different way than exactly rows and columns.
0:16 And the way we do that is with arrays. So just a little refresher, right? We have these columns that are like A, B, C.
0:24 They can also be 1, 2, 3, and we have rows 1, 2, 3 all the way down. And when we go into app script and we want to say, get an entire row, we use this get range, or sorry, an entire column or an entire row, we use get range, and we can have a starting point, which is in this case gi, in this GI range one, one, which is a one.
0:46 And then the third option in Git range is the number of rows. So in this case it's 20. And then then number of columns.
0:55 And in this case it's one. So this is getting the A column from a one down to a 20. If we wanna get an entire row or a bunch of columns, we have this function that I Rowe called show columns, which is actually just one row.
1:12 We start again at the same place, A one or one one. The third option again is number of rows. We only want one row and we have 20 columns across.
1:21 This gets us a one to whatever the 20th letter is over here. <laugh>, we won't do the math on that.
1:31 Now these get val, when you get values, it c turns it into an array. Now in JavaScript and in programming arrays are very special and very interesting to use.
1:42 One thing to note is that array, the first item in an array is always number zero. So we'll get into that.
1:49 Actually, there's a whole video about that with bracket notation. I'm gonna show you that later. But this is all about arrays, but the way that arrays work is not like you can't really exactly turn them from rows and columns into a race.
2:03 Let me show you what happens. So in this show columns, which is actually just one row, let's run it and see what we get.
2:11 We get two brackets, hello world, then a bunch of commas. And then we also get this, which was in the sheet over here in the K column.
2:20 So what we get all these blanks, but what it is, it's an array that's in brackets. We don't have to just return an array.
2:30 We can create an array inside of our app script. How we do that is variable new array. And we have two options.
2:40 We can do is and have two brackets, create an array. This is the exact same thing as the next thing I'm gonna show you.
2:48 Variable newer array is new array with a capital A and parentheses. These two things are for all intents and purposes, right at this moment, the same thing.
3:02 They are interchangeable. I particularly like to use this new array. The reasoning is, is that it is clearer to read.
3:12 If I am reading this in my app script and I'm going line by line and I see equals new array, I can read that like a human.
3:21 I don't have to translate that in my mind to that's a new array. Whereas if I do new array with these two brackets, this is clearly a new array.
3:30 It is just an empty brackets, but two brackets is pretty hard to read amongst a bunch of different app script and a bunch of lines of script new array.
3:39 I can literally read that. Except thing is these two brackets are much, much easier to type, right? It's much faster to type a new array than equals two brackets.
3:50 So it's up to you and your own personal touch at this moment, right? There are some differences that I actually don't necessarily know.
3:56 I haven't really encountered many different differences between these two. But in JavaScript, I think they are relatively interchangeable as well.
4:07 So if you're coming from JavaScript, you'll know this very well, probably know this better than I do. But to create just a new array, we do that, but we don't have to create a new array if we're just getting the values.
4:20 Now, if we get values, let's look at this again. The other way, if we're just getting rows, and now this is, you know, it sounds weird, we're getting a bunch of rows, which is a column.
4:31 So let's do this. Show Rose, run this. And this is far different. This is, we got the two brackets, but then hello world is in a bracket and everything else is a comma and brackets.
4:44 This is an array of array. And actually the other one is also an array of arrays. How we get these, how do, how do we get that?
4:53 Hello world, which is the first part of the first part. First item of the first item, first array is through bracket notation, which I will get into later in another video, but I do want to go through that.
5:06 I want to just double check and double show you these differences. Right? When we got this 1, 11 31 s and then 20 columns, it was comma separated within the first array.
5:22 And so you'll hear me say that again later. First, array, array of arrays. An array, one single array is just a number of items in an order.
5:33 Like the first one, the second one, the third one, the third one, the first thing in your array could be another array, which the first array in your array has an array inside of it.
5:45 And it's ordered first, second, third, fourth. So these two are very different in that this is an array with arrays.
5:55 This is one, this is The, the first array in one array. So we have arrays in arrays. Arrays are always the same.
6:05 They have an order of elements, and that's it. I mean, you can put an array inside of an array and you can put an array, another array inside of another array as far as you want.
6:17 Just so you know this, and we'll go deeper into this in bracket notation, but first we're gonna get to the for loop, I think next, which is very, very exciting.

Courses

Spreadsheet Automation 101: Introduction to Pre-course Videos

Automate Google Sheets With Zero Experience

Automatically Clear Content | Refresh Reuse Recycle Templates

Spreadsheet Automation 101: Functions

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

Automatically Uncheck A Daily Checklist

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