I've been looking for a way to rate tracks that I listen to on my phone and have those ratings sync back to MediaMonkey 4. I figured out the other night that I could use Tasker on my Android to read scrobbling metadata (song title, artist, album, track duration in seconds) and write this data plus a track rating that I plug in after the track plays to a Google Sheet.
The second part of this is to write a MediaMonkey 4 script to read the data, look up the song and adjust the rating accordingly. I ran into a bit of a roadblock in that reading a Google Sheet with VBScript is well above my skill level. Right now I'm trying to get the data via an Excel spreadsheet, but while I'm working on that was hoping someone else might have insights into getting information from a Google Sheet.
Would appreciate any insights!
Syncing ratings from a mobile phone
Moderators: Gurus, Addon Administrators
Re: Syncing ratings from a mobile phone
Use MediaMonkey for Android to play and Rate and sync it with MediaMonkey on your PC and you'll get the Ratings synced back to the PC files.
Download MediaMonkey | License
Help: Knowledge Base | MediaMonkey for Windows 5 | MediaMonkey for Android
Lowlander (MediaMonkey user since 2003)
Help: Knowledge Base | MediaMonkey for Windows 5 | MediaMonkey for Android
Lowlander (MediaMonkey user since 2003)
Re: Syncing ratings from a mobile phone
Thanks but unless I'm mistaken that requires syncing the songs to my mobile device - instead I'd rather stream them from an auto playlist so I don't have to copy tracks over to my phone to listen to them.
Re: Syncing ratings from a mobile phone
Download MediaMonkey | License
Help: Knowledge Base | MediaMonkey for Windows 5 | MediaMonkey for Android
Lowlander (MediaMonkey user since 2003)
Help: Knowledge Base | MediaMonkey for Windows 5 | MediaMonkey for Android
Lowlander (MediaMonkey user since 2003)
Re: Syncing ratings from a mobile phone
OK, I've got the bulk of this working but have a question on the UpdateAll method. I've borrowed from a few folks to arrive at the following snippet of code:
Set SongIterator = SDB.Database.QuerySongs(" SongTitle = '" & Replace(arrSheet(0,intCount),"'","''") & "' AND Album = '" & Replace(arrSheet(2,intCount),"'","''") & "' AND Artist = '" & Replace(arrSheet(1,intCount),"'","''") & "' ")
While Not SongIterator.EOF
Set Song = SongIterator.Item
If Song.SongLengthString = arrSheet(3,intCount) Then
Song.Rating = arrSheet(4,intCount) * 10
End If
Song.UpdateDB
Song.WriteTags
SongIterator.Next
Wend
This takes an array that I've populated with the Excel data, does a lookup in the database on title, artist and album and then from that list of results compares the song length in string format to hopefully match the correct song(s) in the database. If everything matches it then updates the rating to match a rating from the spreadsheet (0-10, multiplied by 10).
The problem I'm running into is that the UpdateAll method returns an error, so I have to use UpdateDB / WriteTags, which ignores the user settings. I guess not a big deal since probably nobody else will ever use the script and I understand how it works, but ideally I would use UpdateAll.
Any idea why I get an error using UpdateAll on the SDB.Database.QuerySongs method, and how to get around this? UpdateAll works in other scripts I've seen where the list is populated from SDB.CurrentSongList, for instance, but apparently not with this particular method.
Set SongIterator = SDB.Database.QuerySongs(" SongTitle = '" & Replace(arrSheet(0,intCount),"'","''") & "' AND Album = '" & Replace(arrSheet(2,intCount),"'","''") & "' AND Artist = '" & Replace(arrSheet(1,intCount),"'","''") & "' ")
While Not SongIterator.EOF
Set Song = SongIterator.Item
If Song.SongLengthString = arrSheet(3,intCount) Then
Song.Rating = arrSheet(4,intCount) * 10
End If
Song.UpdateDB
Song.WriteTags
SongIterator.Next
Wend
This takes an array that I've populated with the Excel data, does a lookup in the database on title, artist and album and then from that list of results compares the song length in string format to hopefully match the correct song(s) in the database. If everything matches it then updates the rating to match a rating from the spreadsheet (0-10, multiplied by 10).
The problem I'm running into is that the UpdateAll method returns an error, so I have to use UpdateDB / WriteTags, which ignores the user settings. I guess not a big deal since probably nobody else will ever use the script and I understand how it works, but ideally I would use UpdateAll.
Any idea why I get an error using UpdateAll on the SDB.Database.QuerySongs method, and how to get around this? UpdateAll works in other scripts I've seen where the list is populated from SDB.CurrentSongList, for instance, but apparently not with this particular method.
Re: Syncing ratings from a mobile phone
Very interesting, thanks!Lowlander wrote: ↑Sat Mar 02, 2019 8:52 pm True.
Check out: http://www.mediamonkey.com/forum/viewto ... txt+import
I've got the basics of the script working. Outside of the question I just posted on UpdateAll, the next thing I need to figure out is how to download the Google Sheet to Excel via the script (might not be easy, we'll see).
After that I still do want to have it read the data directly from the Sheet instead of Excel - if you have any thoughts on how to accomplish this it would be appreciated.
I'm including the code below. The Excel sheet is a simple sheet with a header row and columns as follows:
Title / Artist / Album / Duration (in seconds)/ Rating (0-10) / Instrumental (off / on)
Come to think of it I intended to add instrumental tags to tracks that I selected as instrumental from my phone - guess I still need to implement that part.
Code: Select all
Sub Excelimport
Dim arrSheet, intCount, firstcell, file, fso, SongIterator, Song
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("c:\users\username\appdata\roaming\mediamonkey\scripts\timestamp.txt") Then
Set file = fso.OpenTextFile("c:\users\username\appdata\roaming\mediamonkey\scripts\timestamp.txt", 1,,-1)
firstcell = file.ReadLine
If Not IsNumeric(Right(firstcell, Len(firstcell)-1)) Then firstcell = "A2"
Else
firstcell = "A2"
End If
arrSheet = ReadExcel( "c:\users\username\downloads\SongData.xlsx", "Sheet1", firstcell, "F1048576", False)
If IsArrayDimmed(arrsheet) Then
Dim h
For intCount = 0 To UBound(arrSheet,2)
If (arrSheet(3,intCount)-Int(arrSheet(3,intCount)/60)*60) < 10 Then
h = "0"&(arrSheet(3,intCount)-Int(arrSheet(3,intCount)/60)*60)
Else
h = (arrSheet(3,intCount)-Int(arrSheet(3,intCount)/60)*60)
End If
arrSheet(3,intCount) = Int(arrSheet(3,intCount)/60) & ":" & h
Set SongIterator = SDB.Database.QuerySongs(" SongTitle = '" & Replace(arrSheet(0,intCount),"'","''") & "' AND Album = '" & Replace(arrSheet(2,intCount),"'","''") & "' AND Artist = '" & Replace(arrSheet(1,intCount),"'","''") & "' ")
While Not SongIterator.EOF
Set Song = SongIterator.Item
If Song.SongLengthString = arrSheet(3,intCount) Then
Song.Rating = arrSheet(4,intCount) * 10
End If
Song.UpdateDB
Song.WriteTags
SongIterator.Next
Wend
SDB.ProcessMessages
Next
Dim f,g
g = Right(firstcell, Len(firstcell)-1)
Set f = fso.CreateTextFile("c:\users\username\appdata\roaming\mediamonkey\scripts\timestamp.txt", True, True)
f.WriteLine("A"&UBound(arrSheet,2)+1+g)
f.Close
End If
End Sub
Function IsArrayDimmed(arr)
IsArrayDimmed = False
If IsArray(arr) Then
On Error Resume Next
Dim ub : ub = UBound(arr)
If (Err.Number = 0) And (ub >= 0) Then IsArrayDimmed = True
End If
End Function
Function ReadExcel( myXlsFile, mySheet, my1stCell, myLastCell, blnHeader )
' Function : ReadExcel
' Version : 3.00
' This function reads data from an Excel sheet without using MS-Office
'
' Arguments:
' myXlsFile [string] The path and file name of the Excel file
' mySheet [string] The name of the worksheet used (e.g. "Sheet1")
' my1stCell [string] The index of the first cell to be read (e.g. "A1")
' myLastCell [string] The index of the last cell to be read (e.g. "D100")
' blnHeader [boolean] True if the first row in the sheet is a header
'
' Returns:
' The values read from the Excel sheet are returned in a two-dimensional
' array; the first dimension holds the columns, the second dimension holds
' the rows read from the Excel sheet.
'
' Written by Rob van der Woude
' http://www.robvanderwoude.com
Dim arrData( ), i, j
Dim objExcel, objRS
Dim strHeader, strRange
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
' Define header parameter string for Excel object
If blnHeader Then
strHeader = "HDR=YES;"
Else
strHeader = "HDR=NO;"
End If
' Open the object for the Excel file
Set objExcel = CreateObject( "ADODB.Connection" )
' IMEX=1 includes cell content of any format; tip by Thomas Willig.
' Connection string updated by Marcel Niënkemper to open Excel 2007 (.xslx) files.
objExcel.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
myXlsFile & ";Extended Properties=""Excel 12.0;IMEX=1;" & _
strHeader & """"
' Open a recordset object for the sheet and range
Set objRS = CreateObject( "ADODB.Recordset" )
strRange = mySheet & "$" & my1stCell & ":" & myLastCell
objRS.Open "Select * from [" & strRange & "]", objExcel, adOpenStatic
' Read the data from the Excel sheet
i = 0
Do Until objRS.EOF
' Stop reading when an empty row is encountered in the Excel sheet
If IsNull( objRS.Fields(0).Value ) Or Trim( objRS.Fields(0).Value ) = "" Then Exit Do
' Add a new row to the output array
ReDim Preserve arrData( objRS.Fields.Count - 1, i )
' Copy the Excel sheet's row values to the array "row"
' IsNull test credits: Adriaan Westra
For j = 0 To objRS.Fields.Count - 1
If IsNull( objRS.Fields(j).Value ) Then
arrData( j, i ) = ""
Else
arrData( j, i ) = Trim( objRS.Fields(j).Value )
End If
Next
' Move to the next row
objRS.MoveNext
' Increment the array "row" number
i = i + 1
Loop
' Close the file and release the objects
objRS.Close
objExcel.Close
Set objRS = Nothing
Set objExcel = Nothing
' Return the results
ReadExcel = arrData
End Function
Re: Syncing ratings from a mobile phone
OK, got everything working, in case anyone finds this helpful. This does not read directly from the Google Sheet and instead just downloads it as an Excel file (you'll need to share the Sheet for this to work correctly).
I can provide direction to implementing the Tasker setup on Android to populate the Google Sheet if anyone would like this.
I can provide direction to implementing the Tasker setup on Android to populate the Google Sheet if anyone would like this.
Code: Select all
Sub Excelimport
strFileURL = "https://docs.google.com/spreadsheets/d/INSERTGOOGLEDOCID/export?format=xlsx"
strHDLocation = "c:\users\username\downloads\SongData.xlsx"
' Fetch the file
Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP.3.0")
objXMLHTTP.open "GET", strFileURL, false
objXMLHTTP.send()
'Response 200 is OK, now download sheet
If objXMLHTTP.Status = 200 And objXMLHTTP.readyState = 4 Then
Set objADOStream = CreateObject("ADODB.Stream")
objADOStream.Open
objADOStream.Type = 1 'adTypeBinary
objADOStream.Write objXMLHTTP.ResponseBody
objADOStream.Position = 0 'Set the stream position to the start
objADOStream.SaveToFile strHDLocation, 2
objADOStream.Close
Set objADOStream = Nothing
End If
Dim arrSheet, intCount, firstcell, file, fso, SongIterator, Song
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("c:\users\username\appdata\roaming\mediamonkey\scripts\timestamp.txt") Then
Set file = fso.OpenTextFile("c:\users\username\appdata\roaming\mediamonkey\scripts\timestamp.txt", 1,,-1)
firstcell = file.ReadLine
If Not IsNumeric(Right(firstcell, Len(firstcell)-1)) Then firstcell = "A2"
Else
firstcell = "A2"
End If
arrSheet = ReadExcel( strHDLocation, "Sheet1", firstcell, "F1048576", False)
If IsArrayDimmed(arrsheet) Then
Dim h
For intCount = 0 To UBound(arrSheet,2)
If (arrSheet(3,intCount)-Int(arrSheet(3,intCount)/60)*60) < 10 Then
h = "0"&(arrSheet(3,intCount)-Int(arrSheet(3,intCount)/60)*60)
Else
h = (arrSheet(3,intCount)-Int(arrSheet(3,intCount)/60)*60)
End If
arrSheet(3,intCount) = Int(arrSheet(3,intCount)/60) & ":" & h
Set SongIterator = SDB.Database.QuerySongs(" SongTitle = '" & Replace(arrSheet(0,intCount),"'","''") & "' AND Album = '" & Replace(arrSheet(2,intCount),"'","''") & "' AND Artist = '" & Replace(arrSheet(1,intCount),"'","''") & "' ")
While Not SongIterator.EOF
Set Song = SongIterator.Item
If Song.SongLengthString = arrSheet(3,intCount) Then
Song.Rating = arrSheet(4,intCount) * 10
If Len(Song.Grouping)=0 And arrSheet(5,intCount) = "on" Then
Song.Grouping = "Instrumental"
ElseIf arrSheet(5,intCount) = "on" And InStr(Song.Grouping,"Instrumental") = 0 Then Song.Grouping = Song.Grouping & "; Instrumental"
End If
End If
Song.UpdateDB
Song.WriteTags
'SongIterator.Item.UpdateAll
SongIterator.Next
Wend
SDB.ProcessMessages
Next
Dim f,g
g = Right(firstcell, Len(firstcell)-1)
Set f = fso.CreateTextFile("c:\users\username\appdata\roaming\mediamonkey\scripts\timestamp.txt", True, True)
f.WriteLine("A"&UBound(arrSheet,2)+1+g)
f.Close
End If
End Sub
Function IsArrayDimmed(arr)
IsArrayDimmed = False
If IsArray(arr) Then
On Error Resume Next
Dim ub : ub = UBound(arr)
If (Err.Number = 0) And (ub >= 0) Then IsArrayDimmed = True
End If
End Function
Function ReadExcel( myXlsFile, mySheet, my1stCell, myLastCell, blnHeader )
' Function : ReadExcel
' Version : 3.00
' This function reads data from an Excel sheet without using MS-Office
'
' Arguments:
' myXlsFile [string] The path and file name of the Excel file
' mySheet [string] The name of the worksheet used (e.g. "Sheet1")
' my1stCell [string] The index of the first cell to be read (e.g. "A1")
' myLastCell [string] The index of the last cell to be read (e.g. "D100")
' blnHeader [boolean] True if the first row in the sheet is a header
'
' Returns:
' The values read from the Excel sheet are returned in a two-dimensional
' array; the first dimension holds the columns, the second dimension holds
' the rows read from the Excel sheet.
'
' Written by Rob van der Woude
' http://www.robvanderwoude.com
Dim arrData( ), i, j
Dim objExcel, objRS
Dim strHeader, strRange
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
' Define header parameter string for Excel object
If blnHeader Then
strHeader = "HDR=YES;"
Else
strHeader = "HDR=NO;"
End If
' Open the object for the Excel file
Set objExcel = CreateObject( "ADODB.Connection" )
' IMEX=1 includes cell content of any format; tip by Thomas Willig.
' Connection string updated by Marcel Niënkemper to open Excel 2007 (.xslx) files.
objExcel.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
myXlsFile & ";Extended Properties=""Excel 12.0;IMEX=1;" & _
strHeader & """"
' Open a recordset object for the sheet and range
Set objRS = CreateObject( "ADODB.Recordset" )
strRange = mySheet & "$" & my1stCell & ":" & myLastCell
objRS.Open "Select * from [" & strRange & "]", objExcel, adOpenStatic
' Read the data from the Excel sheet
i = 0
Do Until objRS.EOF
' Stop reading when an empty row is encountered in the Excel sheet
If IsNull( objRS.Fields(0).Value ) Or Trim( objRS.Fields(0).Value ) = "" Then Exit Do
' Add a new row to the output array
ReDim Preserve arrData( objRS.Fields.Count - 1, i )
' Copy the Excel sheet's row values to the array "row"
' IsNull test credits: Adriaan Westra
For j = 0 To objRS.Fields.Count - 1
If IsNull( objRS.Fields(j).Value ) Then
arrData( j, i ) = ""
Else
arrData( j, i ) = Trim( objRS.Fields(j).Value )
End If
Next
' Move to the next row
objRS.MoveNext
' Increment the array "row" number
i = i + 1
Loop
' Close the file and release the objects
objRS.Close
objExcel.Close
Set objRS = Nothing
Set objExcel = Nothing
' Return the results
ReadExcel = arrData
End Function