# data[].attributes.body
10:00 – 11:00 am: Revenge of the Sheets – Learn to Google Sheets the Jedi Way

Presenter: Jesse Lubinsky

Use of Google Forms and Sheets in the Classroom

Spreadsheets: He admits they aren’t necessarily a sexy bit of software, or exactly user-friendly at first

Cells: Formulas are shown in upper left corner of header, not within the cell itself

-        To view formula, click on cell, read header

Level 1: Basic Tips and Tricks

Sorting

-        Data Tab

o   Rather than simply sort, choose Sort Range

§  This way, can avoid sorting your own header

o   Alternately: can incorporate “add another sort column”

§  Sort by a primary and then a secondary criteria!

Filtering

-        Using Data menu, click Filter button, or Filter Views…

o   Now, at top of each header row, there’s a drop-down arrow

§  Different values, can select or de-select criteria

§  Note: Generating new formulas in filter view is not advised in shared docs

-        Issue: Google documents are designed to be collaborative

o   If you’ve filtered or sorted, you’ve just impacted view for others

o   SOLUTION: Filter View creation – changes this to a private view

§  Under Data Menu, Filter Views, create a New Filter View

·        Outside of spreadsheet becomes dark (this makes it known to you that you’re in a special view)

·        Numbers will still change collaboratively, however – only View is changed, and only privately

o   NOW… to show to others, can Name your custom Filter View and save it

§  Others can now apply this filter and see the view you’ve set up

Data Validation

-        Allows us to restrict types/values of data that can be entered into a cell

-        Especially good for shared docs

o   Example: One cell or entire column must be numerical (“10” not “10 minutes”)

o   This function provides warning to user that incorrect information is “invalid” and forces correction

o   Can additionally define restrictions with other parameters (example: Only “Y” or “N” accepted, not “Yes” or “No”)

§  Can be done in Forms OR in Sheets

o   Can additionally choose valid email address style

§  And, in advanced formats, can even restrict to a valid email domain!

Conditional Formatting

-        Allows you to have text or background change color dynamically based on content

o   Example: “Failing” or “No-Mark” grades could be highlighted red with white text

o   Can do by specific colors, OR by gradient (shades of green, for example)

-        Conditional Formatting is accessed through RIGHT CLICK, Conditional Formatting

o   Or by Menu Tab “Format”

Protect a Range

-        Can apply permissions to individual cells or groups of cells within a sheet

o   RIGHT CLICK, Protect Range

o   Especially helpful if a collaborative sheet contains formulas that require restriction from students or others who could ruin the doc

Mapping Sheets

-        Takes list of addresses from Google Sheets and creates a Google Map image of those addresses!

-        This is an Add-On (must download)

-        Will only allow mapping on one criteria, however

-        Go to “Add-Ons” tab, choose Mapping Sheets

o   Can select criteria with filters as well

§  Any data classified or grouped can now be clicked on & off in resulting Map

o   Click “Build This” when ready

o   Can now view in Map

§  Can also click on/off by criteria as displayed on map

o   Can hover over any pinpoint on map – contains customized criteria you may have included within your Sheet

Save as Doc

-        Convert any Google Spreadsheet into a Doc for easy printing and improved readability

-        This is another “Add-On”

o    Name new doc, define heading locations, any custom settings

o   Can now easily print and use as a Doc

o   Once in Doc, can also customize by font, etc.

Mail Merge

-        For use with Gmail

o   Can track WHO opens messages (could be especially helpful in certain circumstances, such as tracking which students are working on a project)

QR Code Generator

-        Easily create directly from Google Sheet, save codes as one Doc or in PNG image files

-        Lovely tool for pre-populating a Google Form with Codes, THEN can (for example) set QR Codes for individual presenters at a Science Fair or History Day. Judges can scan QR Code of a presenter, and enter data accordingly

FLUBAROO

-        Helps quickly grade and analyze online assessments and assignments. Shows analytics and emails scores to students

-        Demo at bit.ly/rosevilleswquiz

-        Check Flubaroo Homepage, and check YouTube for Flubaroo walkthrough as well

-        Flubaroo generates a Google Sheets report, highlighting which STUDENTS struggles, and which QUESTIONS were the most troublesome

o   Pretty neat!

-        Within moments, students have scores and feedback!

Doctopus

-        Integrates well with Google Classroom (very little additional detail given)

Autocrat

-        Takes info from a Sheet, automatically populates a Doc or PDF File

-        Visit “New Visions” Homepage for Autocrat

Fusion Tables

-        Have your Data tell a Story

-        Example: Location of butterflies in given areas and conditions (incl. gender, varieties, etc.)

-        Can apply this to a map, but (unlike Mapping Sheets) can utilize a large number of data points!

Pivot Tables

-        Create quick summaries of data

-        Report Editor allows definition of filters by Row, Column, Values

-        This may be especially helpful with repeated test scores, such as the STAR reading examinations

ImportRange

-        Import range of cells from another Sheets file. Once users are granted permission, any editor on the destination spreadsheet to pull from the source spreadsheet. Allows a Master Spreadsheet, pull in info from a variety of feeder spreadsheets, and will continue to update Master Sheet in Real Time.

# data[].attributes.headline
Nice photo Jen!  Amy B here..thanks for sharing your learning!  

# data[].attributes.subject
Nice photo Jen!  Amy B here..thanks for sharing your learning!  

# data[].attributes.body
you can import a range of cells from another Google Sheets file. Once users are granted permission, any editor on the destination spreadsheet to pull from the source spreadsheet...

# data[].attributes.body
Here are some personal notes I took on the session I would love to share :)

https://docs.google.com/a/rocklinusd.org/document/d/1RpzepIfD_rGmWDAzJKUmjOwLL8CqPliWPSfl0dzBwkU/edit?usp=sharing

# data[].attributes.body
Wow! Instantly grades and analyzes online assignments and assessments; shows analytics and emails scores to STUDENTS!
Video Walkthrough

# data[].attributes.body
https://www.youtube.com/watch?v=lrcpOr1F6xc

# data[].attributes.body
You can tell Google not to allow users to edit specific formulas/cells in a shared document. COOL!

# data[].attributes.body
available as a right click or under Format

# data[].attributes.headline
Data Validation: available in both FORMS and SHEETS!

# data[].attributes.subject
Data Validation: available in both FORMS and SHEETS!

# data[].attributes.body
you can restrict the values entered into a cell as well as provide warnings for invalid data: under Data - Validation

# data[].attributes.headline
FILTER VIEW to change the way you view it and NOT how other users - see it: Data - Filter View...

# data[].attributes.subject
FILTER VIEW to change the way you view it and NOT how other users - see it: Data - Filter View...

# data[].attributes.body
Data- Filter Views- create a new filter view. You filter view will only allow you to see the new filter. If you update or change data it does change whats in the  actual spreadsheet in real time. Content will change but filter is unique if you use filter view.

# data[].attributes.headline
change .../edit to .../copy on any Google share link to force sharers to make a copy automatically!

# data[].attributes.subject
change .../edit to .../copy on any Google share link to force sharers to make a copy automatically!

# data[].attributes.body
Hi Jen!

# data[].attributes.body
Sort will change the data in a spreadsheet and and filter will just allow you to find a specific piece of data you are looking for.

# data[].attributes.body
bit.ly/jesseresources