I run a small guitar studio with three teachers. We currently use google Calendar to keep track of our lessons and google docs to keep track of our payments. We are looking for someone to setup a few spreadsheets and app scripts to pull information from the Calendar into a weekly report spreadsheet, where each teacher can view the lessons they have coming up this week, and as they occur record payments, lesson notes, and other data. That data then needs to be collected into a monthly ledger spreadsheet to keep track of the payments, and also sent to each student's individual sheet keeping track of their lesson notes and payments also. Finally we need a running tally of where someone is in their lessons to be sent back to the calendar where we can reference it easily. this will be a number 1/2, 3/4 based on which lesson of their prepaid block they are on.
I believe all this to be possible using an appscript to pull data from the calendar to a spreadsheet, then using importrange etc. to send it around to other spreadsheets, and then finally another appscript to send it back to the calendar.
So we would like to have the following files:
Weekly Reports for each teacher collecting: Student Name, Time, Date, Notes, Attended, Length, Block, Paid, Method, Payment Notes. The names, times and dates should be populated automatically from the calendar, the teacher then fills out the rest of the data.
Student Database with a file for each student containing: Name, Address, Phone, Email, Birthdate and then a running list of lesson notes and a payment history (all of which can be compiled from the weekly reports).
Payment Ledger containing: Name of Payee, Name of Student, Date, Amount, Method, Payment Notes, Name of Teacher its coming from. This too should be gathered from the weekly reports.
When people pay for their block of 4 lessons, we need the system to keep track of which lesson of the 4 they're on and push it back to the calendar for easy reference, so that past events show up as johnny 3/4 etc.
let me know if you have any questions.