Embarking on my own research

Yeah, I'm using a stock image. What of it?

I've embarked on my first research project. I don't mean the kind from college where I'd spend long, langourous hours in the library looking for books that someone else had already checked out, in order to find some vaguely applicable snippet of information I could shoehorn into some haphazardly compiled mess of words.

I've been wanting to get involved in some real research for a while: something that's legitimately applicable to my field. I'm clearly not an academic. I like education, learning, and teaching, but I do not like colleges and academia in general. This, therefore, is not some professionally back research. It is, however, something I'm legitimately interested in.

As a preceptor for local paramedic programs, I get the occasional student who may come to me lacking some knowledge or preparedness in one particular area or another. I legitimately enjoy helping to identify those weak points, and helping guide them through a process to learn it. I'm not lecturing during capstone, but I will provide some resources: podcasts, videos, articles, websites, etc, and together we usually sit down and learn something, and then we try to make that intuitive. This is part of what propels my desire to start a podcast: somthing that my old parter and I have discussed doing for several years now.

But how do you identify a topic to discuss? I'm not going to be showing up day 0 to start an FP-C refresher. I'm not going to be presenting the latest ground breaking research. I have no authority in those areas of my profession. Neither do I want to show up wholly unprepared and mumble my way through some continuous stream of consciousness with no real meat, the way I wrote research papers in college. I'd rather find out what people need and want. How else do you gather an audience than by asking people what they want, and delivering it to them.

Which brings me to my research subject. I've made a Google Forms survey asking people what they felt they were lacking coming out of their EMS education. Where are these students (and people already in the field) feeling weak or unsure, and how can we shore those spots up? Would they be interested in direct contact for an interview? Would they be interested in joining an email list to find out when and where their questions are addressed? Finally, do they want this shared back to their programs/agencies (as a way of spot checking and improving education)?

A survey made in Google Forms

The goal, as I said, is to indentify those needs. However, as the repsonse pool grows, it's going to become cumbersome combing through the responses, so I did some digging to find out how you can make that automatic.

Well, it turns out that Google Forms integrates well, and automatically with Google Sheets, and can automatically import your survey responses to a linked sheet.

Survey responses automatically imported to spreadsheet

Well, I've never been super knowledgable about Excel, and Google's Workspace Apps don't exactly work like Microsoft Office, so I was certain this was going to be a chore. Boy was I right. Now, not only was I right, I spent several hours trying to dig, learn, and trouble shoot, and it was almost by pure happenstance that I found out just where the problem was, and so I'm going to share that process with you, in perhaps somewhat painstaking detail.

Once you have your answers imported, you can setup the spreadsheet as a "named range" which can be just particular cells within the spreadsheet, or can be the entire spreadsheet.

If you click on Data in the top menu, scroll down to Named Ranges.

This will open up a dialog box on the right side where you can outline your range.

So for instance, here's mine.

The first line: form_responses is what I am actually naming this specified range.

The second line: form_responses is what I had named the spreadsheet. So you can imagine my surprise when I was initially querying the spreadsheet form_responses and it would return an error telling me there was no such thing.

Here's a full spreadsheet so we can get a better idea of what we're talking about.

Limiting the named range

So note, again, that the top is what we're naming the range, and the second is the sheet that we're querying for this range. Note that it is the same as the name of the sheet at the bottom. Also note that, unlike in my worksheet where my range was all of column A through all of column N like so:

form_responses!A:N

This example has limited the range from A1 through E6 like so:

dataSet!A1:E6

And it's highlighted the applicable area in the spreadsheet.

Why are we having to do this? I'm not really sure, but suffice it to say, if you try to query dataSet on another sheet without dictating a named range, it won't query the original sheet. So once you've set up your named range, you can then input functions on other spreadsheets attached to this one to pull the information you want out, and put it into a new document.

So I'm able to pull out just the questions that people submit using a Query function:

Pulling specific form responses in order to catalog answers

Note the function I'm using here:

=query(form_responses,"select D where D is not null")

I've told my new sheet to query the designated Named Range "form_repsonses" that I set up in my earlier example, and then I told it what I wanted it to do. In this case, I wanted it to import any cells from column D that weren't empty.

All of the functions will follow this basic structure. You'll tell it what you want it to do:

=query

You'll tell it where you want it to look:

=query(form_responses,

Then you'll tell it what you want it to do:

=query(form_responses,"select X

And finally, you'll set conditions using any of a variety of conditions such as if, when, equals, contains, is, is not, etc (if you have any):

=query(form_responses,"select X where X contains 'Y'")

There are further commands that let you specifict how you want the info presented or labeled such as "alphabetically, ascending" or "numerically, descending." You can find much more detail in write-ups I'll link below.

Using this method, I was also able to pull the individual contact info of the ones wanting to be emailed when we start answering their questions. In this case, we set the trigger or condition to when they answered "Yes" to the question of if they wanted to be contacted when we started rolling out our platform for answering these questions.

Automatically importing a contact list

And that was all based on functions querying the first spreadsheet wherein my survey answers are automatically piped in. So as people submit answers to my survey, all of these sheets will be automatically updated. Now I can add my old partner as a collaborator on all or just some of these, and he can have access to, say, the questions submitted, or to the contact info for those wanting a direct interview.

I'm not knowledgable enough on this stuff to be doing a full write-up of Google functions. Here are links to some of the resources I used, in increasing level of complexity: Seer Interactive has a more easily readable write-up here. Ben Collins had a more technical write-up here. Finally, I used a Google Docs write up here.

There it is. Nothing ground breaking here, obviously, but I was excited to find the level of accessibility and interoperability between Google's Workspace apps and what I was able to make them do (even if it did take me several hours to learn how to do it), and I wanted to share what I learn in the hopes that it may help someone else down the road.

I'm hoping that this will generate some useable leads for the podcast down the road, and that the information provided will be somewhat useable for providing feedback to schools and agencies to help better tailor their programs. If you're in EMS or studying to get into EMS, I invite you to take the survey and get your own chance to direct your education.

Take the Survey

Sign up below to stay up to date on the goings on, and get updated when we get our podcast up and running. As always I welcome feedback and commentary.