Importing leaderboard data to Google spreadsheets
Here I will show you how to add data to your spreadsheet that updates automatically with the ES leaderboards. So when your horse levels up or gains a new title, it will display on your spreadsheet. Let's get started!
Step 1
Step 2
Step 3
Click on the very first cell in your new spreadsheet (A1) and then in the bar above enter the following (or copy and paste it).
=importrange("URL"; "Leaderboard!A1:H2000") Attention! Depending on which language is your default, you might have to change the semicolon after the URL to a comma. I know this is the case for the German version.
You will get an error message, but that's fine for now. |
Step 4
Now open the leaderboard you want to import. You can find all the leaderboards here. In our case, we open the reining leaderbaord in our browser. Next, copy the link from your status bar. It should look like this:
|
https://docs.google.com/spreadsheets/d/1gxUrqu2Ni4cC1VW7uKMP8cr8rySALGARQKYink0INyE/edit#gid=2136715377 |
Step 5
Step 6
Step 7
Now go back to your original spreadsheet (Sheet1, or however you've called it) and prepare it like this:
Have a colum for the horse's ES ID-number, one for their name and all the other information you want on the spreadsheet. For the sake of keeping this short, I only want to import the horse's current level (you could, however, import any data from the spreadsheet, I'll tell you how further down). |
Step 8
Click on the cell you want to import the data to. In our case that's cell C2. In the bar at the top, enter the following code:
=VLOOKUP(A2,Reining!B1:H1000,7,FALSE) If you get an error message it can be because of your language: try changing the commas (,) to semicolons (;) and it should work. Like this:
=VLOOKUP(A2;Reining!B1:H1000;7;FALSE) A2 here is referring to the cell in which the horse ID is stowed. You have to change this number for every horse you add. The next horse, for example, would have its ID stowed in cell A3. H1000 is the last cell that's included in our range. Depending on the leaderboard, you might have to change this number to 2000 in order to get all the registered horses. This is the case for the dressage leaderboard, for example. |
warning
Other info
If you want to display the horse's points or the title, you have to change the number accordingly. In the code "=VLOOKUP(A2,Reining!B1:H1000,7,FALSE)" you will notice the number 7. This number is referring to the 7th column in the specified range. In this case, it's the number which gives us the information for the horse's level. Here is a table with the numbers for other information:
1 = horse ID#
2 = horse name
3 = breed
4 = title
5 = points total
6 = points this year
7 = current level
So if you wanted to display the horses' title in our current example, you would click on cell D2 and type this in the upper bar:
=VLOOKUP(A2,Reining!B1:H1000,4,FALSE)
Adding other leaderboards
If you want to add another leaderboard, you will have to create a new sheet, call it something unique (if it's the dressage leaderboard, you might want to call it "Dressage") and import the dressage leaderboard data in to cell A1, as seen in step 3 above.
Next, when you want to import the data to your main spreadhsheet (step 8), you will have to alter the code like this:
=VLOOKUP(A2,Dressage!B1:H1000,7,FALSE)
Here we exchanged "Reining" for "Dressage", because we called the sheet "Dressage". Remember to change the other numbers accordingly!
Next, when you want to import the data to your main spreadhsheet (step 8), you will have to alter the code like this:
=VLOOKUP(A2,Dressage!B1:H1000,7,FALSE)
Here we exchanged "Reining" for "Dressage", because we called the sheet "Dressage". Remember to change the other numbers accordingly!