I use Google services almost constantly, but the one that I find myself truly intrigued by is their Drive service. It has completely replaced every other program out there for any word processing and spreadsheet needs I have. What’s more, the “always on” cloud functionality it brings makes it far more powerful than any desktop office suite.

One of the more interesting things I did with it recently was to create a spreadsheet with a set of reminders that automatically send me an email when they are due. It was simple and easy, and works great! In this article, I’ll be sharing my spreadsheet and scripting tricks with you.

First, you’ll need a Spreadsheet on Google Drive that you plan to use to hold your data and your scripts. Since this is going to be a reminder spreadsheet, we’re going to be working primarily with dates. For instance, my spreadsheet looks like:

Reminder NameLast Date CompletedDays BetweenNext DateDays Left
Change Furnace Filter1/1/201390
`=B2+C2-1`
`=D2-today()`
Take Out Recycling1/1/201314
`=B3+C3-1`
`=D3-today()`
Write Blog Article1/1/20137
`=B4+C4-1`
`=D4-today()`

Here I’ve listed some chores, but you could use this for whatever – prescription reminders, recurring meetings, birthdays, anything you might want a reminder for.

Date math is relatively easy in spreadsheet terms. If you add or subtract integers from dates, you can calculate new dates automagically. The `today()` macro does what you’d expect – it just returns today’s date, ready to be used alongside other dates for more date mathemagic.

I’ve shown the formulas here, although when you enter them into the spreadsheet they’ll automatically populate with the computed values. Also remember that you can click and drag the little “dot” in the lower right hand corner of the active cell to automatically set up formulas in an entire range of cells. Once you’ve filled out row 2, you can just drag down and have rows 3 and 4 (and so on…) filled in with minimal effort.

Remember that in order for the reminders to work, you’re going to want to keep the Last Day Completed column updated. Whenever you do the task, put today’s date into that column. Do note that you can’t “cheat” and use `today()` there, because if you do it will update to the current date every time you open the spreadsheet or the script runs, which would be a bad thing!

## Part 2: Scripting

Now you’ve got your data entered, it’s time for the automation. Google Drive uses a Javascript-like scripting language, so if you can write Javascript or jQuery style scripts, you’ll be fine.

If you’re new to coding, you might want to run through an interactive tutorial like Codeacademy.

However, if you’ve written code and feel comfortable in any other language, you should be fine. Javascript is very C-like in its syntax, except that variables are weakly typed (everything is just “var”) and there are a number of shortcuts like associative arrays and “for each” style loops. We won’t be doing any funky lambda functions or anything advanced like that.

To get started, click the Tools menu, and then click Script Editor… to bring up the script editing window. You can choose from some templates here, any is fine since we’ll be writing our own code here in a second. You might as well click Spreadsheet if you’re overwhelmed by the options. :)

Here’s the script we’re going to use:

```function checkReminder() {
// set the first sheet as active
// fetch this sheet

// figure out what the last row is
var lastRow = sheet.getLastRow();

// the rows are indexed starting at 1, and the first row
var startRow = 2;

// grab column 5 (the 'days left' column)
var range = sheet.getRange(2,5,lastRow-startRow+1,1 );
var numRows = range.getNumRows();
var days_left_values = range.getValues();

// Now, grab the reminder name column
range = sheet.getRange(2, 1, lastRow-startRow+1, 1);
var reminder_info_values = range.getValues();

var warning_count = 0;
var msg = "";

// Loop over the days left values
for (var i = 0; i <= numRows - 1; i++) {
var days_left = days_left_values[i][0];
if(days_left == 7) {
// if it's exactly 7, do something with the data.
var reminder_name = reminder_info_values[i][0];

msg = msg + "Reminder: "+reminder_name+" is due in "+days_left+" days.\n";
warning_count++;
}
}

if(warning_count) {
MailApp.sendEmail("youremail@yourdomain.com,anotheremail@anotherdomain.com",
}

};
```

This is a lot to process at once, so let’s break it down.

```  // get the spreadsheet object
// set the first sheet as active
// fetch this sheet
```

We start by grabbing the “active spreadsheet” which is the file on the Google Drive that this script is living in. Then, we need to get one individual sheet (or page) from the script. In this example, I’m setting the active sheet to be the first one in the spreadsheet document, just in case we’ve gone and added another at some point. Then I’m storing the active sheet in the “sheet” variable. This will let us grab regions to work on later in the script.

### Getting the Cells of Interest

