C&RL News February 2016 86 The reference and instruction department of Rogers Library at Francis Marion Uni- versity 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 depart- mental 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 docu- ments, 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 sys- tematic assessment of our instruction classes so that we could develop data-driven de- partmental plans. Because our classes were entirely “one-shots” customized for each class, we had no standard learning outcomes. Without learning outcomes, there are stan- dards 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 track- ing 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 in- struction data (a professor assessment form and a librarian report) in addition to the at- tendance records we were already dealing with. We set out to find a way to automatically collect all of the instruction data into one re- port 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 Tammy Ivins Data-driven decisions Creating automated reports with data from multiple Google Forms Tammy Ivins is transfer student services librarian at Randall Library at University of North Carolina- Wilmington, email: ivinst@uncw.edu. At the time this article was written, she was head of reference a t Fr a n c i s M a r i o n U n i ve r s i t y ’s R o g e r s L i b r a r y. © 2016 Tammy Ivins ACRL TechConnect February 2016 87 C&RL News 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 instruc- tion, their impressions of our impact on their students’ research skills, and sug- gestions. 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, acces- sible from anywhere, and flexible. Google Forms provided a fairly simply and straightforward process to create col- lect 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 col- lected 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: =importRange(“insert spreadsheet_key here”,”Sheet1!a1:a”) 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 li- brarian report and the professor survey forms Figure 1. Every Google Sheet has a unique “spreadsheet_key.” View this article online for more detailed images. Figure 2. Data can be transfered from one Google Sheet to another, using the unique “speadsheet_key.” C&RL News February 2016 88 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. Next, we created a new Google Sheet that will serve as our master collection of statistics (we named it “instruction statis- tics”). Inside that new instruction statistics sheet, we designated the first spreadsheet (spreadsheets ap- pear 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 pre- viously noted unique spreadsheet_keys to the previously noted formula and inserted the newly edited for- mula in the upper lefthand box of one of the spreadsheets (Figure 2). Now the spreadsheet will au- tomatically import all of the data col- lected by the associ- ated Google Form. The spreadsheet will self-update every time someone submits a new response to the original Google Form. The process is repeated until all of the data collected by each Google Form is au- tomatically 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 consoli- date 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 re- port. This is when we decided how the instruction statistics report would be ar- ranged 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 exclud- ed the qualitative comments collected by our professor sur- veys even though librarians use that feedback individu- ally). Finally, we used s t a n d a r d G o o g l e 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 Instruc- tion Statistics report (Figure 3). Challenges discovered Numerous people were given access to the Figure 3. Our data is automatically consolidated into a single report. February 2016 89 C&RL News 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 “down- load 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 “Instruc- tion 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 sta- tistics 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 depart- ment gets the recognition it deserves and the resources that it needs. Manually transcrib- ing data from numerous sources is time- consuming, prone to human error, and just not convenient. For libraries that use Google Forms to col- lect data, using the “spreadsheet_key” to con- solidate 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 . “ I n f o r m a t i o n L i t e r a c y C o m p e - tency 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 Novem- ber 10, 2015, https://support.google.com /docs/answer/75943?hl=en. ThThee #1#1 ssouourcrcee foforr jojobsbs iinn LiLibrbrararyy anandd Information Science and Technology JOB SEEKERS Refi ne your search—easier advanced search options. Filter and sort hundreds of job ads by position type, employer, location, job title, and more. Create customized job alerts on the fl y. Post your résumé for employers. HRDR joblist.ala.org EMPLOYERS Strengthen your candidate pool— ALA reaches the most engaged professionals and students. Simplify recruitment—new fl at-rate pricing, multi-ad packages, and optional enhanced postings for increased visibility. New! New! New!