Essay:Build a Deck Inventory Spreadsheet with Google Scripts

From Archon Arcana

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.

InventorySpreadsheetStep1.png

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.

InventorySpreadsheetStep2.png

Replace the existing code in the code editor with the code below:

InventorySpreadSheetStep3.png

//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.

InventorySpreadsheetStep4.png

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.

InventorySpreadsheetStep5.png

From the Master Vault, copy the URL from one of your decks and paste it into the URL column of the spreadsheet.

InventorySpreadsheetStep6.png

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.

InventorySpreadsheetStep7.png

You'll be presented a screen to review the permissions for the script. Click Allow.

InventorySpreadsheetStep8.png

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.

InventorySpreadsheetStep9.png

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.