Building Torn City API Scripts with Google Apps Script

Nov 9 2022
Torn City.
Torn City.


Ideally you are familiar with Torn City but if you aren't don't worry. At the very least you're somewhat familiar with Application Programming Interfaces (APIs) and know some basic programming.

Together, we're going to build a script to generate a graph of a stock's price history over the last hour.

1. Introduction

The text-based MMO called Torn City exposes a free-to-use API for their playerbase. In this tutorial we're going to build a simple Google Apps Script-based script to call the API and generate a graph of a particular stock's prices over the last hour:

Fancy Graph.

How it works:

  1. The user enters their Torn City API key.
  2. The user enters the Stock ID. A reference table is provided with the stock ID, name, and acronym.
  3. The user clicks the "Run" button. The button is an image with a script/function attached that is called when the image is clicked.
  4. The function calls the Torn City API, parses the JSON response, and populates the stock price history table in the spreadsheet.
  5. The Google Sheet uses the timestamps and prices to auto-populate the graph.

2. Prerequisites

There are a couple things we need to do first:

3. Calling the API

The first thing we should do is populate a table with the existing stock names, symbols/acronyms, and IDs. These will be used as a reference for end-users of the script. The API documentation provides you a way to try making API calls before you use them in your programs:

Try Torn API.

I like to format the JSON response body in VS Code so it's easier to read:

JSON Response.

You can see that the JSON response body contains the stock ID, name and acronym, as well as other useful information like it's current price and the type of benefit it provides. We only need the stock ID, name and acronym right now. In the next section we're going to display this info to the user to make it easily accessible.

4. Setting Up the Sheet

We need to set up the Google Sheet so that the end user knows where to enter both their API key and stock ID as well as to execute the script.

Here is how I laid mine out:

Sheet Setup.

You can insert an image "over the cells". Then by right-clicking the image you left-click the three vertical dots in the upper right to assign a script to the image:

Assign Script.
Assign Script 2.

5. Populate Stock Reference Table

The code below does the following things:


Next we create variables to store the data we want, as well as a row counter to iterate over each row. For each object (aka "key", not to be confused with the API key) we will grab and store the stock ID, name, and acronym into our variables. Then we set the respective cell data with our data, and finally iterate to the next row. We repeat this until there are no more objects/keys in the JSON response body.

myFunction Pop Stocks.

When we hit "Run" to execute our script you will be prompted to grant your project access to your linked Google Account:


Once you've done that we can execute the script and get the stock reference table populated:

Stock Ref Table.

6. Populate Stock Prices Table

The stock IDs, names and acronyms almost never change so we're going to comment out the code where we populated that data:

Comment Code.

Torn City's API requires us to select a single stock ID before calling the API to retrieve timestamped price data for the last hour of a stock:

Call Stock API.
Historical Price Data.

This requires us to update the STOCK HISTORY sheet layout:

New Layout.

Here is myFunction with the new API call. Keep in mind the red squares for the following changes/additions:

New myFunction.

Here is the stock price history of Insured On Us (IOU, ID=5) stock:

IOU Price History.

You'll notice the timestamp data is returned in Unix Epoch which is not very user friendly. We can make it pretty by converting the timestamp into UTC using the following formula:

Convert Timestamp.

If we want to reverse the timestamp order we can do that automatically in the function. See lines 44-46:

myFunction Final.

7. Create the Graph

The final step is to insert a graph with the appropriate data and make it as simple or as fancy as you'd like. The graph will update each time the script is executed.

Stock History.