I do not hate Google Sheets. Perhaps you have used it before. It’s rather similar to Microsoft Excel – except that I do not hate it – I hate Excel 🙂

One of the features of Google is that, besides the robust set of formulas it comes with, with a little JavaScript, you can really extend it to be like your own app. You can even add your own formulas if they don’t exist yet. Since I do not  hate Google Sheets – and I do hate Microsoft Excel – I have decided to share with you some things you can do with Google Sheets.

“But Yaakov,” you ask, “can’t you also extend Excel with VB script or C#?”

Shut up! I hate Excel! I hate VBA script! I hate rather dislike C#!

Now, in my totally contrived and imaginary scenario, let’s imagine you are trying to learn Meseches Nazir for Moshe Noach ben Shlomo HaCohen’s yahrzeit, or something like that. Let’s image that this Yahrzeit falls out on March 3rd this year. You would like to:

  1. See how many days you have for studying.
  2. See how much you would have to study each day to finish on time.
  3. Have that calculation update if you miss a day.
  4. Set a schedule of what you will learn each day to finish on time.
  5. Include a schedule for review – where you review what you’ve learnt the next day, as well as next week.

Then imagine you would like to do stuff like sync this with your calendar, or send your self an email (from your Gmail account) to remind you what you’re supposed to be studying that day.

“Well gosh,” you say, “that would be a cool way to use Google Sheets! Are you gonna show me all that?”

Well, maybe, at least till I get bored of it.


Of course, first, you would make a new sheet by going to the Google Sheets homepage and making a new sheet. Then you would name the sheet something appropriate, like “Nazir Learning Plan”.

 

Next, you would probably want to find out how many days you have to study the given material. Nazir has 65 blatt (2 sided pages) so you will divide whatever is left of Nazir but the amount of days left. This is rather simple to do and doesn’t require any scripting.

In the first row make 5 column headers:

  1. Today’s Date
  2. Blatt in Nazir
  3. Day of Yahrzeit
  4. Days Left
  5. Blatt Per Day

Select the day under “Today’s Date” and type “=TODAY()”. This cell will now always reflect the current date whenever you open the Google Sheet.

There are 65 blatt in Nazir, so under “Blatt in Nazir” put 65 the yahrzeit comes out on March 3rd, so under Day of Yahrzeit put 3/3/2018 and then for days left put =C2-A2.

Now to figure out how many blatt you have to do a day, in the cell under “Blatt Per Day”, simply put =B2/D2. This will reveal that you must do a little more than 1/2 a blatt a day to finish on time. Of course, so far we haven’t done anything particularly note worthy. We’ll need to be able to get these numbers and store them as variables in order to generate a schedule as well as have this work with other Google services, like Calender or Gmail. Now is where the magic starts.

The documentation for all the stuff we’re going to do is available in the Google App Scripts documentation and reference material.

First, we’ll show that we can access content in our spreadsheet to be used as variables for our scheduling program.

To get started, we’ll open the “Tools” menu and select “Script editor”.

This will take us to a brand new script – here:

Let’s name our project “Learning Schedule”

and name our function something that makes sense – like “makeSchedule”.

Now lets make a variable that tells Google we want to interact with the Google Sheets API – according to the Google Docs1, this is “SpreadsheetApp” (pretty intuitive) let’s store this in a variable2 we’ll name “app”. Within the curly braces include:

var app = SpreadsheetApp;

Then to get the active spreadsheet (the spreadsheet we’re using) we add the method “getActiveSpreadsheet()” and to get the specific sheet we’re using (a spreadsheet can have multiple sheets) we add the “getActiveSheet()” method – since we’ll probably be doing this quite a bit, let’s also store this in a variable which we’ll call “activeSheet”:

var activeSheet = app.getActiveSpreadsheet().getActiveSheet();

First let’s run the app by pressing the play triangle, then give it permission to run for your account.

Now, let’s store the number of blatt in Nazir in a variable called “blattInNazir” by getting the value of the number in the “B2” range on the Spreadsheet. Well do this like this:

var blattInNazir = activeSheet.getRange("B2").getValue();

This will store the value in cell “B2”. Now, to see that we’ve got it, let’s log the variable to the Google Scripts logger – like this:

Logger.log("There are " + blattInNazir + " blatt in Nazir");

Now run the app by pressing the play button and then go to the “View” menu and select “Logs”:

and voila! The logs tell you that there are, indeed, 65 blatt in Nazir!! Hooray! Now pat yourself on the back and next time, we’ll do something useful with this. 😉

Follow Dew of Your Youth on Social Media!
Liked it? Take a second to support DewofyourYouth on Patreon!
  1. Everything about the Google API in this little tutorial comes from there.
  2. If you’re unfamiliar with JavaScript, “var” is the keyword for variable assignments.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.