Get Album and Track Data from Spotify

After writing up my article on connecting to Spotify using Power Query, I thought it would be helpful to provide some functions that will allow you to get album and track data from Spotify.

A sample project to illustrate getting album and track data from Spotify

Rather than just dump a bunch of functions on you, let's walk through a project to get all albums and songs released by an artist, as well as Spotify's song popularity and other statistics. Now you can choose any artist you want for this, but I'm going to use Taylor Swift, given her extensive catalogue (which requires dealing with pagination in the functions.) Keep in mind that this article is not focused on how I built these functions, but rather using the functions that I'm sharing below to return detailed records on almost 1,200 individual song tracks from Taylor. (Seriously!)

Before you start, it is essential that you have set up your Power Query project with the following three queries:

• The ClientID query (to hold your Spotify Client ID)
• The ClientSecret query (to hold your Spotify Client Secret)
• The getSpotifyData custom function (to extract data from the Spotify API)

Full steps to set up these items is detailed in steps 1-3 of our Connect to Spotify using Power Query article.

Function 1: getSpotifyArtistID

As you'll see in a bit, in order to get album and track data from Spotify for our given artist, we need to make a call using the artist's ID, not their name. So before we get there we need a function that allows us to easily look up that id.

The code below is the getSpotifyArtistID function that will allow us to do just that:

let
    // Function Purpose: Look up an artist's Spotify id by name 
    // Written by Ken Puls, FCPA, FCMA, MSMVP (www.excelguru.ca) 
    // For full setup and use documentation, see: 
    //      https://excelguru.ca/<replace this url> 
    Source = (ArtistName as any) => let
        //  For documentation on the API call used, see 
        //  https://developer.spotify.com/console/get-search-item/
        sUrl = "https://api.spotify.com/v1/search?q=" & 
            Text.Replace(ArtistName," ","%20") & "&type=artist",
        PageContents = Table.FromList(
            getSpotifyData(sUrl)[artists][items], 
            Splitter.SplitByNothing(), 
            null, null, ExtraValues.Error),
        ExpandContents = Table.ExpandRecordColumn(
            PageContents, 
            "Column1", {"id", "name"}, {"id", "name"}),
        FilterToArtist = Table.SelectRows(ExpandContents, each [name] = ArtistName),
        artist_id = try FilterToArtist{0}[id] otherwise "artist_id not found!"
    in
        artist_id
in
    Source

To use this, create a new blank query, rename it as "getSpotifyArtistID", go to View -> Advanced Editor, replace all the code with what you see above, and click OK.

Once done, the easiest way to get what we need is simply to select the function, type the artist's name in the box, and click Invoke:

You'll get a new query which returns the artist's id which, in Taylor's case, is 06HL4z0CvFAxyc27GXpf02 (as shown below):

A couple of quick things worth noting here:

