Import data from google sheet using api key

Question

Hi! I tried to connected my google sheet and excel sheet. The problem is in permitions. My google sheet have not viewed by anyone and i should use some authentication. I choose api key and created this request:let
    Source = OData.Feed("link", null, [Implementation="2.0", ApiKeyName="API_KEY_EXCEL"])
in
    SourceIn auth window i entered api key. And i receive this error:DataFormat.Error: OData: The given URL neither points to an OData service or a feed: 'https://accounts.google.com/v3/signin/identifier?&lt;identifier&gt;&amp;API_KEY_EXCEL=&lt;api-key&gt;&amp;continue=https://docs.google.com/spreadsheets/d/&lt;sheet-id&gt;/edit?API_KEY_EXCEL=&lt;api-key&gt;&amp;followup=https://docs.google.com/spreadsheets/d/&lt;sheet-id&gt;/edit?API_KEY_EXCEL=&lt;api-key&gt;&amp;ifkv=&lt;ifkv&gt;&amp;ltmpl=sheets&amp;osid=1&amp;passive=1209600&amp;service=wise&amp;flowName=WebLiteSignIn&amp;flowEntry=ServiceLogin&amp;API_KEY_EXCEL=&lt;api-key&gt;'.
Details: https://accounts.google.com/v3/signin/identifier?&lt;identifier&gt;&amp;API_KEY_EXCEL=&lt;api-key&gt;&amp;continue=https://docs.google.com/spreadsheets/d/&lt;sheet-id&gt;/edit?API_KEY_EXCEL=&lt;api-key&gt;&amp;followup=https://docs.google.com/spreadsheets/d/&lt;sheet-id&gt;/edit?API_KEY_EXCEL=&lt;api-key&amp;ifkv=&lt;ifkv&gt;&amp;ltmpl=sheets&amp;osid=1&amp;passive=1209600&amp;service=wise&amp;flowName=WebLiteSignIn&amp;flowEntry=ServiceLogin&amp;API_KEY_EXCEL=&lt;api-key&gt;How can i fix it?

peiyezhu · Answer

http://doc.yonyoucloud.com/doc/wiki/project/google-oauth-2/overview.htmlhttps://developers.google.com/api-client-library/php/guide/aaa_oauth2_websome sdk availablehttps://www.cnblogs.com/TRY0929/p/16051564.htmlTry above .https://stackoverflow.com/questions/71818373/signing-in-to-google-sheets-from-excel-power-queryI have opened this url.but inside urls https://www.linkedin.com/pulse/accessing-api-oauth2-using-m-language-power-bi-diogenes-santosandhttps://developers.google.com/identity/protocols/oauth2can not be visited.

peiyezhu · Answer

api keyWhat is OData and relative api?Does the google sheet provide api to acheive data by formula?Any official document available about this api?

emiagin · Answer

I found this documentation about OData:https://learn.microsoft.com/en-us/powerquery-m/odata-feedLooks like i have wrong serviceUri link... I should use this one i think:https://developers.google.com/sheets/api/reference/rest?hl=ruI'll try this format, hope it'll work:/v4/spreadsheets/{spreadsheetId}/sheets/{sheetId}:copyTo

emiagin · Answer

peiyezhu&nbsp;i tried to use this request using api key:OData.Feed("https://sheets.googleapis.com/v4/spreadsheets/&lt;sheet-id&gt;?ranges='Common'!A1%3AC5", null, [Implementation="2.0", ApiKeyName="API_KEY_EXCEL"])But receive an error:Expression.Error: Access to the resource is forbidden.This request is works in&nbsp;Method: spreadsheets.get &nbsp;|&nbsp; Google Sheets &nbsp;|&nbsp; Google for Developers&nbsp;try method window, but needs api key + oauth. I think i need to connect by google oauth in excel?

peiyezhu · Answer

https://developers.google.com/sheets/api/reference/rest?hl=ru

I know oData is a restful api but I can not open url like above from google.com because of the network forbidden.

https://techcommunity.microsoft.com/t5/excel/post-api/td-p/3762681

Here is sample api service without key.

you can try it with webservice formular or oData to test.
http://e.anyoupin.cn/?s2=select%20%20*%20from%20Second_to_last_value_from_table_with_condition

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get
if possible,post the contents of above url,we can do research together.

Forum Discussion

Import data from google sheet using api key

Share

Resources