```  // figure out what the last row is
var lastRow = sheet.getLastRow();

// the rows are indexed starting at 1, and the first row
var startRow = 2;

// grab column 5 (the 'days left' column)
var range = sheet.getRange(2,5,lastRow-startRow+1,1 );
var numRows = range.getNumRows();
var days_left_values = range.getValues();

// Now, grab the reminder name column
range = sheet.getRange(1, 1, lastRow-startRow+1, 1);
var reminder_info_values = range.getValues();
```

Now we’ve got to get the part of the sheet that we’re actually interested in. In this case, all we need is the “days left” column, and the “reminder name” column. However, we want this column for every valid row in the spreadsheet.

So, we start by figuring out which is the last valid row in the spreadsheet. Luckily, there’s an easy function for that. We already know the first row we want to consider, so that can be a constant for now.

From here, we need to use sheet.getRange to get the actual spreadsheet data. It’s function signature looks like:

`getRange(row, column, numRows, numColumns)`

This function is a bit confusing, since it takes a 1-indexed column and row, but will return a 2-D array that is 0-indexed. That means, if we want cell A1, we would need to call:

```var range_A1 = getRange(1, 1, 1, 1);
var values_A1 = range_A1.getValues();
```

After this, the actual value of cell A1 would be in values_A1[0][0].

### Processing the Cell Data

Now that we’ve got our values, we can loop over them and decide if we need to take action:

```
var warning_count = 0;
var msg = "";

// Loop over the days left values
for (var i = 0; i <= numRows - 1; i++) {
var days_left = days_left_values[i][0];
if(days_left == 7) {
// if it's exactly 7, do something with the data.
var reminder_name = reminder_info_values[i][0];

msg = msg + "Reminder: "+reminder_name+" is due in "+days_left+" days.\n";
warning_count++;
}
}
```

Here, the msg variable is going to hold the contents of our email, if we choose to send one. We’ll choose to send one based on whether or not we have any reminders, which we’re going to count with the `warning_count` variable. I’m comparing `days_left` to 7, which means that we’ll get a reminder exactly one week before the task is due. You could change this to whatever you wish – even negative numbers, if you want to be reminded if something comes past due!

You could also add a couple of tests here. Perhaps you’d like a 1-week left reminder and a day-of reminder. Just use 2 `if` statements, or an `if {} else {}` instead of the simple `if` I’ve got here.

### Sending the Reminder Email

```  if(warning_count) {
MailApp.sendEmail("youremail@yourdomain.com,anotheremail@anotherdomain.com",
}
```

Finally, it’s time to send the email. This is pretty straightforward and Google really makes it simple to do. The `if(warning_count)` check makes sure that we don’t send an email if there’s nothing to be reminded about.

However, for debug reasons, you may want to test your script and not send an email. If you want to do this, you can use:

```Logger.log(msg);
```

This will log a message when you run your script instead of sending the email. To see this log, you’ll need to go to the View menu, and choose Logs…. This will show you what log messages were printed each time your script has run.

Finally, make sure you save your script!

## Part 3: Events

Great, so now we’ve got a spreadsheet tracking our whatevers, and a script that can process the data and do something meaningful with it. Now we’ve got to automate. Again, Google makes this super easy. From the Resources menu, choose Current Project Triggers…. A menu will pop up, just click add one now to add a new trigger.

From here, you want to pick:

• Your function name (I called mine `checkReminders` in the sample code)
• Time Driven
• Day Timer
• Whatever time of day is convenient – I use “Midnight to 1am” so that I have a reminder first thing in the morning.

Then click Save. Now the spreadsheet will update and run the reminders script once a day, and send you an email.

## Similar Problems

I have a spreadsheet where each row has its own email address, and I want to send a similar email to a bunch of people instead of your example of sending email to just one. How do I do this?

This is called a mail merge and is a pretty common thing to do with spreadsheets. Google has a tutorial you can read about mail merges on their Google App Script website that you can read. You’ll see many of the same functions, like `getRange` and `sendEmail`, just used in slightly different ways.

If you were able to follow this tutorial, chances are pretty good that you can follow their code and get their example working as well.

While I’m on the subject of Google’s tutorials, they have a whole list of them, which you can view right here. There are a bunch of interesting ideas for projects in here to maximize the way you use Google Drive/Docs.

## Conclusion

Once you’ve got the fundamentals under your belt, it’s really not that hard to do all sorts of exciting things with spreadsheet scripting. The sky’s the limit! There’s nothing stopping you from sending your reminders only on certain days of the week, for example. You can even embed your spreadsheets into other applications… but that’s another topic for another post :)

More like this: Computing, Distilled Awesomesauce

• Elizabeth

Hi there — Great post, thank you! I’m very new to scripting, but I think I can figure it out and this will add a great deal of functionality to my processes!

