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?"
/*
The getColIndexByName helper function returns the column index for a given column name.
*/
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.
/*
2017-10-31 SK: Since 2017-10-30, it is noticed that the entries
into "First Study Date" column, from the form are always in date format,
and that the date, which has no time component in form is set to 23:00 (11 pm)
of previous day to the actual date entered on the form.
So a correction is introduced to correct the day, if 23:00 is noticed in
the time component. Day is adjusted to the next day of the value
in "first study date" column, which is the actual date entered in the form.
getHours() is a javascript Date class method
*/
function correctTheDate(DayDateEntered)
{
//Browser.msgBox("getHours() is " + Day1Date.getHours());
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
/*
2017-10-06 SK : Adds days by converting them to milliseconds
*/
function addDays(date,numDays) {
//converts number of days to add in Ms
var timeinMs = numDays * 24 * 60 * 60 * 1000;
//gets the current date in milliseconds since epoch 1970
var curDtInMs = date.getTime();
var newTimeInMs = curDtInMs + timeinMs
var newDate = new Date();
//create new date from the sum of milliseconds since epoch
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.
/*
Calculates the revision dates, sets the calendar events and e-mails the summary, for the the last
row in the active sheet if the "Calendar Set?" column is not set to "yes" when the method is triggered
Finally it will set the value in the "Calendar Set?" column for that row to 'Yes', to prevent it from being set in future.
*/
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);
//Browser.msgBox(Day1Date);
var DayNextRev = new Date(Day1Date);
//Set the "2nd Day Revision"
DayNextRev = addDays(Day1Date,1);
sheet.getRange(lastRow, getColIndexByName("2nd Day Revision")).setValue(DayNextRev);
var Day2 = "2nd Day Revision is Scheduled on " + DayNextRev.toDateString()
// Creates an all-day event with email reminder for calendar, on the 2nd day
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);
//Set the "4th Day Revision"
DayNextRev = addDays(Day1Date,3);
sheet.getRange(lastRow, getColIndexByName("4th Day Revision")).setValue(DayNextRev);
var Day4 = "4th Day Revision is Scheduled on " + DayNextRev.toDateString()
// Creates an all-day event with email reminder for calendar, on the 4th day
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);
//Set the "7th Day Revision"
DayNextRev = addDays(Day1Date,6);
sheet.getRange(lastRow, getColIndexByName("7th Day Revision")).setValue(DayNextRev);
var Day7 = "7th Day Revision is Scheduled on " + DayNextRev.toDateString()
// Creates an all-day event with email reminder for calendar, on the 7th day
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);
//Set the "30 Day Revision"
DayNextRev = addDays(Day1Date,29);
sheet.getRange(lastRow, getColIndexByName("30 Day Revision")).setValue(DayNextRev);
var Day30 = "30 Day Revision is Scheduled on " + DayNextRev.toDateString()
// Creates an all-day event with email reminder for calendar, on the 30th day
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);
//Set the "60 Day Revision"
DayNextRev = addDays(Day1Date,59);
sheet.getRange(lastRow, getColIndexByName("60 Day Revision")).setValue(DayNextRev);
var Day60 = "60 Day Revision is Scheduled on " + DayNextRev.toDateString()
// Creates an all-day event with email reminder for calendar, on the 60th day
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);
//Set the "90 Day Revision"
DayNextRev = addDays(Day1Date,89);
sheet.getRange(lastRow, getColIndexByName("90 Day Revision")).setValue(DayNextRev);
var Day90 = "90 Day Revision is Scheduled on " + DayNextRev.toDateString()
// Creates an all-day event with email reminder for calendar, on the 90th day
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);
//Record the fact that calendar is set, for this row
sheet.getRange(lastRow, getColIndexByName("Calenadar Set ?")).setValue("yes");
//Obtain the current active users email address to email the revision schedule
var userEmail = Session.getActiveUser().getEmail();
//send the email of the summary revision schedule
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.
No comments:
Post a Comment