Designing a efficient revision system (personal use) - using Google Apps Script

In my previous article, about my journey to achieve AWS CDA, I mentioned about a study-aid I created called the "1-2-4-7" system, which organizes revision schedules to improve memory retention, by prompting to revise on the 2nd, the 4th and on the 7th day. Optionally it also reminds on the 30th, 60th and 90th days.

For this purpose I created in Google Apps Script, a program called which allows me to specify a topic and subtopic/page number, and the first study date. On submission, this program will save that information into a Google spreadsheet, Set up Google calendar events to revise that topic on the 2nd , 4th, 7th, 30th, 60th and 90th days, and sends me a summary email and remainder emails on those days.

The goal of this article is to describe the design and architecture of that system, in 3 simple steps, and by the end of this article the reader will be familiarized with the idea, the fundamental components involved, and the interaction between them.

So let me start with defining what we expect this program to accomplish, in technical terms, the requirements:

1) Record the topic with sufficient detail that needs to be revised

2) Record the first study date of the topic

3) Remind user to revise the topic on the following days: 2, 4, 7, 30, 60, 90

4) Set calendar (in this case, google calendar) events on the revision days mentioned

5) Calculate & Record in the spreadsheet the revision days for the (given)topic

6) Send a email of the revision plan for each recorded topic, once setup

Why Google Apps ?

A permanent record of any event, in the context of computer science, is ideally stored in a database storage system. Void of such system, or for other reasons such as cost efficiency , spreadsheets are traditionally used for storing information ranging from sales records to game scores.

This system being a personal study-aid, along with the requirements to integrate with other tools such as calendar and email, makes the google apps, which already does possess these tools, an ideal candidate for building the system using it. Also the economy achieved for personal use, utilizing the google apps cannot be ignored either.

So let us start...

Step 1) Creating a google spreadsheet:

We will be using a google spreadsheet, to store our revision records. For this purpose login into your google account, and create a google spreadsheet and name it "Revision Tracker" (or something of your choice)

Step 2) Creating a google form:

Google form, is used to collect information that we need to store in the spreadsheet, such as the topic of study, and the first study date. Let us call it "Revision Tracker Form" (or something of your choice)

Google forms, provides a easy UI design that lets you add fields, of a variety of data types. For our purposes, we add 3 required fields:

1. Learning Source Title
2. Sub Section
3. First Study Date

Now, the last few steps in setting up our system involve linking this form with our spreadsheet from Step 1

Step 3) Link Google form with spreadsheet:

For this, switch over to the responses tab on the form, you created in Step 2 and select the Spreadsheet from Step 1 as the response destination

This would create a new sheet, called "Form Responses" on the spreadsheet (Revision Tracker) where the responses will be recorded. You can optionally rename that sheet, and add additional columns that suit your purpose.

Our linked form, can be easily accessed from our Spreadsheet using Form > Go to live form

Since this "Form Responses" sheet, will be our System of Record (SOR) for our Revision/topic records, we would want to add additional columns that calculate and record the 2nd, 4th, 7th, 30th, 60th and 90th days of revision. In addition a column will be set to record the fact about whether the corresponding calendar events are set or not (yet).

With that we are setup to proceed to the next step, where we accomplish the functional goals of our system, which are:

1. To calculate the 2nd, 4th, 7th, 30th, 60th and 90th days of revision
2. To send the summary email of our revision plan
3. To set the calendar events corresponding to our revision schedule

These will be discussed in our next article, but be warned that it can get a bit slightly heavy (for some, at least) on the programming end. But I will do my best in the hopes of de-mystifying Google Apps & un-leashing the awesome power of Apps Script for those whose interest is piqued by this article of 3 simple steps to setup your own revision system.