Data flow mini share: OpenApply -> Google Sheets -> Google Data Studio

DataFlowMiniShare.md

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

One-time setup

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.

1. Navigate to this repo, and make a copy of the spreadsheet at the link provided.

2. When the copy is made, go to Tools —> Script Editor. This takes you to the spreadsheet's AppsScripts project

3. Go to the Globals.gs file, and add the client_id and client_secret information, which you need to obtain via OpenApply's API Manager. Ensure to use V3.

4. Go to OpenApply.gs and ensure that the statuses property is ['enrolled']

5. Still in OpenApply.gs, run the run_Main_OA_ function, and wait for it to complete.

See includeCustomFields screenshot below

Configure the automatic update trigger

1. In the AppsScript project navigate to /triggers by finding the icon on the left side panel called "Triggers". (You'll have to hover to see the name)

2. Add Trigger (lower right-hand corner) and fill-in the following values:

   1. "Choose which function to run": run_Incremental_OA
   2. "Which runs at deployment": Head
   3. "Select event source": Time-driven
   4. "Select hour interval": Every hour

(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 enrolled to elsewise on OpenApply, they will be removed from the sheet on the next update

Setup OpenApply

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.

1. Navigate to Settings -> Forms

2. Identify a form where the data can be populated by the parents (usually this would be an Application form)

3. "Add field" and fill in the following values:

   1. "Short Name": consent_photograph
   2. "Field Label": your choice of wording that appears to the end user
   3. Type: Multiple Choice
   4. "Select from preset options": Yes & No

   See "Add Field" screenshot below

4. Save, and add the question to the above-identified form in the desired location

5. 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.

1. Select the id and email columns in the spreadsheet
2. Copy as values to a new sheet
3. Add a column with header consent_photograph
4. Enter values "Yes" for this new column
5. 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:

1. Recommend that you delete the email column, thus ensuring you do not accidentally change anyone's email addresses!
2. Download the csv (preferably without the email addresses)
3. Navigate to Settings -> Import
4. 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)

5. 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:

1. Create a Supplmentary Form with the consent questions. Add it to the checklist for currently enrolled students.
2. Use the mail merge and bulk email feature to send them a link to the Supplemental Form
3. 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 consent_photograph field

Now that the data is in OpenApply, we'll run the update. First we'll have to add the new question to the spreadsheet.

1. Go back to the AppsScript Project, and navigate to OpenApply.gs. There, change the values of includeCustomFields to ["consent_photograph"]

2. Optionally, you can include other custom fields in that list, if you know the short name. A useful standard one is parent_guardian.

3. Run the run_Incremental_OA function again.

4. Find the new column custom_fields.consent_photograph at the very right-most column, and the respective data.

5. 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

Start here.

Identify the data source as the spreadsheet

2. Add data
3. Google Sheets
4. Select the name of the spreadsheet you were working on in the Spreadsheet column
5. Select "oa_main" (or the name you renamed it to)
6. Click Add
7. "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:

1. Remove date range dimension (as this feature is not applicable to this use case)
2. In Dimension, add first_name, last_name, gender, grade, and custom_fields.consent_photograph.

You can change the name of each so it displays something more human-readable. See "Dimensions" screenshot below

3. 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

4. Click on "Add a Control" and add grade, and then another control for "first_name"

5. 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: custom_fields.consent_photograph="Yes"

Formula for converting "Grade 1" to just integers: CAST(REGEXP_EXTRACT(grade, "\\d+") as number)

6. Click on "View" to try out how it'll work for the end user

7. 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 field

Add Trigger

Add Data to Example

Dimensions

Find Trigger

includeCustomFields

Photo calculated Field

Run Incremental

Updating Existing