BOT's #3: Auto-populating Google Forms answer options from a range in Google Sheets
One of the hardest things to do for an educator/event planner even in the new tech-savvy world is marking attendance. With several techs in RFID, Facial Recognition, Biometric scanners, and many others the question you want to ask yourself is the readiness of individuals and organizations to invest in this tech.
Using Google’s Sheets, Apps Script, and Forms you should have a solution knocking at your door. If you have been using the aforementioned Google Apps, then this should be easy for you but if you have not, then you can either reach out to me or take several free and paid courses with Ben Collins.
Follow the following steps to get this solution.
1. Create a Google Sheet
The first thing you want to do is create a Google Sheet, for this demo, I created one and named it Students Attendance. You can create your own and pick a name that you can best remember or make a copy of the one I created.
Once the workbook is created the next thing you want to think about is the schedules of classes or other forms of managed data with dictionaries. For our case we have used the formula:
=QUERY(IMPORTRANGE("add_your_class_calendar_spreadsheet_URL", "Consolidated_Schedule!A:H"), "SELECT Col4,Col1,Col2")
... in cell A1 to import the classroom schedule from the Gantt Chart Article. The use case of this data is to ensure that students are picking the right classes that should then aid in the attendance marking by the instructors too.
2. Create your Google Forms
For this specific instance, we are creating two Google forms. One we’ll use Google Apps Script on and one we are not.
Our first Google Form is the one we are not using Google Apps Script on. See the image below or column headers in the Registration tab for data labels. The data labels should be self-explanatory, for tips on how to create a Google Forms from scratch, there are hundreds of resources on YouTube to get you there.
Now to our second Google Form, the one we are using Google Apps Script on. See the image below or column headers in the Attendance tab for data labels.
3. Write your Script
To start off writing your script with Google Apps Script, navigate to Tools on the main menu and select Script editor.
领英推荐
To copy the code directly, make a copy of the spreadsheet we have used in this demo while navigating as instructed.
The process of writing the script involves:
Write a function to add another menu item to your current Spreadsheet menu. See on the image below the menu item we have added as an updated menu which is not part of the native Google Sheets menu.
Now that we have gone through the process, there are two ways you can run the script. The first one is going to the new menu we have just created ( for example Update Form - Math ) or your equivalent of the same and selecting on the submenu for which you want to update. The second option is going to Apps Script Dashboard, under My Projects, select the Script/project you want to set an automated trigger for. The triggers could be set to run on an update, after X hours, or by your preference.
This brings us to the end of setting up an auto-populated Google Form from a range in Google Sheets. Please reach out if something is not clear or would like to work with me in building something similar for your use.
Resources
About the author
Rogers is a data and emerging tech enthusiast. He is the Co-founder and VP of Operations at OurPass.
OurPass is a global neobank providing businesses of all sizes with access to every banking, payment, and business management tool they need to start, grow and scale their businesses.
Our mission is to create a borderless world of successful businesses and we are constantly building new tools to ensure that whatever stage a business is in, they have access to every tool they need to thrive, all in one place.
Founded in 2021 as a one-click checkout company, we made a pivot in July 2022 when we saw that beyond offering a niche service, we could provide end-to-end solutions that helped entrepreneurs grow every aspect of their businesses.
In just a few months since our transition, we have become a leading business bank serving thousands of customers including some of the biggest retail outlets in Nigeria such as Spar, Shoprite, and Eat N Go (parent company of Dominos, Cold Stone Creamery, and Pinkberry), processing about a million transactions monthly.
OurPass also holds a Microfinance Banking License from the Central Bank of Nigeria.
To unlock limitless smart business banking that OurPass app offers, download the app from the?App Store?or?Google Play Store, create an account, and join thousands of businesses growing with us.