Embedded Files
I decided to build this model in Google Sheets so that anyone with a web browser could access it, as opposed to requiring people to have a copy of Excel available. Though most of the workbook would carry over to Excel nicely, the API calls would need to be fixed, and some formulas and formatting would need to be adjusted, so unfortunately it's not as simple as just exporting to xlsx.
However, the drawback to this is you need to have a Google account in order to access it. I realize not everyone has that, though I do believe there are more people with a Google account than those with ready access to Excel.
Regardless, if you have a Google account, here's how you get your own copy of the model.
Step 1: Go to the latest version of the model posted on this site and click the Google Sheets Link
Step 1: Go to the latest version of the model posted on this site and click the Google Sheets Link
You can go to the full Version History to find every version, or see below for the most recent one. Click the "Google Sheets Link" on the left-hand side to be brought to the viewable-only public release of the workbook.
LATEST VERSION
LATEST VERSION
Initial public release. All core systems active, including:
- Drop rate calculations for Trophy and Leather Shipments, Palawan Phylacteries, Supply Stashes, map event rewards, and Unidentified Gear.
- Average price of exotic gear drops.
- Salvage rates for gear by rarity, including breakouts for CF-SoM and SF-SoM / Mystic Kits.
- API calls and data ingestion from gw2spidy.com and the official GW2 API.
- Core profit model calculations and trended profit model calculations, along with several other key metrics.
- Primary user interface dashboard with data visualization, as well as user-accessible API refresh capabilities.
Step 2: Click on File >> Make a copy...
Step 2: Click on File >> Make a copy...
Once the workbook loads, you won't be able to edit or type anything into the workbook. That's by design. You need to make your own personal copy of the workbook for your own Google account. To do that, click on "File >> Make a copy..." to begin the process.
Make sure you are logged in to your Google account before trying to make a copy, otherwise the option to do so won't be available to you.
Step 3: Choose a name and save it to your Google Drive
Step 3: Choose a name and save it to your Google Drive
Decide what you want the file to be named in your Google Drive cloud storage, and where in Drive you want it saved, then click "OK".
At that point, a new tab in your browser should appear, and it will load the copy of the workbook that is now stored in your personal Google Drive. You should now be able to interact with all worksheets, enter your inputs on the User Dashboard, update the API calls on the "Update API Tables" sheet, control permissions for that copy of the workbook, and more. Basically, you now have full control over your own personal copy of the model.
That's it, you're done! If you want to use that version of the model again, just return to your Google Drive and open the document.
IMPORTANT INFORMATION ON THE API CALL FUNCTION
IMPORTANT INFORMATION ON THE API CALL FUNCTION
The workbook uses a custom "ImportJSON" function currently developed by GitHub user "bradjasper", along with others contributors. The function is what pulls in the pricing data from the APIs, and without it, the workbook is utterly worthless. This function should already be in the personal copy you make for yourself, so there's no further steps needed to get it working when you first download it.
However, if for whatever reason the function stops working, wasn't copied with the workbook, breaks in some other way, or you just deleted it because you like watching the world burn, here's how you get the function back and working again.
Step 1: Get a copy of the code from the GitHub page
Step 1: Get a copy of the code from the GitHub page
Click on the "Raw Code Page" link to the left to be brought to a text-only page that houses the custom Google Sheets function. Press "Ctrl + A" anywhere on the page to select all text, then "Ctrl + C" to copy it.
The "Source Code Page" link brings you to the user-friendly page that houses the code for the GitHub project.
The "README Page" link brings you to the overview README document for the GitHub project, where you can see the version history, authors, and more information.
"Source Code Page" looks like this.
"Raw Code Page" looks like this.
Step 2: Open your personal copy of the model from your Google Drive
Step 2: Open your personal copy of the model from your Google Drive
Return to your Google Drive cloud storage and open the workbook in Google Sheets.
Step 3: Click on Tools >> Script editor...
Step 3: Click on Tools >> Script editor...
Follow the menu options to click on "Tools >> Script editor..." to bring up the programmable script interface for Google Sheets.
Step 4: Paste the ImportJSON code into the Script Editor and save
Step 4: Paste the ImportJSON code into the Script Editor and save
When you open the Script Editor, if it's empty it should look like the screenshot to the right. If there are any existing projects, they should appear and you'll see their related code. Some code may be your own related to the Google Drive, so take care not to delete anything that looks like your own work.
If your Script Editor looks like the screenshot to the right, it's safe to delete the small amount of code highlighted there.
Next, paste all the code you copied earlier from the GitHub page into the empty work space on the right. Click on the project title and rename it to whatever you like, then click "Save" when done.
Step 5: Confirm function is available again in Google Sheets
Step 5: Confirm function is available again in Google Sheets
Return to your Google Sheets workbook, find an empty cell that isn't used for anything, and type in "=importjson", making sure to include the equals sign.
If the custom function has been entered and saved successfully, you should see a drop down menu like in the screenshot to the right which displays all the available functions that start with "importjson". If you see this, congratulations, it's working!
You may need to force a refresh of the ImportJSON functions making the API calls. To do this, go to the "UPDATE API TABLES" sheet in the model workbook and follow instructions there to refresh the API calls.
Keep in mind that using this method means the ImportJSON function is saved as a script within the workbook you used to open the Script Editor. It should not appear in other documents stored on your Google Drive. What this means is if you're having problems with multiple versions of the model, you'll need to repeat this process for each one. Again, this should not be an issue for anyone since the copy workbook process to get the model in the first place should include the script to begin with, but this is a warning just in case.
- Get the Model: How to Get It
- Get the Model: How to Use It
- Get the Model: Version History
- Get the Model: Special Thanks
- Get the Model: Suggestions / Bugs
Google Sites
Report abuse