Refresh Tokens for Gmail access using GoogleAuthenticator

[shajvarg]

Hi,
I am grateful for the great tools for using webservices with VBA.
I tried to access Gmail using these tools.
Everything works but the access tokens time out as they are supposed to.
I wanted to know if there is a way to get "refresh tokens" without user intervention using "IE explorer" page once the user has confirmed access to gmail.
In the current gmail.bas the user has to be reauthenticated everytime after the webclient object is released from memory.
thanks in advance

[RichardWein]

Hi shajvarg,
You're right that the supplied Gmail authenticator module doesn't handle refresh tokens. You could add your own code to the module for handling refresh tokens. I used to do that, but now I've stopped using authenticator modules. I found them awkward to use, since they work recursively: you call WebClient.Execute to make an API call, and within that call to WebClient.Execute other calls get made to WebClient.Execute (albeit a different WebClient) to handle the Oauth2 authentication calls. I prefer to handle the authentication outside the main call to WebClient.Execute.

I'm attaching my own module that I use for handling Google API calls. Feel free to use this, and to change it however you like. Some notes:

1. I've only used it with Google Drive and Google Sheets, but it should be the same for Gmail too, except that you will probably need to change the OAUTH_SCOPES constant.
2. My module uses a few constants and procedures declared in other modules. I've put these in a separate module called "OtherStuff".
3. You need to insert your own values for the CLIENT_ID and CLIENT_SECRET constants.
4. I don't use Internet Explorer. I use the the user's own default browser. Since I can't read the OAuth2 authorization code from that browswer, the user has to copy the code presented by Google in the browser, and paste into an InputBox which the macro opens. (This way of handling it is specifically allowed for by the Google API. In fact, when I tried using IE, Google refused to authenticate on the grounds that the browser's not secure enough.)
5. The code stores the refresh token in the Windows registry.
6. I handle errors by raising error number 9999 (declared as constant CUSTOM_ERROR). You may want to include a top-level error handler for catching these errors and displaying the error description.

I hope that helps.

GoogleApiShared.zip

[shajvarg]

Thanks Richard for the code and the detailed help.
I had a small issue regd the code.
Response.StatusCode = NonHttpCode
NonHttpCode is not defined in my webhelpers bas module
it has only
Public Enum WebStatusCode
Ok = 200
Created = 201
NoContent = 204
NotModified = 304
BadRequest = 400
Unauthorized = 401
Forbidden = 403
NotFound = 404
RequestTimeout = 408
UnsupportedMediaType = 415
InternalServerError = 500
BadGateway = 502
ServiceUnavailable = 503
GatewayTimeout = 504
End Enum

I couldn't find this anywhere else also..

Can you please let me know the actual value of NonHttpCode?

Thanks again
Shaju

[RichardWein]

Oh yes, I forgot. I have my own modified version of WebClient.Execute, which handles errors differently. I dislike the way that standard VBA-Web handles errors raised by WinHTTP.

If you don't want to change WebClient.Execute, please just delete the references to NonHttpCode. Alternatively, use my version of the WebClient module (attached) and add a declaration of NonHttpCode to the WebHelpers module, like this:

Public Enum WebStatusCode
    OK = 200
    created = 201
    NoContent = 204
    NotModified = 304
    BadRequest = 400
    Unauthorized = 401
    Forbidden = 403
    NotFound = 404
    RequestTimeout = 408
    UnsupportedMediaType = 415
    InternalServerError = 500
    BadGateway = 502
    ServiceUnavailable = 503
    GatewayTimeout = 504
    
' *** Added by Richard Wein 25/10/2018 ***
    NonHttpCode = 999
    
End Enum

WebClient.zip

[shajvarg]

Thanks Richard once again..
I got it working by removing the code verification routine as it was saying invalid code verifier after getting authorization code with plain method. I also tried S256, but google disallowed it.

I also got IE to work.. that took more time as there are different reasons to the unsecure browser issue. I took an easy way out and deleted the internet explorer key section in USER section in the registry which worked. the other being looking at policies section..

Shaju

[RichardWein]

Hi Shaju.
It's strange that you had the problems with PKCE code verification. It's worked OK for me. But, as you say, you can remove it. Google makes PKCE optional.

I'm glad you managed to get IE to work. I hope I never have to use IE again, but I'm concerned I might need it in the future. Not all APIs provide the option to copy the the Authorization Code.

[hnsywnwtsly]

Hi Richard,
First I apologize for my English, I use Google Translate
Thanks so much for sharing your code upgrade, I find it extremely useful,
Although I could not use it as it is but I do manage to get a token from it, which allows me to use the authentication only once, and subsequent times to use the refresh token.
I manage to retrieve emails with it, but I could not find how to send an email (I'm really weak with the API), I wonder if you have information on how to send an email.
Another question, will the refresh token be valid forever? And I'll never have to verify again?
Thank you

[shajvarg]

Hi,
I have written some code for sending emails..
you have to add "https://www.googleapis.com/auth/gmail.send" in your scope
and

Dim Request As New WebRequest
Request.resource = "users/{userId}/messages/send"
Request.AddUrlSegment "userId", "me"
Request.AddQuerystringParam "uploadType", "media"
Request.ContentType = "message/rfc822"
Request.ContentLength = Len(sMsg)
Request.Body = sMsg
Request.Method = HttpPost

where sMsg is a proper MIME formatted email message. This particular code uses
baseurl as "https://www.googleapis.com/upload/gmail/v1/"
and is meant for sending attachments also. but it will work for plain emails also

hope it helps
Shaju

[hnsywnwtsly]

You can also add an example of how to create a MIME message ?

[shajvarg]

so the example below is an actual MIME msg.. except the attachment is truncated. if u are sending only a message without attachment you can send till the first boundary and end it with boundary -- https://developers.google.com/gmail/api/guides/sending this link should get you going. Shaju Content-Type: multipart/mixed; boundary=_NextPart_9E20D285C001973CCA9D MIME-Version: 1.0 To: ***@***.*** From: ***@***.*** Subject: Email with attachment

…

--_NextPart_9E20D285C001973CCA9D Content-Type: text/plain; charset="UTF-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Pls see the files attached

--_NextPart_9E20D285C001973CCA9D Content-Type: application/vnd.ms-excel; name="Example.xls" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="Example.xls" 0M8R4KGxGuEAAAAAAAAAAAAAAAAAAAAAPgADAP7/CQAGAAAAAAAAAAAAAAADAAAAAQAAAAAA AAAAEAAA7AAAAAIAAAD+////AAAAAAAAAABiAAAA/wAAAP//////////////////////////

--_NextPart_9E20D285C001973CCA9D--

On Thu, 24 Mar 2022 at 20:32, hnsywnwtsly ***@***.***> wrote: You can also add an example of how to create a MIME message ? — Reply to this email directly, view it on GitHub <#471 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AYDE2QNW37PU7TFESMMFTUTVBR7YRANCNFSM5QB6T3XQ> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[hnsywnwtsly]

This is the server response I get
{ "error": { "code": 400, "message": "Invalid JSON payload received. Unexpected token.\nContent-Type: multip\n^", "errors": [ { "message": "Invalid JSON payload received. Unexpected token.\nContent-Type: multip\n^", "domain": "global", "reason": "parseError" } ], "status": "INVALID_ARGUMENT" } }

And that's the code I use
` sMsg = "Content-Type: multipart/mixed; boundary=_NextPart_9E20D285C001973CCA9D" & vbCr & vbCr & _
"MIME-Version: 1.0" & vbCr & vbCr & _
"To: ******@gmail.com" & vbCr & vbCr & _
"From: ******@gmail.com" & vbCr & vbCr & _
"Subject: Email with attachment"

Dim Request As New WebRequest
Request.Resource = "users/{userId}/messages/send"
Request.AddUrlSegment "userId", "me"
Request.AddQuerystringParam "uploadType", "media"
Request.ContentType = "message/rfc822"
Request.ContentLength = Len(sMsg)
Request.body = sMsg
Request.Method = HttpPost

Request.SetHeader "Host", "www.googleapis.com"
Request.SetHeader "Authorization", "Bearer " & Auth.Token

Dim Response As WebResponse
Set Response = GmailClient.Execute(Request)

`

What's wrong with my code?

[shajvarg]

u have to change the baseurl of gmailclient to "https://www.googleapis.com/upload/gmail/v1/"
and add the scopes as mentioned before
shaju

[hnsywnwtsly]

It seems to be moving in the right direction.
Now the server response is "Recipient address required"
I do not understand why, I have the part of "To", I tried to enclose it in quotes, in a single quotation, nothing helps

[shajvarg]

Hi Richard,
Thanks for all the help provided till now.
I had a query. So the refresh tokens work for about 2 weeks after which re-authenication is asked.. Is there anyway to prevent that? I noticed that professional email clients only aske once for the authorization.

Thanks
Shaju

[zgrose]

Just as an aside, you'll want to be careful with refresh tokens in apps that aren't very secure (like VBA apps). Native apps can take advantage of OS-level encryption to keep the refresh tokens safe(r). The options for VBA to do the same are more limited.

[hnsywnwtsly]

Apparently there is no unequivocal answer.
look at this
https://stackoverflow.com/questions/8953983/do-google-refresh-tokens-expire

[shajvarg]

Just as an aside, you'll want to be careful with refresh tokens in apps that aren't very secure (like VBA apps). Native apps can take advantage of OS-level encryption to keep the refresh tokens safe(r). The options for VBA to do the same are more limited.

Agree, But we can easily encrypt it and store

[shajvarg]

Apparently there is no unequivocal answer. look at this https://stackoverflow.com/questions/8953983/do-google-refresh-tokens-expire

True.. It appears as long as the code is in testing phase, the refresh tokens last for 7 days only. If you submit for verification then it remains valid without expiring.. Issue is for vba code... how can we submit for verification.. it is not an app..

[hnsywnwtsly]

Why not? VBA is a desktop application, when you create certificates in the "application type" you have to select "desktop application".
I tried to submit for verification, I opened a private site in wix I did a proof of ownership of the site (in wix there is a built-in tool for this) but now I have another problem, I attach a picture

I think something needs to be changed in the code I found this line in the code
Private Const LOGIN_REDIRECT_URI As String = "urn:ietf:wg:oauth:2.0:oob"
but I do not know what to replace it with

[hnsywnwtsly]

The problem is solved, see here