Advanced Version
This is the Advanced Version. This spreadsheet requires category setup (application, com, etc) this would replace the need for another gradebook/markbook. Using this template, you will create a form with multiple assessment criteria and have those assessments summarized in the Google Sheet. Decide on your assessment headings and associate those headings with particular assessment categories using some of the formulas explained below. The default scale for assessment is 5,4,3,2,1,0,no mark. 5 corresponds to a 4+ (90%-100%) and 4 is for a mid-to low level 4 (80%-90%). Then 3,2,1 correspond to those particular levels.
Click --> Copy Advanced Sheet to create your sheet. Then follow the instructions below:
The copied Spreadsheet will open up, the first thing you need to do is RENAME it and MOVE TO FOLDER using the File menu at top ORGANIZE. **Tip create a new folder when you select move to folder for each course**
Next add your class list to combined name (first name last name) and student number to the “classlist” tab. You can get this list using a simple “user defined” export from Markbook or SIS then copy and paste into the appropriate columns. Make sure you paste combined name and student numbers into their corresponding columns. Then column C will combine those.
NEXT GO TO STUDENT RESULTS TAB: You will then want to decide on the “Headings” you are going to assess. There are 6 “Headings” by default if you wish to add more, you will need to make the appropriate adjustments to the “Student Results” sheet (ie. The purple section needs to be expanded. Just Drag the formulas across to fill right)
For advanced sheet: use a simple =”cell reference” formula (eg. =B11) to assign heading summaries to specific Categories. For example if Heading 1 corresponds to Application input =B11 into the ‘Percent’ cell for Application. If you have multiple headings corresponding to one Assessment category use the “weight” cells and use a Sumproduct formula (link to info on sumproduct) or mode formula.
NEXT GO TO SETUP TAB: Press CREATE FORM wait till finished → then CLICK ACTIVATE. Your form is created.
link: Youtube
This video is slightly outdated the setup and sharing has become easier with new scripts, the video will be updated shortly but gives you a good view of the setup procees
ONLY USE THESE BUTTONS FOR INITIAL SETUP: if you wish to add to your form manually do it from here on out.
Find your form by using the top menu “FORM” → “edit form”. RENAME and MOVE to same FOLDER as sheet Take a look at the Form and make any custom adjustments you wish (page breaks etc)
Once you have your form complete, either email yourself the link to the form or create a shortened url using “bit.ly”, “tinyurl.com” or some other custom link shortener so you can easily access the form and create a bookmark/add to homepage on your phone/tablet.
Start assessing your students and watch the data stream in. On the Students Results tab select a student from the drop down box in cell A1 (note that this sheet is best used on a desktop/laptop, as the mobile version is limited in viewing) to see results summary.
GO BACK TO SPREADSHEET STUDENT RESULTS TAB: Click on the green cells A34 and B34 and press “ENTER” this will “wake up” the formulas and data will stream in as you change the student from the top (A1) drop down arrow.
If you would like to have individual tabs for each student INSTEAD of using the drop down menu in the “student results” tab. GO TO “createTabs” and click on the “create tabs” pic. The advantage to using these tabs is you can manually adjust the levels for each student (note changes on this tab are NOT reflected on the ‘students results’ tab).
If you find a formula is not calculating you may need to simply click on the cell then press “Enter”, this tends to “wake up” formulas. As this sheet is still new if you find something is not quite working the way it should please send me an email (dawiwchar@gmail.com) explaining what you found.
The Following will Explain the process of sharing live assessment data with your students
UPDATE: as of Dec. 20 the sharing of tabs has been scripted into the template spreadsheets. After creating a tab for each student using the “createTabs” tab use the “shareTabs” tab to share the tabs. Ensure that email addresses are entered/correct then click the shareTabs button. Then click on each link to “ALLOW ACCESS” for the formula in cell A2 of the new sheets. The spreadsheets will show up in your students “shared with me” folder in their drive.
This video is slightly outdated the setup and sharing has become easier with new scripts, the video will be updated shortly but gives you a good view of the setup procees