Data-driven decisions: Creating automated reports with data from multiple Google Forms

Tammy Ivins


The reference and instruction department of Rogers Library at Francis Marion University has automated the creation of their monthly departmental instruction reports by consolidating the data collected by multiple Google Forms. This process will be useful to any library or department that uses Google Forms to collect data and is interested in automating its reporting process.

Background

This system was developed as a result of two simultaneous initiatives in the Reference and Instruction Department.

The first initiative was to develop a more efficient system for tracking the number of students taught. Our previous system called for the librarians to, post-instruction, write the number of students taught on the paper calendar in the reference office. The departmental assistant would add those numbers up manually and enter them into the Excel spreadsheet that served as our annual report. Once all the data from all aspects of our department (including reference, collection development, archives, government documents, etc.) were gathered, the report would be sent to the department head. Even with the best team, this workflow introduces several opportunities for human error. Additionally, it prevented the library dean, department head, and other librarians from being able to access up-to-date statistics at any time.

The second initiative was to begin systematic assessment of our instruction classes so that we could develop data-driven departmental plans. Because our classes were entirely “one-shots” customized for each class, we had no standard learning outcomes. Without learning outcomes, there are standards by which to assess the classes. So we began assessing the quality of our instruction through post-instruction surveys sent to the teaching faculty whose classes we visited. Simultaneously we would also begin tracking the content that our librarians included in their instruction sessions, with the goal of using that data to eventually develop standard learning outcomes. The ACRL “Information Literacy Competency Standards for Higher Education”1 were chosen as the standard by which we’d track out content in a post-instruction librarian report.

This introduced two new sources of instruction data (a professor assessment form and a librarian report) in addition to the attendance records we were already dealing with. We set out to find a way to automatically collect all of the instruction data into one report that would serve as an up-to-the-minute summary of our instruction.

The need

The first step was planning our data collection tools. We immediately decided to make class attendance a question on the post-instruction librarian report, eliminating the need for the tracking on the paper calendar.

At the same time, the librarian report should collect other basic information about the classes (professor, department, etc.), which we had not previously been tracking, while also tracking the instruction content. The post-instruction professor survey, meanwhile, would include questions about the faculty’s satisfaction with our instruction, their impressions of our impact on their students’ research skills, and suggestions.

This left me with two data collection tools to create: the report (completed by the librarians) and the survey (completed by the professors). Google Forms was the logical system for creating these tools, as it was free, backed-up on the cloud, accessible from anywhere, and flexible.

Google Forms provided a fairly simply and straightforward process to create collect data, but the results would still need to be received and manually manipulated into an instruction statistics report. So the next challenge was how to take the raw data collected by two different Google Forms (so that data is stored in two separate Google Sheets) and automatically create a single instruction statistics document.

The solution

The challenge of consolidating data from separate Google Forms was solved by the discovery that the Google Sheets “import range” tool is not limited to pulling data from other pages inside a single sheet but can in fact be used to pull data from multiple Google Sheets.2 The formula is as follows:

[Formula ID: fd1-0770086]
<mml:mrow>
<mml:mo>=</mml:mo>
<mml:mtext>importRange</mml:mtext>
<mml:mo>(</mml:mo>
<mml:mo>“</mml:mo>
<mml:mtext>insert spreadsheet</mml:mtext>
<mml:mo>_</mml:mo>
<mml:mtext>key here</mml:mtext>
<mml:mo>”</mml:mo>
<mml:mo>,</mml:mo>
<mml:mo>”</mml:mo>
<mml:mtext>Sheet</mml:mtext>
<mml:mn>1</mml:mn>
<mml:mo>!</mml:mo>
<mml:mi>a</mml:mi>
<mml:mn>1</mml:mn>
<mml:mo>:</mml:mo>
<mml:mi>a</mml:mi>
<mml:mo>”</mml:mo>
<mml:mo>)</mml:mo>
</mml:mrow>

