VBA+JSON+Excel+Frustration^2

[heckle...@gmail.com]

Greetings,

I have been searching for the last week to try and figure out how to simply send a JSON categoryID update to an API my company uses.  This has lead to a bunch of side rabbit holes I've had to run down and I've spent roughly the last 3 hours browsing this website as result after several days.   My problem seems quite simple and very common, but the more I dig into it the more I think there isn't a solid answer anywhere.

I've posted the issue onto stackoverflow ( http://stackoverflow.com/questions/21786734/excelvbajsonput-requests ) and am awaiting direction from more seasoned professionals.  It was suggested I just grab a simple PUT request as a starting point and that is what I did.  I am totally open to any new ideas on how to do this as I am basically a layman.

I've downloaded the cDataset file, but all the modules and sheets just overwhelm me.  I'm not sure how to use those files separate of that one worksheet and I get the feeling this website is much to technical for me as even the items in context that seem to be 'easy' are beyond me and when we start mixing in custom classes/modules it just muddies what little understanding I already have .

My 1st Objective:
Take a category ID (or set of) and using Excel+JSON with a PUT request to update the IDs on a customer through the API (which excepts only XML or JSON).

Here is an example session from that API:

PUT /v1/customers/2938293/locations/39483?api_key=xxxxxxxxxxxxx HTTP/1.1 Host: api.site.com Content-Type: application/json;charset=UTF-8 { "zip": "92886", "phone": "7147147147", "countryCode": "US", "state": "CA", "locationName": "Backpack Brigade", "isPhoneTracked": false, "specialOfferIsDeal": false, "specialOffer": "Check out our new Summer Backpacks!", "folderId": "0", "city": "Yorba Linda", "id": "123", "customerId": "140149", "categoryIds": [ 90, 833 ], "suppressAddress": false, "address": "4345 Bastanchury Road", "websiteUrl": "http://backpackbrigade.com/", "hours":"2:12:00:PM:5:00:PM,3:12:00:PM:5:00:PM,4:12:00:PM:5:00:PM,6:12:00:PM:5:00:PM,7:12:00:PM:5:00:PM", "additionalHoursText": "Sunday by Appointment", "description": "Best Backpack Store in Southern California!", "twitterHandle": "backpackbrigade", "logo": { "url": "http://cms.site.com/cms/328812732-backpack.png", "description": "Picture of a backpack" }, "displayLatitude": 33.8991997, "displayLongitude": -117.8437043, "emails":["ad...@backpackbrigade.com"] }

For example I want to add these IDs to a client:

{"categoryIDs" : [ 1, 2096, 2008, 2009 ]}

What I am unsure on how to do is to package this data and send it to the API.

General Questions I have:
Are all APIs that use JSON and XML Restful? 
Is REST properly explained as 'you send something, you get something back'? No duplex communication.

I'm sure this is a simple exercise for you guys and I'm not just looking for an answer, I would like to understand what I'm doing so I can further my overall knowledge.

Much appreciated ahead of time. 

Bruce - I will gladly throw followed links on all my personal web properties if I can get this figured out!

[Bruce Mcpherson]

Glad to help, but let's step back a bit. The vocabulary about categories and clients is unfamiliar without knowing about the API specifics. 

what does a conversation look like...

- you get your data from where? ( a spreadsheet ? what does it looks like ?)

- what do you send and why do you send it ? (are you updating, inserting, deleting or simply querying?)

- what do you expect to get back ? (data?, status?)

- where do you want to put what you get back? (in a spreadsheet?)

- how is the url constructed from the data ? (what is variable, what is constant?)

- other than an api key is there any other form of authentication ?

- can you share the api documentation … all the answers to questions above will be apparent from that...

JSON and XML are simply data structure representation syntax. The main characteristics of restful queries are that they are stateless and platform independent. So you send something that describes whats needed and you get something back. restful queries are very simple to implement, but you need to describe what the conversation looks like.

--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[heckle...@gmail.com]

MUCH appreciated Bruce.  Let me know if you require any more information/clarification.

The data I have to PUT into the API comes from a case study myself and a teammate have been doing.  We've identified category IDs that are better suited for a set of accounts (over 200) and simply need to replace the IDs in these accounts with the ones we've identified as being potentially 'better' for classification of each account. 

