Blog Feed

TL:DR My schools want to keep track of the lessons I’m teaching, my company wants the schools to fax them schedules. Trapped in the middle, I came up with an unofficial solution that makes me happy, but due to technical reasons I don’t use it, but you can take a look if you want.

This doesn’t seem like something you should have to think about, right? Your company and the school/BOE should be figuring this out and letting you know. But I got tired of being asked to be the middle man between my schools and my company. So I made an online schedule using Google sheets. Now the teachers could adjust my schedule weeks in advance. And my coordinator could see this in real time. If we used this then I’d no longer be being asked to tell the teachers to fax my schedule to my coordinator so she can email me a picture of the fax… But I was told that this would not work for the current system. So officially, I don’t use this myself.

I’ve removed all the Japanese from this schedule, so that it is potentially usable for any teacher in any country. There are many notes littered around the schedule to help guide you in its use. You can remove the notes whenever by right clicking the cell. First here is the schedule: ALT Schedule.

Open it, and then save a copy to your own drive. You will need a google account to do this, but using the schedule itself won’t require any account or sign in. Just adjust sharing settings so anyone with a link can edit.

Main points

• The schedule is customizeable.
• The schedule is stored on the cloud and can be viewed and/or edited by multiple parties.

How to customize

First step. Go to the Data tab on the bottom left hand corner.

On the Data sheet enter in your Name, your school names, and the classes you normally attend. This will adjust your schedule by putting your name at the very top, the drop down menus in row 6 will have all of your schools, and your schedule drop down menus will be your commonly used classes. If you have less classes than I provided just select the cells with the classes you won’t ever use and press backspace on your keyboard. If you have a special needs class or something you often do like a conversation class, test prep, potion mixing, or something then manually input it and it will be added to your drop down menus. If your classes use a different ordering system, like Grade 1 Class A, Class B, Class C, etc. etc. etc. then you will have to clear the table and put in 1-A, 1-B, 1-C. The bottom line is whatever you put into the sheet in the classes menu will then appear on your schedule in the drop down menus.

Keep in mind that the drop down menu items are set to appear in horizontal order on the Data sheet. I know that sounds confusing, but what it means is if you were to write your classes on the Data sheet like this:

1-1	2-1	3-1	4-1
1-2	2-2	3-2	4-2
1-3	2-3	3-3	4-3

Then the drop down menues would look like this:

• 1-1
• 2-1
• 3-1
• 4-1
• 1-2
• 2-2
• 3-2
• 4-2
• 1-3
• 2-3
• 3-3
• 4-3

If that’s how you would like the drop down to appear you can customize your classes like that, but it doesn’t make sense to me.

The cells under the drop down menus on your schedules are blank. You can write short memos here about the class contents. For more in-depth information, if you need to, can be written as a note or comment by right clicking the cell you wish to attach a note or comment to. Notes will pop up when your cursor hovers over the cell. Comments are unique because they will alert anyone who has a google account connected to the schedule and will also add a number to the sheet tab to denote how many comment threads are on the sheet. Comments can also be used to carry out conversations between users.

On your schedule you can merge and unmerge you cells in row 6 depending on how your schedule is arranged. For example, I go to one school per week, so I merge the 5 cells of row 6 like this.

The other ALT in my town has 3 schools that he goes to every week. School 1 he goes to on Monday and Tuesday, School 2 he goes to on Wednesday and Thursday, and School 3 he goes on Friday. He could merge the row 6 cells under Monday and Tuesday together, and do the same to the cells under Wednesday and Thursday.

Customize it to make it useful. If you have a very random schedule that varies from week-to-week just leave all the cells unmerged and fill in each day individually, it doesn’t take much time.

After you finish customizing your Data sheet I suggest you hide it.

Hidden sheets can still be accessed, but it’s not something you want to change often.

I hide my weekly schedule sheets after the week is over. It keeps the current week as the top sheet, and reduces the number of tabs I have to comb through. But, if i want to look back and previous weeks I still can.

Next Week

To make a new sheet for your next week of work is simple. Right click the tab of the sheet you wish to duplicate and the option pops up.

This will create an exact copy.

Switch to the copy, and change the date in Cell C3.

This will automatically update all dates and days on the the sheet. Double click the Copy of “ “ sheet tab at the bottom to rename it to the new start date. Select all the Cells and hit backspace on the keyboard to clear the classes and schools if you want.

Push My Buttons

I did make some buttons at the top of the schedule, but to use them you’ll have to allow the Google scripts access to your google account. It is the same as when using Microsoft Excel and enabling macros. The reason that Google warms you that that script is potentially dangerous is because they do not have control over what scripts people write. It’s like a company selling pens, but putting warnings on the pens that their company isn’t responsible for what the pens are used to write or draw. That being said running unknown scripts on your computer is a lot more dangerous for your computer than a pen someone’s using to write shitty Twilight fanfic. So I have actually removed all the script from this schedule. If you want to use these handy buttons then you’ll have to implement it yourself. Either way it will ask you for permission to run the scripts, but this way you can actually see what you’re running and know that I’m not some bored hacker trying to steal all your cat meme ideas.

In Cell J2 I have stashed this script:

function NewSheet() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.duplicateActiveSheet();
  spreadsheet.getRange('C6:I22').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  
  };

function ClearSheet() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C7:I22').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};

These are the two functions you need for both buttons. One is named NewSheet and the other is named ClearSheet. In the merged Cells G3-I3 I have detailed instructions on how to copy and paste this script to use it.

If you prefer you can record your own macros though. These instructions are in Cell J3.

These two buttons already have the scripts assigned to them under NewSheet and ClearSheet. So copy pasting or recording macros under these names will make the buttons work automatically. With a single click of the mouse you can clear all of your scheduled classes or make a new blank sheet for next week. But if you don’t want to use these buttoms right click to select and delete them.

Sharing the Schedule

So who you share your schedule with is up to you. Because I made this and my company did not, technically it’s not company policy. But both my coordinator and my teachers were initially happy with the conceptMost teachers are tech savy enough to appreciate the simplicity and ease of use. Honestly, they’re just relieved to not have to print out the schedule and fax it every week. Elementary schools are different because schedules are more rigid and set, but JHS the class schedules tend to change randomly and often. I don’t get a finalized schedule until mid week sometimes. And daily changes are the norm. I keep a written schedule on my desk for all the teachers to see, but the using an online schedule would let me update changes in real time so my company could see. You could even put a shortcut to the schedule on a school computer to make it easy for the teacher to access.

You can email the link to the teacher of course. I usually shorten the link with TinyUrl or something. But if internet access is restricted then emails might not work. If this is the case I shorten the link with TinyUrl. Then just type the link in the browser. Alternatively, you could type the link on a Word doc, save it to USB. Then open the doc on the computer and Ctrl+click the link to open your schedule.

To make the shortcut on the desktop just click and drag the icon in the URL bar to the desktop and then rename it.

---

If you can help me with my Java. I would like for the NewSheet script to also advance the date and rename the sheet. I cannot figure out how to make it advance the date though… I have gotten the sheet auto rename to work though.