How to auto-sync Leaderboard data to Google Sheets

Step 1 – Add the Jsonimport functionality

Follow steps 1 to 4 to add the Jsonimport functionality:

• https://www.lido.app/tutorials/json-to-google-sheets

Step 2 – Prepare your SynthesisVR Leaderboard URL

• https://synthesisvr.com/knowledge-base/reading-leaderboard-through-api/

Step 3 – Create a new Google Sheets file

Add 3 empty sheets:

• SynthesisData — this Sheet will hold the data coming from Synthesis
• AnalyzedData — will hold the sorted data
• PublicView — will hold the final with the best results per player

SynthesisData:

Copy/paste the following formula under A1:

=QUERY(IMPORTJSON("https://api.synthesisvr.com/data/leaderboard/11111111-1111-1111-1111-111111111111/1371/3%20month",,"","noTruncate"),"SELECT Col13,Col14,Col11,Col7,Col8,Col15 WHERE Col13 IS NOT NULL")

Don’t forget to replace 11111111-1111-1111-1111-111111111111 with your actual Synthesis API key.

AnalyzedData:

Copy/paste the following formula under A1:

=QUERY(SynthesisData!A2:N10000,"SELECT A ,B,C,D,E,F ORDER BY C DESC")

PublicView:

Copy/paste the following formula under A1:

=UNIQUE(AnalyzedData!A:B)

Copy/paste the following formula under C1:

• =MAXIFS(AnalyzedData!C:C,AnalyzedData!A:A,A1,AnalyzedData!B:B,B:B)
• .. drag C1 down, so it can auto-fill the highest score ..

Step 4 – Enable the automated Google Sheets hourly synchronization

Under Google Sheets, click on “File” and select “Settings”. Switch to the “Calculation” tab and switch the “Recalculation” period to “On change and every hour”.

Step 5 – Manage the Sheet permissions

For the SynthesisData and AnalyzedData Sheets – click the down arrow next to the name and select the “Hide” option:

You can now share the Leaderboard publicly. Make sure people not belonging to your organization are not allowed to make changes to the file.