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
