Creating a Grade System with Google Sites

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

 

Pre-Requisites:

 

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:

 

  1. A Markbook created in Google Sheets
  2. A Website created in Google Drive

 

Setting up the Sheet:

 

  1. Create a regular markbook
  2. Add a column to the left of column A
  3. Title the column ‘Username’ (in cell A1)
  4. In Cell A2 types ‘Permissions – Hidden’
  5. Next to each student type their username (usually their e-mail address)
  6. 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
  7. Ensure that the data type for column A is set to Plain Text (Format > Number > Plain Text)

Screen Shot 2014-10-15 at 5.19.32 PM

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.

 

  1. Click here to open up the required script,
  2. Make a copy of this script (File > Make a Copy)
  3. Click Publish > Deploy as Web App
  4. In the pop up type ‘Version 1’ in the version number box
  5. Click ‘Save New Version’
  6. In the Box ‘Who has access to this app’ choose ‘Anyone within xxxxxxx domain’
  7. Click ‘Deploy’
  8. Screen Shot 2014-10-15 at 5.27.57 PM
  9. In the box that now appears, ensure that you copy the URL that you are given (save it somewhere!)
  10. Click on ‘Ok’
  11. Before closing this tab, click on the ‘Play Button’ in the toolbar (next to the Spider)
  12. Screen Shot 2014-10-15 at 5.33.19 PM
  13. Click ‘Continue’
  14. Screen Shot 2014-10-15 at 5.35.08 PM
  15. Click ‘Accept’
  16. Screen Shot 2014-10-15 at 5.35.16 PM
  17. 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:

 

  1. On the page that you want to add the gradebook click on the ‘Pencil’
  2. Screen Shot 2014-10-15 at 5.40.01 PM
  3. Click on Insert > More Gadgets
  4. In the search box type ‘Awesome’ and click Search
  5. The following gadget will appear, click on the gadget
  6. Screen Shot 2014-10-15 at 5.40.29 PM
  7. Click on ‘Select’
  8. 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.
  9. Screen Shot 2014-10-15 at 5.45.44 PM
  10. 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.
  11. Screen Shot 2014-10-15 at 5.45.14 PM
  12. Click on the green ‘Save’ button at the top of the box
  13. You can now choose, the height, the width (and if you scroll down) if you want a title, border etc..
  14. Click on ‘OK’
  15. Click on the ‘Save’ button in the top right hand corner
  16. Your table should now appear, showing the rows that you have permission to view

 Screen Shot 2014-10-15 at 5.51.20 PM

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.

Advertisements

5 thoughts on “Creating a Grade System with Google Sites

  1. Steve Peters says:

    Great post. Struggling with one element of setting up the sheet. You state “In any cells that you want all students to be able to see type ‘=join(“,”,A5:A27)’”. How can I do this? Surely it will replace the data I have stored? Maybe i’m reading this wrong. Any help is much appreciated.

    • crippit says:

      You are going to type this in the permissions column where you want all students to be in that row. It essentially just sticks all the cells together and separates them with a comma.

  2. bpugh2 says:

    I have been using apps script proxy for use with the Awesome Table gadget for a few months on three sheets where student responses to a novel are collected (14 columns, approx. 560 rows of data thus far). Beginning this week, Awesome Table is returning data to students erratically: some see their own responses, others get none, not even an error.

    I have the web app set to execute app as me and can be used by anyone within our domain.

    Any idea what may be happening? Thank you (any my students will, too!).

    Brian.

    Note: I’m sorry, but I was distracted by our two boys momentarily and posted this comment in the wrong place.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s