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

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.

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

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.

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

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.

or this variation of it:

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:

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

Here is the formula to extract follower count:

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

The following screenshot shows this formula:

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!

^ Back to Contents

## Import Instagram data

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

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:

See the Instagram Import Sheet.

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

## 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):

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.

## 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:

See the Alexa Ranking Import Sheet.

^ Back to Contents

## 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:

See the Quora Import Sheet.

## 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:

See the Reddit Import Sheet.

## 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")

## Import Soundcloud data

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:

See the Soundcloud Import Sheet.

## 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:

See the GTmetrix Import Sheet.

## Import 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.

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:

See the Bitly Import Sheet.

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

In cell A1:

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:

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

## 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:

• 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):

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

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.

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:

## 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!

## Resources

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:

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

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

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

### 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:

### Step 5: Run your function

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

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

### 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:

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();
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 10: See external data in your Sheet

You should now get the random fact showing up in your 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();
sheet.getRange(sheet.getLastRow() + 1,1).setValue([fact]);

}

Your output now will look like this:

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!

function onOpen() {
}

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

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

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:

It’s actually not as difficult as it looks.

### Getting started with the iTunes API Explorer

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:

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");

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):

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... ]
}

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:

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:

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:

• 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:

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

function onOpen() {
}

// function to call iTunes API
function calliTunesAPI(artist) {

// Call the iTunes API
var response = UrlFetchApp.fetch("https://itunes.apple.com/search?term=" + artist + "&amp;limit=200");

var json = response.getContentText();
return JSON.parse(json);

}

function displayArtistData() {

// pick up the search term from the Google Sheet
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)';
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 &lt; albumB) { return -1; } else if (albumA &gt; 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.

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

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

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:

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
*/
function swapi() {

// Call the Star Wars API
var response = UrlFetchApp.fetch("http://swapi.dev/api/planets/1/");

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

Create a button using the rectangle 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:

What else?

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

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.