I use this same API for auditing information across the accounts, but I do so with an excel plug-in that is meant for scraping and it only does GET and places the JSON that is returned into a single cell that I use a combination of FIND(), LEFT(), MIDDLE(), etc. to compare against a database of client information.

- you get your data from where? ( a spreadsheet ? what does it looks like ?)

The set of IDs that I wish to be pushing to the API are housed in an excel spreadsheet.

- what do you send and why do you send it ? (are you updating, inserting, deleting or simply querying?)

An example would look like this: 1, 1009, 1250.  It has to be packaged in JSON which according to the documentation would look like this: {"categoryIDs" : [ 1, 2096, 2008, 2009 ]}



- what do you expect to get back ? (data?, status?)

I expect to get a status 200/OK confirming the information was successfully PUT into the account.

- where do you want to put what you get back? (in a spreadsheet?)

Simple confirmation would be preferable, this is something that will likely loop through the whole account set.

- how is the url constructed from the data ? (what is variable, what is constant?)

From what I understand the URL is constructed by my administration accounts path + the API key and another argument to return status, general account information on either an individual account level or at a global level.

- other than an api key is there any other form of authentication ?

Only API key.

- can you share the api documentation … all the answers to questions above will be apparent from that...

http://www.yext.com/support/reseller-api/

After looking through a lot of the cDataset I can see a lot of things I can make use of, I just need a little more understanding it would seem.

On Monday, February 17, 2014 4:03:14 PM UTC-6, heckle...@gmail.com wrote:

Greetings,

I have been searching for the last week to try and figure out how to simply send a JSON categoryID update to an API my company uses.  This has lead to a bunch of side rabbit holes I've had to run down and I've spent roughly the last 3 hours browsing this website as result after several days.   My problem seems quite simple and very common, but the more I dig into it the more I think there isn't a solid answer anywhere.

I've posted the issue onto stackoverflow ( http://stackoverflow.com/questions/21786734/excelvbajsonput-requests ) and am awaiting direction from more seasoned professionals.  It was suggested I just grab a simple PUT request as a starting point and that is what I did.  I am totally open to any new ideas on how to do this as I am basically a layman.

I've downloaded the cDataset file, but all the modules and sheets just overwhelm me.  I'm not sure how to use those files separate of that one worksheet and I get the feeling this website is much to technical for me as even the items in context that seem to be 'easy' are beyond me and when we start mixing in custom classes/modules it just muddies what little understanding I already have .

My 1st Objective:
Take a category ID (or set of) and using Excel+JSON with a PUT request to update the IDs on a customer through the API (which excepts only XML or JSON).

Here is an example session from that API:

PUT /v1/customers/2938293/locations/39483?api_key=xxxxxxxxxxxxx HTTP/1.1 Host: api.site.com Content-Type: application/json;charset=UTF-8 { "zip": "92886", "phone": "7147147147", "countryCode": "US", "state": "CA", "locationName": "Backpack Brigade", "isPhoneTracked": false, "specialOfferIsDeal": false, "specialOffer": "Check out our new Summer Backpacks!", "folderId": "0", "city": "Yorba Linda", "id": "123", "customerId": "140149", "categoryIds": [ 90, 833 ], "suppressAddress": false, "address": "4345 Bastanchury Road", "websiteUrl": "http://backpackbrigade.com/", "hours":"2:12:00:PM:5:00:PM,3:12:00:PM:5:00:PM,4:12:00:PM:5:00:PM,6:12:00:PM:5:00:PM,7:12:00:PM:5:00:PM", "additionalHoursText": "Sunday by Appointment", "description": "Best Backpack Store in Southern California!", "twitterHandle": "backpackbrigade", "logo": { "url": "http://cms.site.com/cms/328812732-backpack.png", "description": "Picture of a backpack" }, "displayLatitude": 33.8991997, "displayLongitude": -117.8437043, "emails":["admin@backpackbrigade.com"] }

[Bruce Mcpherson]

I haven't been able to test this of course without the api key, but lets say you have a sheet called locationdata with all the changes needed that looks like this

locations	customers	categoryids
1230	123	1,30,19
231	989	17,90,2

this is all the code you need