One (probably very basic) question — what triggers the script to run? Does it just run in the background every day (night)? Or would I need to open the spreadsheet that contains the script?

Thanks again!

• Hey Elizabeth, welcome to WOTS :)

The “Events” section (section #3) details how to get the script to run regularly. When you configure the trigger, you’ll get the option of how often you want to run the script, and at what time of day.

You do have to make sure to reset the date in the “Last Date Completed” column, though.

• Aaron

I have a spreadsheet where I log all the due dates for various bills. I have a Sheet for every month. Is there a way to parse through multiple sheets?

• Yes, you sure can. You’ll want to look at the API docs for getSheets (which I’ve linked there).

You can use that to get all the Sheet objects in the Spreadsheet, and then you can do whatever you need to on those Sheet objects to generate your script’s results.

• Great Post! We just set up a simple system for reminders on tool callibration at our factory! Question, have you seen any good scripts for parsing incoming information on Gmail (company domain)?

• Dan,

Glad this helped! There’s a sample script on Google’s site for grabbing a bunch of data from a Gmail inbox which might make a good jumping off point for your new project.

You can also go to the Google Script Editor and start a new GMail script, which will give you another potential starting point.

There’s a full list of Gmail related script functions on this page which is probably going to be a good reference as you hone in on what you want the script to do.

• Thanks for the response, I will check out those resources.

• Thanks for the tutorial. Just made an auto emailed with this script. One question though, is there a way I can email the content of specific cells to my inbox?

• Yes, that’s what the function `sheet.getRange(...)` does. It returns a 2D array of cell values. From there you can index the array to get the contents of the cells. I do this in the the full script with the variable `reminder_info_values`.

• Ray Sumardi

hi agent86, i almost got everything right. The idea is to get the spreadsheet to report the scores of my students

Everything works fine except the email is reporting me numbers that doesn’t exist in the sheet

I modified the “Task Reminder” is due in “Days left”

with

“Name of student” has a score of “Score”

But the wrong student names are paired with the wrong scores

here’s the code:

```function checkReminder() {
// set the first sheet as active
// fetch this sheet

// figure out what the last row is
var lastRow = sheet.getLastRow();

// the rows are indexed starting at 1, and the first row
var startRow = 1;

// grab column 5 (the 'days left' column)
var range = sheet.getRange(2,2,lastRow-startRow+1,1 );
var numRows = range.getNumRows();
var days_left_values = range.getValues();

// Now, grab the reminder name column
range = sheet.getRange(1, 1, lastRow-startRow+1, 1);
var reminder_info_values = range.getValues();

var warning_count = 0;
var msg = "";

// Loop over the days left values
for (var i = 0; i <= numRows - 1; i++) {
var days_left = days_left_values[i][0];
if(days_left >0) {
// if it's exactly 7, do something with the data.
var reminder_name = reminder_info_values[i][0];

msg = msg + "Reminder: "+reminder_name+" has a score of "+days_left+".\n";
warning_count++;
}
}
Logger.log(msg);

};
```

what did i do wrong?

• Ray Sumardi

Here;s the result

Reminder: A has score of 7.454545454545454. Reminder: B has score of 7.458333333333333. Reminder: has score of 6.532864357864358.

The format of my excel is just 2 columns.

Column A the name, Column B the score

thank you, any help would be appreciated. it’s been bugging me for a week

• The format of the getRange call is:

`getRange(row, column, numRows, numColumns)`

So if you’re calling it twice on the same sheet (once to get `days_left_values` and the other to get `reminder_info_values`) and then you’re going to index them and associate them by row, you should probably start with different columns, but the same row in both calls, correct?

I hope that helps you spot your error :)

• maresa sumardi

sssssolved ! :D

thanks a lot agent86. You’re a lifesaver. now i am applying this technique to several spreadsheets:

1. students answer their exam on the spreadsheet. results are sent automatically to my email

2. i input the scores to another sheet and the results are tallied, averaged and sent to my email

3. i make a chart for it at the end of month :)

i also just realized this is a gaming blog :P

• Glad you managed to figure it out :)

While it is at times a gaming blog, we also cover a lot of tech related stuff as well. I and my colleague EBongo are engineers and gamers, and we just write about what we like :)

• EBongo

Yes, I believe we file that under “technotainment”.

• Andrew

yes, thanks for sharing agent86.

To get the ‘??—-??’ – you need to right click your file on google drive then press ‘share’ – the url appears in a text box, the ‘encoded’ bit = ‘??—-??’

PS: I couldn’t find a way to cc / bcc more than one person at a time [even though the developer doc says a comma separated list would do] !!

