Macro required to get data from Spotify and Apple Music

[Drewmyster]

This is something I’d really like the capability of doing via a spreadsheet, a formula (or I guess macro specifically), that is able to look up data from Spotify or Apple Music and return what would be classed as an artists “latest release” and also the date of the release.

Taking two examples;

Atist: Miley Cyrus (Artist(A1)), [Lookup latest release] returns “Plastic Hearts” (Title(A2)), “Album” (Type(A3)), “November 27, 2020” (Release Date(A4)).

Artist: Kylie Minogue (Artist(B1)), [Lookup latest release] returns “Real Groove (Studio 2054 Remix)” (Title(B2)), “Single” (Type(B3)), “December 31, 2020” (Release Date(B4)).

Is someone able to write a macro or show me how to do this?

 

[mikerickson]

From my Mac, I can write an AppleScript that will read data from an Excel worksheet, take data from the Spotify app and put data back in that Excel worksheet.

Since Microsoft has removed the MacScript command, this has to be driven by Apple Script.

A further problem is that the scripting language for Spotify is very limited. Artist is a text property of a track object and nothing more. So Latest Relase isn't a thing.

I presume there would be similar difficulties with Spotify's interactions with a Windows system.

Spotify doesn't want the public to have access to their database unless Spotify gets paid, either by subscription or by advertisers. Have you noticed that you can't copy text from the Spotify app and paste into a worksheet.

 

[Drewmyster]

From my Mac, I can write an AppleScript that will read data from an Excel worksheet, take data from the Spotify app and put data back in that Excel worksheet.

Since Microsoft has removed the MacScript command, this has to be driven by Apple Script.

A further problem is that the scripting language for Spotify is very limited. Artist is a text property of a track object and nothing more. So Latest Relase isn't a thing.

I presume there would be similar difficulties with Spotify's interactions with a Windows system.

Spotify doesn't want the public to have access to their database unless Spotify gets paid, either by subscription or by advertisers. Have you noticed that you can't copy text from the Spotify app and paste into a worksheet.

How does it work with AppleScript then? Just exporting data and then importing to excel (Or Numbers I would guess)?

Would such information be more readily accessible from Apple Music or iTunes instead of Spotify then?

I’m just trying to envisage how the data gets pulled from one source and then what exactly it would return into the spreadsheet, given what you say about the artist being a text property of the track. So what exactly would you be asking the source to provide if it isn’t able to return the latest release information?

 

[mikerickson]

I don't know the scripting capablities of Apple Music. I see mMusic (which replaced iTunes) is primarly a program for downloaded music so I wouldn't use it for finding a latest release.

Here's an example of an apple script that takes a playlist from Spotify and puts it in an Excel sheet.

global Delimiter
set Delimiter to ";"

tell application "Microsoft Excel"
    activate
    set uiVal to (button returned of (display dialog "One song?" buttons {"Cancel", "No", "Yes"} default button "No"))
    set OneSongFlag to (uiVal = "Yes")
    
    set keyCell to (get column 1 of (get entire row of active cell))
    set firstCell to keyCell
    if value of keyCell ≠ "" then
        
        set uiVal to (button returned of (display dialog "There is data. Overwrite?" buttons {"Cancel", "OverWrite", "Find Empty"} default button "Find Empty"))
        if uiVal = "Find Empty" then
            set keyCell to my FirstBlankRow(keyCell)
        end if
        set firstCell to keyCell
    end if
end tell

if uiVal ≠ "Cancel" then
    
    tell application "Spotify"
        if player state = playing then
            --next track
        else
            play
        end if
        delay 0.5
        set totalDuration to 0
        
        repeat while (player state = playing)
            -- do track data
            set ThisTrack to current track
            set myNew to artist of ThisTrack
            set myNew to myNew & Delimiter & (name of ThisTrack)
            set myNew to myNew & Delimiter & (album of ThisTrack)
            set totalDuration to totalDuration + (duration of ThisTrack)
            set durVal to my DurationToHMS(duration of ThisTrack)
            set myNew to myNew & Delimiter & durVal
            set myNew to myNew & Delimiter & (popularity of ThisTrack)
            -- write track data
            my WriteText(myNew, keyCell)
            tell application "Microsoft Excel"
                set keyCell to get offset keyCell row offset 1 column offset 0
            end tell
            
            -- loop control
            if OneSongFlag then
                pause
                delay 0.5
            else
                next track
                delay 0.5
            end if
        end repeat
        
        set durVal to my DurationToHMS(totalDuration)
        
        
        if OneSongFlag then
            play
        else
            --my WriteText("total duration = " & durVal)
        end if
        beep
    end tell
    tell application "Microsoft Excel"
        set keyCell to get offset keyCell row offset -1 column offset 0
        set keyCell to get resize keyCell row size 1 column size 4
        set keyCell to my InclRange(keyCell, firstCell)
        select keyCell
    end tell
end if

on DurationToHMS(myDur)
    set mySec to myDur / 1000 as integer
    set myMin to (mySec / 60 - 0.5) as integer
    set mySec to mySec - (60 * myMin)
    if mySec < 10 then
        set secVal to "0" & mySec
    else
        set secVal to "" & mySec
    end if
    
    set myHour to (myMin / 60 - 0.5) as integer
    set myMin to myMin - (60 * myHour)
    if myMin < 10 then
        set minVal to "0" & myMin
    else
        set minVal to "" & myMin
    end if
    
    return ("" & myHour & ":" & minVal & ":" & secVal)
    
end DurationToHMS

on WriteText(NewText, xlLocation)
    set AppleScript's text item delimiters to {Delimiter}
    set NewItems to text items of NewText
    --display dialog "" & (count of NewItems)
    --log NewItems
    tell application "Microsoft Excel"
        set xlWriteTo to (get resize xlLocation row size 1 column size (count of NewItems))
        --display dialog (get address xlWriteTo)
        set value of xlWriteTo to NewItems
        select xlWriteTo
    end tell
end WriteText

on FirstBlankRow(someCell)
    tell application "Microsoft Excel"
        set myCell to someCell
        repeat until value of myCell = ""
            if value of myCell ≠ "" then
                set myCell to get offset myCell row offset 1 column offset 0
            end if
            --display dialog "done " & (get address of myCell with external)
        end repeat
        return myCell
    end tell
end FirstBlankRow

on InclRange(aRange, bRange)
    tell application "Microsoft Excel"
        set aAddress to get address aRange
        set bAddress to get address bRange
        
        return (range (aAddress & ":" & bAddress))
    end tell
end InclRange

The general logic is
begin loop
Tell Spotify to return the artist,track name, artist, and length of current track
Tell Microsoft Excel to write that information to a worksheet
Tell Spotify to change to the next track
loop if that ends the playlist

 

[Drewmyster]

Is there any other way to do it?