How to track patient outcomes using Google Forms and Sheets

FXNL Staff
January 14, 2019
5 min read

How to track patient outcomes using Google Forms

Patient reported outcome measures (PROMs) are an important part of clinical practice. They enable clinicians to assess and track the effectiveness of their interventions and they empower patients to share the impact of their condition and treatment on their life.

Implementing PROMs in routine clinical practice is still a challenge. Unless your practice uses software-based PROMs through electronic medical records (EMR), it is time-consuming to collect and interpret paper-based questionnaires.

One way to utilize electronic PROMs is through Google Forms and Google Sheets. Both services are free to use and can be useful to track patients over time. This article walks you through the process of setting up a Google Form questionnaire, linking it to Google Sheets, automatically collecting and calculating questionnaire results, and tracking these results over time.

Create a Google Form

The first step is to create a Google Form for each PROM you want to use in the clinic. Below, we have re-created the Lower Extremity Functional Scale (LEFS) in Google Forms.

There are a few options for collecting identifying patient info. We included email addresses as they are unique to each individual, but you can use name, date of birth or generate a unique code for each patient. The form cannot spellcheck inputted names. If a patient spells their name differently each time they fill out the form, it will be harder to monitor outcomes over time for that patient.

It’s also best to set up the columns as responses and rows as questions, in a multiple choice grid format with ‘require a response in each row’ enabled. This makes it easier to analyze results once the form is connected to the spreadsheet and avoids skipped questions.

Link Google Form to Google Sheets

Once you are satisfied with your questionnaire, create a spreadsheet by selecting the green button under the RESPONSES tabs of the Google Form.

A notification will pop up to ask you to select a response destination for your form. By default, the name of your form is automatically inputted. Go ahead and create a new spreadsheet.

You will then see this spreadsheet under your Google Sheets account. In this example, we have altered our form to require a patient’s name and therapist’s name. Google Sheets conveniently adds a timestamp column to keep track of the date and time of form responses. The remaining columns are the rows in our multiple choice grid from our Google Form, which are the PROM questions.


Set up Google Sheets

Add another sheet to your spreadsheet (using the + symbol at the bottom left of the screen). This tab will be your Vlookup Table. A Vlookup table maps an input value from one column to a value from a different column. For example, we would map the input value ‘No Difficulty’ to the value ‘4’. The input value is automatically added to the columns in our first spreadsheet tab called Form Responses when a user submits responses to our form. The answer that the patient selected is matched to the value in the corresponding column of our Vlookup Table spreadsheet. It is important to keep the spelling and capitalization the same between forms and spreadsheets to prevent errors. Below are the corresponding LEFS scores for each response.

Here is an example Vlookup Table for the QuickDASH questionnaire:

Automatically calculate outcomes scores

Add another spreadsheet tab and label it Number Responses. Copy and paste the first row from the Form Responses tab.

The next part involves adding the appropriate formulas to link all of the spreadsheets together. The first step is to add the following formula to the first row columns in your Number Responses spreadsheet. This formula takes the values from the corresponding column in Form Responses (which gets updated automatically after each patient completes the Google Form) and adds them to this spreadsheet.

=ArrayFormula(IF(ISBLANK(‘Form Responses’!$A$2:$A),””,’Form Responses’!$A$2:$A))

The letter A in this formula will be changed to match the column you are inputting this formula into. The number after the $ sign corresponds to the row number of the spreadsheet.

Once you reach the columns that record the responses from the PROMs, you must enter a different formula.

=ArrayFormula(IF(ISBLANK(‘Form Responses’!$D$2:$D),””, VLOOKUP(‘Form Responses’!$D$2:$D,’Vlookup Table’!$B$2:$C$6,2,0)))

This formula gets the text response from the corresponding column in Form Responses, matches the text to the corresponding numeric value in the Vlookup Table spreadsheet and returns that numeric value in the Number Responses sheet column.

To get the final questionnaire score, add a Total column at the end with the appropriate PROM formula. For LEFS, the formula will be the sum of the responses.

Test your Google Form and spreadsheet

Now that the Google Form and Google Sheet are both set up, it’s time to test it out. Go to your form and fill out the questionnaire as if you were a patient completing it. After you hit submit, a record with the raw data should be automatically entered into your Form Responses spreadsheet. At the same time, the Number Responses sheet should have the numeric values for that specific questionnaire mapped to the appropriate responses.

Numeric Responses tab with numeric values automatically replacing text responses

To see how this entire process in action, submit a survey responses below:

Fill out Lower Extremity Functional Scale questionnaire

And watch the spreadsheet below update automatically with your responses:

Example Google Sheet survey responses

Tips to implement forms in your clinic

You can save each Google Form link as a separate tab in a browser. Select the appropriate survey tab on a tablet and hand it to a patient to fill out in the waiting room before their appointment. The clinician can have the spreadsheet open on their computer and can view responses prior to seeing the patient.

A more efficient process involves sending the appropriate survey link to a patient via email prior to their appointment day to collect responses.

Next steps

Now that responses are recorded automatically in a spreadsheet, there are more advanced data analysis techniques that can be applied to reveal insights about your clinical practice. Stay tuned for the next instalment where we dive into some of those techniques.

FXNL Staff