January 5, 2016
Click here for the script (or read its exciting story bellow first).
I wrote a script to help me with invoicing my students. This is a process that takes place every last day of each month. Since I charge a fee per lesson (as opposed to charging a fee per month independent of the number of lessons) my workflow consisted of going through a spreadsheet with all the lessons, fish out the ones that correspond to each student, fill in the email message template with the correct information (which includes the name of the student, the lessons they took that month, the total they should pay, the name of the current month and the name of the next month) and send the message to the correct email address. This workflow resulted in a invoicing process which took too long - sometimes having me deferring it for some days - and very susceptible to human error - which was concerning to me in my position as the human).
Due to the aforementioned problems, I have been dabbling on the idea of writing a script that could take care of some of the steps of this workflow for me. However I wanted something that I could use even when out of the office, therefore a main version of this script had to live on my iPad. This highly reduced my possibilities for script creation. Then I run into a problem I inevitably run into when trying to automate many workflows: how to access the data. In this case the relevant data was living in a spreadsheet in my Google Drive. This is not half as bad as it could be if I had the data in, let's say, Numbers, since services like IFTTT can communicate with Google Sheets. However even this being the case, it is still cumbersome and limiting doing so.
But the problem was solved by moving from spreadsheets altogether. Curiously this change was not forced by the will to automate the student invoicing, but to solve another issue I was facing concerning communication between the students and I regarding canceled and re-scheduled lessons. Some months ago I had created a Google Calendar for each student, named after the student and containing all the lessons they took and the ones already scheduled. This turned out to be the missing piece in being able to automate the workflow.
After having pieced all the necessary infrastructure to correctly script this workflow and based on my requirements, the answer to where should this script live seemed obvious: it should be a Workflow. You can find a version of the final result here. This script might be useful for you as is if you:
- give lessons or tutor people;
- charge a fixed fee per lesson or tutoring session;
- have or are willing to create a calendar named after each student, up to date regarding past lessons1;
- invoice the students monthly at the end of each month.
Of course if you are proficient in scripting with Workflow then you can tweak this script to adjust it to your needs. Also, you should operate in the Apple ecosystem, or have at least one iOS device. I will walk you through the steps of how to adjust this script.
- Define the fee you charge per lesson (variable
priceLessons, set to 20 by default).
Scroll all the way down and you will find a text field. Here you can write your own text. If you decide to write your own text, be very careful about using the variables set throughout the script. These are:
student- the name of the student (and the calendar);
currentMonth- the month to which the invoice concerns;
lessons- a list of the lessons the student took with the format
day of the week (short)
day of the month(e.g.: "Tue21");
valueLessons- the total value to be paid by the student, it equals
numberLessons(being the latter not used on my text);
- `nextMonth - the month after the one the invoice concerns (I use this variable to tell the students until which date they should pay).
On the same text field you can change the symbol of the currency.
- On the last block you can change the default subject.
For the sake of simplicity, I named the calendars after the student's first name (I'm glad to have moved to a country where different people actually have different first names). You can name them anything else, for example using the student's first and last name. However you must not forget your naming convention otherwise the script might not work in the best scenario, or you might send an invoice to the wrong person. To help me with this I've set up a checklist on my task manager with the names of all students written in the way I titled the calendars.
The usage of the script is straightforward, but anyway here is a user's manual:
- run the script,
- input the name of the student when requested,
- after the email message editor appears, verify if the text is correct (this part is actually optional and entirely up to your trust on the machines) and input the student's email on the corresponding field.
If you have any questions about this workflow shoot them at me on twitter.
I higly recommend implementing this system, since it greatly simplifies the process of logging cancelations. Also it prevents the abuse of the phrase "I told you before". ↩