In my previous
article about designing a efficient revision system using Google Apps Script, a study-aid, I discussed the steps involved in the setup, of Google Sheet as a Database store, and Google sheet for collecting information.
The goal of this article is to describe the engine that drives that system, in few simple steps, and by the end of this article the reader will be familiarized with the idea, of how to program Google sheets, to calculate the days of revision and setup Google Calendar events, for those days.
So let me start with defining what we expect this program to accomplish, in technical terms, the requirements:
1) Calculate the following days of revision, based on first study date: 2, 4, 7, 30, 60, 90
2) Set calendar (in this case, google calendar) events on the revision days mentioned
3) Send a email of the revision plan for each recorded topic, once setup
So let us start...
Step 1) Creating your helper methods in Script Editor:
Open the script editor from the Google spreadsheet that you set up in the last article, from Tools > Script Editor
Now add the following helper methods in your Script editor:
getColIndexByName() finds the column number based on column name.
It utilizes the
SpreadsheetApp class to find the active sheet, and the index number of the column, based on the column name, that is passed in. So in summary, it answers the question "What column number does this column name belong to?"
function getColIndexByName(colName)
{
var sheet = SpreadsheetApp.getActiveSheet();
var numColumns = sheet.getLastColumn();
var row = sheet.getRange(1, 1, 1, numColumns).getValues();
for (i in row[0]) {
var name = row[0][i];
if (name == colName) {
return parseInt(i) + 1;
}
}
return -1;
}
correctTheDate() method is used to correct a issue noticed in google forms.
It is noticed that when the date that is being entered is being set to the 11PM of the previous day. It may be a issue due to the Timezone differences, and hence it is adjusted to the next day, if the data entered as the "first study date" in the google form is being recorded as 11PM of the previous day.
function correctTheDate(DayDateEntered)
{
if(DayDateEntered.getHours() == 23)
{
DayDateEntered.setDate(DayDateEntered.getDate() + 1);
}
return DayDateEntered;
}
addDate() method adds a given number of days to a given date.
Date & time is maintained internally inside a computer, as a count of the number of milliseconds that has passed since Jan 1, 1970. To calculate any number of seconds from a given date we convert a given date to milliseconds since epoch, and add it to the number of days, we want to add, converted to milliseconds. Finally, we convert the total number of milliseconds to date, and we will have the given date advanced by the given number of days
function addDays(date,numDays) {
var timeinMs = numDays * 24 * 60 * 60 * 1000;
var curDtInMs = date.getTime();
var newTimeInMs = curDtInMs + timeinMs
var newDate = new Date();
newDate.setTime(newTimeInMs);
return newDate;
}
As you will soon see, we use this addDays() method to calculate our 2nd, 4th study dates and so on, for our revision schedule, correctTheDate() to address issues with the First study date, and getColIndexByName() to quickly locate the columns by their name.
Step 2) Creating your form submit method :
setRevisionSchedule() calculates the revision dates, sets the calendar events and e-mails the revision summary
This method which gets triggered on submission of linked Google form, (trigger set up will be in next step) will accomplish the following steps, in that order:
- Calculate and set the revision dates in the respective spreadsheet columns
- Set the calendar events corresponding to revision dates with a email reminder, 5 minutes before event commencement using CalendarApp class, in the default users calendar
- Finally, sends a email of the revision schedule summary using MailApp and the sendEmail() method to the user's default email address.
function setRevisionSchedule(e)
{
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var isCalendarSet = sheet.getRange(lastRow, getColIndexByName("Calenadar Set ?")).getValue();
if(isCalendarSet != "yes" && lastRow > 1)
{
var LearningSourceTitle = sheet.getRange(lastRow, getColIndexByName("Learning Source Title")).getValue();
var SubSection = sheet.getRange(lastRow, getColIndexByName("Sub Section")).getValue();
var Day1Date = new Date(sheet.getRange(lastRow, getColIndexByName("First Study Date")).getValue());
Day1Date = correctTheDate(Day1Date);
var DayNextRev = new Date(Day1Date);
DayNextRev = addDays(Day1Date,1);
sheet.getRange(lastRow, getColIndexByName("2nd Day Revision")).setValue(DayNextRev);
var Day2 = "2nd Day Revision is Scheduled on " + DayNextRev.toDateString()
var DayNextRevEvent = CalendarApp.getDefaultCalendar().createAllDayEvent("2nd Day Revision of Title: " + LearningSourceTitle + " | Sub: " + SubSection,DayNextRev,{Description: '2nd Day revision based on 1-2-4-7 principle'});
DayNextRevEvent.addEmailReminder(5);
DayNextRev = addDays(Day1Date,3);
sheet.getRange(lastRow, getColIndexByName("4th Day Revision")).setValue(DayNextRev);
var Day4 = "4th Day Revision is Scheduled on " + DayNextRev.toDateString()
var DayNextRevEvent = CalendarApp.getDefaultCalendar().createAllDayEvent("4th Day Revision of Title: " + LearningSourceTitle + " | Sub: " + SubSection,DayNextRev,{Description: '4nd Day revision based on 1-2-4-7 principle'});
DayNextRevEvent.addEmailReminder(5);
DayNextRev = addDays(Day1Date,6);
sheet.getRange(lastRow, getColIndexByName("7th Day Revision")).setValue(DayNextRev);
var Day7 = "7th Day Revision is Scheduled on " + DayNextRev.toDateString()
var DayNextRevEvent = CalendarApp.getDefaultCalendar().createAllDayEvent("7th Day Revision of Title: " + LearningSourceTitle + " | Sub: " + SubSection,DayNextRev,{Description: '7th Day revision based on 1-2-4-7 principle'});
DayNextRevEvent.addEmailReminder(5);
DayNextRev = addDays(Day1Date,29);
sheet.getRange(lastRow, getColIndexByName("30 Day Revision")).setValue(DayNextRev);
var Day30 = "30 Day Revision is Scheduled on " + DayNextRev.toDateString()
DayNextRevEvent = CalendarApp.getDefaultCalendar().createAllDayEvent("30 Day Revision of Title: " + LearningSourceTitle + " | Sub: " + SubSection,DayNextRev,{Description: '30 Day revision based on 1-2-4-7 principle'});
DayNextRevEvent.addEmailReminder(5);
DayNextRev = addDays(Day1Date,59);
sheet.getRange(lastRow, getColIndexByName("60 Day Revision")).setValue(DayNextRev);
var Day60 = "60 Day Revision is Scheduled on " + DayNextRev.toDateString()
DayNextRevEvent = CalendarApp.getDefaultCalendar().createAllDayEvent("60 Day Revision of Title: " + LearningSourceTitle + " | Sub: " + SubSection,DayNextRev,{Description: '60 Day revision based on 1-2-4-7 principle'});
DayNextRevEvent.addEmailReminder(5);
DayNextRev = addDays(Day1Date,89);
sheet.getRange(lastRow, getColIndexByName("90 Day Revision")).setValue(DayNextRev);
var Day90 = "90 Day Revision is Scheduled on " + DayNextRev.toDateString()
DayNextRevEvent = CalendarApp.getDefaultCalendar().createAllDayEvent("90 Day Revision of Title: " + LearningSourceTitle + " | Sub: " + SubSection,DayNextRev,{Description: '90 Day revision based on 1-2-4-7 principle'});
DayNextRevEvent.addEmailReminder(5);
sheet.getRange(lastRow, getColIndexByName("Calenadar Set ?")).setValue("yes");
var userEmail = Session.getActiveUser().getEmail();
MailApp.sendEmail({
to: userEmail,
subject: "Revision Schedule for Title: " + LearningSourceTitle + " | Sub: " + SubSection,
htmlBody: "<h2><b>Revision is Scheduled for Title: " + LearningSourceTitle + " | Sub: " + SubSection + "</b></h2><br><hr><br>" +
Day2 + "<br> " +
Day4 + "<br> " +
Day7 + "<br>" +
Day30 + "<br> " +
Day60 + "<br> " +
Day90 + "<br><br>" +
" <h3>---- THE 1-2-4-7 System ------</h3><br>"
});
}
}
Step 3) Setting up trigger, for your form submit method :
As mentioned, in the previous step method setRevisionSchedule() needs to be triggered when the linked Google form, which records the Study details, is submitted.
This is performed using the Edit > Current project's triggers option in the Script editor.
Select the method "setRevisionSchedule()" for the dropdown, under "Run" column, and "From spreadsheet" for the Events. Time-driven events, are for when you would like to perform events on a schedule, which does not fit our current requirement.
For, when to trigger the method, select "On form submit", since that is when we want our method "setRevisionSchedule()" to run.
That is it. You now have a very efficient and organized study-aid. All that is left now is to grab the link for the google form, and start using it. This leads to our next step.
Step 4) Grab the link, for the study-aid :
The link for the Google form, which allows us to enter the information regarding the topic of study, and the first study date, can be obtained using the "Form" menu on the associated Google Spreadsheet.
This will lead to the form, which provides the link.
You can copy that link, bookmark it, or embed it in your own webpage and use it to access the form for your study-aid. Please bear in mind, that you will be requested to provide our script necessary permissions to run and modify your Google Spreadsheets, the first time you run it.
Thank you for patiently following these steps and congratulations, on setting-up, and programming your own study-aid, which will allow you to have a very organized revision schedule, improving your chances of retaining the topics you studied for a long time. Feel free, to comment on the article or reach out for any suggestions.
