Google Sheet Variable Pull
Feb 12, 2021
Hello! I'm wondering if anyone can help with a project I'm building. I have a Google Sheet with a list of student names and an assignment (which is a link to set up an appointment on a calendly page). Rather than students needing to sort through the full list of names, I want them to be able to enter their name and then see their assignment as a clickable link.
Attached is a google sheet with sample data to help clarify. I would want a student to be prompted to enter their name (which would pull from column 1). They would then see their assignment and the link appear on the screen (pulling from columns 2&3). https://docs.google.com/spreadsheets/d/1C3zoCJrw3w35aNz3knlCdMjY4LJ3bbQbfS1Q8WyvXbw/edit#gid=0
This doesn't seem like it should be too complicated, but I can't figure out where to start. Any ideas? Thanks!
1 Reply
Hi!
That sounds like an efficient way to filter data. Your students will save time because they'll easily find the link that they need. Upon checking the interactive objects that you may add to your course, I am unable to find either a data entry field or a drop-down list that can be hyperlinked to another set of data.
No worries, there are a couple of workarounds that you may try but they both follow the idea of a drop-down list instead of a data entry field where he/she may just enter his/her name.
1. Create a custom drop-down list as recommended in this helpful video.
There's no need to embed your Google Sheet file this way, but you will have to create a drop-down menu using shapes, then link them to the layers that will show the URL to the assignments. This is feasible if you have a relatively small number of students because there should be one layer for every student.
2. Insert the Google Sheet file as a web object. This can easily be done using the 'Insert' tab in the main ribbon. Further steps are in this another helpful video.
This can be more efficient for the author if there is a large number of students and because there's no need to work with triggers and slides/layers. I have taken the liberty to add a slicer to the Google Sheet file that you attached. This way, the student may either type his/her name or manually select it so he/can easily find the URL for him/her. If you're interested in exploring slicers, here's the guide that will help you further.
I'll be more than happy to collaborate with you on this so feel free to send over a file that we can work on. I've started with one on my end.
I hope this helps you in any way. Cheers!
Google Sheet with Slicer