bruk's automagic WoW character tracking and audit spreadsheet stuff.
Two version to choose from!
Updating your sheet from Legion to BfA
You really *should
* make a copy of one of the new templates to use as there have been many changes to the columns, formatting and formulas.
Click on the file menu on the template and select "Make a copy...". You will need to enter your API key into the script, as usual, to get it to work.
If you are using the manual entry template, you should be able to copy and paste your characters into the BfA version with no problems.
Some folks like to maintain their old sheet, and if that's the case, you can copy just the tab from one of the templates to your existing sheet by right clicking on it selecting 'Copy to...' and then selecting your old sheet.
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.
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
- (work in progress) Heart of Azeroth/Azerite armor info
- 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
- BfA reputations
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. Typing anything into Cell A1 will achieve the same thing. You can also force an individual update by changing letters in a character's name/realm/region from/to upper/lowercase.
back to the top
OTHER Automatic Spreadsheets I've made
Here are some more spreadsheets I've worked on. They are very likely out of date and may not work, but if you like to tinker with code they may be of value.
- 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
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!
- 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/Legion Orderhall such as when your alts' caches are nearly full, who's got finished
missions and work orders.
back to the top