sheetsAPI don't work

154 views
Skip to first unread message

blonde...@gmail.com

unread,
Jun 13, 2017, 4:18:38 AM6/13/17
to Excel Liberation
Hello,
I follow all steps of your tutorial "Using the Google Sheets V4 API from VBA to integrate Sheets and Excel" and i have an error when i try to play the one off function.
After click on allow the function crash with this run-time error '-2147024809(80070057)'. And when i try to click on push button i have this error : 

Failed to authorize to google for scopes sheets:denied code need to supply new credentials - nothing in registery

And when i click on ok, a  new window appear with this message : 

failed getting sheets meta data {
  "error": {
  "code": 403,
  "message": "This request is missing a valid API key.",
  "status": "PERMISSION_DENIED"
  }
}

I don't understand because i followed all steps in your tutorial and of course i created a new OAuth credentials and enabled the Google Sheets API.
I have no idea why it's not work. Can you help me please ?

Many thanks in advance for your help.

(I'm french, so sorry for my bad english)

Bruce Mcpherson

unread,
Jun 13, 2017, 4:52:39 AM6/13/17
to excel-r...@googlegroups.com
Actually this forum has been moved to G+ a long time ago. You can subscribe here.

For your problem - 
Did you run the once-off function to write your credentials to the registry ? 

Take a look in the registry and see if there are any keys that have been written under xliberation. This is where your Google credentials are securely stored.


Bruce






--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblings+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

blonde...@gmail.com

unread,
Jun 13, 2017, 5:52:13 AM6/13/17
to Excel Liberation
Thanks for your answer. What do you mean when you say "the registery" ?
Is it this page where there is the credentials ? Like this :
 
 

Bruce Mcpherson

unread,
Jun 13, 2017, 6:47:06 AM6/13/17
to excel-r...@googlegroups.com
I mean the windows registry... you can use regedit to see what's in there

The function (I think its called onceoff) takes the credentials you've created in the Google console above, and writes them to the windows registry. You only ever need do this once.

getGoogled then reads these credentials from the registry when it needs to connect. This is how your credentials are kept safe, yet accessible from windows.

It sounds like either you didnt run the once off function, or something went wrong.

bruce



--

blonde...@gmail.com

unread,
Jun 13, 2017, 8:09:31 AM6/13/17
to Excel Liberation
Ok, what is xliberation ? Is it a folder ?
I looked in the tree structure but i don't found it. I have five main folders :

Bruce Mcpherson

unread,
Jun 13, 2017, 8:17:45 AM6/13/17
to excel-r...@googlegroups.com
You can use find to search for the key "xliberation" in the trees.

blonde...@gmail.com

unread,
Jun 13, 2017, 8:28:08 AM6/13/17
to Excel Liberation
Yes that is what i did (right-click on each folders and click "find"), but i had no result for each folders... Is it normal ?

Bruce Mcpherson

unread,
Jun 13, 2017, 8:32:23 AM6/13/17
to excel-r...@googlegroups.com
No .. it sounds like you haven't actually run the once off function.

.. this

Private Function sheetsOnceOff()
    
    getGoogled "sheets", , _
    "CVgxxxxxxxxxePfe"
    
End Function

On 13 June 2017 at 13:28, <blonde...@gmail.com> wrote:
Yes that is what i did (right-click on each folders and click "find"), but i had no result for each folders... Is it normal ?

--

blonde...@gmail.com

unread,
Jun 13, 2017, 8:41:15 AM6/13/17
to Excel Liberation
No i have already run this function, I replaced the two last lines with my credential :

 Private Function sheetsOnceOff()
    
    getGoogled "sheets", , _
    "qt7zgt9A6kE9y4oAoAhaGari"
    



End Function

And when i run the function i have this error : 

Bruce Mcpherson

unread,
Jun 13, 2017, 8:44:26 AM6/13/17
to excel-r...@googlegroups.com
Ok.. so it hasn't written your credentials.. have to figure out why. Maybe some missing windows libraries or something. Which iE and windows version? 

I'll look at what might cause that later today.
Bruce

Sent from my iPad
--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.

blonde...@gmail.com

unread,
Jun 13, 2017, 8:53:37 AM6/13/17
to Excel Liberation
I work with windows 7, excel 2010 and IE 11 in a virtual machine (virtualbox).

Bruce Mcpherson

unread,
Jun 13, 2017, 8:57:32 AM6/13/17
to excel-r...@googlegroups.com
That's a lot of thing that could go wrong. I don't think that such old versions will work. I can't test it myself as I don't have any of those things.

Sent from my iPad

On Jun 13, 2017, at 1:53 PM, blonde...@gmail.com wrote:

I work with windows 7, excel 2010 and IE 11 in a virtual machine (virtualbox).

--

blonde...@gmail.com

unread,
Jun 13, 2017, 11:24:22 AM6/13/17
to Excel Liberation
Oh it's bad for me, I'm in intership in a company where all computers running windows 7 and office 2007.
Do you have an idea to get around this problem ? Or do you know another way to write in Google Sheets with VBA without using OAuth ?

Bruce Mcpherson

unread,
Jun 13, 2017, 11:32:05 AM6/13/17
to excel-r...@googlegroups.com
No, the only way to write is via Oauth (security). désolé mais .. c'est dommage

On 13 June 2017 at 16:24, <blonde...@gmail.com> wrote:
Oh it's bad for me, I'm in intership in a company where all computers running windows 7 and office 2007.
Do you have an idea to get around this problem ? Or do you know another way to write in Google Sheets with VBA without using OAuth ?

--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblings+unsubscribe@googlegroups.com.

blonde...@gmail.com

unread,
Jun 13, 2017, 12:07:12 PM6/13/17
to Excel Liberation
Too bad...
Oh you speak french ! :)
Thanks you very much for your help and the time you dedicated for me. Keep up your great work, your website is a wealth of information !
I wish you well and thanks ever so much again !
 

Rich

unread,
Jul 4, 2019, 6:30:38 AM7/4/19
to Excel Liberation
Hi,
I've just stumbled across this thread over 2 years later as I've just faced the same problem as we still have some Windows 7 PC's
I found that it was this line of code in the httpPost function that was causing the issue:

 ohttp.send data
by changing the datatype for data from string to variant solved it for me.

Once I had credentials saved in the registry and I ran into a new problem that was solved by changing the version of Msxml2 from 6 to 3 in the 
getHttpObject function

Private Function getHttpObject(Optional timeout As Long = 0) As Object
   
'// some installation dont have server object installed, so fall back
    Dim ob As Object
    On Error GoTo missing


   
    Set ob = CreateObject("Msxml2.ServerXMLHTTP.3.0")

Thanks Bruce for an excellent sample project, 

Cheers

Rich

Bruce Mcpherson

unread,
Jul 4, 2019, 6:13:02 PM7/4/19
to excel-r...@googlegroups.com
Thanks.im sure this will be useful for others
Bruce.

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
 
If you have received this email in error please notify the originator of the message. This footer also confirms that this email message has been scanned for the presence of computer viruses.
 
Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Aggregate Industries.
 
Aggregate Industries UK Limited,  Registered in England and Wales Company Number 00245717. Registered Office: Bardon Hall, Copt Oak Road, Markfield, Leicestershire, LE67 9PJ.

--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/excel-ramblings/9b1e2779-731a-4df1-b05d-bbdf0e3db70f%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages