Data Flow mini share: OA > GSheet > GDS
In this tutorial, we'll use OpenApply as the source of truth to store extra information about each student, connect it via automatic updates to a Google Spreadsheet, and then connect it to Google Data Studio as a data source.
This solution if for a use case where there is a data point for each student, such as consent to be photographed, that needs to be retrieved occassionally by practitioners. It uses OA's custom fields, a tool that downloads and incrementally updates to a Google Spreadsheet, which is then used as a data connector to a Data Studio project.
Schools can then embed the graphs with filtering and search capability into a Google Site to surface the tool. They will then be able to look up this information.
Setup the data source spreadsheet
The following procedures will set up a Google spreadsheet that downloads OpenApply's currently enrolled students. You can use this spreadsheet as the data source for the graphs we will build, and it gets updated every hour.
Navigate to this repo, and make a copy of the spreadsheet at the link provided.
When the copy is made, go to Tools —> Script Editor. This takes you to the spreadsheet's AppsScripts project
Go to the
Globals.gsfile, and add the
client_secretinformation, which you need to obtain via OpenApply's API Manager. Ensure to use V3.
OpenApply.gsand ensure that the
OpenApply.gs, run the
run_Main_OA_function, and wait for it to complete.
See includeCustomFields screenshot below
Configure the automatic update trigger
In the AppsScript project navigate to
/triggersby finding the icon on the left side panel called "Triggers". (You'll have to hover to see the name)
Add Trigger (lower right-hand corner) and fill-in the following values:
- "Choose which function to run":
- "Which runs at deployment":
- "Select event source":
- "Select hour interval":
- "Choose which function to run":
(See "Find Trigger" and "Add Trigger" screenshots below)
You may adjust the frequency of how often the update occurs; however, please note that Data Studio only updates every 15 minutes. Generally, once per hour or once per day is often enough for most use cases.
Features of the data source
- Sheets can be renamed desired, updates will still occur on that sheet.
- Columns may be moved across as desired
- If students' status changed from
enrolledto elsewise on OpenApply, they will be removed from the sheet on the next update
Add or edit the Yes / No consent question
The steps below demonstrate exactly what needs to accomplished, but the general approach to do this is to go to the Settings —> Forms —> and ensure there is "Multiple-Choice" with Custom —> "Yes / No".
If you already set one like this up, it is recommended that you change the "Short Name" to
consent_photograph in order to follow instructions below; the exact name is needed in subsequent steps.
Navigate to Settings -> Forms
Identify a form where the data can be populated by the parents (usually this would be an Application form)
"Add field" and fill in the following values:
- "Short Name":
- "Field Label": your choice of wording that appears to the end user
- "Select from preset options":
Yes & No
See "Add Field" screenshot below
- "Short Name":
Save, and add the question to the above-identified form in the desired location
Add the same question to any other forms as needed (e.g. the re-enrollment form)
If you have any difficulties with these steps, our support network is available to help
Populate our new
consent_photograph field via import
If this is a new question on your forms, it will not yet be present in the OpenApply data download yet, as we have to populate it with values on each student to have the intended effect.
It is common for schools to export all the data, change the values as needed, and re-import. We will take a different approach, utilizing the spreadsheet we just created.
- Select the
- Copy as values to a new sheet
- Add a column with header
- Enter values "Yes" for this new column
- Populate student rows who have not consented with "No" as appropriate. The email address column will allow you to identify the student
You can now migrate this information you've just built and bring it into OpenApply:
- Recommend that you delete the
- Download the csv (preferably without the email addresses)
- Navigate to Settings -> Import
- Click on "Update existing"
Update Existing ensures that you will not accidentally create students, and you will get errors if is not selected (since you aren't providing enough of the required fields for creation)
- Select the field mapping as appropriate
Optionally add a "Supplemental Form" for ad hoc polling
Instead of (or in addition to) migrating the information as described above, you could choose to poll the entire parent population, asking them to fill out a one-off form via OpenApply's Supplemental Form feature.
The idea is the following:
- Create a Supplmentary Form with the consent questions. Add it to the checklist for currently enrolled students.
- Use the mail merge and bulk email feature to send them a link to the Supplemental Form
- Parents click the link in the email, and are presented with a banner that says to fill out for each of their children
It is recommended that you create these supplemental forms and tag the academic year on the name.
Update the spreadsheet to contain
Now that the data is in OpenApply, we'll run the update. First we'll have to add the new question to the spreadsheet.
Go back to the AppsScript Project, and navigate to
OpenApply.gs. There, change the values of
Optionally, you can include other custom fields in that list, if you know the short name. A useful standard one is
Find the new column
custom_fields.consent_photographat the very right-most column, and the respective data.
Optionally, move the column to the left for easy reference
Wondering why the column is
custom_fields.blah_blah? Internally, OpenApply stores all the questions created on the website on a namespace on the student data, called
custom_fields. If you use the API directly, you would find them in that property on the student endpoint as well.
Setup Google Data Studio
Identify the data source as the spreadsheet
- Add data
- Google Sheets
- Select the name of the spreadsheet you were working on in the Spreadsheet column
- Select "oa_main" (or the name you renamed it to)
- Click Add
- "Add to report"
When complete, it will create a sample report. Click on it, and notice the settings on the right side.
Shape the data
The data table you are given by default after the last step above is one that Data Studio chooses for you. Usually it displays a column of rows with "Record Count."
Looking at the right side, it's very busy. But all you need to know is the "Data source" and "Dimension" and "Metric" areas. Start by changing the values as below:
- Remove date range dimension (as this feature is not applicable to this use case)
- In Dimension, add
You can change the name of each so it displays something more human-readable. See "Dimensions" screenshot below
The table should update to show the values of each student in tabular format. That's great, but let's add the ability for users to filter by grade and search by name
Click on "Add a Control" and add grade, and then another control for "first_name"
Optionally, you can add "Calculated Fields" to the data source that converts existing values (like "Grade 1") to more suitable values (like the number "1"). Not only is this more readable, but allows proper sorting. Instructions for this is beyond the scope of the tutorial, but we'll provide useful formulas here:
Formula for converting "Yes / No" to boolean:
Formula for converting "Grade 1" to just integers:
CAST(REGEXP_EXTRACT(grade, "\\d+") as number)
Click on "View" to try out how it'll work for the end user
Optionally add a "100% Stacked Bar Graph" to get an overview of the consent data. In that way, you can see what percentage of students have agreed.
Add Data to Example
Photo calculated Field