Using App Script with a JSON API

Somebody who shall remain nameless (@SantiagoPerez - oops) asked me if I could put together a tutorial on working with API’s, so here it is. It’s my first tutorial, so please be gentle :pray:

Whilst there is an app at the end of this, the app is not the main focus of the tutorial, so don’t expect to be impressed by that. The main focus is to give a gentle (?) introduction to Apps Script, and demonstrate how it can be used to interface with an API endpoint. I’ve tried to break down and explain the Apps Script bits as much as possible, so even if you’ve never used Apps Script before (@Robert_Petitto), hopefully you can follow along :slightly_smiling_face:

Right, let’s get started…

Part 1 - The API

For the demo, I’ve picked Deck of Cards. This API, oddly enough, allows you to play around with a deck of cards. It has several methods, but I’ll just focus on 3 of them:

  1. Get a new deck of cards
  2. Shuffle the deck
  3. Draw some cards

Lets have a look at each of them in turn…

A Brand New Deck: https://deckofcardsapi.com/api/deck/new/

Which returns:

    {
        "success": true,
        "deck_id": "3p40paa87x90",
        "shuffled": false,
        "remaining": 52
    }

Okay, so we get a deck_id, with 52 cards, and it hasn’t been shuffled.

Next, Shuffle the Deck: https://deckofcardsapi.com/api/deck/<<deck_id>>/shuffle/

With this one, we have to provide the deck_id as a part of the request. It returns:

    {
        "success": true,
        "deck_id": "3p40paa87x90",
        "shuffled": true,
        "remaining": 52
    }

This time shuffled is true, and it also tells us how many cards are left in the deck (which won’t necessarily be 52)

And finally, draw some cards: https://deckofcardsapi.com/api/deck/<<deck_id>>/draw/?count=2

This time, we need to provide the deck_id, and tell it how many cards we want.
The response:

    {
        "success": true,
        "cards": [
            {
                "image": "https://deckofcardsapi.com/static/img/KH.png",
                "value": "KING",
                "suit": "HEARTS",
                "code": "KH"
            },
            {
                "image": "https://deckofcardsapi.com/static/img/8C.png",
                "value": "8",
                "suit": "CLUBS",
                "code": "8C"
            }
        ],
        "deck_id":"3p40paa87x90",
        "remaining": 50
    }

This is a little more complex, but basically what we have is an object that contains an array of cards, and each card has a series of key/value pairs that define its attributes. When it comes to decoding that, let’s say we want to get the URL of the image for the first card, we might do something like var image = json.cards[0].image.

cards[0] is referencing the first element in the cards array. But I’m getting ahead of myself. Let’s setup a sheet…

Part 2 - The Sheet

I decided to use two sheets, one for the Deck and one for the Cards. Why? No particular reason other than it seemed like a good idea at the time :grin:

The Deck Sheet:
Screen Shot 2020-12-18 at 11.02.23 PM

Columns A/B/C will be used to store the responses from the API calls, and columns D & E will be used to trigger the API calls (more on that later).

The Cards Sheet:

Columns A-H will be used to store my card details. I’m going to draw 2 cards each time, so I have 2 of each for Code, Value, Suit & Image. And I’ve set them up so that Glide will treat them as array columns. Column I will be used to trigger an API call to fetch 2 new cards.

I’ve also created some Named Ranges in my sheet for some of the values I’ll need to reference in my script. This just makes life a little bit easier, and stuff is less likely to break if you start adding or removing columns/rows later on.
Screen Shot 2020-12-18 at 11.16.32 PM

Right, time for the fun stuff :joy:

Part 3 - Getting down & dirty with the Apps Script

First thing we need to do is find the Script Editor. It can be found via the Tools menu option:

Screen Shot 2020-12-18 at 11.12.14 PM

Okay, let’s write some code. I’m using screenshots here as it’s easier to reference line numbers in the code, but I’ll provide the full code at the end so it can be copied/pasted.

I’m going to be making lots of API calls, so the first thing I’ll do is write a helper function just for that purpose. This will save me having to write the same few lines of code over and over (and over) again.

