Every. Single. Year. For 15 years. I sit down with a new lesson plan book and my old lesson plan book and start copying.
Not this year. This year our school is under construction and everything is in boxes. There are no new lesson plan books. Necessity is the mother of invention I guess. Time for something different. I spent a bit of time this morning figuring out conditional formatting in Google Sheets and came up with something that I think will work for me this year.
Here's the link.
Not this year. This year our school is under construction and everything is in boxes. There are no new lesson plan books. Necessity is the mother of invention I guess. Time for something different. I spent a bit of time this morning figuring out conditional formatting in Google Sheets and came up with something that I think will work for me this year.
Here's the link.
Once you open the document, go to 'File', then 'make a copy' and you're ready to rock-and-roll.
Adding and Deleting Individual Cells.
I also strongly suggest installing this Google Sheets Add On. It lets you add or delete a single cell in Sheets and gives you options for moving the affected columns or rows. No more arrows going every which way in my plan book!
Please know, if you add in a cell it will mess with the conditional formatting. It splits up the current conditional formatting into three separate sections. To fix this, select the column heading with the messed up formatting, go to Format then Conditional Formatting. On the right-hand side of the page you should see multiple. Find the option that starts with the column number and a 1, for example C1: ... Delete every other formula EXCEPT for the one starting with C1. Now edit the C1 formula so the range becomes C1:C1000--meaning delete everything between the C1 and the C1000. It's not ideal, but it's still easier than erasing pages in a lesson plan book.
Multiple lines in one cell.
In order to get multiple lines in the same cell: Alt+Enter (this works on Chrome at least).
Adding and Deleting Individual Cells.
I also strongly suggest installing this Google Sheets Add On. It lets you add or delete a single cell in Sheets and gives you options for moving the affected columns or rows. No more arrows going every which way in my plan book!
Please know, if you add in a cell it will mess with the conditional formatting. It splits up the current conditional formatting into three separate sections. To fix this, select the column heading with the messed up formatting, go to Format then Conditional Formatting. On the right-hand side of the page you should see multiple. Find the option that starts with the column number and a 1, for example C1: ... Delete every other formula EXCEPT for the one starting with C1. Now edit the C1 formula so the range becomes C1:C1000--meaning delete everything between the C1 and the C1000. It's not ideal, but it's still easier than erasing pages in a lesson plan book.
Multiple lines in one cell.
In order to get multiple lines in the same cell: Alt+Enter (this works on Chrome at least).
Jump to Today's Date.
If you want to be a power user and be able to jump to today's date in the doc, here's a script to do just that.
While logged into Google Sheets. Go to Tools -> Script Editor
Copy and past the following code into the script editor:
---DO NOT PASTE THIS LINE----
function onOpen() {
var menu = [{name: "Jump to today's date", functionName: "jumpToDate"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
jumpToDate();
}
function jumpToDate() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getRange("A:A");
var values = range.getValues();
var day = 24*3600*1000;
var today = parseInt((new Date().setHours(0,0,0,0))/day);
var ssdate;
for (var i=0; i<values.length; i++) {
try {
ssdate = values[i][0].getTime()/day;
}
catch(e) {
}
if (ssdate && Math.floor(ssdate) == today) {
sheet.setActiveRange(range.offset(i,0,1,1));
break;
}
}
}
-----DO NOT PASTE THIS LINE-----
Name your script something descriptive. Hit save. That's it, you're done.
When you refresh the Google Sheet, there will be a "custom" menu after the help menu. In the custom menu, you will find the option "Jump to today's date".
If you want to be a power user and be able to jump to today's date in the doc, here's a script to do just that.
While logged into Google Sheets. Go to Tools -> Script Editor
Copy and past the following code into the script editor:
---DO NOT PASTE THIS LINE----
function onOpen() {
var menu = [{name: "Jump to today's date", functionName: "jumpToDate"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
jumpToDate();
}
function jumpToDate() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getRange("A:A");
var values = range.getValues();
var day = 24*3600*1000;
var today = parseInt((new Date().setHours(0,0,0,0))/day);
var ssdate;
for (var i=0; i<values.length; i++) {
try {
ssdate = values[i][0].getTime()/day;
}
catch(e) {
}
if (ssdate && Math.floor(ssdate) == today) {
sheet.setActiveRange(range.offset(i,0,1,1));
break;
}
}
}
-----DO NOT PASTE THIS LINE-----
Name your script something descriptive. Hit save. That's it, you're done.
When you refresh the Google Sheet, there will be a "custom" menu after the help menu. In the custom menu, you will find the option "Jump to today's date".
If you make any improvements, let me know in the comments so I can use them.
RSS Feed
