10 ninjas - Andrew Stillman - New Visions for Public Schools_353

Prévia do material em texto

DIY Workflows and 
Light Duty Data Systems
for EDU
Andrew Stillman
Senior Systems Developer
June 11, 2013
Modular, flexible tools for data-driven and efficient processes
Forms Sheets
Sites
Charts
Scripts
Some Building Blocks
● CSV exports from source systems
● Data input validation methods
● 10 spreadsheet Ninja functions
● Google Docs, Sheets, Sites, Forms
● Google Form prepopulation hacks
● Flexible, UI-Driven Scripts
● Data synchronization across 
sheets
○ =IMPORTRANGE()
○ pushData
● Merges using gallery scripts:
○ formMule, autoCrat
Importing CSV Exports 
from Core Systems , , , , ,
always think about what 
KEY will be used to link up 
your data
User Data Entry
Validation Methods
● Data validation - for direct Spreadsheet 
input
● Form dropdowns - better for large numbers 
of users. Downside: maintaining form 
dropdowns.
● Form Prepopulation via URL - HYPERLINK 
and CONCATENATE in spreadsheet to 
populate form values
formRanger script
Range-Referenced Form Items
● Define the options in list, checkbox, and multiple choice 
Google Form questions by referencing any column in 
your Spreadsheet
● Set the trigger of form option refresh to occur after every 
form submission (allows form options to be informed by 
all prior form submissions), after every spreadsheet edit, 
or every 5 minutes.
● See example.
User Data Entry
Validation Methods
=VLOOKUP()
=FILTER()
=IFERROR()
=COUNTIF()
=COUNTA(IFERROR(FILTER()))
=SUMIF()
=IF()
=CONCATENATE()
=HYPERLINK()
=IMPORTRANGE()
10 Functions for the GAFE 
Systems Ninja
VLOOKUP()
Stands for VERTICAL LOOKUP
Returns a single value, pulled from a column 
in one sheet (or range) into the cell based on 
a match in the first column of the range being 
searched.
Watch out! Student ID numbers are frequently exported 
as "Plain Text" rather than numeric values. Data type 
must match for VLOOKUP to work.
Example: Using OSIS as a match, look up current grade in Class A for each 
student from a list that lives in another sheet...
match
retrieve current 
grade
Compiled 
Data
Sheet
Class 
A Grades 
Sheet
v
Anatomy of VLOOKUP
VLOOKUP takes 4 arguments...and returns a single value.
Matching 
what 
value?
single value 
or cell 
reference
In left column 
of which data 
range?
bounded OR 
"bottomless"
range.
Good practice to 
$lock range 
boundaries
From what # 
column in the 
range?
Numerical column 
number
Is the range 
sorted?
TRUE or FALSE
(0 or 1 also works)
Almost always set 
to FALSE.
=VLOOKUP(arg_1, arg_2, arg_3, arg_4)
=VLOOKUP(A2,ClassA!$A:$C, 3, FALSE) 
=FILTER(sourceArray, arrayCondition_1, arrayCondition_2, etc.)
Takes in an array, and array conditions and 
returns an array (single or multiple columns) of 
values that match any number of criteria. 
What is an array condition?
ex) Sheet2!A:A>65
In the example below, the FILTER function is being used in cell A2 of a "Students" 
sheet to select the names of students on the "Scores" sheet who are college ready in 
ELA (>74) but not in Algebra (<80) to plan an intervention. 
=FILTER(Scores!A2:A, Scores!B2:B>74, Scores!C2:C<80)
In Red: This is the range on which you want to search for values that match 
the filter criteria. 
In Blue: This is the criteria to match within an array in the range. Could also 
include >, >=, <, <>, etc. Add more criteria separated by commas.
Notes on using FILTER:
FILTER can be used to return 
multiple columns (e.g. by replacing 
red portion of above with A2:C11). 
Length of all ranges must be 
equivalent or the FILTER function 
will return an error.
Click here to 
open sample 
spreadsheet.
=IFERROR(test, value)
Suppresses the "#N/A" error returned by 
functions like VLOOKUP and FILTER when no 
match is found.
=IFERROR(VLOOKUP(C2,Sheet2!A:D,2,0)
Returns blank if no match is found and 2nd 
argument is left blank.
=COUNTIF(range, criteria)
Takes in an array and returns a count of the 
number of matches on a criteria.
Form Data
Tally Sheet
=COUNTIF('Form Data'!D$2:D,A2)
=COUNTIF(range, criteria)
Purpose of 
formula: count the 
number of times the 
student ID in cell A2 
of the Tally Sheet 
shows up in the form 
data (our lateness 
log)
=COUNTA(IFERROR(FILTER()))
Gives us a way to count on multiple match 
criteria.
COUNTA provides a count of the number of 
numeric or non-numeric values in an array.
IFERROR suppresses the #N/A when zero 
results are returned by FILTER
In the example below, the COUNTA(IFERROR(FILTER()))) combination is used to 
count the number of students who meet the criteria below
=COUNTA(IFERROR(FILTER(Scores!A2:A, Scores!B2:B>74, Scores!C2:C<80)))
Notes on using FILTER:
FILTER can be used to return 
multiple columns (e.g. by replacing 
red portion of above with A2:C11). 
Length of all ranges must be 
equivalent or the FILTER function 
will return an error.
Click here to 
open sample 
spreadsheet.
=SUMIF(range,criteria,sum_range)
Sums numeric values in a range that match a 
criteria on another range.
=SUM(FILTER(range,criteria,sum_range))
Sums numeric values in a range that match 
multiple criteria on another range.
Form Data
Tally Sheet
=SUMIF(range, criteria, sum_range)
=SUMIF('Form Entries'!D$2:D, A2, 'Form Data'!G$2:G)
Purpose of formula: sum 
the number of minutes late 
that show up along with 
the OSIS# in cell A2 of the 
Tally Sheet shows up in 
the form data (our 
lateness log)
=IF(test, then_value, otherwise_value)
Tests a condition and returns one value if true, 
another if false.
ex) =IF(A2<65,"Fail","Pass")
Logical conjunctions (=AND(),=OR()) can be useful here...
=IF(AND(A2<70,A2>=65),"D","other than D")
IFs can be nested, though it gets dizzying fast
=IF(AND(A2<70,A2>=65),"D",if(AND(A2>=70,A2<80,"C",..))
=CONCATENATE(string, string, etc.)
Returns a string that is the additive 
combination of all inputs. Good for building 
pre-populated form URLs.
=HYPERLINK(url, link_text)
Returns a clickable, text hyperlink to a 
spreadsheet cell.
Hacking Forms
Form pre-population via URL arguments
HYPERLINK and CONCATENATE
URL-encoding whitespaces
Running formMule with copydown of formulas
=SUBSTITUTE(text, search_text, new text, occurrence)
Good for replacing whitespaces with + in 
form values:
ex) =SUBSTITUTE(A2, " ", "+")
Scripts
Gone viral (in 12 months)
formMule
sent 130,000 personalized Emails
created 8,500 calendar events and 
sent 2,600 SMS messages
had 1,200 unique users
Doctopus
managed 210,000 student assignments
was installed by almost 6,000 unique 
teachers
autoCrat
created 550,000 personalized 
Documents
had 4,900 unique users
Goobric
Doctopus Demo
=IMPORTRANGE(ssKey,Range)
Useful in limited cases for bringing a range of 
spreadsheet data from one spreadsheet to 
another.
ssKey is the long string in the URL of any Google 
Spreadsheet
Use pushData script for more robust 
applications....
Synchronized Data Across 
Multiple Spreadsheets
"Console" 
or controller
Spreadsheet
the almighty
pushData
scriptSource 
Spreadsheets
Destination
Spreadsheets
pushData Demo
Merges
Spreadsheet-to-Email
Spreadsheet-to-Calendar
Spreadsheet-to-Text Message
Spreadsheet-to-Document
Spreadsheet to any combination of the above
Any of these can be
● Form-Triggered
● Form-Triggered with VLOOKUPS and other formulas 
running in the form data sheet.
● Time-triggered
docAppender: Use a Google Form to add 
content to the end of existing Google 
Documents. Optionally, pin a subfolder 
to each document.
2
3
4
User visits WebApp and selects Doc to append1
User clicks through to Google Form
with Document ID of Doc to be appended
Form submissions recorded in spreadsheet
Triggers Goog Doc to be appended with form field 
values
Selected field values 
appended as 
bulleted list or table 
in selected Doc.
Spreadsheet to Email Merge
Conditional
1
2
3
4
5
Data sheet formMule
script
6
(optional)
IF column 
value
EQUALS
something
m
er
ge
 v
