Shopify API call (1 Viewer)

peskywinnets

Registered User.
Local time
Today, 13:46
Joined
Feb 4, 2014
Messages
576
Really struggling here wrt constructing the initial API call (for Shopify) ...there's not a lot of related info via a google search.

Has anyone got any code to get me off the ground?

All I seek is the most basic of code to construct a proper formatted get request using Shopify API key (username) & secret key (password)

here's the URL that works... (this is just a test site that will be deleted in a week or so, so I'm not worried about sharing the keys)


(in other words if you copy that URL into a browser you don't get an error)

I'm clearly missing the point re how to reconstruct the above in VBA, here's what I've done (which is wrong)....

Code:
Public Function Shopify()
strLogin = "https://pht-store.myshopify.com/admin/api/2021-01/orders.json/authenticateUser?login=d6615cf12fa04c957a470c5d18d50a52&apiKey=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"

Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
XMLHTTP.Open "GET", strLogin
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.send

strReturn = XMLHTTP.responseText
Debug.Print strReturn

Any top tips?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 06:46
Joined
Oct 29, 2018
Messages
21,357
Hi. Do you have a link to Shopify's API website?
 

peskywinnets

Registered User.
Local time
Today, 13:46
Joined
Feb 4, 2014
Messages
576
Hi. Do you have a link to Shopify's API website?

They have this...


and also this


...which is a lot of reading. My problem is just getting off the ground with the right 'call', e.g....

Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
XMLHTTP.Open "GET", strLogin
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.send

using my provided key d6615cf12ea04c957a470c5d18d50a52 & secret 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

my immediate goal is just to get something returned by their API server that looks like the same as when you use this manual URL....

https://d6615cf12ea04c957a470c5d18d50a52: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@pht-store.myshopify.com/admin/api/2021-01/orders.json

My problem is everyone is using other coding languages to construct their API calls ...and I'm not much good at picking the bones out of what people have implemented in other languages & translating them into what I need to do in VBA!

Once I have assembled call in the right structured format, I'm off to the races!
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:46
Joined
Sep 21, 2011
Messages
14,041
Well I used your code after Dimming the various variables :( and received an output in strReturn, but did not know what to do with it after that?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:46
Joined
Oct 29, 2018
Messages
21,357
They have this...


and also this


...which is a lot of reading. My problem is just getting off the ground with the right 'call', e.g....

Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
XMLHTTP.Open "GET", strLogin
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.send

using my provided key d6615cf12ea04c957a470c5d18d50a52 & secret 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

my immediate goal is just to get something returned by their API server that looks like the same as when you use this manual URL....

https://d6615cf12ea04c957a470c5d18d50a52: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@pht-store.myshopify.com/admin/api/2021-01/orders.json

My problem is everyone is using other coding languages to construct their API calls ...and I'm not much good at picking the bones out of what people have implemented in other languages & translating them into what I need to do in VBA!

Once I have assembled call in the right structured format, I'm off to the races!
Hi. Thanks for the links. I had to help someone recently with pulling orders data from Amazon, but I had to read their API's website first.

I'll take a look at those links and let you know if I figure it out.
 

peskywinnets

Registered User.
Local time
Today, 13:46
Joined
Feb 4, 2014
Messages
576
Well I used your code after Dimming the various variables :( and received an output in strReturn, but did not know what to do with it after that?
Yes, I got a load of verbose stuff back, but I don't think it's right (which suggests my API call was wrongly constructed) - what I would be expecting back (if the API call was constructed correctly) would be something like seen when manually typing this URL...

https://d6615cf12ea04c957a470c5d18d50a52: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@pht-store.myshopify.com/admin/api/2021-01/orders.json

so this would be returned...

{"orders":[]}
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:46
Joined
Sep 21, 2011
Messages
14,041
Yes, I got a load of verbose stuff back, but I don't think it's right (which suggests my API call was wrongly constructed) - what I would be expecting back (if the API call was constructed correctly) would be something like seen when manually typing this URL...

https://d6615cf12ea04c957a470c5d18d50a52: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@pht-store.myshopify.com/admin/api/2021-01/orders.json

so this would be returned...

{"orders":[]}
So why can you not supply that url with VBA?
 

peskywinnets

Registered User.
Local time
Today, 13:46
Joined
Feb 4, 2014
Messages
576
So why can you not supply that url with VBA?

I guess that's what I'm seeking ...i.e. how to construct that supplied URL as an HTTP Get request in VBA, specifically using these bits in red...

XMLHTTP.Open "GET", strLogin
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.send

I don't understand sufficiently about the SetRequestHeader & the associated authentification (but this might not be the issue)
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:46
Joined
Sep 21, 2011
Messages
14,041
I've only ever used a simple url request with an api key on one site that downloads the info I need, and that is in Excel as well.

You do not say what you actually want to do with it? and that link you supplied appeared to use different api code for different purposes like Billing etc.
Here are a few links https://www.google.com/search?q=use...9i57j33i160.6917j0j4&sourceid=chrome&ie=UTF-8 that might help, but I'll bow out. Sorry. :(
 

peskywinnets

Registered User.
Local time
Today, 13:46
Joined
Feb 4, 2014
Messages
576
I've only ever used a simple url request with an api key on one site that downloads the info I need, and that is in Excel as well.

You do not say what you actually want to do with it? and that link you supplied appeared to use different api code for different purposes like Billing etc.
I've kludged together a lot of my own APIs (interfacing with Ebay etc), but they weren't REST type APIs (which is what the Shopify API here is)... additionally, I was able to source sample VBA code from all over the web towards getting an end result I needed ...but I've never managed to get off the ground with a REST API call, so stage one is just to get the basic API call working (structured in the correct format) ....parsing the data will be a learning curve but that's the next stage!

REST APIs are now the standard, I'd be surprised if nobody in the VBA world has managed to deploy a REST API using VBA.
 

bastanu

AWF VIP
Local time
Today, 06:46
Joined
Apr 13, 2010
Messages
1,401

theDBguy

I’m here to help
Staff member
Local time
Today, 06:46
Joined
Oct 29, 2018
Messages
21,357
I guess that's what I'm seeking ...i.e. how to construct that supplied URL as an HTTP Get request in VBA, specifically using these bits in red...

XMLHTTP.Open "GET", strLogin
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.send

I don't understand sufficiently about the SetRequestHeader & the associated authentification (but this might not be the issue)
Hi. So, after reading all the documentation, I got halfway there with this.
Code:
    .Open "GET", "https://pht-store.myshopify.com/admin/api/2021-01/orders.json", False
    .setRequestHeader "Accept", "text/json"
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Authorization", "Basic ZDY2MTVjZjEyZWEwNGM5NTdhNDcwYzVkMThkNTBhNTI6c2hwcGFfMjk1NjcxNmYwYjI4YzU3YzYyZWQ0MmRjOTRhYjcxNmU="
    .Send
    Debug.Print .responseText
Hope that gets you closer...
 

peskywinnets

Registered User.
Local time
Today, 13:46
Joined
Feb 4, 2014
Messages
576
Hi. So, after reading all the documentation, I got halfway there with this.
Code:
    .Open "GET", "https://pht-store.myshopify.com/admin/api/2021-01/orders.json", False
    .setRequestHeader "Accept", "text/json"
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Authorization", "Basic ZDY2MTVjZjEyZWEwNGM5NTdhNDcwYzVkMThkNTBhNTI6c2hwcGFfMjk1NjcxNmYwYjI4YzU3YzYyZWQ0MmRjOTRhYjcxNmU="
    .Send
    Debug.Print .responseText
Hope that gets you closer...
Wow...thanks....that's it! Their API server returned...

{"orders":[]} (just like the manual URL did)

How did you come up with this bit...

ZDY2MTVjZjEyZWEwNGM5NTdhNDcwYzVkMThkNTBhNTI6c2hwcGFfMjk1NjcxNmYwYjI4YzU3YzYyZWQ0MmRjOTRhYjcxNmU=

vs the Username(API Key) of d6615cf12ea04c957a470c5d18d50a52 & Password (Secret Key) of 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

Clearly there's a significant bit of the process I've not grasped here yet!

Many thanks :cool:
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:46
Joined
Oct 29, 2018
Messages
21,357
How did you come up with this bit...

ZDY2MTVjZjEyZWEwNGM5NTdhNDcwYzVkMThkNTBhNTI6c2hwcGFfMjk1NjcxNmYwYjI4YzU3YzYyZWQ0MmRjOTRhYjcxNmU=

vs the Username(API Key) of d6615cf12ea04c957a470c5d18d50a52 & Password (Secret Key) of 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
I was trying to produce that manually, but couldn't find the right combination yet. So, I cheated and lifted that up from the test site you provided earlier.
 

peskywinnets

Registered User.
Local time
Today, 13:46
Joined
Feb 4, 2014
Messages
576
Many thanks to the DBGuy ...sterling input there :)

Just in case anyone is trying to use the above code with the supplied key/secret I've now deleted on my test site keys as I've now imported some live/personal data
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:46
Joined
Oct 29, 2018
Messages
21,357
Many thanks to the DBGuy ...sterling input there :)

Just in case anyone is trying to use the above code with the supplied key/secret I've now deleted on my test site keys as I've now imported some live/personal data
Hi. Good luck with your project!
 

peskywinnets

Registered User.
Local time
Today, 13:46
Joined
Feb 4, 2014
Messages
576
Just to help anyone else out here (who may find this thread at a later date via google etc.)

here's some code that shows how to request all your Shopify products as a JSON API call using VBA ...

Code:
Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")

    XMLHTTP.Open "GET", "https://my-store.myshopify.com/admin/api/2021-01/products.json", False
    XMLHTTP.setRequestHeader "Accept", "text/json"
    XMLHTTP.setRequestHeader "Content-Type", "application/json"
    XMLHTTP.setRequestHeader "Authorization", "Basic OWY1NjZkNGVjOWYyMTA1ZjU2MmEzNzI5MWI2N3Q1NjY6c2hwcGFfNmZmZTk1NmY2OWRjMDZjZjY5NGJlYmU4NzVhMGJhMTM="
    XMLHTTP.send
    Debug.Print XMLHTTP.responseText

The bit that caught me out (& thankfully the DBGuy solved) is this hashed string..

OWY1NjZkNGVjOWYyMTA1ZjU2MmEzNzI5MWI2N3Q1NjY6c2hwcGFfNmZmZTk1NmY2OWRjMDZjZjY5NGJlYmU4NzVhMGJhMTM=

What that bit is, is your 'API Key' & 'Secret' joined (with a ":" separating them) & hashed, there are a couple of ways to achieve this...

1. Use Firefox developer console & drop in your required URL (which is provided in your shopify dashboard), for example, if you API Key (username) is d6615cf12ea04c957a470c5d19d50e52 & your password (Secret) is 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 & your shopify address is my-store.myshopify.com then the full URL to request your Shopify products will look like this...

https://d6615cf12ea04c957a470c5d19d50e52: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@my-store.myshopify.com/admin/api/2021-01/orders.json

but for your VBA code, you need the hashed version of d6615cf12ea04c957a470c5d19d50e52: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

Fire up a firefox browser, from the top right settings menu, select 'Web developer' then 'Network' ...now copy/paste the URL into the address bar (& hit enter), now select 'headers' tab at the top of screen ...scroll down & you will see authentication field - it's that string you need to put in your code above

Or...

2. add a module to access so you can hash your API Key & Secret yourself, I found this link which shows the code to do this...


just add the code below into a module then call it, for example

Code:
APIKey= "3yWdlBESOpFIih3f1N1WjkMx90VIT7q5"
Secret = "apn8zxnqhrwIVYpFk6AA4eotkfQoF3g6"
hashed = Base64Encode(APIKey & ":" & Secret)
debug.print hashed

the contents of variable hashed is the string you need when making an API call


Code:
Function Base64Encode(sText)
    Dim oXML, oNode
    Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
    Set oNode = oXML.createElement("base64")
    oNode.DataType = "bin.base64"
    oNode.nodeTypedValue = Stream_StringToBinary(sText)
    Base64Encode = oNode.Text
    Set oNode = Nothing
    Set oXML = Nothing
End Function


'Stream_StringToBinary Function
'2003 Antonin Foller, http://www.motobit.com
'Text - string parameter To convert To binary data
Function Stream_StringToBinary(Text)
  Const adTypeText = 2
  Const adTypeBinary = 1

  'Create Stream object
  Dim BinaryStream 'As New Stream
  Set BinaryStream = CreateObject("ADODB.Stream")

  'Specify stream type - we want To save text/string data.
  BinaryStream.Type = adTypeText

  'Specify charset For the source text (unicode) data.
  BinaryStream.Charset = "us-ascii"

  'Open the stream And write text/string data To the object
  BinaryStream.Open
  BinaryStream.WriteText Text

  'Change stream type To binary
  BinaryStream.Position = 0
  BinaryStream.Type = adTypeBinary

  'Ignore first two bytes - sign of
  BinaryStream.Position = 0

  'Open the stream And get binary data from the object
  Stream_StringToBinary = BinaryStream.Read

  Set BinaryStream = Nothing
End Function

'Stream_BinaryToString Function
'2003 Antonin Foller, http://www.motobit.com
'Binary - VT_UI1 | VT_ARRAY data To convert To a string
Function Stream_BinaryToString(Binary)
  Const adTypeText = 2
  Const adTypeBinary = 1

  'Create Stream object
  Dim BinaryStream 'As New Stream
  Set BinaryStream = CreateObject("ADODB.Stream")

  'Specify stream type - we want To save text/string data.
  BinaryStream.Type = adTypeBinary

  'Open the stream And write text/string data To the object
  BinaryStream.Open
  BinaryStream.Write Binary

  'Change stream type To binary
  BinaryStream.Position = 0
  BinaryStream.Type = adTypeText

  'Specify charset For the source text (unicode) data.
  BinaryStream.Charset = "us-ascii"

  'Open the stream And get binary data from the object
  Stream_BinaryToString = BinaryStream.ReadText
  Set BinaryStream = Nothing
End Function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:46
Joined
Oct 29, 2018
Messages
21,357
Just to help anyone else out here (who may find this thread at a later date via google etc.)

here's some code that shows how to request all your Shopify products ...

Code:
Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")

    XMLHTTP.Open "GET", "https://my-store.myshopify.com/admin/api/2021-01/products.json", False
    XMLHTTP.setRequestHeader "Accept", "text/json"
    XMLHTTP.setRequestHeader "Content-Type", "application/json"
    XMLHTTP.setRequestHeader "Authorization", "Basic OWY1NjZkNGVjOWYyMTA1ZjU2MmEzNzI5MWI2N3Q1NjY6c2hwcGFfNmZmZTk1NmY2OWRjMDZjZjY5NGJlYmU4NzVhMGJhMTM="
    XMLHTTP.send
    Debug.Print XMLHTTP.responseText

The bit that caught me out (& thankfully the DBGuy addressed) is the bit in bold. What that bolded bit is, is your 'API Key' & 'Secret' hashed, there are a couple of ways to achieve this...

1. Use Firefox developer console & drop in your required URL (which is provided in your shopify dashboard), for example, if you API Key (username) is d6615cf12ea04c957a470c5d19d50e52 & your password (Secret) is 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 & your shopify address is my-store.myshopify.com then the full URL to request your Shopify products will look like this...

https://d6615cf12ea04c957a470c5d19d50e52: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@my-store.myshopify.com/admin/api/2021-01/orders.json

but you need the hashed version of d6615cf12ea04c957a470c5d19d50e52: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


Fire up a firefox browser, from the top right settings menu, select 'Web developer' then 'Network' ...now copy/paste the URL into the address bar (& & hit enter), now select 'headers' tab at the top of screen ...scroll down & you will see authentication field - it's that string you need to put in your code above

The other way is to add a module to access so you can hash it your self, I found this link which shows the code to do this...



just add the code below into a module then call it, for example

Code:
APIKey= "3yWdlBESOpFIih3f1N1WjkMx90VIT7q5"
Secret = "apn8zxnqhrwIVYpFk6AA4eotkfQoF3g6"
hashed = Base64Encode(APIKey & ":" & Secret)
debug.print hashed

the contents of variable hashed is the string you need when making an API call


Code:
Function Base64Encode(sText)
    Dim oXML, oNode
    Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
    Set oNode = oXML.createElement("base64")
    oNode.DataType = "bin.base64"
    oNode.nodeTypedValue = Stream_StringToBinary(sText)
    Base64Encode = oNode.Text
    Set oNode = Nothing
    Set oXML = Nothing
End Function


'Stream_StringToBinary Function
'2003 Antonin Foller, http://www.motobit.com
'Text - string parameter To convert To binary data
Function Stream_StringToBinary(Text)
  Const adTypeText = 2
  Const adTypeBinary = 1

  'Create Stream object
  Dim BinaryStream 'As New Stream
  Set BinaryStream = CreateObject("ADODB.Stream")

  'Specify stream type - we want To save text/string data.
  BinaryStream.Type = adTypeText

  'Specify charset For the source text (unicode) data.
  BinaryStream.Charset = "us-ascii"

  'Open the stream And write text/string data To the object
  BinaryStream.Open
  BinaryStream.WriteText Text

  'Change stream type To binary
  BinaryStream.Position = 0
  BinaryStream.Type = adTypeBinary

  'Ignore first two bytes - sign of
  BinaryStream.Position = 0

  'Open the stream And get binary data from the object
  Stream_StringToBinary = BinaryStream.Read

  Set BinaryStream = Nothing
End Function

'Stream_BinaryToString Function
'2003 Antonin Foller, http://www.motobit.com
'Binary - VT_UI1 | VT_ARRAY data To convert To a string
Function Stream_BinaryToString(Binary)
  Const adTypeText = 2
  Const adTypeBinary = 1

  'Create Stream object
  Dim BinaryStream 'As New Stream
  Set BinaryStream = CreateObject("ADODB.Stream")

  'Specify stream type - we want To save text/string data.
  BinaryStream.Type = adTypeBinary

  'Open the stream And write text/string data To the object
  BinaryStream.Open
  BinaryStream.Write Binary

  'Change stream type To binary
  BinaryStream.Position = 0
  BinaryStream.Type = adTypeText

  'Specify charset For the source text (unicode) data.
  BinaryStream.Charset = "us-ascii"

  'Open the stream And get binary data from the object
  Stream_BinaryToString = BinaryStream.ReadText
  Set BinaryStream = Nothing
End Function



Hi. Thanks for the update! Good info.
 

Users who are viewing this thread

Top Bottom