Line 1 defines the function name, and tells it to expect one parameter (url) when it is called.
Line 2 uses the URLFetchApp method to go get whatever is at the end of that URL
Line 3 extracts the contents of the response, encoded as a string
Line 4 uses the JSON.parse() method to convert that string into a javascript object
Line 5 sends the result back to where ever the function was called from
Simple, eh?

Okay, let’s get a deck of cards:

Line 9 defines the URL we need to call.
Line 10 uses the helper function to call the API and get the data, and we store the response in a variable called deck
Line 11 is deliberately left blank :stuck_out_tongue:
Line 12 gets a reference to the active spreadsheet…
Line 13 gets a reference to the ‘Deck’ sheet
Line 14 writes the data to the sheet… maybe I need to break that down a bit…
sheet.getRange(2,1,1,5) - here I’m defining a range of cells that I want to write my data to. Essentially that translates to “gimme a two dimensional range of cells, starting at row 2, column 1, that is 1 row high and 5 columns wide”

setValues([[deck.deck_id, deck.remaining, deck.shuffled, '', '']]); - and here I’m writing the data into that range.

getRange() returns an array of arrays, so I need to feed the same thing back to it.

One thing to note in the above is that the last two values in the array that I’m writing to the sheet are both empty ('',''). This is essentially reseting the trigger cells.

Clear as mud? :innocent:

Time to shuffle the deck!!

Line 19 - here I make use of one of the named ranges that I created earlier to get the value of my deck_id
Line 20 - construct the URL, using the deck_id
Line 21 - intentionally blank :yum:
Line 22 - go get the data
Line 23 - get a reference to my Deck sheet
Line 24 - write the data back to the sheet

The only thing that’s really changed here is that the deck is now shuffled (shuffled.shuffled) hmm, tautology? :face_with_monocle:

Now we can get some cards!

Lines 29-31 - get the deck_id, construct the URL, and fetch the data
Line 32 - looks innocent enough, but probably worth spending a few lines on. Let’s go back and look at the API response:

After line 31, that entire JSON response is stored in the variable I called, er, response. If I just want the array of cards from that object (which I do), then I need to dig into it a bit. And the way I do that is by using var cards = response.cards. Once I have that, I can use the cards variable to easily reference the attributes of each card (and save some typing). And that’s what goes on at lines 35-41 - where I’m writing the card data into the sheet.

And now we need some triggers…

Part 4 - Let's get Trigger Happy

Triggers are like little watch dogs, but even better, in that they will do stuff when you’re not around. Sometimes bad stuff, but mostly good - hopefully. Well I guess that watch dogs also do stuff when you’re not around, but more often than not it’s stuff that you didn’t actually want them to do. ie. bad stuff.

Earlier on, I defined some named ranges. Here is where I’m going to use them. The idea is that I’m going to make a trigger and tell it to keep an eye on certain cells (my named ranges), and if something changes then BOOM!!, spring into action. Here goes…

Line 1 - the name of the function isn’t important, but I’m going to use this as an ON CHANGE trigger, so I call it on_sheet_change. Keeps things nice and tidy :slightly_smiling_face:
Lines 3-4 - here I’m checking the value in my NewDeck named range (which is just a single cell), and if it’s not empty, then I’ll go and get a new deck of cards by calling my get_new_deck function
Line 5 - Return! Go back! Do not pass Go!
Lines 7-10 - same thing, except shuffle the deck
Lines 11-14 - once more, in case we need some new cards.

Activating the Trigger:

Look for the little clock icon in the Script Editor sidebar:

Click on the “Add Trigger” button (bottom right):
Screen Shot 2020-12-19 at 12.56.11 AM

And add your trigger:

The two important things are the name of the function that should be called, and the event type - which needs to be “On change”

Save that, and we’re ready to rock and roll!!

Part 5 - The Code

