How To Use The IMPORTRANGE Function In Google Sheets To Transfer Data From One Sheet To Another

Since Google Sheets are files in the cloud, not on your desktop, you can’t click on a cell in a different Sheets file to connect them.

Instead, you use the IMPORTRANGE function in Google Sheets to connect Google Sheet files and import data from one Sheet file into another.

Once set up, the function will automatically sync with the source data so that changes are reflected in the destination Sheet.

IMPORTRANGE In Google Sheets

If you look closely, you’ll see a URL in the formula — the URL of the source Google Sheet file, where the data is being imported from.

Continue reading How To Use The IMPORTRANGE Function In Google Sheets To Transfer Data From One Sheet To Another

How to import social media statistics into Google Sheets: The Import Cookbook

[Editor’s Note 2024: unfortunately, most of these solutions, which are 4+ years old, no longer work. Modern websites are built dynamically in the browser with JavaScript, and Google Sheets IMPORT functions no longer work in this case. If you’re still looking for reliable formula-style importing capabilities, check out the third-party tool IMPORTFROMWEB.

I’m leaving this article here for reference purposes as it shows interesting examples of XPath constructions.]

Google Sheets has a powerful and versatile set of IMPORT formulas that can import social media statistics.

This article looks at importing social media statistics from popular social media channels into a Google sheet, for social network analysis or social media management. If you manage a lot of different channels then you could use these techniques to set up a master view (dashboard) to display all your metrics in one place.

Contents

  1. Facebook
  2. Twitter
  3. Instagram
  4. Youtube
  5. Pinterest
  6. Alexa rank
  7. Quora
  8. Reddit
  9. Spotify
  10. Soundcloud
  11. GTmetrix
  12. Bitly
  13. Linkedin
  14. Sites that don’t work and why not
  15. Closing thoughts
  16. Resources

How to import social media statistics into Google Sheets with formulas

The formulas below are generally set up to return the number of followers (or page likes) for a given channel, but you could adapt them to return other metrics (such as follows) with a little extra work.

Caveats: these formulas occasionally stop working when the underlying website changes, but I will try to keep this post updated with working versions for the major social media statistics.

Example workbooks: Each example has a link to an associated Google Sheet workbook, so feel free to make your own copy: File > Make a copy....

facebook icon Import Facebook data

November 2018 update: The Facebook formula is working again! The trick is to use the mobile URL 😉 Thanks to reader Mark O. for this discovery.

Start with the mobile Facebook page URL in cell A1, e.g. this url

https://mobile.facebook.com/benlcollinsData

or this variation of it:

https://m.facebook.com/benlcollinsData

Here is the Google Sheets REGEX formula to extract page likes:

=INDEX(REGEXEXTRACT(REGEXEXTRACT(LOWER(INDEX(IMPORTXML(A1,"//@content"),2)),"([0-9km,.]+)(?: likes)"),"([0-9,.]+)([km]?)"),,1) * SWITCH(INDEX(REGEXEXTRACT(REGEXEXTRACT(LOWER(INDEX(IMPORTXML(A1,"//@content"),2)),"([0-9km,.]+)(?: likes)"),"([0-9,.]+)([km]?)"),,2),"k",1000,"m",1000000,1)

The following screenshot shows these formulas:

Facebook Data

See the Facebook Import Sheet.

^ Back to Contents


twitter logo Import Twitter data

This formula is no longer working for extracting Twitter followers and I have not found an alternative.

Start with the mobile Twitter handle URL in cell A1, e.g.

https://mobile.twitter.com/benlcollins

Here is the formula to extract follower count:

=VALUE(REGEXEXTRACT(IMPORTXML(A1,"/"),"(?:Following )([\d,]+)(?: Followers)"))

The following screenshot shows this formula:

Import twitter data

Note 1: This Twitter formula seems to be particularly volatile, working fine one minute, then not at all the next. I have two Sheets open where it’ll work in one, but not the other!

See the Twitter Import Sheet.

^ Back to Contents


Build Business Dashboards With Google Sheets

Digital marketing dashboard in Google Sheets
  • Learn how to build beautiful, interactive dashboards in my online course.
  • 9+ hours of video content, lifetime access and copies of all the finished dashboard templates.
  • Learn more

instagram logo Import Instagram data

This formula is no longer working for extracting Instagram followers and I have not found an alternative.

Start with the Instagram page URL in cell A1:

https://www.instagram.com/benlcollins/

Then, this formula in cell B1 to extract the follower metadata (this may or may not work):

=IMPORTXML(A1,"//meta[@name='description']/@content")

This extracts the following info: “230 Followers, 259 Following, 465 Posts – See Instagram photos and videos from Ben Collins (@benlcollins)”

