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

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