To set up the import, we opened the results for each form (by opening each form and clicking on “view responses” along the top). In our case, this meant opening the librarian report and the professor survey forms and viewing the associated results. When viewing the results, we noted the unique “spreadsheet_key” associated with each set of results, as seen in Figure 1.


Figure 1. Every Google Sheet has a unique “spreadsheet_key.”

Next, we created a new Google Sheet that will serve as our master collection of statistics (we named it “instruction statistics”). Inside that new instruction statistics sheet, we designated the first spreadsheet (spreadsheets appear as tab along the bottom of the page) as our instruction report, while other spreadsheets would be used to hold data imported from the Google Forms.

To import the data into the sheets, we added one of the previously noted unique spreadsheet_keys to the previously noted formula and inserted the newly edited formula in the upper lefthand box of one of the spreadsheets (Figure 2). Now the spreadsheet will automatically import all of the data collected by the associated Google Form. The spreadsheet will self-update every time someone submits a new response to the original Google Form.


Figure 2. Data can be transfered from one Google Sheet to another, using the unique “speadsheet_key.”

The process is repeated until all of the data collected by each Google Form is automatically being imported into individual sheets inside the master instruction statistics sheet. In our case, we started with only two set of data (the librarian instruction reports and the professor feedback survey), but you can use this process to consolidate results from as many Google Forms as needed.

Once all of our data-imports were set-up, we turned back to the first spreadsheet of the instruction statistics sheet and designed our instruction report. This is when we decided how the instruction statistics report would be arranged and what categories would be included. We chose not to include some categories of data if the data were not a useful part of our regular reports (for example, we excluded the qualitative comments collected by our professor surveys even though librarians use that feedback individually).

Finally, we used standard Google Sheet formulas to retrieve the data from the spreadsheets,3 conduct any math (averages, sums, etc.) needed, and insert the data into the appropriate field in the Instruction Statistics report (Figure 3).


Figure 3. Our data is automatically consolidated into a single report.

Challenges discovered

Numerous people were given access to the instruction statistics sheet so that they could see the up-to-date instruction statistics. To reduce confusion, we chose to “hide” the spreadsheets (inside of the master instruction statistics sheet) that held the importing-data.

We found that we couldn’t use the “download as” feature to save the Instruction Statistics report, as it saves a PDF with dozens of blank pages at the end. Instead, to download a PDF or to print the instruction statistics, we have to go through these steps:

  1. Open the Google Spreadsheet “Instruction Statistics.”
  2. Click on the “print” button (upper-left hand corner).
  3. In the first print dialog, set appearance preferences. We recommend “No gridlines,” “Actual size,” and “Portrait.”
  4. In the second print dialog, choose to print only pages 1–2. Print or choose “Save as PDF” as the printer.

Finally, we realized that the instruction statistics must be reset once a year by clearing out the data from the previous year. We must clear all the old responses to the forms (otherwise they will show up in the stats for the next year) by going into each Google Form, viewing the responses, and clearing the old responses. We highly recommend that the old responses be saved rather than deleted.

Conclusion

The accurate and timely sharing of data is an important part of ensuing your department gets the recognition it deserves and the resources that it needs. Manually transcribing data from numerous sources is time-consuming, prone to human error, and just not convenient.

For libraries that use Google Forms to collect data, using the “spreadsheet_key” to consolidate that data into one spreadsheet opens the door to a fully automated, continuously up-to-date department report system. This not only reclaims valuable time and effort that has previously been spent creating monthly and annual reports, but also enables department heads, librarians, and library heads to make better data-driven decisions.


Notes
1. “Information Literacy Competency Standards for Higher Education. ,” ACRL, 2000 , www.ala.org/acrl/standards/informationliteracycompetency.
2. Google, “IMPORTRANGE. ,” Docs Editors Help, accessed September 15, 2015, https://support.google.com/docs/answer/3093340.
3. Google, “Reference Data from Other Sheets. ,” Docs Editors Help, accessed November 10, 2015, https://support.google.com/docs/answer/75943?hl=en.
Copyright © 2016 Tammy Ivins

Article Views (2017)

No data available

Contact ACRL for article usage statistics from 2010-April 2017.