Google Apps Script is a server side scripting language that interacts with Google Docs and other Google products. Among other things it can be used to add custom functionality to a Google Spreadsheet. I have used it to write a simple reminder service.
This could be made more sophisticated with each action having a different owner that should receive a reminder. But it should serve as an example for someone just getting started with Google Apps Scripts to build on. Or for someone that wants to build their own reminder service.
The steps we're going to follow are
- Create a spreadsheet to contain our list of actions
- Add a Close Action function
- Add Close Action to the spreadsheet menu
- Add a Send Reminders function
- Add Send Reminders to the spreadsheet menu
- Schedule the Send Reminders function to run each night
Create and name the spreadsheeet (I called mine "Actions"). You'll need two tabs. Call one "Actions" and the second "Closed". These will contain the list of open and closed actions respectively. Add a header row to each tab naming the columns: "Created", "Closed", "Due" and "Summary". The first three columns will be dates for when each action was created, when it was closed, and when it is due. The summary is the text that will be sent in the reminder email.
You may want to add a few examples on each sheet and make sure that the dates are formatted correctly.
Add a Close Action function
The closeAction function is simpler than the sendReminders, so let's start with it. Click Tools / Script Editor. A tab should open with the following function
function myFunction() {
}
Highlight the function and replace it with the following
function closeAction() {
// get the active spreadsheet and sheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
// check that the user is on the sheet named "Actions"
var name = sheet.getName();
Logger.log("name is " + name);
if (name != "Actions") return;
// get the row the user is currently on
var row = sheet.getActiveRange().getRow();
var data = sheet.getRange(row,1,1,5).getValues();
Logger.log(data);
// add it to the end of the Closed sheet
var closedSheet = spreadsheet.getSheetByName("Closed");
var newRow = closedSheet.getLastRow();
var newRange = closedSheet.getRange(newRow+1, 1, 1, 5);
// add todays date into the closed date column
var today = new Date();
data[0][1] = Utilities.formatDate(today,"EST","MM/dd/yy");
newRange.setValues(data);
// delete the row from the actions sheet
sheet.deleteRow(row);
}
When you save, you'll be prompted to name the project. Let's call it "Reminder Scripts". Now let's add the closeAction function to the spreadsheet menu to make testing it easier.
Add Close Action to the spreadsheet menu
We want the Close Actions entry to appear in the spreadsheet menu every time we open the document. So let's add a function which will run every time the document is opened. Paste the following in below the closeAction function:
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Close Action", functionName: "closeAction"} ];
ss.addMenu("Actions", menuEntries);
}
Select this new onOpen function in the pull down list in the toolbar and click the run button. When you go back to the spreadsheet now you should see an "Actions" menu item. Click this and there should be a "Close Action" sub-menu.
Let's test the closeAction function. Add a few example actions on the actions sheet, leaving the closed date blank. Click anywhere on the row of one of these actions and then click Actions / Close Action. The action should be deleted automatically. When you switch to the closed sheet you should see this action has been added with today's date.
Add a Send Reminder function
So we have sheets with our open and closed actions, and we have a menu item to mark an open action as closed. Now let's get to sending reminders. Add the following function below the onOpen function in the Script Editor. Make sure to change the email address at the top of the function.
function sendReminders() {
var emailAddress = "youremailaddress@gmail.com";
var daysBefore = 5; // days before due date to send reminder
var today = new Date();
var message = "due\tsummary\n";
// get url and name of spreadsheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var url = spreadsheet.getUrl();
var name = spreadsheet.getName();
var subject = "Reminders from " + name;
// get the number of actions
var sheet = spreadsheet.getSheetByName("Actions");
var numRows = sheet.getLastRow() - 1;
// if there are no actions then return
if (numRows == 0) return;
// read all the actions into an array
var data = sheet.getRange(2, 1, numRows, 5).getValues();
// Sort by ascending due date
data.sort( function (a, b) { return a[2]-b[2] });
for (i in data) {
var row = data[i];
// skip actions that are closed
var closed = row[1];
if (closed != "") continue;
// skip actions that are not due
var due = row[2];
var days = daysLeft(due, today);
if (days > daysBefore) continue;
// add due reminders to the email message
var summary = row[3];
message += days + "d" + "\t" + summary + "\n";
}
// add the url of the spreadsheet to the end of the email
message += "\n" + url;
MailApp.sendEmail(emailAddress, subject, message);
Logger.log(message);
}
You'll also need to add this function which is used above. This calculates the number of days between two dates.
function daysLeft(date1, date2) {
var ONE_DAY = 1000 * 60 * 60 * 24
var date1_ms = date1.getTime()
var date2_ms = date2.getTime()
return Math.round((date1_ms-date2_ms)/ONE_DAY)
}
Add Send Reminders to the spreadsheet menu
Find the onOpen function that you created earlier and change it as follows
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Close Action", functionName: "closeAction"},
{name: "Send Reminders", functionName: "sendReminders"} ];
ss.addMenu("Actions", menuEntries);
}
Now make sure there are some actions that are overdue or within 5 days of due. Click Actions / Send Reminders. The first time you do this, Google will present a popup asking that you authorize the function. This is because of the use of MailApp. Go ahead and authorize. Now you'll need to click Actions / Send Reminders. This time you should get an email with all the due reminders.
Notice that anytime you change the sendReminders function, Google will prompt you to re-authorize. If all you want is a reminder service where you have to remember to go in and send yourself reminders, then you are done. Assuming you want the reminders to be automatic then we need to...
Schedule the Send Reminders function to run each night
From the Script Editor click Triggers / Current Script's Triggers... and click Add a new trigger. Select sendReminders, Time Driven, Day timer, 5am to 6am. This will cause sendReminders to run once each night between 5am and 6am.
Summary
That's it. You should now have a working reminder service built using Google Apps Script. You can enter in all those actions and receive a simple email reminder of all the due ones every day. Remember, it will keep reminding you about an action until you open the spreadsheet and mark the action as closed. That's what the link in the email is to help with, and the Close Action item in the Actions menu.
Here are some of the capabilities of Google Apps Script that we've used here
- Creating a function that runs whenever the Google Spreadsheet is opened
- Creating a custom menu in Google Spreadsheet which runs a function
- Using MailApp to send an email
- Creating a trigger to run a Google Apps Script function nightly
Even if you're not looking to build a reminder service, hopefully this post will serve as a useful example of some of these capabilities of Google Apps Script. Post a comment to let me know if this is useful. If you have trouble with it, post a comment and I'll try to help.
Fantastic...This is what I dreamed to have such a script.
ReplyDeleteReminder script works fantastic.
Closed script runs without error but items in the Action sheet is not moving to the Closed sheet.
Days left function ; Please tell what is this for. On run this gives an error as follows
TypeError: Cannot call method "getTime" of undefined. (السطر 84)Dismiss
Please solve the problem of "close function. ( not moving from Action sheet to Closed sheet)
Thanks really ..May the Almighty God Bless you. Peace be upon you.
Please mail me at sacosana@gmail.com
Thanks.. It worked for me the Closed function after i change the name of the sheet from Actions to actions.
ReplyDeleteOnly the "daysleft function" gives the following error eventhough it did not affect anything
TypeError: Cannot call method "getTime" of undefined. (السطر 84)Dismiss
Thanks again...
sacosana - thanks for the feedback. I've corrected the case on the "actions" and "closed" sheets in the functions above (now "Actions" and "Closed" to match the instructions).
ReplyDeleteI've also added a comment to explain that DaysLeft() is a function that calculates the number of whole days between two dates.
I'm not sure why you're seeing a TypeError. Perhaps there is a row in your actions sheet that has a corrupted date?
Thanks for the Script.It works fine. but It gives an Error as follows when there is no date available in "Due"
ReplyDeleteTypeError: Cannot find function getTime in object . (line 84)
if "closed column" is filled with data then It skips without error and also no reminder mail. Logic is okay once it is finished we dont require a reminder. but
My case, the "due column" will be filled only later depends on other criteria. But without date on due column it gives the above error. Just because of one row where the due dates is not filled I will not get reminder even though "due dates" are filled for other rows for different cases.
Please help in resolving this.
I think if you want to skip actions which do not have a due date you can make the following modification (add the if statement below):
ReplyDeletevar due = row[2];
if (due == "") continue;
var days = daysLeft(due, today);
I haven't tested this. Let me know if it works.
Thanks Mr. Alex for the "If statement."
ReplyDeleteIt works only if I remove the following script.
// skip actions that are not due
var due = row[2];
var days = daysLeft(due, today);
if (days > daysBefore) continue;
How to have both running.
The second problem is that We should get reminder mails only if there is something to be reminded. But for an incident even if everything is closed we get a blank reminder mail with just Title, Summary and Due.
How to stop this.
Wish you a and all a very Happy new year 2012...
Hello Mr. Alex. Good day. It works perfectly for me after adding the script for the blank due column.( skiping if the due columnn is blank).I have added after the skip action that closed. Perfect.
ReplyDeleteYes. I too need a script to be added to avoid getting blank reminder if actions are closed or if the date is not yet due. Reminder mail shoud come only if there is something to be reminded. Please help.
Please help in getting the script to avoid getting blank reminder if actions are closed and also if the date is not yet due. Reminder mail shoud come only if there is something to be reminded. Please help.
ReplyDeleteThanks for this script! How would I tweak the script to send reminders to multiple email recipients?
ReplyDeleteThis comment has been removed by the author.
ReplyDeletesimply fantastic! thank you for making it so simple!
ReplyDelete@steve, simple add ur email ids with a comma separating then. ex: test@test.com,test1@test1.com
ReplyDeleteGetting the following message (TypeError: Cannot find function getTime in object ) after changing the onOpen code. Also, "Send Reminders" does not appear on the drop-down menu under "Actions."
ReplyDeleteAny suggestions most appreciated.
Many thanks.
Sounds like a typo somewhere. Perhaps the name of the date variable (date1 or date2) is spelled differently between the function signature and where getTime is called on it?
ReplyDeleteThanks—I'll take a look, but I did a straight cut and paste, so I'm not sure what could have happened.....
ReplyDeleteHere's the full script if that helps:
ReplyDeletefunction closeAction() {
// get the active spreadsheet and sheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
// check that the user is on the sheet named "Actions"
var name = sheet.getName();
Logger.log("name is " + name);
if (name != "Actions") return;
// get the row the user is currently on
var row = sheet.getActiveRange().getRow();
var data = sheet.getRange(row,1,1,5).getValues();
Logger.log(data);
// add it to the end of the Closed sheet
var closedSheet = spreadsheet.getSheetByName("Closed");
var newRow = closedSheet.getLastRow();
var newRange = closedSheet.getRange(newRow+1, 1, 1, 5);
// add todays date into the closed date column
var today = new Date();
data[0][1] = Utilities.formatDate(today,"EST","MM/dd/yy");
newRange.setValues(data);
// delete the row from the actions sheet
sheet.deleteRow(row);
}
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Close Action", functionName: "closeAction"},
{name: "Send Reminders", functionName: "sendReminders"} ];
ss.addMenu("Actions", menuEntries);
}
function sendReminders() {
var emailAddress = "benzacar@gmail.com";
var daysBefore = 5; // days before due date to send reminder
var today = new Date();
var message = "due\tsummary\n";
// get url and name of spreadsheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var url = spreadsheet.getUrl();
var name = spreadsheet.getName();
var subject = "Reminders from " + name;
// get the number of actions
var sheet = spreadsheet.getSheetByName("Actions");
var numRows = sheet.getLastRow() - 1;
// if there are no actions then return
if (numRows == 0) return;
// read all the actions into an array
var data = sheet.getRange(2, 1, numRows, 5).getValues();
// Sort by ascending due date
data.sort( function (a, b) { return a[2]-b[2] });
for (i in data) {
var row = data[i];
// skip actions that are closed
var closed = row[1];
if (closed != "") continue;
// skip actions that are not due
var due = row[2];
var days = daysLeft(due, today);
if (days > daysBefore) continue;
// add due reminders to the email message
var summary = row[3];
message += days + "d" + "\t" + summary + "\n";
}
// add the url of the spreadsheet to the end of the email
message += "\n" + url;
MailApp.sendEmail(emailAddress, subject, message);
Logger.log(message);
}
function daysLeft(date1, date2) {
var ONE_DAY = 1000 * 60 * 60 * 24
var date1_ms = date1.getTime()
var date2_ms = date2.getTime()
return Math.round((date1_ms-date2_ms)/ONE_DAY)
}
I can't see any errors. A few questions. When you close and open the spreadsheet, do neither the close action nor send reminders items appear in the menu bar?
ReplyDeleteCan you check that there are no invalid dates in the spreadsheet? It occurs to me that the typeerror you are seeing would happen if the function read a cell that didn't have a date in it and then tried to call getTime. Even a blank line in the spreadsheet could cause that problem.
ReplyDeleteHi, Alex—Close Action and Send Reminders both appear, but when I click on a date cell in any of the active columns and try to send a reminder, I get the same message as before: TypeError: Cannot find function getTime in object.
This is all that's in the Actions tab of the spreadsheet.
Created Closed Due Summary
Get on it.
Now.
2/2/2013 2/14/2013 Let's go.
2/2/2013 2/18/2013 Come on.
________________
Sorry for the bother. I'm sure I must be overlooking something obvious. Many thanks!
I think the problem is those two rows with "Get on it." and "Now.". It looks like those are in rows 2 and 3 and that they have no dates. So either you have left the date fields blank or you have put the text in the date column. Either way it's going to cause the sendReminders function to fail, because it expects to find valid dates in all cells of column A except the header. If you delete rows 2 and 3 it should work.
ReplyDeleteThanks. This is awesome. I know this is years too late, but may I ask one question? Is there any way to have one reminder (row) go to one email address and another reminder (a different row) go to another email address?
ReplyDeleteLet me see if I understand what you're looking to do. Are you thinking of adding a new column to the Actions sheet, so that you have the headings: Created, Closed, Due, Summary, Email. And then you want one reminder email per row in the Actions sheet, with it being sent to the address in the Email column for that row?
DeleteIf that's what you're looking for then at a high level you'll need to change the sendReminder function in the following ways:
- add a line in the for loop which sets emailAddress = row[4]
- move the closing brace } after the Logger.log to bring the sending into the loop
- adjust the lines that set the message value so they just contain the current reminder
If I misunderstood what you're trying to do, let me know.
Hey I need some help with an error about the following - TypeError: Cannot call method "getTime" of undefined. (line 83, file "Code") I am stuck and dont know what to do to make it work?
ReplyDeleteTypically this happens if there is a row in the spreadsheet without a valid date in the due column, can you try removing rows in the spreadsheet until the problem goes away to isolate which row might be causing it?
DeleteI know I am super late to the party, but I'd like you to know that this script is amazing!! Thank you for keeping it in the interwebz.
ReplyDelete