Check out the Spreadsheet template
Download the Spreadsheet from here. This is a very simple spreadsheet that contains a sheet per each month and all of them looks like this
- Task: Brief description about the task we need to do. For example: “Washing the car”, “Do the shopping” or whatever action we want to be notified.
- Notified (From/Till): We introduce the range of days of the month in which we want to be notified.
- Done: There’s a selectable in which the only option is “completed”.
Open the script editor
Click on Tools and then Script editor
The Script editor is the place in which we will build our own agenda notifier.
Let’s code our notifier!
Google offers us an awesome API’s to interact with Google services such as Gmail, Documents, Contacts among others. The editor provide us with a very nice Content assist (ctrl+Backspace) that will show us the available options.
Create a function that return the list of tasks not completed
For example, if today is day 7th of January, the function will return all the tasks not completed where column from is lower (or equal) than 7 and column till is upper (or equal) than 7.
- We read the sheet with given name.
- We iterate over all the rows and we push in the array the tasks that should be notified today and they have not been completed yet.
Order the tasks in the array that need to be completed sooner
Create a document with the list of tasks and send by email to our email account
- We create a temporary Google document and put the list of tasks to be done
- Document is sent by email to the email account in variable emailTo
- Remove the temporary document
We create the main function that we take the day of today and will orchestrate the calls to the above functions
Full code looks like
Run the main function and retrieve an email
Click on Run > Run function > main and our code will be executed.
Immediately, we will retrieve an email with and attached document that contains the list of tasks to be done
The attached document would look like this
Create a trigger and be notified
Click on Edit > Current project’s triggers
- Click on Add trigger button (It’s shown on the bottom right of the page)
We select the main function and the type of event source and time based trigger.
Finally we choose the time we want to be notified.
That was just a basic guide to show how of easy is consuming Google API’s and run scripts written by us.
The full code can be found on Github
If you enjoyed the article, please share it!