Last month I had a problem. The problem was the school. Not school in general but because of the coronavirus situation, my school was closed until last month. And the teachers decided to give us a lot of tests. I was looking for some simple all-in-one solution for keeping all the tests in one place. I wanted to have all the tests in one place and let other students from my class see the list of tests, add tests, and have a clear overview of them.
Important: This is not a tutorial rather my thinking process while creating this small project with few descriptions.
My schools use Google G Suit and every student has a school Google Account. The first solution I thought of was to use this system which is already built by top developers in Google and don't build something like this all by myself. This solution is also the easiest for my classmates because they don't need to learn using any other platform that is not that user friendly.
I´ve decided to carry on with the Google G suit platform and use a Google Spreadsheet table. So I´ve created a table with subjects, tests for that week in a subject, date of the test, and other info. Using sharing I was able to share this table with everyone in my class. But why do I write this article? It is not about creating a spreadsheet but about working with Google Apps Script.
To be honest, looking into a table every day is not the best user experience even tho my "customers" are just my classmates. That´s why I decided to send an email to all my classmates every Sunday just before the new school week starts. This way they have all the info in their inboxes just before the week starts and they can fully prepare for the upcoming tests.
The process is pretty simple:
- Create a Google Spreadsheet table and share it with everyone
- Create a Google Apps Script which is using data from our table
- Get data from our table, process it, save it
- Automatically send emails to everyone with info from the table
So basically every student gets info in their inboxes about what tests are upcoming for them. Also, every student under the school domain can edit the table and tests I don´t know about.
Making a new table is easy, the process has no difference for a developer or a regular user. Creating a script is also simple.
All I needed to do is click Tools > Script Editor
A new window opens with blank function.
We can create our own function with everything we need and use infrastructure that is already built and being used by millions of users worldwide. This way we can save time, we do not have to create our own infrastructure.
To get data from our table, we first need to set our table by specifying its name or select the active sheet. For example, emails that want our "newsletter" is in the table one by one after each other. That means we have some starting row and a number of rows. In this case number of rows where an email is specified. For example, if we have 10 email addresses starting on row 1, the starting row would be row 1 and the number of rows would be 10, etc. This goes not only for emails but also for other values like the subject name, test description, test date, and other values. We set rows, starting rows for each of these values.
getRange() method is for specifying row and column using a number, For example, column A is 1, etc.
Later in the code, I decided to write a FOR (var I in...) cycle which goes through every row of data and saves it into a multi-dimensional array.
Each category (like subject name or test name ) is one single array in this general array called Testy (tests in Czech).
To send an email we can again use the already made-up method. The message var is just a bung of text I want to have in my email + values from our big multi-dimensional email. Each time I just pick a small piece of information I want to have in the email right now.
To send email automatically (for example every Sunday in my case) I just created a new trigger that automatically runs the function at a specified time. Creating a trigger is simple since all we have to do are a few clicks because this IDE has a graphical interface for creating triggers. This saves a lot of time because we don't have to implement this functionality all by ourselves.
I really enjoyed creating my function on Google´s platform and I really look forward to creating more!
If you find this usefull I would be greatful for your like on this post or by following my profile. Its is free and would make me very happy!