• Andrew

I included the send.mail within the loop – sending a custom email to each of a list of email addresses. That attachment [to each email was [obviously] the same file. I assume it is possible to programmatically identify the fileid and thereby customise the attachment for each email sentto…didn’t look into that though. Once again, very useful stuff agent86, thanks.

• camille

Wahoo, I’m just discovering scripts and find it awesome what can be done with them! I have a big spreadsheet with contact informations (about 10 columns including name, birthday, email) I’d like to automatically send an email to people on my spreadsheat using their name and email. Anyone know how to do that?

• Hey camille. What you’re talking about is called a “mail merge” and it’s a pretty common problem to have. Google’s got an example of doing this with the same scripting language we’re using here – check it out at this link.

• Andrew

Hi, this is the code I used for a personalised email [but with the same attachment] to a list of folk:

function repeatemail() { // get the spreadsheet object var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // set the first sheet as active SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]); // fetch this sheet var sheet = spreadsheet.getActiveSheet(); // figure out what the last row is var lastRow = sheet.getLastRow(); // the rows are indexed starting at 1, and the first row // is the headers, so start with row 2 var startRow = 2; // grab column 1 (the ’email address’ column) var range = sheet.getRange(2,1,lastRow-startRow+1,1 ); var numRows = range.getNumRows(); var emailaddress_values = range.getValues();

// Now, grab column 2 (the ‘index’ column) range = sheet.getRange(2, 2, lastRow-startRow+1, 1); var indexinfovalues = range.getValues(); // Now, grab column 3 (the ‘message’ column) range = sheet.getRange(2, 3, lastRow-startRow+1, 1); var messageinfovalues = range.getValues();

// Now, grab column 4 (the ‘first name’ column) range = sheet.getRange(2, 4, lastRow-startRow+1, 1); var firstnamevalues = range.getValues();

// Now, grab column 5 (the ‘last name’ column) range = sheet.getRange(2, 5, lastRow-startRow+1, 1); var lastnamevalues = range.getValues();

//var warningcount = 0; var msg = “”; // Loop over the email addresses for (var i = 0; i <= numRows – 1; i++) { var emailaddress = emailaddressvalues[i][0]; var indexinfo = indexinfovalues[i][0]; var messageinfo = messageinfovalues[i][0]; var firstname = firstnamevalues[i][0]; var lastname = lastnamevalues[i][0]; msg = “Hi “+firstname+”,\n\n in the ‘interesting’ file attached [and previously sent to you on the 2nd September], your personal INDEX identity number,”; msg = msg + ” displayed in the first column of the file, is: \n\n'”+indexinfo+”‘\n\n “+messageinfo+”.\n”; //see https://www.google.co.uk/#q=MailApp.sendEmail+How+to+use+arguments+bcc+cc+and+replyTo+.&start=40 //couldn’t get a comma separated list to work on the cc line so had to use a single entry on both cc and bcc… var file = DriveApp.getFileById(‘dfgibgjjg;dolfobkg-whatever’); MailApp.sendEmail(emailaddress,””+firstname+”, your Claim ‘Index Number’ = “+indexinfo+””, msg, {attachments: file.getAs(MimeType.PDF), cc: ‘[email protected]’, bcc: ‘[email protected]’}); } };

• Andrew

oops ! – sorry for the poor formating…

• Drew

Hi, in our spreadsheet different rows can have different email addresses associated with them (i.e. project leads)

How could it be configured to send the email to the address found in the appropriate column of the spreadsheet (let’s say, column B)?

Thanks :)

• Hey Drew,

A lot of people who come and read this article want the answer to this question. :) This is called a mail merge, and Google’s got an example. I went ahead and added a section to the article to cover the topic of mail merges, which links to Google’s example code. Hope that helps!

• Drew

Hi, Google’s mail merge tutorial doesn’t seem to get into “email reminders” (time-based functions) at all

I think what I (and maybe some other people) are looking for is for these two concepts to be combined into one apps script

To bring it back to your script, it almost seems as though in your section “Sending the Reminder Email” that the code could reference, instead of a static email address, a cell value that meets the criteria – i.e. the adjacent date column cell is within the proper time range to trigger

• Yes, that’s certainly something that can be done. It’s honestly a pretty simple extension of the logic – the `MailApp.sendEmail` call needs to be in the loop, rather than after it.

Alternatively, if you were able to follow my usage of Events, you could add that functionality to Google’s example.

I can code something up to solve this problem, but:

