Find Json Object from the response text

  • This webpage contains the responsetext

    Code
    1. https://syndication.redplum.com/kilgore/StandardSyndicationPartner/offers/?provider=thor&filterByZipCode=77477&filterByLoyaltyProgram=all


    I am trying to return the info to Excel using the following, however I do not know the Json object name.


    If I replace the Public Sub InitScriptEngine() with this code:

    Code
    1. Public Sub InitScriptEngine()
    2. Set ScriptEngine = New ScriptControl
    3. ScriptEngine.Language = "JScript"
    4. ScriptEngine.AddCode "function getProperty(jsonObj, propertyName) { return jsonObj[propertyName]; } "
    5. ScriptEngine.AddCode "function getKeys(jsonObj) { var keys = new Array(); for (var i in jsonObj) { keys.push(i); } return keys; } "
    6. End Sub


    Excel imports the page but returns columns of "success", "success", "success", "success", "success", "77477RP". The next row consists of the word "object" repeating itself several times across the 6 columns.


    How can I specify the object, when I don't know what it is? I attached a sample workbook, in addition to the code above. Thank youozgrid.com/forum/core/index.php?attachment/59977/ for any assistance.

  • Re: Find Json Object from the response text.


    Stick with the original Json code from http://stackoverflow.com/quest…parsing-json-in-excel-vba (why have you changed it?) and:

  • Re: Find Json Object from the response text.


    Thank you for the response. I was changing the original code because I was having trouble writing the equivalent code that you gave me to put the data in Excel. The whole code now executes without any errors, but it returns an empty Excel sheet.

    ozgrid.com/forum/core/index.php?attachment/59987/

  • Re: Find Json Object from the response text.


    You haven't written the array to the sheet:

    Code
    1. With Sheets(1)
    2. .Cells.Clear
    3. .Range("A1").Resize(UBound(Output), UBound(Output, 2)).Value = Output
    4. End With

    Also, the For loop should be:

    Code
    1. For lRow = 1 To UBound(Keys) + 1

    compare Keys and Output to understand why.

  • Re: Find Json Object from the response text


    Would appreciate some help understanding how to determine some values. I am using the same code as above for another webpage. http://api.qples.com/api/clien…6&callback=parse_response
    This is the first object from that page.
    parse_response({"coupons":[{"coupon_id":"1494","description":"any ONE %281%29 San-J 10 oz. Gluten Free Asian Cooking Sauce ","thumb_url":"https:\/\/d1mqfygpcburh5.cloudfront.net\/a05da51b3c4d94138b8680ca083e2080_thumb.jpg","offer_amount":"$0.55 OFF ","brand_id":"97","saving_in_dollar":"0.55","product_name":"Cooking Sauce ","brand_name":"San-J"},


    So coupons is the object and this is a nested object? How do I determine what the index number should be for the script? This is the only part of the above code that has been changed, and I keep getting an "object expected" error.


    I assume my error is coming from not having the correct index integer, but I don't know how to determine what it should be. Thank you for any explanations.

  • Re: Find Json Object from the response text


    Can anyone help me with the above json? I feel like I have set it up exactly as the other, but it keeps returning an "object expected" at this line
    Set DecodeJsonString = ScriptEngine.Eval("(" + JsonString + ")"). I really don't want to ask for help every time I try to set up a new sheet and would really just appreciate any suggestions or pointers as to what to look for when getting such an error. I don't understand how to determine the index either. Just trying to teach myself by trial and error. This is a snippet of the response:

    Code
    1. parse_response({"coupons":[{"coupon_id":"1267","description":" any ONE %281%29 2.8 oz. %E2%80%93 4.2 oz. Revolution Foods Meal Kit ","thumb_url":"https:\/\/d1mqfygpcburh5.cloudfront.net\/ab63990309f0850539e901fcc9d0bf90_thumb.jpg","offer_amount":"$1.00 OFF ","brand_id":"63","saving_in_dollar":"1.00","product_name":"Meal Kit","brand_name":"Revolution Foods"},


    and this is my full code.



    Thank you in advance.

  • Re: Find Json Object from the response text


    Thanks for the explanation, the new VB code returns the error "variable not defined". I changed this line DecodeJsonString(CStr(JsonString)) to Mid as well and get an "argument not optional" error.


    Would my best approach be to start over with a whole javascript code to parse the javascript response?ozgrid.com/forum/core/index.php?attachment/60110/

  • Re: Find Json Object from the response text


    Quote from KK33317;710529

    Thanks for the explanation, the new VB code returns the error "variable not defined". I changed this line DecodeJsonString(CStr(JsonString)) to Mid as well and get an "argument not optional" error.

    I'm not sure which line(s) you've changed - only the DecodeJsonString call needed changing. This works for me:

  • Re: Find Json Object from the response text


    Quote from John_w;710532

    I'm not sure which line(s) you've changed - only the DecodeJsonString call needed changing. This works for me:


    Thank you once again. That works perfect. I was changing the line in the public function, not the call area.

  • Re: Find Json Object from the response text


    This could be sufficient:


  • Re: Find Json Object from the response text


    Still using the above code. I have a new page I am trying to get the json from. It appears to me, I have two arrays and I would like the array that has the object that starts "data". This is the first section of the page:

    Code
    1. { "content": { "collection": [ {"count": "396", "data": [ {"actualpoststart": "4\/27\/2014 12:00:00 AM", "additionaldealinfo": "", "availableonline": "N", "availableonlineproductcode": "", "brandid": "0", "brandname": "Other", "buynow": "", "description": "70 ct. Zyrtec, Claritin or Allegra.\r\n\r\ncon tarjeta Alivio de 24 Horas para las Alergias", "finalprice": "34.9900", "fineprint": "", "gridlistingofferid": "", "highprice": "", "image": "http:\/\/akimages.shoplocal.com\/dyn_li\/150.0.75.0\/Retailers\/Walgreens\/140427_01A_16_BIL_R1_cmb_9.jpg", "imageflag": "7", "interestexposureratio": "0", "inventoryproductid": "", "keyword": "", "largeimage": "Y", "listingend": "5\/3\/2014 12:00:00 AM", "listingid": "-2040835712", "listingstart": "4\/27\/2014 12:00:00 AM", "lowprice": "", "originaldeal": "", "pretailerid": "-99389", "pretailername": "Walgreens", "price": "$34.99 with card", "pricequalifier": "", "productdescription": "", "promotioncode": "Walgreens-140427", "promotionenddate": "5\/3\/2014 12:00:00 AM", "promotionidentifier": "AJ900", "promotionpostenddate": "5\/3\/2014 12:00:00 AM", "promotionpoststartdate": "4\/27\/2014 12:00:00 AM", "promotionstartdate": "4\/27\/2014 12:00:00 AM", "promotiontag": "Walgreens-140427", "promotiontitle": "This Week's Ad", "promotiontypeid": "1", "retailerproductcode": "", "savingsprogramvalue": "", "score": "", "storeid": "2431325", "title": "24-Hour Allergy Relief", "totalinterestexposureratio": "0", "vo": "item" },


    The entire page is here


    My error is actually coming from this line

    Code
    1. Public Function DecodeJsonString(ByVal JsonString As String)
    2. Set DecodeJsonString = ScriptEngine.Eval("(" + JsonString + ")")
    3. End Function


    I am getting
    run time error '1006':
    expected ')'


    Thanks for any help.

  • Re: Find Json Object from the response text


    These are the keys I am trying to get.


    This is what it shows in the error.[ATTACH=CONFIG]60286[/ATTACH] I tried using "collection" as the object and get the same error.


    Off topic: I tried using the code you provided me in post #13 a few days ago and keep getting an error for the "Get". I deleted that attempt, so don't remember specific error, but had to do with IXhtml. Still a worthwhile error as it finally clued me in that the code is C++. May seem blatantly obvious to everyone but a greenpea like myself. Anyway, C++ looks like a simpler code to some degree and when I get a chance I'm going to play with it more. Thanks

  • Re: Find Json Object from the response text


    I continue to get a runtime error of expected ")". I have added and deleted ) all over this code trying to find the error and all I do is change the error. In the attached jpg it is two portions of the code pasted in one. The bottom portion is from the call part of the code, I am fairly certain the error is in the second part shown and I also notice it wants to call the json object differently. The json object is named as "data" currently.
    [ATTACH=CONFIG]60312[/ATTACH]


    Here is a sample workbook. Any help would be gratefully appreciated. Thanks