Processing the conversation

In Instrumenting co-operating app I showed how a web app might create some data in a dead drop to pass back to VBA. In my example, i happen to deal with messages using a log, as described in The dead drop log, but that's not mandatory. All you need is some way of knowing which dead Drop class instances you are expecting to hear from.

Here's the full code for processing the log

Public Sub processDeadDrop(Optional redo As Boolean = False, Optional deleteWhenProcessed As Boolean = True)
    
    ' example showing how you might take feedback data for an entire workbook
    Dim job As cJobject, ds As cDataSet, dr As cDataRow, data As cJobject, _
        subject As cJobject, good As Boolean, dsDrop As cDataSet
    
    ' first step is to get all the known requests
    Set dsDrop = getDeadDropLog
    For Each dr In dsDrop.rows
        ' only do the unprocessed ones or override
        If redo Or IsEmpty(dr.cell("processed")) Or Len(dr.cell("processed").toString) = 0 Then
            With getdeaddrop(dr.cell("class").toString, "messages", False, dr.cell("key").toString)
                ' now we can get all the message data for this
                If Not .scriptDb.getObjectsByQuery.isOk Then
                      MsgBox ("failure getting conversation " & .scriptDbClass)
                Else
                    ' find the the subject of this conversation
                    Set data = .scriptDb.jObject.child("results")
                    Set subject = data.find("subject")
                    If subject Is Nothing Then
                        MsgBox ("failure getting subject for " & .scriptDbClass)
                    Else
                        ' now update the subject sheet with any dialogues
                        Set ds = New cDataSet
                        ds.load subject.toString
                        good = False
                        For Each job In data.children
                            ' we're only handling comments'
                            If isSomething(job.childExists("type")) Then
                                If job.toString("type") = "comment" Then
                                    If updateSubjectCell(ds, job, "comments") Then
                                        deleteMessage .scriptDb, deleteWhenProcessed, job
                                        good = True
                                    End If
                                End If
                            End If
                        Next job
                        If good Then
                            dr.cell("processed").value = Now
                        End If
                        ds.tearDown
                    End If
                End If
            End With
        End If
    Next dr
    dsDrop.column("processed").Commit
    dsDrop.tearDown

End Sub
Private Function updateSubjectCell(ds As cDataSet, job As cJobject, colName As String) As Boolean
    Dim drd As cDataRow
    updateSubjectCell = False
    If ds.headingRow.exists(colName) Is Nothing Then
        MsgBox ("you need to create a " & colName & " column in sheet " & ds.where.Worksheet.name)
    Else
        For Each drd In ds.rows
            If Trim(drd.cell("uniqueid").value) = Trim(job.child("uniqueid").value) Then
                ' found a match - update
                drd.cell(colName).Commit job.child(colName).value
                updateSubjectCell = True
                Exit Function
            End If
        Next drd
    End If
End Function
Private Function deleteMessage(pdb As cScriptDbCom, deleteWhenProcessed As Boolean, job As cJobject) As Boolean
    
    deleteMessage = True
    If deleteWhenProcessed Then
        With pdb.deleteObject(job)
            If Not .isOk Then
                MsgBox ("failed to delete " & job.stringify)
                deleteMessage = False
            End If
        End With
    End If
    
End Function


Walkthrough

Note that there is no specific structure to the message - it's something that you define. Here's what I got back

{   "results":[      {
         "subject":"venuemaster",
         "stamps":{
            "created":"2/5/2014 1:05:21 PM",
            "api":"VBAv0103",
            "handler":"GASv0102"
         },
         "info":"xliberation public data for testing",
         "objectId":"S344890315031"
      },
      {
         "title":"Intex",
         "cj":2,
         "stamps":{
            "created":"2/5/2014 1:05:48 PM",
            "api":"jsv0102",
            "handler":"GASv0102"
         },
         "type":"comment",
         "comments":"somewhere in japan",
         "uniqueId":"103",
         "objectId":"S344882053107"
      }
    ]}
 
Using this code
                If Not .scriptDb.getObjectsByQuery.isOk Then
                      MsgBox ("failure getting conversation " & .scriptDbClass)
                Else
                    ' find the the subject of this conversation
                    Set data = .scriptDb.jObject.child("results")
                   

When I first created this data in getting a deadDrop handle from VBA, i created a data field called subject that would contain the worksheet for which this data is destined. So the first job is to find that in the returned data. Once I have it I can load the original sheet. 

                    Set subject = data.find("subject")
                    If subject Is Nothing Then
                        MsgBox ("failure getting subject for " & .scriptDbClass)
                    Else
                        ' now update the subject sheet with any dialogues
                        Set ds = New cDataSet
                        ds.load subject.toString


Now I can get associate the data through the uniqueid field, and update the comments column

                        For Each job In data.children
                            ' we're only handling comments'
                            If isSomething(job.childExists("type")) Then
                                If job.toString("type") = "comment" Then
                                    If updateSubjectCell(ds, job, "comments") Then
                                        deleteMessage .scriptDb, deleteWhenProcessed, job
                                        good = True
                                    End If
                                End If
                            End If
                        Next job

and update the comments column

Private Function updateSubjectCell(ds As cDataSet, job As cJobject, colName As String) As Boolean
    Dim drd As cDataRow
    updateSubjectCell = False
    If ds.headingRow.exists(colName) Is Nothing Then
        MsgBox ("you need to create a " & colName & " column in sheet " & ds.where.Worksheet.name)
    Else
        For Each drd In ds.rows
            If Trim(drd.cell("uniqueid").value) = Trim(job.child("uniqueid").value) Then
                ' found a match - update
                drd.cell(colName).Commit job.child(colName).value
                updateSubjectCell = True
                Exit Function
            End If
        Next drd
    End If
End Function

and delete the original message if I want (note that the subject line is still there - i could have deleted that too, but leaving it will mean that additional data can still be entered until the message expires)

Private Function deleteMessage(pdb As cScriptDbCom, deleteWhenProcessed As Boolean, job As cJobject) As Boolean
    
    deleteMessage = True
    If deleteWhenProcessed Then
        With pdb.deleteObject(job)
            If Not .isOk Then
                MsgBox ("failed to delete " & job.stringify)
                deleteMessage = False
            End If
        End With
    End If
    
End Function

Here's the result

with updates collected from the web app and picked up back in VBA

You can get me on Google plus, Twitter or this forum. See ScriptDB as a dead drop for more on this

For help and more information join our forumfollow the blogfollow me on twitter
Comments