Please provide a brief description.
Please make a selection.
Please make a selection.
Please provide a description.
@support
Account
Account
Collating data from multiple activities in Google Sheets
I have multiple storyline activities, all in standalone modules. I want to be able to summarize everything in one google sheet. How would I go about collating the data? I see that when a person does the module, the information is just entered on the next line, so if they do a second module, there will now be two entries, same name, same email but points in two different columns. How would you ask the system to find that person and email and put the points in the same row, different column?
Reply
Posted on 5/6/2024
@support
In your Storyline file, there's a variable stencilsheetstoken which contains the row number of the current user in your Google Sheets document. So when a learner opens the module, you can check whether they already have a row assigned to them, and, if they do, update the value of the stencilsheetstoken so that the writing occurs on that same row.
Here's an example of the workflow:
- Ask the user's email address at the start of the module and save it to a Storyline variable.
- Write a formula to Google Sheets to search whether this email already exists. For example: SheetsSet('email_lookup', '=MATCH("email@address.com",B:B, 0)');
- The formula above assumes that emails are saved to column B, but it can, of course, be any column.
- Read the calculated value of the formula you just saved. For example, SheetsGet('email_lookup', 'userrrow');
- Add a trigger when 'userrow' changes to check if the value is numeric and is greater than 0 (this will indicate that the email address already exists).
- Set the value of stencilsheetstoken to the value of userrrow. The data will now be saved to the existing row.
As a best practice, consider cleaning up the email address entry before writing the formula to avoid duplicates in your document. For example, you may want to convert it to lower or upper case, trim empty spaces on the ends, etc.
Here's an example of the workflow:
- Ask the user's email address at the start of the module and save it to a Storyline variable.
- Write a formula to Google Sheets to search whether this email already exists. For example: SheetsSet('email_lookup', '=MATCH("email@address.com",B:B, 0)');
- The formula above assumes that emails are saved to column B, but it can, of course, be any column.
- Read the calculated value of the formula you just saved. For example, SheetsGet('email_lookup', 'userrrow');
- Add a trigger when 'userrow' changes to check if the value is numeric and is greater than 0 (this will indicate that the email address already exists).
- Set the value of stencilsheetstoken to the value of userrrow. The data will now be saved to the existing row.
As a best practice, consider cleaning up the email address entry before writing the formula to avoid duplicates in your document. For example, you may want to convert it to lower or upper case, trim empty spaces on the ends, etc.
Reply
Posted on 5/6/2024