I was recently approached by a teacher who wanted to display her markbook on a Google Site, but she only wanted each student to see only their data. Essentially achieving the same as you can with Engrade or Edmodo. I asked around and the G+ community came up with the goods suggesting Google Sites and the Gadget Awesome Tables.
Awesome tables, is a gadget which allows you to display information from a spreadsheet in a far more powerful way than Google Sites & Drive will ever allow you to achieve.
Here is a step by step guide to creating a Grade Book using Google Docs & Awesome tables:
Awesome Tables Grade Book
Creating a Gradebook in Google Sites
You must be using a Google Apps for Education Domain ( I believe that this will work without, but I haven’t tested it) and you need to have access to your students e-mail addresses. In addition to this you also need:
- A Markbook created in Google Sheets
- A Website created in Google Drive
Setting up the Sheet:
- Create a regular markbook
- Add a column to the left of column A
- Title the column ‘Username’ (in cell A1)
- In Cell A2 types ‘Permissions – Hidden’
- Next to each student type their username (usually their e-mail address)
- In any cells that you want all students to be able to see type ‘=join(“,”,A5:A27)’ where A5:A27 is the range that contains the usernames of the students that you want to be able see this row
- Ensure that the data type for column A is set to Plain Text (Format > Number > Plain Text)
Your sheet should now look something like this:
Your sheet is now set up and ready to go…
Setting up the Proxy Script:
The proxy script is required to pull data from your sheet without giving users permission to view it. You don’t need any scripting experience, simply follow these instructions to deploy the script as a web app on your domain.
- Click here to open up the required script,
- Make a copy of this script (File > Make a Copy)
- Click Publish > Deploy as Web App
- In the pop up type ‘Version 1’ in the version number box
- Click ‘Save New Version’
- In the Box ‘Who has access to this app’ choose ‘Anyone within xxxxxxx domain’
- Click ‘Deploy’
- In the box that now appears, ensure that you copy the URL that you are given (save it somewhere!)
- Click on ‘Ok’
- Before closing this tab, click on the ‘Play Button’ in the toolbar (next to the Spider)
- Click ‘Continue’
- Click ‘Accept’
- An error message will come up in red at the top of the screen, simply click ‘dismiss’ and close the tab
Your proxy web app is now running and will serve as a connection between your website and the sheet, fetching just the required information for that user.
Setting up the Site
In your Google Site:
- On the page that you want to add the gradebook click on the ‘Pencil’
- Click on Insert > More Gadgets
- In the search box type ‘Awesome’ and click Search
- The following gadget will appear, click on the gadget
- Click on ‘Select’
- In the box that appears, enter the URL of the Google Sheet that contains your markbook, ensure that you have the correct sheet name, and that the range that you want to use is selcted. You don’t need to include a number after the second row. In my example I have A1:I as in my sheet I have data from A1 to Column I. If you include a column which is blank, an error will appear.
- Now click on ‘Advanced Parameters’ and paste into the Apps Script Proxy URL Box, the URL that you copied when you created the web app earlier.
- Click on the green ‘Save’ button at the top of the box
- You can now choose, the height, the width (and if you scroll down) if you want a title, border etc..
- Click on ‘OK’
- Click on the ‘Save’ button in the top right hand corner
- Your table should now appear, showing the rows that you have permission to view
You can see here, that the only row that is shown is mine, however in the screenshot of the sheet in the first section you can see that there are multiple users in the sheet.