Connect to Spotify using Power Query

Spotify has a massive music database, and in this article we're going to look at how to connect to Spotify using Power Query.

Step 1: Get API credentials from Spotify

The first thing you'll need to do is to collect a "Client ID" and "Client Secret" from Spotify's developer portal. Of course, you'll need a Spotify account for this, but the good news is that you can sign up for a free account if you don't already have one.

To generate (or access) your Client Key and Client Secret, the steps are as follows:

• Log in to the Spotify Developer portal
• Create a new Application
• Expand the "Show Client Secret" area

Keep in mind that your Client ID and Client Secret are unique to you, and should not be shared with anyone!

Step 2: Create ClientID and ClientSecret queries in Power Query

Next, we need to create two new (blank) queries in a new Excel (or Power BI) project. To do this:

• Excel: Go to Data -> Get Data -> From Other Sources -> From Blank Query
• Power BI: Go to Home -> Get Data -> Blank Query

One you have your new query:

• Name the query "ClientID" (note the capitalization and lack of spaces)
• Copy and paste the ugly Client ID character string from your Spotify app
• Paste it into the formula bar and press Enter

Create another query for "ClientSecret" which holds the Client Secret from your Spotify app.

Note: I have deliberately chosen queries, not parameters to hold the ClientID and ClientSecret. This was done to avoid having to explicitly pass these two variables in to every function that I would subsequently build against the Spotify API. If you are trying to use this technique to build a Power BI template app, you will want to go with parameters as they will not cache your private data upon deployment.

Step 3: Create the "getSpotifyData" function

The next step is to create a function which can be fed any valid url and extract the results from the Spotify API. The trick to this query is that Spotify's API requires OAuth authentication, which requires hashing your Client ID and Client Secret together into Base64, retrieving a bearer token based on that information, and then passing the bearer token with each API request. Fortunately, the function below takes care of all of that for you. To use it:

• Create a new Blank Query and call it "getSpotifyData"
• Go to View -> Advanced Editor and replace any existing code with the following:

(APIQuery as any) =>
let
// Function Purpose: Return results based on an API call to Spotify
// Written by Ken Puls, FCPA, FCMA, MSMVP (www.excelguru.ca)
// For full setup and use documentation, see:
//     https://excelguru.ca/connect-to-spotify-using-power-query/
// Note: You are free to copy and use this code provided you 
// preserve all code comments and links

//  First, concatenate the Client ID & Client Secret and convert to base64
authKey =
    "Basic " & Binary.ToText(
        Binary.Buffer(
            Text.ToBinary( ClientID & ":" & ClientSecret )
        ),
        0
    ),

// Retrieve OAuth bearer token from Spotify
GetJson =
    Web.Contents(
        "https://accounts.spotify.com/api/token",
        [
            Headers=[
                #"Authorization"=authKey, 
                #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"
            ],
            Content=Text.ToBinary("grant_type=client_credentials")
        ]
    ),
FormatAsJson =  Json.Document(GetJson),         

// Extract token from the Json response
AccessToken =  FormatAsJson[access_token],          

// Lock in text of api string
sAPICall =  APIQuery,           

//Retrieve JSON response
JsonResponse =
    Json.Document(
        Web.Contents(
            sAPICall,
            [
                Headers=[
                    Authorization="Bearer " & AccessToken,
                    Accept="application/json",
                    #"Content-Type"="application/json"
                ]
            ]
        )
    )
in
    JsonResponse

Step 4: Generating (and using) valid API queries

The next step is to invoke the function by passing it a valid URL. But how do you figure that part out? For me, the answer was to play with the Spotify Console, as this let me quickly build a valid URL which I could then test with the getSpotifyData function.

As an example, let's say that I wanted to extract a list of albums and songs for one of my favourite Canadian bands; Barstool Prophets. The first thing I need to work out is their "artist_id". As it turns out, the console shows that there is a "search" api, which can be used to search for an artist by name. Here's how I configured it:

I copied that URL, and made a new query which calls the "getSpotifyData" function by typing the following into the formula bar:

= getSpotifyData("https://api.spotify.com/v1/search?q=Barstool%20Prophets&type=artist&limit=10")

After hitting Enter, I was then prompted to declare my Authentication method. The correct choice here is "Anonymous":

Followed by declaring the privacy levels for the data source: "Public"

It then only took a couple of seconds to return my result, which will always return a single Record as shown here:

Step 5: Extracting data from the getSpotifyData function

It is worth noting that the getSpotifyData function will always return a single record. This was an conscious choice I made when creating the function, as not all API results contain the same data. In this way the function is fairly robust, and invoking it should always bring back data.

So let's look at how I was able to extract the Artist ID from the base record returned:

• Click the Record to drill in to its content
• Click the List (in the second row) to drill in
• Converted the list to a Table
• Expanded all columns from Column1
• Filtered the "name" column to my desired artist (Barstool Prophets) to get right of the synonyms
• Removed all columns except for id
• Right clicked the ID value -> Drill down

And the result is that I was able to extract the artist ID so that I can use it in other queries. (In this case, Barstool Prophets is 3nFAPKnefnIIRohVdAzKyX)

Step 6: Putting it all together to Connect to Spotify using Power Query

Now that I have my artist ID, I want to get a list of albums and tracks for Barstool Prophets. After doing a little research in Spotify's API Console I found "Get Artist's Albums" under the Artists section and "Get Album Tracks" under album. So here's are the steps I did:

1. Create a new query which uses the following formula:

= getSpotifyData("https://api.spotify.com/v1/artists/3nFAPKnefnIIRohVdAzKyX/albums")

2. Click the list contained in the items line to drill in
3. Convert the list to a table
4. Expand id, name and release_date from [Column1]
5. Rename 'id' as 'album_id' and 'name' as 'album_name'
6. Add a Custom column (called Custom) using the following formula

= getSpotifyData("https://api.spotify.com/v1/albums/"&[album_id]&"/tracks")

6. Expand (only) 'items' from the records in the [Custom] column
7. Expand the lists in the [item] column records to new rows
8. Expand duration_ms, id, name and track_number from the [items] column
9. Rename 'id' as 'track_id' and 'name' as 'track name'
10. Reorder the data

The results are shown here:

Need some useful functions or examples?

Have a look at the following article:

• Get album and track data from Spotify

(I'll add links to more as I create them!)

Ending Thoughts

The tricky parts in developing this solution to connect to Spotify using Power Query was getting the OAuth bearer key to work (buffering was key there), and avoiding the triggering of the formula firewall. By leveraging the re-usable getSpotifyData() custom function, however, both of these issues seem to be solved.

If you're doing this regularly, you'll probably want to build a collection of functions that you can call in order to make this super easy. I've been working on a few of those, and will post them in a future update.

One thing of note is that I have not been able to get this to work for any API URL which starts with /v1/me (including user profile or playlist data.) I'm not sure if the authentication method is different there or not, but they keep complaining about credentials where everything else works. Ultimately, I don't believe this is a big issue though, as there seems to be alternate methods that will allow you to access things. (Playlist data can be queried via /v1/playlists/{user_id}, which does return results.)

Also, as a quick debugging tip... the OAuth token only last for about 60 minutes. Given the way Power Query caches previews, this can cause issues when you are developing where trying to make a call to the API gets you into a situation where you keep getting prompted to declare your authentication as anonymous in the query editor. (This should not be an issue in a full refresh.) Should this happen to you, you can reset things by going to File - > Options & Settings -> Data Source Settings and clearing the Spotify credentials, then refreshing your data preview.