Paypal API returned data is JSON - how do I parse?

[peskywinnets]

So I've just come up against a 'gotcha'...namely an Ebay supplied shipping address can differ from the Paypal shipping address...I need to send to the Paypal shipping address. The only way I can get the Paypal shipping address is from Paypal using their API....but the returned data is not XML (that I'm getting familiar with parsing) but it's in a format that looks like this...

{
  "payments": [
    {
      "id": "PAY-0US81985GW1191216KOY7OXA",
      "create_time": "2014-06-30T23:48:44Z",
      "update_time": "2014-06-30T23:49:27Z",
      "state": "approved",
      "intent": "order",
      "payer": {
        "payment_method": "paypal"
      },
      "transactions": [
        {
          "amount": {
            "total": "41.15",
            "currency": "USD",
            "details": {
              "subtotal": "30.00",
              "tax": "1.15",
              "shipping": "10.00"
            }
          },
          "description": "This is the payment transaction description.",
          "item_list": {
            "items": [
              {
                "name": "hat",
                "sku": "1",
                "price": "3.00",
                "currency": "USD",
                "quantity": "5"
              },
              {
                "name": "handbag",
                "sku": "product34",
                "price": "15.00",
                "currency": "USD",
                "quantity": "1"
              }
            ],
            "shipping_address": {
[COLOR="Red"]              "recipient_name": "John Doe",
              "line1": "4th Floor,One Lagoon Drive",
              "line2": "unit #34",
              "city": "Redwood City",
              "state": "CA",
              "phone": "4084217591",
              "postal_code": "94065",
              "country_code": "US"[/COLOR]

And so on...

I'm only interested in pulling out the shipping address in Red.

How would I tackle this?

I'm figuring dump the returned API data to a text file, then read down the text file line by line until I match "shipping_address", then pull in each line's data & pipe to an access table field?

Paypal API references:

https://developer.paypal.com/docs/api/payments/#sale_get)

https://developer.paypal.com/docs/api/overview/

 

[CJ_London]

assuming you only have one shipping address per file and it is the last 'set of data' in the file you can isolate the relevant data along the following lines

dim myData() as string
mydata=split(mid(JSONfile,instr(JSONfile,"Shipping Address")+20),":")

this will give you elements of
1 - "recipient_name"
2 - "John Doe",
"line1"
3 - "4th Floor,One Lagoon Drive",
"line2"
4 - "unit #34",

etc

which you can parse through

for I=1 to unbound(mydata)-1 '(ignore first element)
fldvalue=replace(left(mydata(I),instr(mydata(I),","),""","")
select case I
case 1
rename=fldvalue
case 2
etc
next I


If you have multiple shipping addresses then split on the shipping_address

myJSON() as string
myJSON=split(JSONFile,"Shipping_Address")

and modify the above code to extract the data

 

[peskywinnets]

That's great & a useful reference

I've since learned of something called Paypal IPN, which might be the way to go here, I need to delve a bit more, but if not, I'll surely be using your code - many thanks!

 

[jdraw]

pesky,

Do you have a full response that you can share? You can change the names of people, city as long as you maintain the format.

 

[peskywinnets]

pesky,

Do you have a full response that you can share? You can change the names of people, city as long as you maintain the format.

Hi JD,

I don't have a response yet, as I've not got to the stage of working out how to make the request in VBA!! (I've learnt Amazon, Ebay....which both work differently .....& now Paypal which works *way* differently!).

That said, Paypal provide a sample response...

https://developer.paypal.com/docs/api/payments/#sale_get (right of screen)

It looks like the IPN method I spoke of in my last reply is just going to be unworkable for my requirements, so the balance is tipping back to parsing the above