Please provide a brief description.
Please make a selection.
Please make a selection.
Please provide a description.
@intellisep
Account
Account
How to create a leaderboard with the 10 highest ranked users from a Google Sheet?
I have a learning game built in Storyline & I use the widget to send usernames & scores to Google Sheet. This works nice for now.
I use this code:
var player = GetPlayer();
SheetsSet('name', player.GetVar('Name'));
SheetsSet('score', player.GetVar('QuizTotal'));
SheetsSet('email', player.GetVar('Email'));
However, I want to use the information stored in the same sheet and create a leaderboard, so I am not sure how to get the 10 highest score rows & the corresponding names from Google sheet & preview them on the leaderboard in Storyline.
Also, I am wondering if the Leaderboard widget helps in this case. I have not found an option to "connect" it to a Google Sheet.
Thanks a lot!
I use this code:
var player = GetPlayer();
SheetsSet('name', player.GetVar('Name'));
SheetsSet('score', player.GetVar('QuizTotal'));
SheetsSet('email', player.GetVar('Email'));
However, I want to use the information stored in the same sheet and create a leaderboard, so I am not sure how to get the 10 highest score rows & the corresponding names from Google sheet & preview them on the leaderboard in Storyline.
Also, I am wondering if the Leaderboard widget helps in this case. I have not found an option to "connect" it to a Google Sheet.
Thanks a lot!
Reply
Posted on 9/22/2024
@support
There are two ways to do this.
1. Use a trigger to set the value of variable stencilsheetstoken to the row number that you need. So if set it to 2 and call SheetsGet(...), you will get the value from row 2, then set it to 3 and call SheetsGet(...) again to get the value from row 3, etc. Just check if you need to wait until the reading completes prior to updating stencilsheetstoken to the next number, but in theory this should work.
2. Use SheetsSet(...) to write a formula into a new column to concatenate all values into a single cell. For example:
SheetsSet('All scores', '=C2&","&C3&","&C4&","...');
SheetsGet('All scores', 'all_scores');
This will result in your variable all_scores receiving a comma-separated string of values, which you can split with JavaScript to get individual entries.
1. Use a trigger to set the value of variable stencilsheetstoken to the row number that you need. So if set it to 2 and call SheetsGet(...), you will get the value from row 2, then set it to 3 and call SheetsGet(...) again to get the value from row 3, etc. Just check if you need to wait until the reading completes prior to updating stencilsheetstoken to the next number, but in theory this should work.
2. Use SheetsSet(...) to write a formula into a new column to concatenate all values into a single cell. For example:
SheetsSet('All scores', '=C2&","&C3&","&C4&","...');
SheetsGet('All scores', 'all_scores');
This will result in your variable all_scores receiving a comma-separated string of values, which you can split with JavaScript to get individual entries.
Reply
Posted on 9/22/2024
@intellisep
Great. Thanks a for your reply.
I have another question which I think is not worth opening a new discussion.
How can you clear the list? Is there a way to reset the list from scratch so there will be 0 scores listed on my next playing?
Or maybe how can just one row of information from the leaderboard, for example to remove the name & score of the 1st place? is that possible, removing one score only from the Leaderboard?
Thanks a lot for your help!
I have another question which I think is not worth opening a new discussion.
How can you clear the list? Is there a way to reset the list from scratch so there will be 0 scores listed on my next playing?
Or maybe how can just one row of information from the leaderboard, for example to remove the name & score of the 1st place? is that possible, removing one score only from the Leaderboard?
Thanks a lot for your help!
Reply
Posted on 9/24/2024
@support
The same approach should work for updating the score: if you set stencilsheetstoken to 2 then call SheetsSet('score',0);
that will set the value on row 2 to zero, then you can set stencilsheetstoken to 3 and call SheetsSet('score',0); again, etc.
that will set the value on row 2 to zero, then you can set stencilsheetstoken to 3 and call SheetsSet('score',0); again, etc.
Reply
Posted on 9/24/2024
@intellisep
& probably my last question, is there a way to reset the leaderboard and remove all the entries. I may get hundreds of results submitted.
Reply
Posted on 9/24/2024
@support
I can't think of a good way to do it other than updating stencilsheetstoken and going one row at a time, which will take a very long time, you might be better off just opening the spreadsheet and doing it manually.
Reply
Posted on 9/24/2024
@support
This tip is just Google Sheets-based and is not related to the widget, but maybe it'll help:
We have this demo on the website https://cluelabs.com/widget-demos/sheets-pull/index.html that lets people make changes to the connected sheet. In order to keep the demo clean, the contents of the sheet resets every night. The way we accomplished that was by creating a scheduled task in Google Apps Script. So if you are familiar with Apps Script and need to clean out your data at regular intervals, maybe you can create a scheduled task as well.
We have this demo on the website https://cluelabs.com/widget-demos/sheets-pull/index.html that lets people make changes to the connected sheet. In order to keep the demo clean, the contents of the sheet resets every night. The way we accomplished that was by creating a scheduled task in Google Apps Script. So if you are familiar with Apps Script and need to clean out your data at regular intervals, maybe you can create a scheduled task as well.
Reply
Posted on 9/24/2024