Looker Studio

How to Add a Unique ID in Excel with a new instance of a Google Form

The goal of this was to try and get a randomly generated unique identifier every time a new form is submitted. In our case a new form submission meant a new patient, and so we wanted some sort of patient ID. After doing some research we found a formula that can be entered that would do just this. Since Cell B1 has the header (“ID”), entering the formula in Cell B2 would create the ID for every subsequent row. It uses the time-stamp in column A to do this and so the ID is only created as new form submissions are added (it doesn’t automatically populate for every row, only the ones that have a timestamp meaning that a new form was submitted.

=arrayformula( if( len(A2:A), "" & text(row(A2:A) - row(A2) + 2, "000") & RIGHT(VALUE(A2:A), 3), iferror(1/0) ) )

Looker Studio

Looker Studio is a dashboard platform that connects to the Google Sheets where all the patient information is stored. As forms are entered, data is collected and stored into Google Sheets. This then connects to Looker Studio to present this data. Here is the link to Looker Studio’s Help Center to learn about all of the features and components that make up Looker Studio: https://support.google.com/looker-studio/?hl=en&sjid=7845246065635886165-NC#topic=6267740

There is also a quick start guide that was created to streamline the process of accessing Looker Studio: https://support.google.com/looker-studio/answer/9171315?hl=en

We recommend referencing the quick start guide to get started with Looker Studio before moving on to more detailed aspects of the Help Center.

Below are some aspects that we used to fit the use of Looker Studio into our project. We utilized a multiselect form, which in the Google sheet, concatenates all the options together (separated by commas) rather than separating them out individually. Hence, we had to use

Creating New Fields

Navigate to looker studio through the following link: https://lookerstudio.google.com/
Find the file you want to edit. Click on “Edit” in the top right corner
On the right side, under Data, there should be an option called “Add a Field” Click on that option.
In the Field Name section, add the name you want to add what vaccine, medical history, or medication name you want to add. For example, if we were to add a new vaccine, we would add Polio Vaccine
Paste the following formula into the Formula textbox:
CASE
    WHEN(REGEXP_CONTAINS(dimension, "specific identifier")) THEN 1
    ELSE 0
END
Within the formula, change the dimension to the type of data you are trying to find. In the Our case with the Google Form, this refers to the question title in the form such as Vaccinations, Medical History, and Medications. For example, if we wanted to see how many people had the Polio Vaccine, we would add Vaccinations as the dimension.
In the specific identifier of the formula, add what exactly you want to find. This filed would be referencing one of the answer choices within the question. Be sure this matches the options that is in the form as well. In our example, we would add Polio within the quotations. It's important to make sure the identifier is in the quotations. The final formula would look like this:
CASE
WHEN(REGEXP_CONTAINS(Vaccinations, "Polio")) THEN 1
    ELSE 0
END
Once the changes to the formula are done, click Save.
Then Click Done.

Updating New Fields

Navigate to looker studio through the following link: https://lookerstudio.google.com/
Find the file you want to edit. Click on “Edit” in the top right corner
On the right side, under Data, there should be an option called “Add a Field” Click on that option.
On the left side of the screen, click on “All Fields”
Identify the original field that you want to change. Click on the “fx” tab on the row.
Within the formula, change the specific identifier to the new name that will be used. For example, if we wanted to add the common name for the polio vaccine, we would update the formula to the following:
CASE
WHEN(REGEXP_CONTAINS(Vaccinations, "IPV")) THEN 1
    ELSE 0
END
Once the changes to the formula are done, click Save.
Then Click Done.

Creating Scorecards

Score cards provide high level overviews of the data.

Navigate to looker studio through the following link: https://lookerstudio.google.com/
Find the file you want to edit. Click on “Edit” in the top right corner
On the right side, under Data, find the metric you are trying the identify.
Drag that metric onto the center of the Dashboard page.
A scorecard will be created (it really is as simple as dragging the metric to the dashboard page, this is what happens by default)

Editing Scorecards

Click on an active score card
You should see the following (Two Panels – Chart and Data):
Drag the name (ex. “Metformin”) from the “Data” panel, to on top of the blue metric in the Metric section (what is currently Type I Diabetes)

The scorecard should update to the count.

Creating Charts

In the toolbar at the top of the editor, click Add a chart and then select a chart from the list.
Click the canvas where you want the chart to appear.
Move and resize the chart, as desired.
Add or change the dimensions and metrics by clicking the fields in the properties panel, or dragging and dropping them from the data panel (to the right of the chart panel) directly onto the chart.
You can also create new charts by dragging a field from the data panel onto the canvas.

Editing Charts

Navigate to looker studio through the following link: https://lookerstudio.google.com/
Find the file you want to edit. Click on “Edit” in the top right corner
Click on a chart
The following will show up on the right (Two panels - Chart and Data)
1. Dimension: Dimension groups the values. In our client case, the grouping is by whether a record falls into the Adult category or Child Category. This helps to see data between Adults that may differ from Children.
2. Metric: Metrics are the values of interest that become grouped by the dimension. In our client case, we were looking at the number of patients with Hypertension, Type I Diabetes, and Type II Diabetes.
To add metrics, simply drag the name of the medical history/condition from the right panel, into the “+ Add Metric” section.
To delete a metric from the chart, hover over the metric and click on the X.