Trying to get ResponseText from a GET request in Twitter

Question

I'm trying to improve my knowledge of VBA, learning about GET, POST and stuff, because I've seen many examples, and can't get what I'm doing wrong. Probably is the Oauth part.

The main problem is that I'm just an Excel guy. I'm not web developer, so my knowledge is almost null, and probably I'm missing a lot of basic stuff.

I hope this question is not too broad.

BACKGROUND: I'm trying to get the ResponseText of a JSON object, from a tweet. The information is public and you don't need to be logged in to see the info I want to get, and you don't need a Twitter account.

For testing, I'm using this tweet: https://twitter.com/StackOverflow/status/1273391252357201922

WHAT I WANT: Checking the code with Developer Tools (I'm using Firefox), I've seen this:

This GET request returns this ResponseText:

So I would like to get that ResponseText into VBA.

MY CODE: Checking different codes here in SO, I've build up this:

Sub test()
    Dim MiHttp As Object
    Dim MiUrl As String
    Set MiHttp = CreateObject("MSXML2.XMLHTTP")
    
    MiUrl = "https://api.twitter.com/2/timeline/conversation/1273391252357201922.json?include_profile_interstitial_type=1&include_blocking=1&include_blocked_by=1&include_followed_by=1&include_want_retweets=1&include_mute_edge=1&include_can_dm=1&include_can_media_tag=1&skip_status=1&cards_platform=Web-12&include_cards=1&include_ext_alt_text=true&include_reply_count=1&tweet_mode=extended&include_entities=true&include_user_entities=true&include_ext_media_color=true&include_ext_media_availability=true&send_error_codes=true&simple_quoted_tweet=true&count=20&ext=mediaStats%2ChighlightedLabel&include_quote_count=true"
    
    With MiHttp
        .Open "GET", MiUrl
        .Send
        DoEvents
     
        Debug.Print .responseText
    End With


    MiHttp.abort
    Set MiHttp = Nothing
End Sub

And it runs, no coding errors, but I get this:

{"errors":[{"code":200,"message":"Forbidden."}]}

So I tried adding RequestHeaders with Authoritation:

adding this line of code before .Send:

.setRequestHeader "authorization", "Bearer AAAAAAAAAAAAAAAAAAAAANRILgAAAAAAnNwIzUejRCOuH5E6I8xnZz4puTs%3D1Zv7ttfk8LF81IUq16cHjhLTvJu4FA33AGWWjCpTnA"

And then I get this in the debugger: {"errors":[{"message":"Rate limit exceeded","code":88}]}

So checked the Twitter library for developers looking info about Bearer stuff and tokens and I must admit I got overwhelmed.

About Bearer

About Tokens

And now I'm lost. I thought this would be kind of easy, because it's public info that everyone can get manually, from any tweet, without using any app or logging in Twitter, but it's looks like I'm wrong, and I'm kind of lost.

FINAL QUESTION: I would like to know if I can get that Bearer token in any way, then apply it into my code, to get that JSON responseText (dealing with the JSON and learning about them would be a totally different question, out of scope here).

And I would like to achieve this with VBA, no other apps or languages, because I've have no idea.

Actually I'm not even interested in the full text, just the part surrounded with red line.

Looking for some help, guide, light.

Thanks in advance and I hope this question is not too broad.

Thanks!

UPDATES: Tested @ChristosLytras's answer. I get this error:

UPDATE JULY 2020: now the working url is:

https://api.twitter.com/2/timeline/conversation/1273391252357201922.json?include_profile_interstitial_type=1&include_blocking=1&include_blocked_by=1&include_followed_by=1&include_want_retweets=1&include_mute_edge=1&include_can_dm=1&include_can_media_tag=1&skip_status=1&cards_platform=Web-12&include_cards=1&include_ext_alt_text=true&include_reply_count=1&tweet_mode=extended&include_entities=true&include_user_entities=true&include_ext_media_color=true&include_ext_media_availability=true&send_error_codes=true&simple_quoted_tweet=true&count=20&ext=mediaStats%2ChighlightedLabel&include_quote_count=true

Stewart

The endpoint you are calling is not a documented and supported endpoint. It's a private / internal API. You should not be trying to use it. If you want the Tweet information, you can use the official Twitter API documented at developer.twitter.com - those 4 metric values are available in the Twitter Developer Labs Tweets and Users API. – Erbil

@AndyPiper Thanks for your comment. The issue here is that developing something to that api probably can't be coded in VBA, (or at least I do not now how to start). – Stewart

If you lift the constraint that it has to be VBA then I can help you. I can write as little Python as required so that you can write the majority of your application in VBA. – Blueberry

@SMeaden The problem is that I don't know how can i combine htat with VBA. I mean, how to execute that part from you, then return to my code. – Stewart

My blog has plenty of examples of calling into Python. I use Python to implement a COM interface. Then with VBA I call VBA.CreateObject("Foo.bar"), then call a method into the class. Data is returned like any other COM call. This is an example of Python COM gateway class, exceldevelopmentplatform.blogspot.com/2019/02/… – Blueberry

Answer 1

You have to pass a valid fetched Guest Token in the request header along with authorization Bearer and you'll have the response. The twitter public API bearer never changes.

In order to get a new and valid Guest Token for each request, you can make a HEAD request using WinHttp.WinHttpRequest.5.1 instead of MSXML2.XMLHTTP and read the gt cookie using a regular expression like gt=(\d+);. That will fetch the cookie headers each time it's being called. You cannot use MSXML2.XMLHTTP because it uses cache and you won't get a new Guest Token each time you request the HEAD.

Working code tested using Excel 2013 with VBA 7.1:

Dim MiHttp As Object
Dim GuestTokenRE As Object
Dim MiUrl As String

Set MiHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
Set GuestTokenRE = CreateObject("VBScript.RegExp")

MiUrl = "https://api.twitter.com/2/timeline/conversation/1273391252357201922.json?include_profile_interstitial_type=1&include_blocking=1&include_blocked_by=1&include_followed_by=1&include_want_retweets=1&include_mute_edge=1&include_can_dm=1&include_can_media_tag=1&skip_status=1&cards_platform=Web-12&include_cards=1&include_ext_alt_text=true&include_reply_count=1&tweet_mode=extended&include_entities=true&include_user_entities=true&include_ext_media_color=true&include_ext_media_availability=true&send_error_codes=true&simple_quoted_tweet=true&count=20&ext=mediaStats%2ChighlightedLabel&include_quote_count=true"

With MiHttp
    ' Make a HEAD request with no cache to get the Guest Token cookie
    .Open "HEAD", "https://twitter.com", False
    .setRequestHeader "User-Agent", "Firefox"
    .setRequestHeader "Pragma", "no-cache"
    .setRequestHeader "Cache-Control", "no-cache"
    .Send

    DoEvents
    
    ' Use a regular expression to extract guest token from response headers
    GuestTokenRE.Pattern = "Set-Cookie: gt=(\d+);"
    GuestTokenRE.IgnoreCase = True
    
    Dim matches as Object
    Set matches = GuestTokenRE.Execute(.getAllResponseHeaders())
    
    If matches.Count = 1 Then
        Dim guestToken As String
        guestToken = matches.Item(0).Submatches.Item(0)
        
        ' Print the Guest Token for validation
        Debug.Print "Got Guest Token", guestToken
        
        ' Now we have a valid guest token, make the request
        .Open "GET", MiUrl, False
        ' Authorization Bearer is always the same
        .setRequestHeader "authorization", "Bearer AAAAAAAAAAAAAAAAAAAAANRILgAAAAAAnNwIzUejRCOuH5E6I8xnZz4puTs%3D1Zv7ttfk8LF81IUq16cHjhLTvJu4FA33AGWWjCpTnA"
        .setRequestHeader "x-guest-token", guestToken
        .Send
        DoEvents
    
        Debug.Print "Got response", .responseText
    Else
        Debug.Print "Could not fetch Guest Token"
    End If

End With

MiHttp.abort

Set MiHttp = Nothing
Set GuestTokenRE = Nothing

Regarding 80072efe error

You'll have to get WinHttp.WinHttpRequest.5.1 to work. The 80072efe error indicates the connection terminates abnormally and you can read more about it here. I didn't have such an issue so these errors do not originate from the endpoint.

Screen capture of the code in action

Friarbird

Have you tried your own solution? I did the same... The response is: {"errors":[{"code":200,"message":"Forbidden."}]} When i changed url to: https://twitter.com/StackOverflow/status/1273391252357201922 it start working ;) – Idioglossia

Thanks for your answer., but i get as response {"errors":[{"code":200,"message":"Forbidden."}]} – Stewart

@MaciejLos of course... I have even tested this using Postman before I add the answer and it worked. – Friarbird

@FoxfireAndBurnsAndBurns The Bearer token is expired. So you want a way to automated the whole process and the bearer token fetching? – Friarbird

N.B. With a new bearer and guest token I could only get this to work including an x-twitter-active-user = yes request header. – Parmenides

@ChristosLytras It would be cool if I could get what you say. I thought this would be kind of easy just because it's public data that can be obtained without logging. But yeha, indeed it would be helpful – Stewart

@FoxfireAndBurnsAndBurns I have included a method that makes a HEAD request before the actual request so to get a valid Guest Token each time. Then I use that token for the actual call and it works. I have updated my answer, please check it out. – Friarbird

Thanks for your efforts. I've tested this code. I get an error on first .send (the one relative to HEAD method). The error number is -2147012866 Updated question with a screenshot. Also, could you please tell me how I should declare matches? what kind of object is it? – Stewart

Which version of VBA do you have? I'm running this on VBA: Retail 7.1.1097 under Excel 2013. Are you behind any firewall? You have to make WinHttp.WinHttpRequest.5.1 work otherwise you won't be able to get the Guest Token for more than once because MSXML2.XMLHTTP somehow cache the response cookies. Also check what the error you're getting is about here #7612224 – Friarbird

Unfair downvote was eliminated! As i mentioned, when i changed URL, this solution start working for me. – Idioglossia

This code works for me. Congrats. Only thing is when I paste response into online JSON parser I can see "retweet_count":2, "favorite_count":9, "reply_count":2,"quote_count":1, so for the grand total of retweets perhaps you add the retweet_count and quote_count together. Because on the web page it says Retweets = 3. – Blueberry

This is exactly what i need, but I can't get make it work. Let me check the links you provided and see if I can make it work. Probably it's a Firewall/Security thing (I'm trying this at office at Excel 2007 and it would be great if I can make it work in Excel 2007) . About VBA version, let me checkt it out. Thanks a lot for this – Stewart

@SMeaden the "retweet_count":2, "favorite_count":9, "reply_count":2,"quote_count":1 is exactly what I'm looking for. I can split that string into an array easily. but could not get it. I hope I can adapt this answer to Excel 2007, because it's exactly what i need – Stewart

My VBA version is VBA retail 6.5.1057. Also, what kind of object is matches? How should I declare it? Because with Option Explicit it tells me I have not declared that variable. – Stewart

@FoxfireAndBurnsAndBurns matches is for to store the result of a regular expression of a VBScript.RegExp object so you declare it as an object Dim matches as Object. That might be a firewall issue if you have WinHttp.WinHttpRequest.5.1 present and declaring without runtime declaration errors. I'll try to find an Office 2007 to also test this if it fails on you. – Friarbird

I have to add Dim matches and you must too if you have Option Explicit at the top of your module. – Blueberry

@SMeaden I don't have Option Explicit as by default it does not apply to my VBA scripts in Excel. Maybe it's an option but anyway I have added the declaration to my answer. – Friarbird

Tested this code on a different machine with Excel 2019 and this worked perfectly!. I Would like to understand the whole process, Also, I hope i can find a solution to get this code to make it work on Excel 2007 (anyways, I'll check the security/firewall settings). Thanks! – Stewart

Just 1 question. ¿What about the Bearer token? Can it change? Can it be retrieved? Just wondering – Stewart

@FoxfireAndBurnsAndBurns thanks for the bounty. This Bearer token is the same for years and I guess it does not change for public APIs. The process is pretty simple; it starts with a HEAD request to twitter.com so to get the Guest Token cookie and then it uses that fresh Guest Token to make the request. I can include that to my answer if you want. I think I have an old VM with Windows 7 and I'll try to install Excel 2007 to check it out as I'm not sure why WinHttp.WinHttpRequest.5.1 does not working and MSXML2.XMLHTTP does. – Friarbird

You won the bounty. Your code works perfectly. I'm pretty sure it's a security issue. Or maybe could be because Excel 2007 is 32 bit and Excel 2019 is 64 bit? Or base64 encoding? If you include in your answer the process, it would be a great documentation for everybody :), so please do it. Just one last question, but not sure if it's a long answer. I guess the Guest Token will be valid only for a certain amount of time. IS there any simple way to check that and get a new one? – Stewart

@FoxfireAndBurnsAndBurns I've updated the answer. I don't have Excel 2019, this is Excel 2013 with VBA 7.1 that I'm running the code in. It could be a compatibility issue but odds are it's more related to the network. To detect if the token is valid, just see your second comment here; with an expired or invalid token you get {"errors":[{"code":200,"message":"Forbidden."}]}, so, if you get a response JSON object with errors inside and an error with code: 200, then the token is expired. You can even leave it like this and get a new Guest Token on every call, it's up to you. – Friarbird

@ChristosLytras Sorry to bother, but the code fails now in the HEAD petition, so it cannot get the cookie part. Could you please guide me on how to resolve it? – Stewart

@FoxfireAndBurnsAndBurns they have changed it. I can't even access the URL using a browser, I get a 403 error. Do you have any other way or URL to get JSON data using the browser? – Friarbird

@ChristosLytras oh!!! i did not notice that. I'll try to get one and test again your code. Maybe is just that. – Stewart

@ChristosLytras Indeed URL changed, is too long now and I can't post it as comment. Edited my original question. – Stewart