Private Function testLocation()

    Dim cb As cBrowser, ds As cDataSet, job As cJobject, s As String, key As String, results As cJobject

    Set ds = New cDataSet

    Set cb = New cBrowser

    Set results = New cJobject

    results.init(Nothing).addArray

    

    s = "https://api.yext.com/v1/customers/"

    key = "?api_key=xxxx"

    With ds.load("locationdata").jObject

        For Each job In .children

            With job.child("categoryids")

                .append JSONParse("[" & .toString() & "]")

                cb.httpPost s & job.toString("customers") & "/locations/" & job.toString("locations") & key, .stringify, _

                    True, , , "PUT"

                If (Not cb.isOk) Then

                    MsgBox "failed" & cb.Text

                Else

                    'store the result

                    results.add.append JSONParse(cb.Text)

                End If

            End With

        Next job

        .tearDown

        cb.tearDown

        ' we could store the results seomwhere if necessary....

        Debug.Print results.stringify

        

        'then

        results.tearDown

    End With

    

End Function

In this example, ive stored the results also, so it would be easy enough (a few lines) to populate another sheet with all the data that came back..

[heckle...@gmail.com]

OK!

I've confirmed that it is connected, but it doesn't seem to be executing the PUT request.  I was curious in how I might go about troubleshooting this?

I added a

MsgBox (cb.Text)

after

results.add.append JSONParse (cb.Text)

...and it is outputting the contents of the account from the API in JSON notation. 

Again Bruce, this has been very much appreciated. I have sandbox info if you'd like.  We can talk on g+ for that info.

On Monday, February 17, 2014 4:03:14 PM UTC-6, heckle...@gmail.com wrote:

Greetings,

I have been searching for the last week to try and figure out how to simply send a JSON categoryID update to an API my company uses.  This has lead to a bunch of side rabbit holes I've had to run down and I've spent roughly the last 3 hours browsing this website as result after several days.   My problem seems quite simple and very common, but the more I dig into it the more I think there isn't a solid answer anywhere.

I've posted the issue onto stackoverflow ( http://stackoverflow.com/questions/21786734/excelvbajsonput-requests ) and am awaiting direction from more seasoned professionals.  It was suggested I just grab a simple PUT request as a starting point and that is what I did.  I am totally open to any new ideas on how to do this as I am basically a layman.

I've downloaded the cDataset file, but all the modules and sheets just overwhelm me.  I'm not sure how to use those files separate of that one worksheet and I get the feeling this website is much to technical for me as even the items in context that seem to be 'easy' are beyond me and when we start mixing in custom classes/modules it just muddies what little understanding I already have .

My 1st Objective:
Take a category ID (or set of) and using Excel+JSON with a PUT request to update the IDs on a customer through the API (which excepts only XML or JSON).

Here is an example session from that API:

PUT /v1/customers/2938293/locations/39483?api_key=xxxxxxxxxxxxx HTTP/1.1 Host: api.site.com Content-Type: application/json;charset=UTF-8 { "zip": "92886", "phone": "7147147147", "countryCode": "US", "state": "CA", "locationName": "Backpack Brigade", "isPhoneTracked": false, "specialOfferIsDeal": false, "specialOffer": "Check out our new Summer Backpacks!", "folderId": "0", "city": "Yorba Linda", "id": "123", "customerId": "140149", "categoryIds": [ 90, 833 ], "suppressAddress": false, "address": "4345 Bastanchury Road", "websiteUrl": "http://backpackbrigade.com/", "hours":"2:12:00:PM:5:00:PM,3:12:00:PM:5:00:PM,4:12:00:PM:5:00:PM,6:12:00:PM:5:00:PM,7:12:00:PM:5:00:PM", "additionalHoursText": "Sunday by Appointment", "description": "Best Backpack Store in Southern California!", "twitterHandle": "backpackbrigade", "logo": { "url": "http://cms.site.com/cms/328812732-backpack.png", "description": "Picture of a backpack" }, "displayLatitude": 33.8991997, "displayLongitude": -117.8437043, "emails":["admin@backpackbrigade.com"] }

[Bruce Mcpherson]

and cb.status? 

my private email is br...@mcpher.com if you want to send me something

[Bryan Heckler]

Status is 200/confirmed. 

I'm using the sandbox and It may be possible that the API won't update listings in the sandbox environment, I'm going to check on that first.

--
You received this message because you are subscribed to a topic in the Google Groups "Excel Liberation" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/excel-ramblings/aEtzwwmV1AI/unsubscribe.
To unsubscribe from this group and all of its topics, send an email to excel-ramblin...@googlegroups.com.