• If you get prompted for authentication and seem to end up in a loop, go to File - > Options & Settings -> Data Source Settings and clearing the Spotify credentials. You'll be prompted to set your authentication methods again, but it fixes an issue in the preview when the OAuth token expires but stays cached.
• Spotify actually returns multiple artists with this search term, so there is logic built in to filter to the artist's name EXACTLY as you typed it. If you get a message that the artist_id is not found, it means that you either misspelled your artist's name, or entered an incorrect handle for your artist. (I.e. one of Canada's most famous bands can be returned by entering "The Tragically Hip", but not "Tragically Hip" or - as we often refer to them - "The Hip".)

Function 2: getSpotifyAlbums (to get album data from Spotify)

With our artist's ID in hand, we can now move on to getting something useful: a list of albums. To do this, we'll use the getSpotifyAlbums function as shown below:

let
    Source = (AlbumID as any) => let
        //  Function Purpose: Retrieve all albums by the specified artist
        //  Written by Ken Puls, FCPA, FCMA, MSMVP (www.excelguru.ca) 
        //  For full setup and use documentation, see: 
        //      https://excelguru.ca/<replace this url> 
        iPagination = 50,
        //  For documentation on the API call used, see 
        //      https://developer.spotify.com/console/get-album-tracks/
        sUrl = "https://api.spotify.com/v1/albums/" & AlbumID & "/tracks?limit=" 
            & Text.From(iPagination)&"&offset=",
        iTotalRecords = getSpotifyData(sUrl & "0")[total],
        PageOffsets = Table.TransformColumns(
            Table.FromList(
                {0..Number.RoundDown(iTotalRecords/iPagination) }, 
                Splitter.SplitByNothing(), 
                null, null, ExtraValues.Error
                ), 
            {{"Column1", each _ * iPagination, type number}}),
        PageContents = Table.FromList(
            Table.AddColumn(
                PageOffsets, 
                "Custom", 
                each getSpotifyData(sUrl & Text.From([Column1]))
                )[Custom], 
            Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        AllRecords = Table.ExpandListColumn(
            Table.ExpandRecordColumn(
                PageContents, "Column1", {"items"}, {"items"}
                ), 
            "items"),
        ExpandContents = Table.ExpandRecordColumn(
            AllRecords, 
            "items", 
            {"duration_ms", "explicit", "id", "name", "track_number"}, 
            {"duration_ms", "explicit", "track_id", "track_name", "track_number"})
    in
        ExpandContents
in
    Source

Once this function has been created and named, we can now either Invoke it (after pasting our artist's id in the ArtistID field), or we can modify our previous function to nest the two calls together:

One of the things you'll notice immediately here is that there are multiple versions of Taylor's albums, and we're not even talking about Red vs Red (Taylor's Version). I suspect this has to do with regional and special editions. Regardless of why, each has its own unique album_id that we can use to interrogate the data further. (Depending on the type of analysis you want to perform, you may want to remove duplicate albums.)

It is worth noting that this function has pagination built in as Spotify will only allow up to 50 records to be retrieved from this API call in one pull. In the case of Taylor, we have pulled back 64 different albums (or versions of albums.)

Function 3: getSpotifyAlbumTracks (to get track data from Spotify)

Of course, the original goal was to get album and track data from Spotify. So we're now at the stage where we can focus on tracks. To do this, we'll leverage a function called getSpotifyAlbumTracks which uses this code:

let
    Source = (AlbumID as any) => let
        //  Function Purpose: Retrieve all tracks in the specified album
        //  Written by Ken Puls, FCPA, FCMA, MSMVP (www.excelguru.ca) 
        //  For full setup and use documentation, see: 
        //      https://excelguru.ca/<replace this url> 
        iPagination = 50,
        //  For documentation on the API call used, see 
        //      https://developer.spotify.com/console/get-album-tracks/
        sUrl = "https://api.spotify.com/v1/albums/" & AlbumID & "/tracks?limit=" 
            & Text.From(iPagination)&"&offset=",
        iTotalRecords = getSpotifyData(sUrl & "0")[total],
        PageOffsets = Table.TransformColumns(
            Table.FromList(
                {0..Number.RoundDown(iTotalRecords/iPagination) }, 
                Splitter.SplitByNothing(), 
                null, null, ExtraValues.Error
                ), 
            {{"Column1", each _ * iPagination, type number}}),
        PageContents = Table.FromList(
            Table.AddColumn(
                PageOffsets, 
                "Custom", 
                each getSpotifyData(sUrl & Text.From([Column1]))
                )[Custom], 
            Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        AllRecords = Table.ExpandListColumn(
            Table.ExpandRecordColumn(
                PageContents, "Column1", {"items"}, {"items"}
                ), 
            "items"),
        ExpandContents = Table.ExpandRecordColumn(
            AllRecords, 
            "items", 
            {"duration_ms", "explicit", "id", "name", "track_number"}, 
            {"duration_ms", "explicit", "track_id", "track_name", "track_number"})
    in
        ExpandContents
in
    Source

Once this function is created and named, it is ready to use. While we could invoke it and feed it a single album, I already have a table of all of Taylor's album_id's listed in the first column. So I'm going to do this:

• Go to Add Column -> Invoke Custom Function
• Select the getSpotifyAlbumTracks function
• Ensure that the AlbumID is pointed at the album_id column

• Expand all fields from the getSpotifyAlbumTracks column

This function once again includes pagination, and returns 30 tracks for the version of "Red (Taylor's Version)" album which is listed under album_id 6kZ42qRrzov54LcAk4onW9. (Full disclosure, I did move the album_id column to the left of the new data, just to display it. The original album name data is still here, just off screen.)

You'll notice that this function returns the song duration (in milliseconds), an "explicit" column (for explicit lyrics), track name, track number and - most importantly for our next function - the track id.

Function 4: getSpotifyTrackFeatures (to enrich track data from Spotify)

Spotify also has a bunch of track feature statistics (based on their own algorithms) including things like danceability, acousticness, energy and - what has to be my favourite descriptor - "speechiness" (among others.) Those statistics are what the getSpotifyTrackFeatures function will allow us to grab:

let
    Source = (TrackID as any) => let
        //  Function Purpose: Retrieve all albums by the specified artist
        //  Written by Ken Puls, FCPA, FCMA, MSMVP (www.excelguru.ca) 
        //  For full setup and use documentation, see: 
        //      https://excelguru.ca/<replace this url> 

        //  For documentation on the API call used, see 
        //  https://developer.spotify.com/console/get-audio-features-track/
        sUrl = "https://api.spotify.com/v1/audio-features?ids=" & TrackID,
        PageContents = Table.FromList(
            getSpotifyData(sUrl)[audio_features], 
            Splitter.SplitByNothing(), 
            null, null, ExtraValues.Error),
        ExpandContents = Table.ExpandRecordColumn(
            PageContents, 
            "Column1", 
            {   "danceability", "energy", "key", "loudness", "mode", 
                "speechiness", "acousticness", "instrumentalness", 
                "liveness", "valence", "tempo", "duration_ms", 
                "time_signature"},
            {"danceability", "energy", "key", "loudness", "mode", 
                "speechiness", "acousticness", "instrumentalness", 
                "liveness", "valence", "tempo", "duration_ms", 
                "time_signature"}
            )
    in
        ExpandContents
in
    Source

Once again, we'll invoke this one via the following steps:

• Go to Add Column -> Invoke Custom Function
• Select the getSpotifyTrackFeatures function
• Ensure that the TrackID is pointed at the track_id column
• Expand all columns from the newly created getSpotifyTrackFeatures column

Here's a look at some of those statistics (after shifting the track_name column so that you know which songs they belong to):

Function 5: getSpotifyTrackPopularity

The final function I'm going to provide in this article is a bit of an outlier. You'd think it would be included in the getSpotifyTrackFeatures function, but it actually comes from a different API call, which is why it is separate. The getSpotifyTrackPopularity function returns the popularity of a track based on Spotify's algorithm, which is based on a mix of factors including how many plays it gets. It's signature is as follows:

let
    Source = (TrackID as any) => let
        // Function Purpose: Retrieve all albums by the specified artist
        // Written by Ken Puls, FCPA, FCMA, MSMVP (www.excelguru.ca) 
        // For full setup and use documentation, see: 
        //      https://excelguru.ca/<replace this url> 

        // For documentation on the API call used, see 
        //      https://developer.spotify.com/console/get-track/
        sUrl = "https://api.spotify.com/v1/tracks/" & TrackID,
        Popularity = getSpotifyData(sUrl)[popularity]
    in
        Popularity
in
    Source

It can be invoked just as the previous two functions. And once done, we have data on 1,199 different Taylor Swift tracks including original, karaoke, international and "Taylor's" versions (among others.)

Final thoughts on getting album and track data from Spotify

The one missing statistic that I think is important is actual plays. To my knowledge however, Spotify doesn't actually provide these numbers via their APIs. (If they do, please let me know so I can build a function for it!)

Ultimately, I hope you'll find these functions to get album and track data from Spotify useful. Do be aware that it will take some time to refresh, as you are pulling from the web and I'm sure Spotify will perform rate limiting!

If you'd like a workbook with all of these calls in it, you can download it here. Just remember that you'll need to:

• Update the ClientKey and ClientSecret queries with your own credentials
• Change the Artist Name in the Source step of the Song Data query to get the artist you want to see.