Essay:Build a Deck Inventory Spreadsheet with Google Scripts
I don't have the largest KeyForge collection out there, but I have enough decks that I felt like it would be worthwhile to set up a spreadsheet to track them. I thought it would be really handy to also keep track of the various deck metrics that Decks of KeyForge offers, but getting the information from DoK into the spreadsheet seemed like a bit of a chore. Using Google Scripts and Google Sheets, the process can be mostly automated. You can see my deck inventory here.
Create the Spreadsheet
Create a new spreadsheet, then put you column headers across the top. I use the following headers for my sheet.
Add the Script
From the Tools menu in Google Sheets, click Script Editor. This will open a new tab where you will put your code.
Replace the existing code in the code editor with the code below:
//Builds custom menu to trigger refreshes
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Deck Info Menu')
.addItem('Refresh DoK Data', 'refreshSAS')
.addToUi();
}
//Gets data on edit
function onEdit (e) {
var column = e.range.getColumn();
var row = e.range.getRow();
var cellnote = e.range.getA1Notation();
var deckid = e.value
var impdeckid = deckid
if (column === 1 && deckid !== undefined) {
Logger.log (deckid)
var deckid = deckid.slice(42);
getSAS(deckid, row);
} else {
};
}
function refreshSAS() {
var sheet = SpreadsheetApp.getActiveSheet();
var row;
for (row = 2; row < 100; row++) {
var deckid = sheet.getRange(row,1).getValue();
if (deckid !== "") {
var deckid = deckid.slice(42);
getSAS(deckid, row);
//sleep to try to rate control and not overburder the API
if(row%24 == 0){
Utilities.sleep(60000);
}
}
else {
}
}
}
function getSAS(deckid, row) {
/*
Source of this data is Decks of Keyforge:
https://decksofkeyforge.com/
*/
//Set headers for decks of Keyforge API
var headers = {
"Api-Key" : "PUT YOUR API KEY HERE"
};
//set params
var params = {
"method" : "GET",
"headers" : headers
};
// Call the DecksofKeyforge.com API to get SAS and AERC information.
//var row = 18
//var deckid = "461e7a63-97f6-4cf6-9865-e7a0a3927c81";
var url = "https://decksofkeyforge.com/public-api/v3/decks/" + deckid;
var response = UrlFetchApp.fetch(url, params);
// Parse response to variables
var json = response.getContentText();
var data = JSON.parse(json);
Logger.log(data)
var name = data.deck.name;
var creatureCount = data.deck.creatureCount;
var houses = data.deck.housesAndCards.map(x=> x.house).join(',');
var sasRating = data.deck.sasRating;
var aerc = data.deck.aercScore;
var actionCount = data.deck.actionCount
var artifactCount = data.deck.artifactCount
var upgradeCount = data.deck.upgradeCount
var expectedAmber = data.deck.expectedAmber;
var rawAmber = data.deck.rawAmber;
Logger.log(rawAmber)
var amberControl = data.deck.amberControl;
var wins = data.deck.wins;
var losses = data.deck.losses;
var chains = data.deck.chains;
var powerLevel = data.deck.powerLevel;
var set = data.deck.expansion;
//Logger.log(set);
if (set === 'AGE_OF_ASCENSION') {
set = "AoA";
} else {
if (set === 'WORLDS_COLLIDE'){
set = "WC";
} else {
if (set === 'MASS_MUTATION'){
set = "MM";
} else {
set = "CotA";
}
}
}
//Logger.log(set);
//Put variables back into sheet
var sheet = SpreadsheetApp.getActiveSheet();
//var row = sheet.getActiveRange().getRow();
if (response !== "undefined") {
sheet.getRange(row,2).setValue([name]);
sheet.getRange(row,3).setValue([set]);
sheet.getRange(row,4).setValue([houses]);
sheet.getRange(row,5).setValue([sasRating]);
sheet.getRange(row,6).setValue([aerc]);
sheet.getRange(row,7).setValue([rawAmber]);
sheet.getRange(row,8).setValue([expectedAmber]);
sheet.getRange(row,9).setValue([amberControl]);
sheet.getRange(row,10).setValue([creatureCount]);
sheet.getRange(row,11).setValue([actionCount]);
sheet.getRange(row,12).setValue([artifactCount]);
sheet.getRange(row,13).setValue([upgradeCount]);
sheet.getRange(row,14).setValue([wins]);
sheet.getRange(row,15).setValue([losses]);
sheet.getRange(row,16).setValue([chains]);
sheet.getRange(row,17).setValue([powerLevel]);
} else {
}
}
Save your work. Rename your files if you want, but don't close the code editor yet.
Get your API key from Decks of KeyForge
Go to the APIs page of Decks of KeyForge and click generate API Key.
Copy your API key into the code editor on line 56, replacing the text "PUT YOUR API KEY HERE"
Save your work and close the code editor window.
Authorize your Script
Reload the window that has your spreadsheet on it. You should now see an additional option on your spreadsheet called Deck Info Menu.
From the Master Vault, copy the URL from one of your decks and paste it into the URL column of the spreadsheet.
Click the Deck Info Menu button and select "Refresh DoK Data". You'll see a banner telling you you need to authorize your script. Click the button to continue. You only have to authorize the script once.
You'll be presented a screen to review the permissions for the script. Click Allow.
Add your Decks
After you authorize the script, click the Deck Info Menu and Refresh DoK Data again. The script should run and populate out all of the data.
Going Forward
If you add a single URL to the URL column, it should do the lookup automatically. If you add a bunch of URLs at once, you'll need to use the Refresh DoK data option to pull all of the data in.
Obviously, feel free to modify the code included here however you want to get the information you need. Note that there is some (very rudimentary) rate limiting built into the script to try to limit slamming the DoK API, DoK has their own rate limits in place, but do what you can to keep from overburdening the server.
About
Blinkingline is a contributor to Archon Arcana and can often be found answering rules questions, judging tournaments, or drinking whiskey. Feel free to reach out to him on the Archon Arcana Discord.