If you are using Google Oauth2 VBA authentication, you sometimes hit problems caused by various lockdown and firewall policies in corporate environments that prevent some of the conversations required for VBA to integrate with Google’s Oauth2 infrastructure.
Rakesh Sarma created this this post with some workarounds that could help.
I posted a question on ‘desktop liberation’ Google+ account. The problem was that the developers were not able to go through the authentication process. There were two kinds of errors that popped up. The first one looks like this.
This page can’t be displayed. Turn on TLS 1.0, TLS 1.1, and TLS 1.2 in the Advanced settings and try connecting to https://accounts.google.com again. If this error persists, it is possible that this uses an unsupported protocol or cipher suite such as RC4, which is not considered secure. Please contact your site administrator.
Meddling with the settings did not help and the link for the details was also of not much help either. If your issue gets fixed by changing the settings then it is fine or else follow these steps.
I have tested the code and it works perfect. All you need to plug-in are the following three things
- Google sheet id in getMySheetID variable in getMySheetID() function
- Client ID in place of 109xxxxxxxxxxieuu2q3.apps.googleusercontent.com in sheetsOnceOff() function
- Secret ID in place of CVgxxxxxxxxxePfe in sheetsOnceOff() function
Run the sheetsOnceOff() after doing above steps. If you get any errors while logging in, then try logging into accounts.google.com from your Internet Explorer browser. You should see a page similar to this in your Internet Explorer browser. I suspect that there is a firewall or antivirus that is blocking this HTTP request.
To confirm that, try connecting the system to another network such as your personal hotspot from your phone. Run the sheetsOnceOff() function again. If you’ve configured correctly then you should be able to login into the Google account. If you want, you can connect back to your company internet and proceed with the next steps. It will work perfectly.
Now go to your IT team and ask them to remove the policy concerning Windows updates firewall. Now, try logging into the google account from a different id. This id should be different from the ID that was used earlier for successful login. Please note that the sheet should be shared with the other person, preferably with edit access, before trying to login.
For this error, lower the security level in Internet Explorer to “Medium” in Internet,”Low” in Local Intranet, “Low” in Trusted Sites.
Uncheck “Enable protected mode” in all four tabs (Internet, Local Intranet, Trusted Sites, and Restricted Sites).
Now run sheetsOnceOff().
You should be able to login and run the code.
About the Author:
Rakesh is an Analyst at a startup research/VC firm called Tracxn. He tracks latest startups and companies in Enterprise Infrastructure sectors such as Cloud Computing, Data Center Infrastructure, Virtualization, and Enterprise Storage.
He is also a data junkie and does data science during weekends. He builds machine learning models in R to uncover insights from data. He can be reached at email@example.com