1. It’s probably going to take me a while to get to it (whereas the resources are available for folks to do it much faster, assuming they could follow my code and Google’s)
2. It’s not really what the article is about, and thus isn’t something I’ve done personally and feel like an expert about. (although it seems like a lot of people are interested in it nonetheless!)

I don’t want to sound harsh, but I do want people to learn to fish rather than me hand them the fish they need :) I’m just a humble gaming/tech blogger, and not really a Google App Script guru or tech support for same ;)

• Andrew

well said agent86 – your code example is what inspired me to do the required further research to do what I needed. I only requested further comment [above] after significant online research which couldn’t answer the query – and having done that I was almost sure I would get no response here [and I haven’t]…

• Drew

Understood. I had hoped maybe it would be a quick addendum :)

I will pursue further, perhaps using your code as a platform to get there

Thanks for posting and your addressing this with a possible solution! :)

• Tom

Just a little tip – you should reduce your calls to the spreadsheet by using sheet.getDataRange().getValues() then working with the array provided.

• agent86ix

Whether or not that’s an optimization depends heavily on Google’s implementation and the dimensions/contents of the spreadsheet. I’m making 2 calls to get values and I could make 1, yes. However, in your example I’d end up with all the rows and columns that are valid in the sheet. If the sheet has a lot of irrelevant rows, I’d have to take that data as well.

It might make sense to benchmark both approaches for a given sheet and see which was faster. However, on the whole, it’s all run in Google’s servers, and I don’t really care if it takes a tenth of a second or an hour, as long as it runs daily. Thus, I’m not really inclined to spend much time optimizing it.

• Pavel Todorov

Hello! Thank you for this great article! I tried to solve the problem my myself, but without success. My problem is that in the column 5 I have a fixed date in the future and I want a reminder when this date – today = 5. Can you help me? I wan to include this in the script, not in the sheet.

• agent86ix

Hey Pavel. If you want, you can do date math in the script – it’s just Javascript for the most part. Perhaps something like this would help.

Again, apologies for the late reply – sometimes comments slip through the cracks!

• Pavel Todorov

I found an mistake. On line 22 range = sheet.getRange(1, 1, lastRow-startRow+1, 1); should be range = sheet.getRange(2, 1, lastRow-startRow+1, 1); to get the correct reminder name. If we start from line 1 we will count the column title as a reminder.

• agent86ix

You are correct – that’s a typo in the article. I’ve corrected it. Apologies for the delay.

• Katie McLarty

I’m using your post to automate changes in tuition rates (based on age). In my spreadsheet each column is a different child, so I had to invert some of the script (row to column). However, when I run the script it only identifies the value in the first row/column. I’ve included the piece that I think is causing the problem, but I’m not sure how to fix it – Please help & thank you!

var lastColumn = sheet.getLastColumn();

var startColumn = 2;

var range = sheet.getRange(3, 2, 1, lastColumn-startColumn+1); var numRows = range.getNumRows(); var daysleftvalues = range.getValues();

range = sheet.getRange(1, 2, 1, lastColumn-startColumn+1); var reminderinfovalues = range.getValues();

• agent86ix

The array that is passed back from sheet.getRange is going to be indexed as range[row][column] regardless of the dimensions of the returned range.

Thus, in the for loop, you’re going to need to iterate over the columns rather than the rows:

This is the original code:

for (var i = 0; i <= numRows – 1; i++) { var daysleft = daysleft_values[i][0];

You’d need something like:

for (var i = 0; i <= numCols – 1; i++) { var daysleft = daysleft_values[0][i];

Notice that i is now iterating over the possible column values, and that the indexes into the daysleftvalues array have been swapped.

Hopefully that puts you on the right track. Unfortunately, I can’t really completely debug everybody’s variant of this script.

• Focha

How can I split the text in the email? For example:

“msg = “Remaining 5 days for goal. Message sent automatically.”

Appear in the email body like this:

“Remaining 5 days for goal.

Message sent automatically.”

• agent86ix

Add a n wherever you want to start a new line. In your case:

“Remaining 5 days for goal.nnMessage sent automatically.”

would probably do the trick.

• Laura

Can anyone help me?

I thought I implemented everything correctly but I did not receive an email. I am not very familiar with coding. I left the code as-is (copy/pasted the code) and changed the lines below.

17: var range = sheet.getRange(2, 6, 13, 1); (column 6 is my “days left” column, and there are 13 rows)

22: range = sheet.getRange(2, 1, 13, 1); (column 1 is my “reminder name” column, and there are 13 rows)

31: if(days_left == 3) { (I wanted a reminder is “days left” = 3)

41: inserted my email address to replace placeholder email

• Miryam Flores

Thanks so much. This helped me a lot, my code and sheet ended up being completly different to yours but I got the basic Idea from you.

• Matt Evans

I cant get the email to send. is there a way to test it

• Noam

WOW, Great article – thanks for that!

• Flavius

great post! just a question…are we able to get and email from table and set as ‘send to’?

• Ionut Hrenciuc

Hi Agent86 ,

Can you please help me because I’m getting the error: “Missing ) after argument list. (line 41, file “Code”) Dismiss” when I’m trying to save the script ? I didn’t modify your script just copy and paste it.

Thank you

• agent86ix

Hey Ionut!

Gosh, that line shouldn’t be in the script at all! The script was getting changed by a special routine that is trying to protect email addresses. We added it recently as part of a security suite. I turned it off for the code sections in this article. Try copying and pasting the code again, and it should work better this time.

Sorry about that! I didn’t even know it was part of the security upgrade…

• Ionut Hrenciuc

I did that already and it’s working very fine. You were truly helpful and I thank you very much. Hope I will find the time to learn how to write a code in Java somewhere in the future. If you have a hint where to start, I am listening. Cheers

• agent86ix

Awesome, glad to hear that helped!

Google Apps scripts use Javascript, which is also the language used for most web browsers. I learned it through a lot of trial and error, although I also have a degree in computer engineering which helps :)

I hear good things about Code Academy so you might check out their interactive courses if you want to learn. There’s a lot of Google-specific functions for interacting with spreadsheets, so you’ll want to refer to the Google documentation for that info.

Hi agent86 – I am trying to use your script “as-is”, and even copy and pasted it. I also copy and pasted your sheet data, and adjusted only a few parts to make it meet my needs. When I run the script, or try to debug it, I get:

“TypeError: Cannot call method “getActiveSheet” of null. (line 5, file “Code”)

Does this mean that the object I’m trying to call is undefined? I’m not really a Java guy – though I’m working on fixing that. From what I understand so far though from my coding past, it’s that I’m not defining the object. Any ideas on how to rectify this?

• agent86ix

Haha, glad to hear it :) The easiest problems are the ones I don’t even have to look at :P

• Instead of email, is it possible to get a reminder in alert message box. How can I use this to run on windows desktop and get the alert message box. (say using autoit or .Net to create an .exe file)

• agent86ix

Hey Arun,

The spreadsheet scripts run on Google’s servers (“the cloud” for the buzzword savvy) and thus can’t make things like alert boxes show up on your desktop. There may be Chrome extensions or something that make this happen, but I don’t know of any personally.

• Bingjie Hu

Thank you so much for writing this! I’ve created a similar reminder system using your code and it works so beautifully.

• Sean J Bauman

Thank you so much for this! Never having used Google Scripts, I was able to adapt your script to meet my company’s needs. This was very helpful!

• Lainey

Thank you for taking the time to explain this process. I had found another site for this type of solution and it was not nearly as clear as you have made yours! With that said, I used some of your script and created one of my own for lead tracking on a form submission/response. I am a neophyte at this stuff and surprised myself that It worked well except for one detail… it strings all the text together in the email. ugh. Here is my script, edited to protect the innocent. (smile)

function SendGoogleForm() { // You may replace this with another email address var email = “[email protected]”;

``````// Enter your subject for Google Form email notifications
var subject = "Website Lead Submitted";
``````

//get the active sheet var s = SpreadsheetApp.getActiveSheet();

//get info from last row var lastrow = s.getLastRow(); var range = s.getRange(lastrow, 1, 1, 13); var values = range.getValues();

MailApp.sendEmail(email, subject, values);

}

I have 13 columns (data fields) and the lead submission will always be the last row. Any ideas on why the data is run together with only commas to separate it. I would like the data to be displayed as a column with a return after each field.

• agent86ix

“values” is an array. sendEmail expects a string. Therefore, values is converted from an array to a string automatically, which probably leads to the comma separated message body.

You’ll need to loop over the array and build your own string, or use the join function or something similar.

• Lainey

Ok. That makes sense. Thank you very much!

• Randy Bach

Thanks for the detailed, I have also modified this slitghtly to fit my needs however I have one piece I can’t figure out. This code is set to fire the Email Function if the Value X == Value Y. I want the email to fire if Value X DOESN’T MATCH Value Y but I can’t figure out what to put in place of the ==. I have tried > and < and <>. None of them work, how would I change this so that it fires if the values don’t match. Below is the code that I’ve created.

function sendEmail() {

var startRow = 2; // First row of data to process

var numRows = 1; // Number of rows to process

// Fetch the range of cells A2:B3

var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());