Here is all the code that was used…

    function get_api_response(url) {
      var response = UrlFetchApp.fetch(url);
      var json = response.getContentText();
      var data = JSON.parse(json);
      return data;
    }
    function get_new_deck() {
      var url = 'https://deckofcardsapi.com/api/deck/new/shuffle';
      var deck = get_api_response(url);
      
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName('Deck');
      sheet.getRange(2,1,1,5).setValues([[deck.deck_id, deck.remaining, deck.shuffled, '', '']]);
    }
    function shuffle_deck() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var deck_id = ss.getRangeByName('DeckID').getValue();
      var url = 'https://deckofcardsapi.com/api/deck/' + deck_id + '/shuffle/';

      var shuffled = get_api_response(url);
      var sheet = ss.getSheetByName('Deck');
      sheet.getRange(2,1,1,5).setValues([[shuffled.deck_id, shuffled.remaining, shuffled.shuffled, '', '']]);
    }
    function draw_cards() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var deck_id = ss.getRangeByName('DeckID').getValue();
      var url = 'https://deckofcardsapi.com/api/deck/' + deck_id + '/draw/?count=2';
      var response = get_api_response(url);
      var cards = response.cards;
      
      var sheet = ss.getSheetByName('Cards');
      sheet.getRange(2,1,1,9).setValues([[
        cards[0].code, cards[1].code,
        cards[0].value, cards[1].value,
        cards[0].suit, cards[1].suit,
        cards[0].image, cards[1].image,
        ''
      ]]);
      ss.getSheetByName('Deck').getRange(2,2).setValue(response.remaining);
    }
    function on_sheet_change() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      if (ss.getRangeByName('NewDeck').getValue() != '') {
        get_new_deck();
        return;
      }
      else if (ss.getRangeByName('Shuffle').getValue() != '') {
        shuffle_deck();
        return;
      }
      else if (ss.getRangeByName('Draw').getValue() != '') {
        draw_cards();
        return;
      }
    }
Part 6 - Acceptance Testing

So, the idea is that if we’ve done all this right we now have 3 “special” cells in our sheets, and if anything changes in any of those 3 cells then magic stuff will happen.

Here it is again with those three cells highlighted for easy reference :wink:

The Deck Sheet
Screen Shot 2020-12-19 at 1.03.01 AM

The Cards Sheet

Let’s see if it actually works. Time for a short video interlude.

Loom | Free Screen & Video Recording Software | Loom

Part 7 - The App (of sorts)

Not much to see here, except a link and a few screenshots.

It kinda works, but it’s clunky and slow because of the sync delay between Glide and the sheets.
But making a super slick app wasn’t the point, was it - Santiago? :slightly_smiling_face:
It’s copyable, so feel free to grab it and play around.

https://kindly-grass-8125.glideapp.io/

Part 8 - The End.

Finally, a couple of useful references if anyone is silly enough to want to learn more:

  1. Apps Script Spreadsheet Service Reference
  2. W3Schools Javascript Reference

Phew!

That ended up being a bit longer than I originally intended. If you made it this far, you deserve a medal.
Anyway, I hope that this has helped to in some way de-mystify things a bit. If not, at least it’s kept me out of trouble for a couple of hours :rofl:

Now, go back to building codeless apps!! :stuck_out_tongue:

40 Likes

@Darren_Murphy wow that was quick! Bookmarked to read & digest later. Thank you

2 Likes

Wow!! @Darren_Murphy! This is awesome! I will be trying this on my own.

I tried using scripts some time back and it did not go well. Let me try this again and actually learn to do it.

I appreciate it!

5 Likes

I now have a weekend project. You’re the best!

2 Likes

haha, have fun!
If you get stuck, you know where I am.
I owe you more than a few favours :grin:

1 Like

Have you been able to make heads/tails of the API column yet?

1 Like

Yes and no.
Yes in the sense that I understand what’s required to make use of it, but no I don’t see myself using it as it is.

When @Mark first posted I just kind of skimmed through the post and thought “this is cool”, without really reading properly. Especially his second post where he explained how to implement a compatible endpoint. It was only when Mark pointed out to me that you can’t just point it at any API and expect it to work that I went back and re-read that post - and the penny dropped.

To use it as it is, you either need to build your own API and endpoint that is compatible with Glide, or - if you want to make use of an existing API - build an interface to act as a proxy between that API and Glide. Either way, it involves some coding and an (under the hood) understanding of how API’s and web servers work. So, unless you’re prepared to do that (I’m not), then it has limited use right now. But it’s an exciting pre-cursor of things to come, there is no doubt of that.

In my case, I’m already comfortable working with API’s and getting data into GSheets using App Script, so I’ll just continue doing that for the time being. Of course the down side to this approach is that it’s not good if you need instant results in the app - which becomes painfully obvious if you take a look at the “app” that emerged from my tutorial :joy:

So being able to do this and have the data written directly to Glide will indeed be a game changer, but we’re not there yet (unless you’re willing to get your hands a bit dirty).

5 Likes

That’s what I’m currently doing—messing around with Glitch as a proxy—managed to get a couple APIs to work so far!

https://community.glideapps.com/t/please-help-us-test-api-column/19920/67?u=robert_petitto

1 Like

@Robert_Petitto - it just occurred to me that it might be possible use use a service like Integromat or Zapier as a proxy between Glide and some_random_api. I’ve never used Zapier and I’m only just getting started with Integromat, so I don’t know. But it may be worth exploring?

But that still leaves the question of how flexible the current Glide implementation is in terms of handling complex data structures - or even just a simple array of return values. In the examples we’ve seen so far from David and Mark it’s just making a call that returns a single value. In that sense, it’s not really any different to what we already do with Cloudinary or quickcharts.io. But what happens if your API returns a complex data structure that looks something like this? Can, or will Glide ever be able to handle that? That is where the true power comes in.

1 Like

Nice work!

In fact glitch is kind of a gift. I’m keeping quite for now. I’m no stranger to glitch; just waiting for the moment.

1 Like

hmm, maybe I need to take a closer look at glitch. I’d never heard of it until a couple of days ago…

1 Like

Thanks for this @Darren_Murphy!

This is great for doing API calls to retrieve and update data overnight and/or for future references, or even for Boolean-based use cases!

Integromat is awesome software - once you discover the power of the Tools module…combined with your knowledge of App Scripts and JSON…I can’t even imagine the next tutorial Santiago gets! :stuck_out_tongue_winking_eye:

(Zapier too, but Integromat is priced better and I prefer their interface structure, in my opinion.)

If Glide could integrate with Integromat it would definitely be a game-changer - but I think they’re already on their radar, so I’ll be patient :nerd_face:

3 Likes

For complex JSON structures, it would be awesome if glide created an entire separate glide table from the JSON object. They could set it up to pull input values from another sheet, then populate the results from the API in a separate table, complete with headings and data.

This is technically what I’m already doing with ImportJSON in the google sheet. I have a personal app where I get data from the API of a campground reservation system to populate an entire sheet with data from one of my favorite campgrounds. I then do some massaging of the data and filtering to find open camp sites for the next year, only on weekends, and only if 2 nights are available. This is a very popular campground and hard to get into if you don’t reserve a year in advanced. The great thing is that people cancel last minute quite often, so this gives me a leg up on jumping on those cancellations. It’s much easier than navigating the campground website and and having to page week by week to see what’s available. This gives me a quick view of what’s currently available over a long period of time and allows me to snag a campsite before anybody else does.

6 Likes

That would be pretty awesome, although if the object is flattened out into a single table then you’d potentially lose the internal relationships and hierarchies.

Maybe I’m dreaming, but what I’m envisaging is something that Mark alluded to, where you have Postman type functionality that allows you to predefine the API response structure within Glide, and then tell Glide where to direct the data. I’d like to think that’s the direction they’re heading with this, but even if that is the end goal then I’d imagine it’s a long ways to go.

1 Like

Fair point. I think how ImportJSON handles that is by still flattening the data, but allowing you to specify the noInherit parameter to hide or show values in subsequent rows from the parent in the heirarchy. So yes, it’s flat data, but with proper filters and self relations, I think you could still work with it. Messy, but I think still possible.

Maybe if Glide decided to go down a similar path, they could blow out the data into separate tables. Or maybe they could allow us to work with it as an object (object.parent{n}.child{n}.item{n}.value). It’ll be exciting to see how this all develops in the future.

2 Likes

I use n8n.io to do something very similar. I basically power my Glideapps Google Sheets through n8n.io.
The setup works really cool with unlimited flexibility

5 Likes

Thanks @Jeff_Hager. Very useful!

1 Like

Wish big for us all sir :smiley:

1 Like

Hi Santosh, I had a read on n8n about 1 or 2 months ago but felt like it would be a big problem if I have to hand over that to any customers, how do you feel about using n8n if you keep control of the automation process?

I see they just published a new n8n cloud, so they can do everything Zapier can now and the actions are stored on their cloud now?

Thanks in advance for your insights.