Hey there stranger!

Sign up to get access.

Spreadsheet Automation 101 Lesson 3: Email Yourself For Loop

About this Tutorial

I’ll walk you through how you can receive an email about tasks that haven’t been done.

Video Transcript

0:00 All right, so in this video we're gonna do a little bit of live coding. It's gonna take a bit of trial and error, but I hope through the trial and error, you see how we can attempt something and see how it works.
0:09 So in this body, we actually want to say, or sorry, in this email, we wanna say tasks not done. And this body needs to just list all of the tasks on our sheet that are not checked off and don't, don't have a date done.
0:26 So we're gonna get first off an array an array, a two, two dimensional array of all of these tasks, including the date done.
0:38 So B through D, we got B one, or no, sorry, b2, all the way to B 10. So that's pretty simple.
0:44 We've done that before. Spreadsheet app dot, get active spreadsheet with GI sheet by name.  We just want to double check it is sheet one dot git range.
0:59 We can do B two colon B 10. Absolutely. We can also hear, if you remember how to do the ranges.
1:07 We can do row two starting. B two is also column two. We can do, what is it, eight rows. How many rows is that one?
1:16 Nine rows and three columns. So nine rows, three columns. That is the same. If we just do B2 colon B 10.
1:28 Was that B 10? Yeah. B 10. All right. And we want to dot get values before we send this email, let's comment it out so we don't have to run that code.
1:42 If you've never seen that before, it's two slashes. We'll comment out any code we don't want to run. And we're just gonna log logger dot log this body.
1:53 Let's just see what this looks like. First off, let's run it. And we have a bunch of false, false trues.
2:00 Why do we have that? Oh, cuz this is not B 10. It is D 10. This is great, right? We're using this logger to debug as we go.
2:09 Great. So here, this is cool. We have an array of arrays where each array is three items, and it starts with either true or false.
2:18 It starts it. The second part is the task one, task two, the name of the task, and the third part is the date.
2:25 All right? So what we can do with this is we will need for, well, first we'll need an array to send out.
2:35 So we want to say variable not done tasks is and we'll just create a empty array. And now in a four loop, we'll do I equals zero.
2:50 Not I equals one. I I equals zero. I is less than. I think we want to do body Or we don't want body, we want, this is tasks.
3:02 We'll rename that we'll do tasks dot length, and we can always log that to double check that that is the exact length we want.
3:14 So for this, what do we want to do? We want to if ta tasks and in brackets. Now here's the fun part.
3:25 Going back to the bracket notation. We want the first item. No, we want the I item. And we want to know the first thing, no, zero, but it's the first thing is equal to false, right?
3:44 If it's true, it's done. And if it is, we want to take not done tasks, stop, push. And we want tasks.
3:59 I, and we want the, the name of the task to be pushed to that array. Okay? Now, out of here, we want logger dot log.
4:09 And let's look at that. Not done tasks. Okay, little bit of live coating, a little trial and error here. So let's save this.
4:18 I think we need to change this tasks, this body to tasks here. We're not using those. It's fine. Let's run it and see what happens, right?
4:27 All right, cool. So we get a list of task one, task three, task six. Let's double check. Task one, three and six.
4:35 Exactly. That is perf. Oh my God, we did that. So we took all of these, this array. We went to each on the for loop.
4:46 We went to each of the items, the first item, the second item, third item. But in that array, we went to the first item and said, is this false?
4:53 Then push the sec. Actually the second, second item of each array, the name of the tasks to this new array.
5:01 We used that push. Wow, that was exciting. And now we can say that this not done tasks is this body, right?
5:09 So let's see if, if this, if this looks okay, it might not look okay. Let's email ourselves. This email not done.
5:19 We have the correct subject test not done. Let's see what it looks like. All right, we'll go to our inbox.
5:27 Task's not done. Task one, test three. Test 6, 7 89. This is perfect. I mean, this is not very beautiful. Not that that great looking, but we got the data.
5:37 We got the data from the sheet. We parsed out if it's true or false, right? If it's done or not and we sent it to ourselves, that is pretty darn cool.
5:47 Alright, in the next video, we are gonna be able to send this email every single day. We're gonna use a time-based trigger for that bite.

Courses

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

Tips to Navigating Thousands of Lines of Code In Apps Script

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