// Fetch values for each row in the Range.

var data = dataRange.getValues();

var youremail = “[email protected]”;

Logger.log(data)

for (i in data) {

``````var row = data[i];

var date = new Date();

var sheetDate = new Date(row[21]);

Sdate=Utilities.formatDate(date,'GMT-0800','yyyy:MM:dd')

SsheetDate=Utilities.formatDate(sheetDate,'GMT-0800', 'yyyy:MM:dd')

Logger.log(Sdate+' =? '+SsheetDate)

if (Sdate == SsheetDate){

var subject = "US Data Not Updated";

var message = "Data failed to load";

}
``````

}

}

• tracey

looking for help on personalizing this… I’d like to send reminder emails to different email addresses (in column G) based on when there is one week (7 days) left in column K. It’s just not working right… :( any insight?

// figure out what the last row is var lastRow = sheet.getLastRow();

// the rows are indexed starting at 1, and the first row // is the headers, so start with row 2 var startRow = 2;

// grab column 11 (the ‘Days left before “Ready for 1st DAPP and Bordatella”‘ column) var range = sheet.getRange(2,11,lastRow-startRow+1,1 ); var numRows = range.getNumRows(); var daysleftvalues = range.getValues();

// Now, grab the puppy name column var range = sheet.getRange(2, 1, lastRow-startRow+1, 1); var numRows = range.getNumRows(); var puppynamevalues = range.getValues();

// Now, grab the foster’s email address var range = sheet.getRange(2,7,lastRow-startRow+1,1 ); var numRows = range.getNumRows(); var fosteremailvalues = range.getValues();

var warning_count = 0; var msg = “”;

// Loop over the days left values for (var i = 0; i <= numRows – 1; i++) { var daysleft = daysleftvalues[i][0]; if(daysleft == 7) { // if it’s today+7, do something with the data. var puppyname = puppynamevalues[i][0]; var fosteremail = fosteremailvalues[i][0]; var message = “Reminder: “+puppyname+” is due for 1st DAPP and Bordatella vaccinations in “+daysleft+” days.n”; var subject = “Puppy Vetting Reminder”; warning_count++; } }

if(warningcount) { MailApp.sendEmail(fosteremail, subject, message); }

};

• Mitchell

Hi all.

Wondering if anyone can provide a little insight for me. Unfortunately I am a complete novice with Google sheets and therefore with script writing also. I am looking to adapt the above script so that an email is sent to me when the value in column 5 (E) is lower than 7. Every time I try to change the values in the script, it shows an error message.

• agent86ix

Sorry I’m so late in replying, but you probably want to change the line:

if(days_left == 7) {

to:

if(days_left <= 7) {

If you don’t want it to send when daysleft is exactly 7, but just want it to send it when daysleft is 6 or lower, then you’d want to make that:

if(days_left < 7) {

• Francisco Araújo

Hey! This is really super useful and easy to understand, thanks!

I have a doubt when trying to add a new ‘IF’: I want to get an email out when we have 2 days left for a given date and on the date itself (so, 0 days left to the date). How should I do it?

How can I fit the second ‘IF’ statement with the already existing code?

• agent86ix

Try changing the line:

if(days_left == 7) {

to:

if(daysleft == 2 || daysleft == 0) {

I’ve made this change myself, and I think it will do what you want.

• Francisco Araújo

Awesome! What’s the rationale behind using the symbol || ? does it stand for an ‘OR’ condition?

• agent86ix

Yes, the || indicates logical OR. If daysleft is equal to 2, or if daysleft is equal to 0, then the if block is executed.

• Francisco Araújo

Perfect, many thanks!

• BSACPO

Thanks for the information, like many others I am a novice. I’ve tried adapting your sample for my needs, but I’m not getting the desired results.

I need a rolling date calculator, using the Hire date (Column I), six months out for Column L, 12 months for Column M and then as those dates pass calculate new dates.

Is a macro the way to go or should i stick with regular formula’s?

• agent86ix

A formula is probably the way to go. You can do date math directly in the cells – there are a couple of examples in the spreadsheet sample at the top of this article.

• Passive Gaming

Hello,

Great post, I have now go it working sending out lots of different bits of information to different people. My only question would be, is there any way to make the e-mail read a bit better? For example with things in bold and underlined?

Thanks

• xcon

hi,

great work! how do i change this to hours instead of days? today() only returns the date not actual hours+mins.

• JOhn

Great Tutorial. If anyone is trying this and its not sending an email, try editing the mailapp script to look like this

MailApp.sendEmail(“[email protected]”, // to

``````              "[email protected]",       // from

"Your Subject Goes Here",      // email subject
``````

msg); // email body

• Brendan Lane

9-months later, I just wanted to say thanks for posting this! For some reason, that fixed it for me :)

• Thanks so much for this. Based on your instructions it’s only taken me a couple hours to code a script that emails me whenever it’s time to back up my photos to an external hard drive.

• Shane

Agent 86 – brilliant, clear instructions. Thanks!

I’ve got what I assume is a fairly straight-forward adaptation: I have everything working fine, but want to add in a function that ignores dates that have passed. I have a master sheet of to-do’s that will roll over each year (hence not wanting to delete the rows once they’re complete)

In a nutshell, I am correctly receiving emails for all dates that are due in 7 days or less. However, this includes a whole bunch of dates that have passed!

Help!

• Muriel Brand

Practical suggestions , For what it’s worth , if your business needs a Capital One JB99344_AOBC , my colleague saw a fillable document here `http://goo.gl/HTIwlJ`

• hemant verma

Thanks for this. But it’s not working in my data. It has 5000+ rows and it keeps giving the error Limit Exceeded: Email Body Size. (line 61, file “Code”).

• Clark Hall

I am new to scripts so to learn I just copied your spreadsheet and script. When I clicked on Resources the only tabs that show up are: Libraries, Cloud Platform Project and Advanced Google Services. Has something changed since you wrote this articel?

• Greg Bonin

I believe it’s under Edit > Current project’s triggers

• Clark Hall

You are right. Thank you very much! I have a sheet with four dates in the same row. Drivers license expires, DOT Exam expires, training is due, MVR check is due. Is it possible to change the script to send me an email when any one of the four are 7 day out?

• Greg Bonin

It might be easier to grab each of those columns in a different variable, then run a FOR loop on each one, followed by the sendEmail block. If you do this, be sure to dump or reset the msg after each sendEMail.

This may not be very efficient if you have a lot of records…

Hello I’m very new to google scripts I am wanting to know if this is scrip can do what I want it to do. if so Ill do some more reading to work it out or if you can point me in the right direction I have a training register that I want it to email the person that their particular training is going to be out of date in say a month. All people are in rows and Training in the coloums

• axe99kk

My solution:

// fetch this sheet const sheet = spreadsheet.getActiveSheet();

// figure out what the last row is const lastRow = sheet.getLastRow();

const startRow = 2;

const nameColumn = 1; const amountColumn = 2; const statusColumn = 3; const dateColumn = 4;

const currentDate = (new Date()).setHours(0,0,0,0);

const pendingPayments = sheet.getRange(startRow, nameColumn, lastRow, 1).getValues().map(function(name, i) { const date = sheet.getRange(i + 2, dateColumn).getValue(); const paymentStatus = sheet.getRange(i + 2, statusColumn).getValue();

``````if (paymentStatus !== 'yes' && date && date.valueOf() === currentDate.valueOf()) {
return {
name: name[0],
amount: sheet.getRange(i + 2, amountColumn).getValue()
}
}

return null;
``````

}) .filter(function(x) { return x })

for (var i = 0; i <= pendingPayments.length – 1; i++) { const message = “Przypomnienie automatycznennZa co: ” + pendingPayments[i].name + “nDo zapłaty: ” + pendingPayments[i].amount + ” PLN”; MailApp.sendEmail(“[email protected]”, “[Wydatki] Przypomnienie o płatności: ” + pendingPayments[i].name, message); } };

• ComPlete novice here with spreadsheet math, formulas, code but…! I think I can figure it out from your clear directions. A couple questions – 1. I’m building a downloadable spreadsheet, so the reminders need to go to the person who’s downloaded to their Drive account. Do I need to add anything to make this happen or not? 2. Regarding the “Reminder Spreadsheet Message”, is the message itself included in the code or are you calling an email message stored elsewhere?

• Jake123Done

Hi! If I only have my email on the list of emails, I do not receive the message. If I add a second email on the message, I receive it on the second email and I see that my email account is CC’ed. Was wondering if there is a setting I am missing.

• Alex Cap

Thanks for this! I modified it to give me an alert in the Spreadsheet based on how many minutes are left before an event happens (or exactly at the time of an event, when 0 minutes are left). It runs perfectly when I manually set daysleft (in my case, timeleft) equal to a number currently in the list of daysleftvalues (timeleftvalues for me) and manually run the script right away. However, an “every minute” trigger does not work as expected.

• Sunil Parte

This is very helpful. Thanks a lot. I made some changes to script to work with standard “To-do List” template in google drive. Here is my script – https://github.com/sunildparte/gsheet-tasks-reminder.