al
ue
s 
an
d 
se
nd
 o
ne
 o
f s
ix
 
po
ss
ib
le
 e
m
ai
l t
em
pl
at
es
Optionally form 
triggered, with 
formula copy 
down
=fx()
=fx()
Spreadsheet to Calendar
Conditional
Data sheet formMule
script (optional)IF column 
value
EQUALS
something
m
er
ge
 v
al
ue
s 
(in
cl
ud
in
g 
st
ar
t t
im
e,
 e
nd
 
tim
e)
 to
 c
al
en
da
r e
ve
nt
s
Optionally form 
triggered, with 
formula copy 
down
=fx()
=fx()
Spreadsheet to SMS or Voice
Conditional
Data sheet formMule
script (optional)IF column 
value
EQUALS
something m
er
ge
 v
al
ue
s 
to
 S
M
S 
or
 V
oi
ce
 
m
es
sa
ge
Optionally form 
triggered, with 
formula copy 
down
=fx()
=fx()
Spreadsheet to any Combination, at 
different points in a process
Conditional
Data sheet formMule
script (optional)IF column 
value
EQUALS
something
1
Optionally form 
triggered, with 
formula copy 
down
=fx()
=fx()
Simple Spreadsheet to Document 
Merge, with Merged Collaborators
Data sheet autoCrat
script
Document 
template
with 
<<Merge Tags>>
Primary Destination Folder
Simple Spreadsheet to Document 
Merge, with Secondary Folder IDs
Data sheet autoCrat
script
Document 
template
with 
<<Merge Tags>>
Primary Destination Folder
Secondary
Destination 
Folders (by ID)
Simple Spreadsheet to Document Merge, 
with VLOOKUPed values and secondary 
folder IDs
Data sheet autoCrat
script
Document 
template
with 
<<Merge 
Tags>>
Primary Destination 
Folder
Secondary
Destination 
Folders (by 
ID)
formMule
script
Optionally 
form 
triggered, 
with formula 
copy down
=fx()
=fx()
Secondary 
lookup sheet
Systems Building Demo
Problem of Practice:
The systems challenges that 
arise for professors in managing 
caseloads of advisees.
Resources
+Andrew Stillman
@astillman
http://www.youpd.org
Google Apps Script for EDU - Builders and 
Users (G+ Community)