Use the file menu in google documents and Make a Copy to use it. If you're new, you'll need an API key
Make sure to copy your API key into the new version of the script when you update!
It's the code.gs or wow_legion.gs code you need to replace (depending on your template)
does your guildroster.gs need update?
Then this is the code you'll need.
The api key should be in the wow_legion.gs, so you won't have to worry about copy/pasting that for this update.
Make sure you're selecting the correct file from the file list
in the script editor.
This uses some additional code that will fetch the characters from your guild. You can limit the results by Level and Rank.
You can sort the results by Role, Level, Name, Rank, and Achievement Points.
Since this uses the same code to do the armory pulls for the data, if your script is showing that it needs updates then you can follow the instructions above to stay up to date.
Loading too many characters at once can cause you to get temp locked from the api. Don't worry though, there's a fail safe built in for this, which you can over ride, if you choose!
A column has been added to the spreadsheet that will change the formatting. If your sheet does not include a 'rank' column to the left of the weapon relics then it is highly recommended that you make a new copy of the template to work with!
If you've already copy/pasted the script into your sheet, you can simply right click the Gear tab at the bottom of the template and 'Copy to..' your existing sheet, then remove your old Gear sheet
You could also add in the row manually (to the left of relics) but the avatars will be screwed up and will now need to be changed to =hyperlink(BW#, image(BV#, 4, 30,30)) (where # is the row number)
You'll also miss out on the conditional formatting that colorizes the relics based on type.. so really.. you should just make a new copy!
Hopefully this is a one time need, but if we find in the future that we want additional columns of data, this might happen again >_o
Back in Mists of Pandaria I created a Google Spreadsheet for myself for tracking my alts and others seemed to take interest in it, so I have updated it for Warlords, and now Legion and taken many suggestions to improve it.
It's great for tracking your guild's progress, your friends, or your alts. You can easily check your entire roster with this!
Keeps track of:
- class, level, specs, average equipped item level, slot item level
- Approximate rank of artifact weapon *
- Type and approximate +ilvl of each relic **
- displays enchants equipped on each item
- Audit of missing or cheap gems
- Lockout, progression and weeks active for current raids
- Heroic and Mythic dungeon progress, lock out, and total dungeons completed
- primary profession levels
* hope to improve this, but the API seemingly doesn't have a verbose "+ilvl" for the relics
It has a custom menu (next to the Help menu on the Google Docs toolbar) that will refresh all of the characters on the list! If you use it, you be prompted to give it permission for it to run. If you're wondering why the answer is addressed in the FAQ
If this still scares you, just don't give it permission and don't use the button.
back to the top
OTHER Automatic Spreadsheets I've made
Here are some more spreadsheets I've worked on!
- Gear details - This will display ilvl, the name and a link to each item
- Guild output - A tool for getting your guild's name quickly to enter into the Group sheet. Displays members by rank
- Mounts* - in development Displays your missing mounts, how to get them, and their cost. Also has a second tab with lockout info for the dungeons you need mounts from
- Pets* - in development Detailed info about your battle pets. Which one's your missing and how to obtain them. Which one's your missing rare and max versions of
* need to be updated for legion
This keeps detailed track of character's gear, showing item level, name, and linking to the item's WoWhead page.
The layout for this design was created by reddit user /u/robinnymann
You can easily copy this sheet to an existing project by right clicking on the sheet's tab at the bottom of the Google Docs where it says "Gear Details" and Select "Copy to.."
Make sure you also copy the script that is used to generate it.
To do this, go to the script editor in the Geardetails spreadsheet project (Tools > Script Editor) and copy the code from Geardetails.gs
Then in the document you want the sheet to be in go to the Script Editor, (Tools > Script Editor) and Create a new Script (File > New > Script File), name it what you want, and paste the code.
You should be good to go!
back to the top
outputs your guild into columns by rank for easy copy pasting
back to the top
This has two sheets (tabs are at the bottom). The first sheet has which mounts you are missing, how to obtain them, and their cost if they have one. It will only show mounts you're missing for your faction, so if you are a double agent, enter a character from the opposite faction in to see what faction only mounts you're missing.
The mounts are sorted by the percentage of players that own that mount, so in theory the mounts at the top of the list will be the easiest for you to obtain.
I'm still working on refining all the source and cost info, but since that is stored on an external location, when it is updated the info will be updated on your sheet if you've made a copy.
Sheet two contains dungeons lock out info for dungeons that you need mounts from. You can enter in multiple alts to keep track of who you've run that dungeon on in this lock out period. This is very experimental, and sometimes not accurate due to the way the character feeds work. Older dungeons are especially prone to not tracking properly. I suggest using an addon like Rarity
to know for sure.
This sheets wonky nature works well enough for me, but it might not be your thing.
back to the top
I love me some battling pets. If you're like me, you may be trying to Catche 'em all. If you're OCD like me, you may be trying to make them all rare too.
If you mark pets you want to work on as favorite, it will tell you how many of those favorites you need to upgrade to rare and level up to max.
Then it will go into detail about all of your pets; which need to be upgraded to rare, which wild ones you don't have a rare one of, your missing max level pets, and maybe most importantly your missing pets and how to get them.
Like the mount spreadsheet, the sources for these pets are still being refined, but since they are stored externally, if you've made a copy of the sheet the sources will update automatically as I work on them.
back to the top
Known Issues / Frequently Asked Stuff
Guild Output Errors
There are a number of known guild output errors, some of them include: no spec on the API for a character. Wrong role reported on the character for current spec.
Newer versions of the roster have had this fix applied to them
but this leads to another issue..
Guild roster not sorting roles properly
The Guild roster is sorted with the Guild API, which unfortunately does not always have the proper Spec selected for a character. The above fix overrides the guild API's reported Spec so that the correct one is reported. But since they may have been marked wrong in the Guild API.. they get sorted wrong. Fingers crossed that Blizz gets this sorted.
Guild ilvl Sort
Unfortunately as item level is not availible from the guild API this is not possible. If to be able to sort by item level then can use the manual name entry template, and use the sort column feature. It's sad. I know :(
Can I change the colors?
Yep! just right click on the offending cell, go to "Conditional formatting" and do what you want. Change the colors, the item level thresh holds, what ever you want. The change should replicate to all other cells that are controlled by those conditions It's pretty easy!
Why does the refresh menu version of the template require authorization to do stuff? I'm scared.
It sounds like something scary, which is why I made it a seperate version of the template incase for what ever reason.. you don't trust me. That's cool, I understand. Let me try to explain it.. The way the refresh feature works has to do with the way
Google Spreadsheets decides to rerun a function and get new data. In order to do this, one of the fields that it calls has to have changed. In order to do this, the script needs to have access to change one of the fields. The field it's changing?
A1, which has white text so you may not have even noticed anything going on there. When you click the refresh option in the menu, the A1 field gets updated with the current time, which is called (but not used) by the script. A1 changes, the script
says "Hey one of my fields is different!" and everything refreshes. Is there a better way? Maybe. Did I even think this up on my own? No. I got it from here.
This bit of code, the white text
in column A1, and the function calling A1 is the only difference between the refresh version and the regular version.
Why can't my guild mates/other people use the refresh feature on my sheet?
It's only usable by anyone able to edit the spread sheet. If you've given them access to view and not edit, then they cannot see or use the menu.
Error on individual character
If most of the sheet is loading the character data properly but you get a hiccup for some one and you KNOW that there's no typos, and you're not using the version of the sheet with the refresh button, try cHanGing a LetTer iN thE naMe or rEAlm frOm uPper
tO LowercAse or vISE vERSA. This will force the script to rerun for that individual character. If it's still not working, check the armory page to make sure they they show up.
Why it take time to populate the fields
There's a random sleep timer on the script which causes each call to sleep for a certain (short) period before calling the Blizz API. This is because if you're requesting a bunch of API calls all at once, it'll block you and say "Hey, don't you think
you've had enough?" No really. You can only make so many calls per second, so that's why it's there.
I got to row 60 and it won't let me add more characters! Why?
There's a soft limit to the number of characters you can add, but you can fix it, just click on the last working row's column E, you'll get a little square in the corner, drag that down as far as you want. Do the same thing for the avatar column
There are 2 reasons for this limit.. 1: Each time the formula is called it runs the script, and as you can guess, that could cause it to go a bit slower. Most people never put more than 50 people into the sheet so they never even notice the limit.
Reason 2: There is actually a limit to the number of calls to the API you can make per second, which is 100. You'd think then you'd be fine with up to 100 characters.. however, the way relics are, we actually have to make an api call for each one. So each character = 4 API calls. You can see where things can get messy!
However, there is a random pause before the script pulls the API each time, so even if you have 100+ characters, you probably won't run into the API calls per second issue, unless you add an extremely large number of characters.
There is also a calls per hour limit, which is 36,000
Want to contribute, have a suggestion, or facing some other problem not addressed here?
Hit me up on twitter (@bruk)
or Reddit (/u/brewk)
back to the top
Awesome Alt Addons
I hear way too often that an addon could do everything my spreadsheet does if you're just using it for tracking alts, as if I don't use addons. Personally I love a good spreadsheet. I like sorting things and clicking on cells.
However, I also LOVE addons, and here are just a few of them that I use to keep tabs on my army of alts.
- shows extremely detailed info about each of your alts and allows you to even search their inventory, see when you have mail expiring, dungeon resets, and more. A must have!
- keeps track of things you should be doing such as world bosses, LFR, picking up your bonus roll tokens, and more. Has some great garrison stuff in there,
but not as good as Broker Garrison. Currently seems to not work for professional cooldowns :(
- tool bar that is highly customizable and sometimes necessary to make other mods usable, such as Broker Garrison
- needs Titan Panel or another addon called Bazooka to run. Will show you detailed info on your Garrisons such as when your alts' caches are nearly full, who's got finished
missions and work orders.
- Keeps track of your alt's professional cooldowns on a given realm. Allows you to do the cooldown from the dropdown menu on Titan Panel, which I
believe is required for this.
back to the top