Next step is to combine this with REGEX to extract the followers for example. Here’s the formula to do that (still assuming url in cell A1):

=INDEX(REGEXEXTRACT(REGEXEXTRACT(LOWER(IMPORTXML(A1,"//meta[@name='description']/@content")),"([0-9km,.]+)( followers)"),"([0-9,.]+)([km]?)"),,1) * SWITCH(INDEX(REGEXEXTRACT(REGEXEXTRACT(LOWER(IMPORTXML(A1,"//meta[@name='description']/@content")),"([0-9km,.]+)( followers)"),"([0-9,.]+)([km]?)"),,2),"k",1000,"m",1000000,1)

This deals with any accounts that have abbreviated thousands (k) or millions (m) notations.

Alternative Approach

The following formulas to extract account metrics appear to only work for the instagram account when you are logged in. It makes use of the QUERY function, SPLIT function and INDEX function to do data wrangling inside the formula.

Here’s the number of followers:

=REGEXEXTRACT(INDEX(SPLIT(QUERY(IMPORTDATA(A1),"select Col1 limit 1 offset 181"),""""),1,2),"[\d,]+")

Here’s the number following:

=REGEXEXTRACT(QUERY(IMPORTDATA(A1),"select Col2 limit 1 offset 181"),"[\d,]+")

Here’s the number of posts:

=REGEXEXTRACT(QUERY(IMPORTDATA(A1),"select Col3 limit 1 offset 181"),"[\d,]+")

The following screenshot shows these formulas:

instagram data

See the Instagram Import Sheet.

^ Back to Contents


youtube logo Import YouTube data

Start with the YouTube channel URL in cell A1:

https://www.youtube.com/benlcollins

To get the number of subscribers to a YouTube channel, use this formula in cell B1:

=VALUE(INDEX(REGEXEXTRACT(LOWER(INDEX(REGEXEXTRACT(INDEX(IMPORTXML(A1,"//div[@class='primary-header-actions']"),1,1),"(Unsubscribe)([0-9kmKM.]+)"),1,2)),"([0-9,.]+)([km]?)"),,1) * SWITCH(INDEX(REGEXEXTRACT(LOWER(INDEX(REGEXEXTRACT(INDEX(IMPORTXML(A1,"//div[@class='primary-header-actions']"),1,1),"(Unsubscribe)([0-9kmKM.]+)"),1,2)),"([0-9,.]+)([km]?)"),,2),"k",1000,"m",1000000,1))

See the YouTube Import Sheet.

^ Back to Contents


pinterest logo Import Pinterest data

In cell A1, enter the following URL, again replacing benlcollins with the profile you’re interested in:

https://www.pinterest.com/bencollins/

Then in the adjacent cell, B1, enter the following formula:

=IMPORTXML(A1,"//meta[@property='pinterestapp:followers']/@content")

to get the following output (screenshot shows older version of the formula, latest one is above and in the template file):

pinterest data

Note, you can also get hold of the profile metadata with the import formulas, as follows:

=IMPORTXML(A1,"//meta[@name='description']/@content")

See the Pinterest Import Sheet.

^ Back to Contents


alexa logo Import Alexa ranking data

Here there are two metrics I’m interested in – a site’s Global rank and a site’s US rank.

Global Rank

To get the Global rank for your site, enter your URL into cell A1 (replace benlcollins.com):

http://www.alexa.com/siteinfo/benlcollins.com/

and use the following helper formula in cell B1:

=QUERY(ArrayFormula(QUERY(IMPORTDATA(A1),"select Col1") & QUERY(IMPORTDATA(A1),"select Col2")),"select * limit 1 offset " & MATCH(FALSE,ArrayFormula(ISNA(REGEXEXTRACT(QUERY(IMPORTDATA(A1),"select Col1") & QUERY(IMPORTDATA(A1),"select Col2"),"Global rank icon.{10,}"))),0)+1)

and then extract the rank in cell C1:

=VALUE(REGEXEXTRACT(B1,"[\d]{3,}"))

US Rank

Assuming you have the Alexa URL in cell A1 again, then the US rank is extracted with this helper formula:

=QUERY(ArrayFormula(QUERY(IMPORTDATA(A1),"select Col1") & QUERY(IMPORTDATA(A1),"select Col2")),"select * limit 1 offset " & MATCH(FALSE,ArrayFormula(ISNA(REGEXEXTRACT(QUERY(IMPORTDATA(A1),"select Col1") & QUERY(IMPORTDATA(A1),"select Col2"),"title='United States Flag'.alt.{50,}"))),0))

and this formula to extract the actual rank value:

=VALUE(REGEXEXTRACT(B1,"[\d]{3,}"))

The following screenshot shows these formulas:

alexa data

See the Alexa Ranking Import Sheet.

^ Back to Contents


Build Business Dashboards With Google Sheets

Digital marketing dashboard in Google Sheets
  • Learn how to build beautiful, interactive dashboards in my online course.
  • 9+ hours of video content, lifetime access and copies of all the finished dashboard templates.
  • Learn more

quora logo Import Quora data

In this instance, I’ve imported the number followers Barack Obama has on Quora.

Quora is a little bit different because I need to use the URL and the profile name in my formula, so I’ve kept them in separate cells for that purpose. So in cell A1, add the generic Quora URL:

https://www.quora.com/profile/

And then in cell B1, add the profile name:

Barack-Obama-44

Then the formula in C1 to get the number of followers is:

=VALUE(QUERY(IMPORTXML(A1&B1,"//a[@href='/profile/"&B1&"/followers']"),"select Col2"))

The following screenshot shows this formula:

quora data

See the Quora Import Sheet.

^ Back to Contents


reddit logo Import Reddit data

Here, I’m using the funny subreddit as my example.

In A1:

https://www.reddit.com/r/funny/

To get the number of followers of this subreddit, use this formula in cell B1:

=IMPORTXML(A1,"//span[@class='subscribers']/span[@class='number']")

Bonus: To get the number of active viewers of this subreddit:

=IMPORTXML(A1,"//p[@class='users-online']/span[@class='number']")

The following screenshot shows these formulas:

reddit data

See the Reddit Import Sheet.

^ Back to Contents


spotify logo Import Spotify monthly listeners

Here’s a method for extracting the number of followers an artist has on the music streaming site Spotify.

First, find your favorite artist on Spotify: https://open.spotify.com/browse/featured

Copy the URL into cell A1 (it’ll look like this):

https://open.spotify.com/artist/2ye2Wgw4gimLv2eAKyk1NB

(This is Metallica, yeah ?)

Then put the following formula into cell A2 to extract the monthly listeners:

=N(INDEX(IMPORTXML(A1,"//h3"),2))

See the Spotify Import Sheet.

To get Spotify playlist data, add the playlist URL into cell A1:

https://open.spotify.com/playlist/37i9dQZF1DX1lVhptIYRda

And use this formula to extract the number of songs and likes:

=QUERY(SPLIT(REGEXEXTRACT(INDEX(IMPORTXML(A1,"//@content"),5),"^(?:[a-zA-Z. ]+ · Playlist · )([0-9]+ songs · [0-9.KM]+ likes)")," · "),"select Col1, Col3")

^ Back to Contents


soundcloud logo Import Soundcloud data

Start with the Soundcloud page URL in cell A1, e.g.

https://soundcloud.com/fleecemusic

Here is the formula to extract page likes:

=ArrayFormula(VALUE(REGEXEXTRACT(QUERY(SORT(IFERROR(REGEXEXTRACT( IMPORTXML(A1,"//script"),"followers_count..\d{1,}"),""),1,FALSE),"select * limit 1"),"\d{1,}")))

Alternative formula:

Here is an alternative formula to extract the page metadata, which includes the likes:

=IMPORTXML(A1,"//meta[@name='description']/@content")

the formula to extract likes is:

=VALUE(REGEXEXTRACT(REGEXEXTRACT(SUBSTITUTE( IMPORTXML(A1,"//meta[@name='description']/@content"),",",""),"\d{1,}.Followers"),"\d{1,}"))

and to extract the “talking about” number:

=VALUE(REGEXEXTRACT(REGEXEXTRACT(SUBSTITUTE( IMPORTXML(A1,"//meta[@name='description']/@content"),",",""),"\d{1,}.Tracks"),"\d{1,}"))

The following screenshot shows these formulas:

soundcloud data

See the Soundcloud Import Sheet.

^ Back to Contents


gtmetrix_logo Import GTmetrix data

GTmetrix is a website that analyzes website performance.

You need to grab the correct URL before you can start scraping the data. So navigate to the GTmetrix site and enter the URL and hit analyze. You’ll end up with a URL like this:

https://gtmetrix.com/reports/www.benlcollins.com/BcHv78bP

Those last 8 characters (“BcHv78bP”) appear to be unique each time you run an analysis, so you’ll have to do this step manually.

Then in column B, I use this formula to extract the Page Speed Score and YSlow Score, into cells B1 and B2:

=ArrayFormula(ABS(IMPORTXML(A1,"//span[@class='report-score-percent']")))

and this formula in cell B3, to get the page details (Fully Loaded Time, Total Page Size and Requests) in cells B3, B4 and B5:

=IMPORTXML(A1,"//span[@class='report-page-detail-value']")

The following screenshot shows these formulas:

gtmetrix data

See the GTmetrix Import Sheet.

^ Back to Contents


Bitly logoImport Bitly click data

Bitly is a service for shortening urls. They provide metrics for how many clicks you’ve had on each bitly link, e,g.

Bitly link metric data for import to Google Sheets

Taking a standard Bitly link (e.g. http://bitly.com/2mmW1lr) and appending a “+” to it will take you to the dashboard page, with the metrics. Then we can use the import data function, a query function and a REGEX function to extract the click metrics.

User clicks are:

=VALUE(REGEXEXTRACT(QUERY(IMPORTDATA(A9&"+"),"select Col1 limit 1"),"(?:user_clicks...)([0-9]+)"))

and global clicks are:

=VALUE(REGEXEXTRACT(QUERY(IMPORTDATA(A9&"+"),"select Col5"),"(?:global_clicks:.)([0-9]+)"))

Clicks from the Bitly network are then simply the user clicks subtracted from the global clicks.

The following screenshot shows these formulas:

Bitly data import in Google Sheets

See the Bitly Import Sheet.

^ Back to Contents


linkedin logo Import Linkedin data

This formula is no longer working for extracting Linkedin followers and I have not found an alternative.

In cell A1:

https://www.linkedin.com/in/benlcollins/

This formula used to work to get the number of Linkedin followers, but no longer:

=QUERY(IMPORTXML(A1,"//div[@class='member-connections']"),"select Col1")

and the output:

linkedin import

There is no example sheet for Linkedin since the formula is no longer working.

^ Back to Contents


cancel icon Sites that don’t work and why not

I’ve tried the following sites but the IMPORT formulas are unable to extract the social media statistics:

  • Linkedin (see above)
  • Similar Web
  • Twitch
  • Mobcrush
  • Crunchbase
  • Angel.co
  • Majestic SEO

These are all modern sites built using front-end, client-side Javascript frameworks, so the IMPORT formulas can’t extract any data because the page is built dynamically in browser as it’s loaded up. The IMPORT formulas work fine on sites built in the traditional fashion, with lots of well formed HTML tags, where the social media statistics are embedded into the site markup that is passed from the server.

Compare this screenshot of the source code for Mobcrush, built using Angular JS it looks like (click to enlarge):

Angular front end

versus what the source code looks for this page on my website (click to enlarge):

benlcollins code

You can see the code for my site has lots of tags which the IMPORT formulas can parse, whereas the other site’s code does not.

If anyone knows of any clever way to get around this, do share!

Otherwise, you’re next option is to venture down the API route. Yes, this involves coding, but it’s not as hard as you think.

I’ll be posting some API focussed articles soon. In the meantime, check out my post on how to get started with APIs, or for a peak at what’s coming, take a look at my Apps Script + API repo on GitHub.

Loading error

Also, even when these formulas are working, they can be temperamental. If you work with them a lot, sooner or later you’ll find yourself hitting this loading issue all the time, where the formulas stop displaying any results:

Loading error

^ Back to Contents


settings logo Closing thoughts

These formulas are unstable and will sometimes display an error message.

I’ve found that adding or removing the final “/” from the URL can sometimes get the formula working again (the issue is to do with caching).

I can make no guarantee that these will work for you or into the future. Whilst researching this article, I came across several older articles where many of the formulas no longer work. So things change!

To summarize: Caveat Emptor!

^ Back to Contents


link icon Resources

^ Back to Contents


As always, leave any comments, corrections or request other social media statistics below.

Icons from Freepik.

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.

Connecting to the Crunchbase API with Google Sheets

UPDATE: This article was originally written in 2017. Since then, the Crunchbase API is now part of their enterprise tier. The old API, which this article is based on, is no longer accessible. As a result, the code I share below will no longer return Crunchbase data. I leave it here for reference. It may be useful if you do use the paid tier.

Crunchbase API with Google Sheets

The Crunchbase API is easily accessible with Apps Script, meaning you can retrieve Crunchbase company data to display, or analyze further, in your Google Sheet. This article shows you how to connect to the Crunchbase API.

How to import data from the Crunchbase API into Google Sheets

Crunchbase is a business information platform; a sort of giant database of information on organizations and people in the business world, especially the digital/technology/startup world.

They have an API so you can programmatically access and retrieve business data. There’s a free tier, which is what I’ll show in this article, and a paid, pro tier, which has a much richer dataset available through the API.

On the free tier, you’re limited to data on organizations and people profiles, through their Open Data Map. It’s a RESTful API with two endpoints (/odm-organizations and /odm-people) and you need to apply for basic access first to get a user key for access.

Continue reading Connecting to the Crunchbase API with Google Sheets