[heckle...@gmail.com]

The following code works:

Private Function testLocation()
    Dim cb As cBrowser, ds As cDataSet, job As cJobject, s As String, key As String, results As cJobject
    Set ds = New cDataSet
    Set cb = New cBrowser
    Set results = New cJobject
    results.init(Nothing).

addArray
   
    s = "https://api.site.com/v1/customers/"

    key = "?api_key=xxxx"
    With ds.load("locationdata").jObject
        For Each job In .children
            With job.child("categoryids")
                .append JSONParse("[" & .toString() & "]")
                cb.httpPost s & job.toString("customers") & "/locations/" & job.toString("locations") & key, .stringify, _
                    True, , , "PUT"
                If (Not cb.isOk) Then
                    MsgBox "failed" & cb.Text
                Else
                    'store the result
                    results.add.append JSONParse(cb.Text)
                End If
            End With
        Next job
        .tearDown
        cb.tearDown
        ' we could store the results seomwhere if necessary....
        Debug.Print results.stringify
       
        'then
        results.tearDown
    End With
   
End Function

Having a worksheet with the correct heading is important.  I had left out a capital letter and while it was giving a success 200 code, it wasn't actually updating the API.  Fixing that and it all worked. 

Adding the following just before the results.teardown, enabled the ability to store the returned data in a separate sheet (all thanks to Bruce and the cDataset modules/classes):

    Dim cr As cRest, sFix As String
    sFix = results.stringify
    Set cr = restQuery("locationresults", , , , "http://nowhere.com", , , False, , , True, sFix)
    makeHeadingsFromData cr
    cr.tearDown
       
    Set cr = restQuery("locationresults", , , , "http://nowhere.com", , , , , , True, sFix)
    cr.tearDown

Cheers!

On Monday, February 17, 2014 4:03:14 PM UTC-6, heckle...@gmail.com wrote:

Greetings,

I have been searching for the last week to try and figure out how to simply send a JSON categoryID update to an API my company uses.  This has lead to a bunch of side rabbit holes I've had to run down and I've spent roughly the last 3 hours browsing this website as result after several days.   My problem seems quite simple and very common, but the more I dig into it the more I think there isn't a solid answer anywhere.

I've posted the issue onto stackoverflow ( http://stackoverflow.com/questions/21786734/excelvbajsonput-requests ) and am awaiting direction from more seasoned professionals.  It was suggested I just grab a simple PUT request as a starting point and that is what I did.  I am totally open to any new ideas on how to do this as I am basically a layman.

I've downloaded the cDataset file, but all the modules and sheets just overwhelm me.  I'm not sure how to use those files separate of that one worksheet and I get the feeling this website is much to technical for me as even the items in context that seem to be 'easy' are beyond me and when we start mixing in custom classes/modules it just muddies what little understanding I already have .

My 1st Objective:
Take a category ID (or set of) and using Excel+JSON with a PUT request to update the IDs on a customer through the API (which excepts only XML or JSON).

Here is an example session from that API:

PUT /v1/customers/2938293/locations/39483?api_key=xxxxxxxxxxxxx HTTP/1.1 Host: api.site.com Content-Type: application/json;charset=UTF-8 { "zip": "92886", "phone": "7147147147", "countryCode": "US", "state": "CA", "locationName": "Backpack Brigade", "isPhoneTracked": false, "specialOfferIsDeal": false, "specialOffer": "Check out our new Summer Backpacks!", "folderId": "0", "city": "Yorba Linda", "id": "123", "customerId": "140149", "categoryIds": [ 90, 833 ], "suppressAddress": false, "address": "4345 Bastanchury Road", "websiteUrl": "http://backpackbrigade.com/", "hours":"2:12:00:PM:5:00:PM,3:12:00:PM:5:00:PM,4:12:00:PM:5:00:PM,6:12:00:PM:5:00:PM,7:12:00:PM:5:00:PM", "additionalHoursText": "Sunday by Appointment", "description": "Best Backpack Store in Southern California!", "twitterHandle": "backpackbrigade", "logo": { "url": "http://cms.site.com/cms/328812732-backpack.png", "description": "Picture of a backpack" }, "displayLatitude": 33.8991997, "displayLongitude": -117.8437043, "emails":["admin@backpackbrigade.com"] }