Syncing ratings from a mobile phone

This forum is for questions / discussions regarding development of addons / tweaks for MediaMonkey.

Moderator: Gurus

mhendu
Posts: 98
Joined: Thu Jan 12, 2006 11:18 am

Syncing ratings from a mobile phone

Post by mhendu » Sat Mar 02, 2019 6:12 pm

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!

Lowlander
Posts: 45838
Joined: Sat Sep 06, 2003 5:53 pm
Location: MediaMonkey 5

Re: Syncing ratings from a mobile phone

Post by Lowlander » Sat Mar 02, 2019 6:18 pm

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.
Lowlander (MediaMonkey user since 2003)

mhendu
Posts: 98
Joined: Thu Jan 12, 2006 11:18 am

Re: Syncing ratings from a mobile phone

Post by mhendu » Sat Mar 02, 2019 8:49 pm

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.

Lowlander
Posts: 45838
Joined: Sat Sep 06, 2003 5:53 pm
Location: MediaMonkey 5

Re: Syncing ratings from a mobile phone

Post by Lowlander » Sat Mar 02, 2019 8:52 pm

Lowlander (MediaMonkey user since 2003)

mhendu
Posts: 98
Joined: Thu Jan 12, 2006 11:18 am

Re: Syncing ratings from a mobile phone

Post by mhendu » Sat Mar 02, 2019 8:56 pm

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.

mhendu
Posts: 98
Joined: Thu Jan 12, 2006 11:18 am

Re: Syncing ratings from a mobile phone

Post by mhendu » Sat Mar 02, 2019 9:09 pm

Lowlander wrote:
Sat Mar 02, 2019 8:52 pm
True.

Check out: http://www.mediamonkey.com/forum/viewto ... txt+import
Very interesting, thanks!

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

mhendu
Posts: 98
Joined: Thu Jan 12, 2006 11:18 am

Re: Syncing ratings from a mobile phone

Post by mhendu » Sat Mar 02, 2019 10:00 pm

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.

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

Post Reply