The problem: We have a list of user emails in Google Sheets. When a user starts a course, we want to be able to authenticate them using their email address. Once authenticated, the course should pull other information about this user from Google Sheets. Is this possible?
The response: If you’re comfortable with formulas, you should have no issues achieving this with the Google Sheets widget for Articulate Storyline.
TL;DR: When it says in the widget instructions to create the stencilsheetstoken variable – this variable stores the row number of the user. So if you can use a formula to find the correct row number by email and then save that number into stencilsheetstoken then the user will get data from that row.
1. Initialize the Sheets widgets normally.
2. Use SheetsSet to write a formula to search for the email.
3. Use SheetsGet to read the calculated value of the formula with the result.
4. Extract the row number from the result and save it into stencilsheetstoken.
5. Use SheetsGet to read the user’s data from the spreadsheet as needed.
Detailed instructions to read user’s data from Google Sheets after matching the user by email:
1. Locate the Google Sheets document you will be working with and connect it to the widget.
2. Download the Sheets Widget starter file for Articulate Storyline and set the widget ID variable as instructed.
3. Create a slide with a text entry box; create variables as needed.
4. Add a trigger to write a lookup formula to the sheet after the user enters the email address and then immediately save the calculated value of the formula to variable ‘stencilsheetstoken’:
5. Add a trigger to read the user’s first and last names and score from the document when variable stencilsheetstoken changes:
6. Publish and test your project. Note that the API provided by Google is quite slow and it may take a few seconds for multiple calls to execute.
This article is available in multiple languages: