API Tutorial For Beginners With Google Sheets & Apps Script

In this API tutorial for beginners, you’ll learn how to connect to APIs using Google Apps Script, to retrieve data from a third-party and display it in your Google Sheet.

Example 1 shows you how to use Google Apps Script to connect to a simple API to retrieve some data and show it in Google Sheets:

API tutorial for beginners: Random math facts from Numbers API in Google Sheet

In Example 2, we’ll use Google Apps Script to build a music discovery application using the iTunes API:

Itunes API with Google Sheets

Finally, in example 3, I’ll leave you to have a go at building a Star Wars data explorer application, with a few hints:

Star Wars API explorer in Google Sheets using Google Apps Script

API tutorial for beginners: what is an API?

You’ve probably heard the term API before. Maybe you’ve heard how tech companies use them when they pipe data between their applications. Or how companies build complex systems from many smaller micro-services linked by APIs, rather than as single, monolithic programs nowadays.

API stands for “Application Program Interface”, and the term commonly refers to web URLs that can be used to access raw data. Basically, the API is an interface that provides raw data for the public to use (although many require some form of API authentication).

As third-party software developers, we can access an organization’s API and use their data within our own applications.

The good news is that there are plenty of simple APIs out there, which we can cut our teeth on. We’ll see three of them in this beginner api tutorial.

We can connect a Google Sheet to an API and bring data back from that API (e.g. iTunes) into our Google Sheet using Google Apps Script. It’s fun and really satisfying if you’re new to this world.

API tutorial for beginners: what is Apps Script?

In this API tutorial for beginners, we’ll use Google Apps Script to connect to external APIs.

Google Apps Script is a Javascript-based scripting language hosted and run on Google servers, that extends the functionality of Google Apps.

If you’ve never used it before, check out my post: Google Apps Script: A Beginnerā€™s Guide

Does coding fill you with dread? In that case, you can still achieve your goals using a no-code option to sync live data into Google Sheets. Check out Coefficient’s sidebar extension that offers Google Sheets connectors for CRMs, BI tools, databases, payment platforms, and more.

Example 1: Connecting Google Sheets to the Numbers API

We’re going to start with something super simple in this beginner api tutorial, so you can focus on the data and not get lost in lines and lines of code.

Let’s write a short program that calls the Numbers API and requests a basic math fact.

Step 1: Open a new Sheet

Open a new blank Google Sheet and rename it: Numbers API Example

Step 2: Go to the Apps Script editor

Navigate to Tools > Script Editor...

Access script editor through toolbar

Step 3: Name your project

A new tab opens and this is where we’ll write our code. Name the project: Numbers API Example

Step 4: Add API example code

Remove all the code that is currently in the Code.gs file, and replace it with this:

function callNumbers() {
  
  // Call the Numbers API for random math fact
  var response = UrlFetchApp.fetch("http://numbersapi.com/random/math");
  Logger.log(response.getContentText());
  
}

We’re using the UrlFetchApp class to communicate with other applications on the internet to access resources, to fetch a URL.

Now your code window should look like this:

Numbers API Google Apps Script code

Step 5: Run your function

Run the function by clicking the play button in the toolbar:

Run Apps Script button

Step 6: Authorize your script

This will prompt you to authorize your script to connect to an external service. Click “Review Permissions” and then “Allow” to continue.

Apps Script Review Permissions

Apps Script authorization

Step 7: View the logs

Congratulations, your program has now run. It’s sent a request to a third party for some data (in this case a random math fact) and that service has responded with that data.

But wait, where is it? How do we see that data?

Well, you’ll notice line 5 of our code above was Logger.log(....) which means that we’ve recorded the response text in our log files.

So let’s check it out.

Go to menu button Execution Log

You’ll see your answer (you may of course have a different fact):

[17-02-03 08:52:41:236 PST] 1158 is the maximum number of pieces a torus can be cut into with 18 cuts.

which looks like this in the popup window:

Apps script logger output

Great! Try running it a few times, check the logs and you’ll see different facts.

Next, try changing the URL to these examples to see some different data in the response:

http://numbersapi.com/random/trivia
http://numbersapi.com/4/17/date
http://numbersapi.com/1729

You can also drop these directly into your browser if you want to play around with them. More info at the Numbers API page.

So, what if we want to print the result to our spreadsheet?

Well, that’s pretty easy.

Step 8: Add data to Sheet

Add these few lines of code (lines 7, 8 and 9) underneath your existing code:

function callNumbers() {
  
  // Call the Numbers API for random math fact
  var response = UrlFetchApp.fetch("http://numbersapi.com/random/math");
  Logger.log(response.getContentText());
  
  var fact = response.getContentText();
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1,1).setValue([fact]);
  
}

Line 7 simply assigns the response text (our data) to a variable called fact, so we can refer to it using that name.

Line 8 gets hold of our current active sheet (Sheet1 of Numbers API Example spreadsheet) and assigns it to a variable called sheet, so that we can access it using that name.

Finally in line 9, we get cell A1 (range at 1,1) and set the value in that cell to equal the variable fact, which holds the response text.

Step 9: Run & re-authorize

Run your program again. You’ll be prompted to allow your script to view and manage your spreadsheets in Google Drive, so click Allow:

Apps Script Review Permissions

Apps script spreadsheet authorization

Step 10: See external data in your Sheet

You should now get the random fact showing up in your Google Sheet:

Random math fact from Numbers API in Google Sheet

How cool is that!

To recap our progress so far in this API Tutorial for Beginners: We’ve requested data from a third-party service on the internet. That service has replied with the data we wanted and now we’ve output that into our Google Sheet!

Step 11: Copy data into new cell

The script as it’s written in this API Tutorial for Beginners will always overwrite cell A1 with your new fact every time you run the program. If you want to create a list and keep adding new facts under existing ones, then make this minor change to line 9 of your code (shown below), to write the answer into the first blank row:

function callNumbers() {
  
  // Call the Numbers API for random math fact
  var response = UrlFetchApp.fetch("http://numbersapi.com/random/math");
  Logger.log(response.getContentText());
  
  var fact = response.getContentText();
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1,1).setValue([fact]);
  
}

Your output now will look like this:

Random math facts from Numbers API in Google Sheet

One last thing we might want to do with this application is add a menu to our Google Sheet, so we can run the script from there rather than the script editor window. It’s nice and easy!

Step 12: Add the code for a custom menu

Add the following code in your script editor:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Numbers API Menu')
      .addItem('Display random number fact','callNumbers')
      .addToUi();
}

Your final code for the Numbers API script should now match this code on GitHub.

Step 13: Add the custom menu

Run the onOpen function, which will add the menu to the spreadsheet. We only need to do this step once.

Add custom apps script menu

Step 14: Run your script from the custom menu

Use the new menu to run your script from the Google Sheet and watch random facts pop-up in your Google Sheet!

Use custom apps script menu

Alright, ready to try something a little harder?

Let’s build ourselves a music discovery application in Google Sheets.

Example 2: Music Discovery Application using the iTunes API

This application retrieves the name of an artist from the Google Sheet, sends a request to the iTunes API to retrieve information about that artist and return it. It then displays the albums, song titles, artwork and even adds a link to sample that track:

Google Sheets and iTunes API using Apps Script

It’s actually not as difficult as it looks.

Getting started with the iTunes API Explorer

Start with a blank Google Sheet, name it “iTunes API Explorer” and open up the Google Apps Script editor.

Clear out the existing Google Apps Script code and paste in this code to start with:

function calliTunes() {
  
  // Call the iTunes API
  var response = UrlFetchApp.fetch("https://itunes.apple.com/search?term=coldplay");
  Logger.log(response.getContentText());
}

Run the program and accept the required permissions. You’ll get an output like this:

iTunes API output

Woah, there’s a lot more data being returned this time so we’re going to need to sift through it to extract the bits we want.

Parsing the iTunes data

So try this. Update your code to parse the data and pull out certain bits of information:

function calliTunes() {
  
  // Call the iTunes API
  var response = UrlFetchApp.fetch("https://itunes.apple.com/search?term=coldplay");
  
  // Parse the JSON reply
  var json = response.getContentText();
  var data = JSON.parse(json);
  
  Logger.log(data);
  Logger.log(data["results"]);
  Logger.log(data["results"][0]);
  Logger.log(data["results"][0]["artistName"]);
  Logger.log(data["results"][0]["collectionName"]);
  Logger.log(data["results"][0]["artworkUrl60"]);
  Logger.log(data["results"][0]["previewUrl"]);
  
}

Line 4: We send a request to the iTunes API to search for Coldplay data. The API responds with that data and we assign it to a variable called response, so we can use that name to refer to it.

Lines 7 and 8: We get the context text out of the response data and then parse the JSON string response to get the native object representation. This allows us to extract out different bits of the data.

So, looking first at the data object (line 10):

iTunes api data packet

You can see it’s an object with the curly brace at the start {

The structure is like this:

{
resultCount = 50,
results = [ ....the data we're after... ]
}

iTunes api data packet

Line 11: we extract the “results”, which is the piece of data that contains the artist and song information, using:

data["results"]

Line 12: There are multiple albums returned for this artist, so we grab the first one using the [0] reference since the index starts from 0:

data["results"][0]

This shows all of the information available from the iTunes API for this particular artist and album:

iTunes api data results

Lines 13 – 16: Within this piece of data, we can extract specific details we want by referring to their names:

e.g. data["results"][0]["collectionName"]

to give the following output:

iTunes api details

Use comments (“//” at the start of a line) to stop the Logger from logging the full data objects if you want. i.e. change lines 10, 11 and 12 to be:

// Logger.log(data);
// Logger.log(data[“results”]);
// Logger.log(data[“results”][0]);

This will make it easier to see the details you’re extracting.

Putting this altogether in an application

If we want to build the application that’s showing in the GIF at the top of this post, then there are a few steps we need to go through:

  • Setup the Google Sheet
  • Retrieve the artist name from the Google Sheet with Google Apps Script
  • Request data from iTunes for this artist with Google Apps Script
  • Parse the response to extract the relevant data object with Google Apps Script
  • Extract the specific details we want (album name, song title, album artwork, preview url)
  • Clear out any previous results in the Google Sheet before showing the new results
  • Display the new results in our Google Sheet
  • Add a custom menu to run the program from the Google Sheet, not the script editor

It’s always a good idea to write out a plan like this before you commit to writing any lines of code.

That way you can think through the whole application and what it’s going to do, which allows you to make efficient choices with how you setup your code.

So the first thing to do is setup a Google Sheet. I’ll leave this up to you, but here’s a screenshot of my basic Google Sheet setup:

iTunes Google Sheet

The important thing to note is the location of the cell where a user types in the artist name (11th row, 2nd column) as we’ll be referring to that in our code.

iTunes API Explorer code

And here’s the Google Apps Script code for our application:

// --------------------------------------------------------------------------------------------------
//
// iTunes Music Discovery Application in Google Sheets
//
// --------------------------------------------------------------------------------------------------

// custom menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom iTunes Menu')
      .addItem('Get Artist Data','displayArtistData')
      .addToUi();
}

// function to call iTunes API
function calliTunesAPI(artist) {
  
  // Call the iTunes API
  var response = UrlFetchApp.fetch("https://itunes.apple.com/search?term=" + artist + "&limit=200");
  
  // Parse the JSON reply
  var json = response.getContentText();
  return JSON.parse(json);
  
}


function displayArtistData() {
  
  // pick up the search term from the Google Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  var artist = sheet.getRange(11,2).getValue();
  
  var tracks = calliTunesAPI(artist);
  
  var results = tracks["results"];
  
  var output = []
  
  results.forEach(function(elem,i) {
    var image = '=image("' + elem["artworkUrl60"] + '",4,60,60)';
    var hyperlink = '=hyperlink("' + elem["previewUrl"] + '","Listen to preview")';
    output.push([elem["artistName"],elem["collectionName"],elem["trackName"],image,hyperlink]);
    sheet.setRowHeight(i+15,65);
  });
  
  // sort by album
  var sortedOutput = output.sort( function(a,b) {
    
    var albumA = (a[1]) ? a[1] : 'Not known';  // in case album name undefined 
    var albumB = (b[1]) ? b[1] : 'Not known';  // in case album name undefined
    
    if (albumA < albumB) { return -1; } else if (albumA > albumB) {
      return 1;
    }
    // names are equal
    return 0;
  });
  
  // adds an index number to the array
  sortedOutput.forEach(function(elem,i) {
    elem.unshift(i + 1);
  });
  
  var len = sortedOutput.length;
  
  // clear any previous content
  sheet.getRange(15,1,500,6).clearContent();
  
  // paste in the values
  sheet.getRange(15,1,len,6).setValues(sortedOutput);
  
  // formatting
  sheet.getRange(15,1,500,6).setVerticalAlignment("middle");
  sheet.getRange(15,5,500,1).setHorizontalAlignment("center");
  sheet.getRange(15,2,len,3).setWrap(true);
  
}

Here’s the iTunes API script file on GitHub.

How it works:

Let’s talk about a few of the key lines of code in this program:

Lines 16 – 25 describe a function that takes an artist name, calls the API with this artist name and then returns the search results from the API. I’ve encapsulated this as a separate function so I can potentially re-use it elsewhere in my program.

The main program starts on line 28.

On line 34, I retrieve the name of the artist that has been entered on the Google Sheet, and we call our API function with this name on line 36.

On lines 42 – 47, I take the results returned by the API, loop over them and pull out just the details I want (artist name, album name, song title, album artwork and preview track). I push all of this into a new array called output.

Next I sort and add an index to the array, although both of these are not mandatory steps.

On line 68, I clear out any previous content in my sheet.

Then on line 71, I paste in the new data, starting at row 15.

Finally, lines 74 – 76 format the newly pasted data, so that the images have space to show properly.

Run the onOpen() function from the script editor once to add the custom menu to your Google Sheet. Then you’ll be able to run your iTunes code from the Google Sheet:

Custom iTunes API menu

Now you can run the program to search for your favorite artist!

More details on the iTunes API:

Documentation for searching the iTunes Store.

Documentation showing the search results JSON packet.

Example 3: Star Wars data explorer using the Star Wars API

This one is a lot of fun! Definitely the most fun example in this API Tutorial for Beginners.

The Star Wars API is a database of all the films, people, planets, starships, species and vehicles in the Star Wars films. It’s super easy to query and the returned data is very friendly.

Star Wars API in Google Sheet

It’s a little easier than the iTunes API because the data returned is smaller and more manageable, and therefore easier to parse when you first get hold of it.

Getting started with the Star Wars API

As with both the previous APIs, start with a simple call to see what the API returns:

/*
 * Step 1:
 * Most basic call to the API 
 */
function swapi() {
  
  // Call the Star Wars API
  var response = UrlFetchApp.fetch("http://swapi.dev/api/planets/1/");
  Logger.log(response.getContentText());
}

The data returned looks like this:

API Tutorial for Beginners: Star Wars API data

So, it’s relatively easy to get the different pieces of data you want, with code like this:

/*
 * Step 2:
 * Same basic call to the API 
 * Parse the JSON reply
 */
function swapi() {
  
  // Call the Star Wars API
  var response = UrlFetchApp.fetch("http://swapi.dev/api/planets/1/");
  
  // Parse the JSON reply
  var json = response.getContentText();
  var data = JSON.parse(json);
  Logger.log(data);
  Logger.log(data.name);
  Logger.log(data.population);
  Logger.log(data.terrain);
}

Well, that should be enough of a hint for you to give this one a go!

Some other tips

In addition to custom menus to run scripts from your Google Sheet, you can add Google Sheets buttons and connect them to a script to run the script when they are clicked. That’s what I’ve done in this example.

On the menu, Insert > Drawing...

Google Sheets insert drawing

Create a button using the rectangle tool:

Google Sheet drawing tool

Finally, right click the drawing when it’s showing in your sheet, and choose Assign Script and type in the name of the function you want to run:

Google Sheet drawing assign script

What else?

Use this CHAR formula to add stars to your Google Sheet:

=CHAR(9734)

I used the font “Orbitron” across the whole worksheet and, whilst it’s not a Star Wars font, it still has that space-feel to it.

The big Star Wars logo is simply created by merging a bunch of cells and using the IMAGE() formula with a suitable image from the web.

Finally, here’s my SWAPI script on GitHub if you want to use it.

API Tutorial for Beginners: Other APIs to try

Here are a few other beginner-friendly APIs to experiment with:

> Giphy API. Example search endpoint: Funny cat GIFs

> PokƩmon API. Example search endpoint: Pokemon no. 1

> Open Movie Database API. Example search endpoint: Batman movies

> International Space Station Current Location. Example search endpoint: ISS current location

Also, here’s the official Google documentation on connecting to external APIs.

Finally, here’s a syntax guide for the common forms of API Authentication using Apps Script.

Let me know in the comments what you do with all these different APIs!

Disclosure: Some of the links in this post are affiliate links, meaning Iā€™ll get a small commission if you click the link and subsequently signup to use that vendorā€™s service. I only do this for tools I use myself and wholeheartedly recommend.

100 thoughts on “API Tutorial For Beginners With Google Sheets & Apps Script”

  1. I changed the url from the script as follows:
    UrlFetchApp.fetch(“http://numbersapi.com/random/date”)
    in a hope to see what happened in that date, but the result was still the math related calculation. Should I modify another script? Thanks.

  2. Hi Ben,
    Please forget my previous comment. I can see it properly now without changing anything else. Thanks.
    Regards,
    Julian

  3. Hi Ben,

    Another question to you. Regarding the iTunes API script , it’s fine with function calliTunesAPI( ), however, I got the following error message when run the function displayArtistData( )
    The coordinates or dimensions of the range are invalid. (line 76, file “Code”)
    Could you please tell me what happened? Thanks.

    1. Hi Julian!

      Sounds like the size of your range in sheet.getRange(15,1,len,6) is not matching the size of the output data array you’re trying to paste in. The code wants to paste in values starting at the 15th row and the 1st column, and then paste in data that is len (equal the size of the output array) rows down and 6 columns wide.

      So you need to ensure that your output data matches these dimensions. The len should be ok because it’s based off your output array, so maybe the width is wrong and not equal to 6? Are you adding the index number at the start of your array?

      (this is from the lines:
      sortedOutput.forEach(function(elem,i) {
      elem.unshift(i + 1);
      });

      )

      Hope that helps. Feel free to share your sheet here.

      Thanks,
      Ben

  4. Hi Ben,
    I made a serious mistake without putting any artist name in the Range(11,2). That’s the reason why the mentioned error message showed up. However, When I wrote down “Serah” trying to get my favorite 3 albums from her, a lot of unrelated results also in the list. How can I modify the script to get the results more accurately?
    Thanks,
    Julian

  5. Hi there,

    Have you possibly seen SheetSU. It creates an API url to simply include in your mobile app code.

    Question is, how can we reference your examples from a mobile app.

    1. Hey Ian,

      Thanks for sharing. I’ve heard of Sheetsu before, but never tried it out. It’s a very cool concept. I think it demonstrates another great use case for Google Sheets for quick prototyping. I do intend to check it out at some stage (full plate at the moment), and I’m sure it would make for an interesting case study to write about here.

      Cheers,
      Ben

  6. Ben, this is amazing content. As always, very well done. Do you have tips/tutorials for doing the same thing with a service that requires OAuth 2.0 authentication? For instance, I would love to fetch my running data from TomTom Sports and set those values in a Google Spreadsheet. However, I am struggling with setting up the OAuth 2.0 steps in Google Scripts.

      1. Hi, Ben! Any update on the AOAuth2 blog post? I have looked at the link you provided to the blogger api example, but much prefer your style of writing and explanation to understand it.

        Thanks!

  7. Hi Ben,

    I have tried this great tutorial about api’s. I have tried to make my code to get a price from a site. This is my script :

    function callNumbers() {

    // Call the Numbers API for random math fact
    var response = UrlFetchApp.fetch(“https://api.coinmarketcap.com/v1/ticker/bitcoin/?convert=EUR”);
    var json = response.getContentText();
    var data = JSON.parse(json);
    Logger.log(data);
    Logger.log(data.price_eur);

    }

    When I open the log I get as answer for the price_eur undefined.

    [17-06-11 16:19:24:386 CEST] [{price_usd=2894.94, symbol=BTC, last_updated=1497190466, 24h_volume_eur=1754584216.0, total_supply=16385025.0, 24h_volume_usd=1964380000.0, market_cap_eur=42367748929.0, price_btc=1.0, available_supply=16385025.0, market_cap_usd=47433664274.0, percent_change_1h=-0.26, percent_change_24h=2.15, name=Bitcoin, rank=1, id=bitcoin, percent_change_7d=14.92, price_eur=2585.760408}]
    [17-06-11 16:19:24:386 CEST] undefined

    I don’t see what I’m doing wrong.

    John

    1. Hello Ben,

      I have found the problem :
      I had to add [0] to the last logger.log

      Logger.log(data[0].price_eur);

      Then it works.

      Kind regards,

      John

  8. Hi Ben,

    Great post! Have you done any posts that cover how to implement API authentication in spreadsheets?

    1. Not yet! Great question though, and I’m working on an OAuth API post at the moment, which I’m planning to publish this month.

      Cheers,
      Ben

  9. An API I’m trying to connect to wants a username and API key (password) sent via http headers, how would I go about adding those to get past the basic security?

    1. Hey Henry,

      You’ll want to include your API key in your headers, so your code will look something like this:

      // URL and params for the API
      var url = 'https://api.example.com/';

      var params = {
      'method': 'GET',
      'muteHttpExceptions': true,
      'headers': {
      'Authorization': 'apikey ' + apikey
      }
      };

      // call the API
      var response = UrlFetchApp.fetch(url, params);
      var data = response.getContentText();
      var json = JSON.parse(data);

      Hope that helps. Which API are you connecting to? There might be something in the documentation.

      Cheers,
      Ben

      1. Hi Ben,

        This tutorial is amazing. Straight forward, actionable examples. Clear at explaining each line of code. If only more tutorials would be like yours.

        I have a question regarding pulling data from a 3rd party service. They say I need to use GET Requests.

        It does involve app key, token, etc. But they have a sandbox account, and I’m trying to work with that.

        Does Google Apps scripts allow for GET requests?

        Thanks!

      2. Hey Ben,

        This reply really helped, but I getting another error after trying what you suggested “ReferenceError: “apikey” is not defined. (line 4, file “Code”)”

        I believe my error is here:

        “‘Authorization’: ‘apikey ‘ + apikey”

        So, what would I put for each “apikey”? Would it be Username for the first one and then the apikey, for the second one?

          1. Hey Ben,

            Thanks for the fast reply. I did give that URL a read and I now have a better understanding of what is happening. Downside, I’m not getting this error in the logs:

            {
            “fault”: {
            “faultstring”: “Invalid ApiKey”,
            “detail”: {
            “errorcode”: “oauth.v2.InvalidApiKey”
            }
            }
            }

            I’m going to double check the dev documentation on my end to make sure I’m not missing anything obvious but my instinct is that I’m not going to find much, it’s not very intuitive documentation. But if you have any other recs/ideas let me know.

            Thanks,
            Nate

      3. it returns this error
        ReferenceError: “apikey” is not defined. (line 6, file “Code”)
        and where do i put my API key code in that code?
        thank you very much for your help.

  10. This is great thank you!!!
    I’ve got everything up and running, except the API I am calling has a page_size parameter with a max of 100. I want to pull all 4300 results into the sheet (43 pages). Is there a way to loop through the UrlFetchApp.fetch function so I can call ?page=2, ?page=3, ?page=4, etc…

    1. Yes, exactly.

      You’ll need a counter to keep track of the page_size parameter, and then you keep looping until you get an api call back with nothing. You can use a loop:

      while ( // condition is true ) { // do url fetch stuff and set page_param = page_param + 100 for next loop }

      or

      do { // do url fetch stuff and set page_param = page_param + 100 for next loop } while ( // condition is true )

      Hope that helps!

      Cheers,
      Ben

      1. Hi Ben. I was trying to make the while function but i’m a total beginner… can you explain a bit more how to put the while function in the overall app?

  11. Hi Ben,
    Don’t you have an example showing how to connect Google Sheets to a SOAP API?
    I’ve been trying for a while to connect my personal google sheet to a MantisBT webservices, but without success.. šŸ™
    Thanks in advance,
    Claudio.

      1. Hi Ben
        I am searching for a week but cant success in getting working code of google sheet soap api working code

        It will be great if you make some turtorial just like above

        Thank you
        Abdul Samad

  12. Hi Ben,

    Thanks for creating this article. I have learnt so much from this already.

    I am trying to integrate Zendesk with sheets API and I am failing to pass the basic authentication (username and password). Could you please help me with that?

    P.S: This is my first attempt with programming of any kind.

  13. Hi Ben

    thanks for this great post.
    I’ve tried to use a GET API which requires JSON input in the URL (123.site/api?input={“id”:”1″}) but it gives me #ERROR in response.

    do you have any idea how to get and handle the response from such an API in gSheets ?

    regards
    Matt

  14. Great tutorial. You give a detailed breakdown of each step. That is very helpful and hard to find. Thanks. I’ve tried to follow the methods you used here but I’m having some problem. The data I receive back from the fetch is already formatted as JSON.

    [18-07-30 10:29:59:272 EDT] {
    “Meta Data”: {
    “1: Symbol”: “pg”,
    “2: Indicator”: “Moving Average Convergence/Divergence (MACD)”,
    “3: Last Refreshed”: “2018-07-30 10:29:55”,
    “4: Interval”: “daily”,
    “5.1: Fast Period”: 12,
    “5.2: Slow Period”: 26,
    “5.3: Signal Period”: 9,
    “6: Series Type”: “close”,
    “7: Time Zone”: “US/Eastern”
    },
    “Technical Analysis: MACD”: {
    “2018-07-30 10:29:55”: {
    “MACD_Signal”: “0.7723”,
    “MACD”: “0.7578”,
    “MACD_Hist”: “-0.0144”
    },
    Code:
    var response = UrlFetchApp.fetch(url);
    var result = response.getContentText();
    //var data = JSON.parse(result);
    Logger.log(result[“Technical Analysis: MACD”]);
    Logger.log(result[“Meta Data”]);
    Logger.log(result);

    The 2 Logger attempts above for “Technical Analysis and “Meta Data” come back as undefined, although they are clearly part of the results. The results come back as the above-formatted JSON data. When I do the JSON parse I end up with the block of data below but none of it has parentheses.

    [18-08-02 14:56:06:762 EDT] {Technical Analysis: MACD={2014-07-01={MACD_Signal=16.0383, MACD_Hist=-0.2964, MACD=15.7418}, 2014-07-02={MACD_Signal=16.1276, MACD_Hist=0.3575, MACD=16.4851}, 2002-12-10={MACD_Signal=0.4882, MACD_Hist=0.1072, MACD=0.5955},

  15. Ben – nice code, good work.
    I’m looking for an example using a PUT or PATCH API where the sheet data is the input as a JSON object. I haven’t figured that one out yet… any pointers?
    Thanks,
    Fred

    1. I am looking for exactly the same thing. I have a long list of items and I need to update a certain key for all those items. I need to use patch for this and add the right values for that key on each item. Trying to figure out how to go about this.

  16. Hi Ben,

    This is the first time I’ve seen a tutorial for api beginners and it was very easy to understand with your method of teaching. Thanks a lot!

    I’m now making a integration of Mailshake and google sheets. I’ve seen your mailchimp and google sheet integration and I thought that it’s quite similar so I followed the process in coding.
    Only, as I run the function it prompts me with the error: SyntaxError: Unexpected token: < (line 18, file "Code"). I'm having a problem in var json = JSON.parse(data); which the line 18. Do you have any solution for this? Thanks in advance.

    Here's the full code I wrote:
    var API_KEY = 'erased';

    function mailshakeList() {

    var url = 'https://api.mailshake.com/2017-04-01&#039;
    var endpoint = 'list?count=100'

    var params = {
    'method': 'GET',
    'muteHttpExceptions': true,
    'headers': {
    'Authorization': 'apikey ' + API_KEY
    }
    };

    var response = UrlFetchApp.fetch(url+endpoint, params)
    var data = response.getContentText();
    var json = JSON.parse(data);

    Logger.log(data);
    }

    I can also give the api key if you want to try the integration of mailshake and google sheets.

    Regards,
    Rodel

  17. Hi,

    I’m new to API’s and the Script Editor for Google. I’m trying to pull information for a project related to the English Premier League. My log output looks something like:

    [18-09-06 12:52:28:526 EDT] {“api”:{“results”:380,”fixtures”:{“65”:{“fixture_id”:”65″,”event_timestamp”:”1533927600″,”event_date”:”2018-08-10T19:00:00+00:00″,”league_id”:”2″,”round”:”Premier League – 1″,”homeTeam_id”:”33″,”awayTeam_id”:”46″,”homeTeam”:”Manchester United”,”awayTeam”:”Leicester”,”status”:”Match Finished”,”statusShort”:”FT”,”goalsHomeTeam”:”2″,”goalsAwayTeam”:”1″,”halftime_score”:”1 – 0″,”final_score”:”2 – 1″,”penalty”:null,”elapsed”:”95″,”firstHalfStart”:”1533927660″,”secondHalfStart”:”1533931380″}

    and my code is:

    var response = UrlFetchApp.fetch(“https://api-football-v1.p.mashape.com/fixtures/league/2”,options)
    var json = response.getContentText();
    var data = JSON.parse(json);
    //Logger.log(response.getContentText());
    Logger.log(data);
    Logger.log(data[“api”][“results”]);
    Logger.log(data[“api”][“results”][0][“event_date”]);
    Logger.log(data[“api”][“results”][0][“round”]);
    Logger.log(data[“api”][“results”][0][“homeTeam”]);
    Logger.log(data[“api”][“results”][0][“awayTeam”]);
    Logger.log(data[“api”][“results”][0][“goalsHomeTeam”]);
    Logger.log(data[“api”][“results”][0][“goalsAwayTeam”]);

    I keep getting “TypeError: Cannot read property…” errors. I’m really unsure how to proceed from here. It always hits me right when I try to do the 3rd logger.log line where I try to log the event_date.

    At the end of the day, I just want an output of all available results with those datapoints listed above.

    Any thoughts/ideas of where I’m going wrong?

    1. Try adding the initial index to the Log.

      Logger.log(data);
      Logger.log(data[ā€œapiā€][ā€œresultsā€]);

      // The Line Below
      Logger.log(data[ā€œapiā€][ā€œresultsā€][0]);
      // The Line Above

      Logger.log(data[ā€œapiā€][ā€œresultsā€][0][ā€œevent_dateā€]);

    2. Hi Andrew,

      The issue seems to be related to the fact, that your response is not organzised within arrays [] as the example above are. As Im facing a similar issue I was wondering, if you were able to solve the issue and whether you could share? Many Thank

  18. Great tutorial, Ben! Lots of good info for a newb like myself. I tried doing the first tutorial and replaced the math trivia urls with other urls, and my issue is the function is trying to put all the data into cell A1. It is errors out because the data is in excess of 5k characters. How do I correct?

  19. Ben, I have a Google app script attached to a button on a Google Site. (Let’s call that script “A”.) When user presses button, it runs a script that opens a Google Sheet. On that sheet, I also have a custom menu (bound script – script “B”) that has a menu item that adds a row to the bottom of the sheet.

    What I want to happen is that when user clicks the Google Site button, the sheet not only opens (A’s operation), but a row is added (B’s operation). But, I can’t figure out how to trigger B from A.

    There is an option in a Google script to make the script an executable API (which is why I found your page here).

    It sounds like from your discussion above that APIs are really designed to extract data. But can they also be used to run scriptsā€”in this case a Google script? Or am I barking up the wrong tree (because I can’t figure out how to trigger script B from within script A).?

    Thanks for any help you can give me.

  20. Brilliant tutorial

    I have an API account ID and Key that i need to send with the URLfetch somehow, specifically with google sheets

    Can you help?

  21. Hi Ben,

    I want to read a google sheet api using Restful service. I can use” Api Key” or “Service Account” but I don’t want a pop up for authorization. How can I do that?

    Thanks in Advance.
    Sachin

  22. Hi Ben, I hope you still read this šŸ™‚

    I need to get my data from an API, (yeah :p), The API is only giving curl example, like that:
    curl https://api.cliniko.com/v1/users/1 \
    -u API_KEY: \
    -H ‘Accept: application/json’ \
    -H ‘User-Agent: APP_VENDOR_NAME (APP_VENDOR_EMAIL)’

    I transferred it into JS to use with the Google Scripts like that:
    fetch(“https://api.cliniko.com/v1/appointments”, {
    headers: {
    Accept: “application/json”,
    Authorization: “Basic API_KEY_HERE”,
    “User-Agent”: “GoogleAppsScript (me@gmail.com)”
    }
    })

    I do absolutely need the accept headers and the user-agent thing.
    The complete scripts I made is that:
    function dataOut() {

    var response = UrlFetchApp.fetch(“https://api.cliniko.com/v1/users/1”, {
    headers: {
    Accept: “application/json”,
    Authorization: “Basic MY-API-KEY”,
    “User-Agent”: “GoogleAppsScript (me@gmail.com)”
    }
    });
    Logger.log(response.getContentText());
    };

    It returns me a 401 which is: 4xx range indicate an error resulting from the provided information (eg. missing a required parameter).
    Please, any help will save my life here!
    Thank you very much

  23. This is fantastic and chillingly on time. Thank you Ben! I was building on your tutorial and ran to a halt when I had to name a column. Is there no way to name a column through Apps Script?

  24. Oh, that was pretty obvious: I need to enter a value into the range where row is 1. If my previous comment cannot be removed, please accompany it with this one. Thank you.

  25. Hi Ben,

    this is amazing post, can you suggest on how to authenticate external API using Token based authentications.

  26. I have the exact same question as Ravi. I’m trying to make use of a REST API with AAD token access. Do you have another post on this topic?

  27. I’m a mobile developer and needed something like this to test and share my database. Thanks – this is an excellent tutorial and your explanations are very good.

  28. Firts of all thanks for your help. this is an awesome tutorial.
    but i want to know how to fetch customer list url of microsoft dynamic navision in google sheet

  29. Is it possible the post the data to a third party API which in turn can consume the value – say for example, an email address in a particular cell

  30. Hiļ¼ŒBen

    Many thanks for teaching me a lot about App Script and Googlesheet.

    When I test the last sample Stat wars, I met the problem:”TypeError: undefined……ā€œforEachā€line 207 “. The code position about the end of the codes:

    data.forEach(function(item) {
    var itemName = (category == “films”) ? item.title : item.name;
    names.push([itemName]);
    });

    There is nothing in googlesheet , no black color no any data.

    Need Help! Thank you!

  31. Awesome post!

    Do you have an easy way to update the data at fixed intervals (e.g. daily, weekly), without having to press the update button?

    I’m trying to utilize this approach. The backend data updates regularly, and I would prefer an auto-update feature.

    Many thanks!

  32. Hey Ben!

    This was a really useful and easy to understand tutorial. I’m trying to apply the same methodology to an image folder to parse the individual file URL’s.

    Do you know if that is possible?

    I’ve started with the log, but can’t seem to identify the individual image URL’s in the logger data as you would with itunes and the albums.

    Thanks so much!
    Belle

  33. Hi ben, your post is really great!!
    I want to ask how to connect, reply and bot reply, and sent data from spreadsheet to line (mobile apps), since they have kind of line@, bots i think its really great to use it for works

    hope your reply soon

    Andi

  34. Great post! Another awesome one is https://sheet2api.com. It gives your the API connected to Google/Excel sheets without needing to do any coding. We’re using it at work to create dynamic mock-ups.

  35. Holy shit, the work and effort in your explanations. I’ve learned so much in the last hour doing this!

    Thank you šŸ™‚

  36. I have very little experience working with APIs and am trying to connect to an online database that requires a username and password to login, as well as an API key to access specific reports. Excel and Power BI make it pretty easy to make the initial login connection but I can’t seem to find any information on how to do this in sheets in order to import data from the database. Since the online database is a little primitive in its reporting, my ultimate goal is to create a dynamic dashboard.

  37. Hallo Ben,
    First: thanks for the post! So clear and clean . . . really congratulation for your teaching method.

    Then my question: I’ve seen that the urlfetch function has limitations for the number of call/day. I However received the following error after a while I was playing with it:
    “Exception: Service invoked too many times for one day: urlfetch.”

    I’ve seen the limit should be 20K/day (https://developers.google.com/apps-script/guides/services/quotas) and I think I did maybe some 10x playing with the tutorial examples . . . . did you have any idea from where this Exception could come from?

    1. Don’t mind at my above request Ben. Just discovered another google sheet example I’ve imported from another source was continuosly updating itself generating thousands of requests.
      For anyone that might be interested I discovered it in this way: go to the script Dashboard (from the script window click on the big blue arrow on the left of the file menu) and then click on My Execution from left menu.
      Thanks anyway.

  38. Thanks for this tutorial.
    I am trying to log only “goals” from the following JSON but cannot do it. any ideas? i have tried Logger.log(data[“splits”][“stat”][“goals”]); but get: Error TypeError: Cannot read property ‘stat’ of undefined
    update @ Code.gs:23

    Info {copyright=NHL and the NHL Shield are registered trademarks of the National Hockey League. NHL and NHL team marks are the property of the NHL and its teams. Ā© NHL 2021. All Rights Reserved., stats=[{splits=[{stat={shortHandedGoals=0.0, shortHandedTimeOnIcePerGame=01:59, powerPlayPoints=0.0, faceOffPct=0.0, evenTimeOnIce=38:00, plusMinus=3.0, powerPlayTimeOnIcePerGame=02:51, powerPlayGoals=0.0, shortHandedTimeOnIce=05:59, penaltyMinutes=4, shots=10.0, shortHandedPoints=0.0, games=3.0, overTimeGoals=0.0, shifts=69.0, powerPlayTimeOnIce=08:34, timeOnIce=52:33, timeOnIcePerGame=17:31, assists=2.0, evenTimeOnIcePerGame=12:40, goals=1.0, gameWinningGoals=0.0, shotPct=10.0, blocked=1.0, pim=4.0, points=3.0, hits=3.0}, season=20202021}], type={gameType={id=R, postseason=false, description=Regular season}, displayName=statsSingleSeason}}]}

  39. Such a great tutorial! I always recommend this blog post to our users at Apipheny (https://apipheny.io) who are getting started with the Google Sheets API. Will also be linking to this tutorial from one of our upcoming posts on our blog! Great work as always, Ben : )

  40. Noob here, just trying to understand APIs… I want to basiclly get the result of this api call into a Google Sheet using this url:

    https://api.coronavirus.data.gov.uk/v1/data?filters=areaType=nation;areaName=wales&structure={“date”:”date”,”areaName”:”areaName”,”areaCode”:”areaCode”,”newPeopleVaccinatedFirstDoseByPublishDate”:”newPeopleVaccinatedFirstDoseByPublishDate”,”newPeopleVaccinatedSecondDoseByPublishDate”:”newPeopleVaccinatedSecondDoseByPublishDate”}

    …but alas, I just get ‘Error formula parse error’ when I use the =importJSON formula and paste in the above – any idea what I’m doing wrong?

  41. Thanks for this excellent content. Are there some genetric guidelines for authenticating 3rd party APIs in Google Scripts where that is required?

    I want to connect an API. I have everything, but just need to find out where the username and password should be placed. I’ve seen various blogs about oAuth, but they all involve specific instances like GitHub. Also is HTTP authorisation a viable method? If so where would I place the script?

  42. Good day

    Hi, I am using an google sheet API I managed to pull the data from my sheet but I need a way if is there to pull a color from a cell, I have different cells with different colors, can anyone help

  43. what I am trying to do is to pull the data from google to html page.
    please find me code down

    this is my code.gs

    function doGet(e) {
    var htmlOutput = HtmlService.createTemplateFromFile(‘DisplaySheet’);
    return htmlOutput.evaluate();
    }

    function getSheetData() {
    var ss= SpreadsheetApp.getActiveSpreadsheet();
    var dataSheet = ss.getSheetByName(‘sheet name’);
    var dataRange = dataSheet.getDataRange();
    var dataValues = dataRange.getValues();
    return dataValues;
    }

    And this is my display

    Name

    <?for(var i = 0; i

    <?for(var j = 0; j

    <?for(var j = 0; j

  44. So, new to this, but I have an element in my response that containst two phone numbers:

    phone_numbers=[+15555555555, +15555555556]

    However, with:

    var output = [];
    items.forEach(function(elem,i) {
    output.push([elem[“first_name”],elem[“last_name”],elem[“emails”],elem[“phone_numbers”]…);

    I only get one phone number from the output. What am I missing?

  45. He Ben!

    Love your tutorials, very informative. Im new to this and trying to figure out how to use the Flaticon API to find icons for a SlidesApp. Unfortunately no success with the authentication and searching yet..

    Tom

Leave a Reply

Your email address will not be published. Required fields are marked *