This one is going out to Jason Howse (@MrHExperience). During the #XPLAP conversation on Tuesday Jason asked for ideas on a Back to the Future type of theme. I suggested the fading in and out effect which in my mind could be accomplished with the Battle Damage VLookUp system. The conversation got stuck in my head so I wanted to see how I could make it work.
Download and Make a Copy of the BTTF Powley Fade Effect (Student Template)
Download and Make a Copy of the Teacher’s XP Level Page
How to make Back to the Future Fade with Google Sheets
Step 1 – Making the Transparency.
To do the fade effect we will need a background and a picture of the person with the background removed. Remove.bg is a pretty simple tool for removing backgrounds. You just need a picture with a face – like this one in the folder -and just drag it onto the remove.bg website.
It will take a second and then pop out a virtual “sticker” that can then be applied to any background. Save the image or cut and paste it into a Google Draw like I do here.
Once the “sticker is applied it looks like this:
Looks pretty good! If you ignore the lighting coming from the wrong direction but not bad for about 2 minutes worth of work!
Step 2 Create the Fade Effect Images
Once the Sticker is applied to the background the fade effect is created by adjusting the transparency level of the sticker. In this example I have created 10 levels of transparency in 10 seperate pictures. Start with 0% Transparency as the base picture. Then make a copy of the Google Draw. I named mine Powley Fade 0% and the copy is Powley Fade 10%. In the Powley Fade 10% (the copy) adjust the transparency level to 10%.
- Click on the sticker.
- Click on Format Options
3. Click on Adjustments and then move the Transparency slider to 10%. It is not possible to slightly see through the sticker (you can kind of see the rock ridge in my cheek).
4. Keep Making Copies and adjusting the transparency setting until the sticker is completely transparent.
Step 3 Publish the Images
In order to add the images into the Spreadsheet Data Table the images will need to be published within Google Draw. The added benefit is that when the images in Google Draw are changed they will automatically update in the spreadsheets. This also allows the images to fill into a cell rather than sit on top of cell which allows the image to adjust size according to the size of the cell.
This is pretty easy to accomplish. For each Google Draw image click on File and then Publish to the Web. There will be an option to create a link or embed code and clicking on the big blue Publish button will do both. For our purposes we need the published LINK.
Step 4 Create the Data Table in the Student’s Spreadsheet
This concept is modeled on a mashup of the Battle Damage in my Dreadsheets and the Student’s Personal Dashboards. This means that there will be a central spreadsheet that is only accessed by the teacher which is a hub for all of the student’s XP. Each student then gets a personal dashboard that will track their individual XP count along with an updated “Fade” Image based on the data table we are about to set up.
To make the Data Table start by creating a Template Spreadsheet. Later this template will be the basis for all students’ dashboards but this will be the home of the basic framework of the fade system.
The image below skips a couple of steps.
- The Spreadsheet is titled BTTF Powley Fade Effect
- The Sheet has two tabs. The first is labeled Family Photo. The second is Table.
- The Table is created in Cells A1 through C11. Column A represents the XP value, Column B will contain the percentage of fade images, and Column C will have the URL address for the Published Google Draw.
The first thing to do is determine the XP Values that will make up column A. As shown in the picture, the values should be written to increase as they go down the spreadsheet. These numbers DO NOT have to be evenly distributed and they can have whatever range desired. In my class I have XP go up to 10,000XP. For this example the top XP Score is 100.
Second thing to do is cut and paste all of those published Google Draw links into the table. This table assumes that as the XP Count increases the student’s image will become more solid. So the Powley 0% Fade link will appear at the 100XP level where as the Powley 100% Fade would appear at the 0XP level.
Third is the formula that will appear in Column B. This is simply <=image(_)>. The space in the parenthesis refers to the cell with the Google Draw link that will be displayed. So in cell B11 the image above’s formula shows =image(C11) which will display the Google Draw link in C11. (I needed to add parenthesis to the formula in the picture above).
Here is what the final table looks like.
Step 5 Create the VLookup Function
The VLOOKUP will use the student’s XP count to lookup which image on the Data Table to display.
In this image I have increased the size of Cell A1 so the picture will have a larger display.
The formula is =vlookup(A3, Table!A1:C11,2). This formula is telling cell A1 to lookup the XP value located in cell A3. The formula indicates that the data table can be found on the tab Table (the ! tells the formula that Table is a tab) and that the data table covers the cells from A1 to C11. The formula will compare the XP value in A3 to find which numbers on the table the XP falls between then return the information from the appropriate row in the second column. The 2 at the end of the formula indicates that it is the 2nd column from the left’s value that should be returned. Since we have an image in the 2nd column the value returned will be the image!
Step 6 Linking up the Teacher’s XP sheet to the Student’s Personal Sheet
Did you notice the hidden column on the last image? This is what I was hiding.
The teacher’s XP spreadsheet URL address is located in Column B. This allows the formula in Cell A3 to be much simpler and will save a lot of time when making each student’s dashboard.
The formula linking the Teachers and Student pages is a simple importrange function. =importrange(B3,”B1″). The formula will go to the spreadsheet linked in cell B3 and return the value in cell B1. In this case cell B1 is Student 1’s XP information.
This Teacher’s XP Page can be much much much more complex than this if so desired but that’s another post. (Actually, its similar to my gradebook which I wrote about in previous posts). As you can see here though, Student 1’s XP is set at 15 which returns this image.
As the XP count grows the image becomes more opaque. Here you can see the student has jump to 50XP.
Step 7 Hiding and Protect the Sheets Data
I don’t want students tinkering around with the formulas. So I hide sheets, columns, and rows I don’t want them to access and use the Protect Range to make sure they can’t mess around with things.
First, Protect Range. This will prevent anyone without permission from changing information on the sheets. Since I don’t want anything changed by the students protecting the whole sheet is fine. It is possible to protect only certain cells, columns, or rows. This is handy for sheets that need students to add information – like a name or a dice roll.
- Right click on the Sheet OR click on the Tab carrot then select Protect Range.
2. This will bring up the Protected sheets & ranges options on the right side of the page. Click + Add a sheet or range.
3. In this case I am protecting the entire tab. Click on Sheet and a drop down menu will provide options for which tab to protect. Select “Table” for this tab and then set permissions. From here it is also possible to protect the other tabs so I protected “Family Photos” as well.
The page is now restricted to those that have permission to edit.
A little lock on the tab shows that the tab has protections on it.
I prefer to hide cells and tabs that I don’t want students to see. This is pretty straight forward. To hide a tab click on the Tab’s carrot (triangle) and select Hide Sheet.
Then students will only see the sheet you want them to see. The tab is still accessible by clicking on the three line button.
The process for hiding a column or row is basically the same. Highlight the desired column(s) or row(s), right click, and select Hide Column(s) or Hide Row(s).
AND THERE YOU GO!
Simple right!?
Once the template is in place it takes 15-20 minutes per student to make the fades and upload them into the table. Be sure to make a new copy of the template for each student, change the table images, and the “Family Photo” importrange function to show the correct student’s XP.
There might be a simpler method but this is what I know.
Download and Make a Copy of the BTTF Powley Fade Effect (Student Template)
Download and Make a Copy of the Teacher’s XP Level Page
Don’t forget to change the Teacher’s XP page URL address in the Fade Effect Template Cell B3! This also means that if you already have your own XP tracker spreadsheet that sheet can be used instead. Just change the URL link as well as the cell that shows the student’s XP.
Classroom Powerups 