Import data from google sheet using api key

[emiagin]

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
    Source

In 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?<identifier>&API_KEY_EXCEL=<api-key>&continue=https://docs.google.com/spreadsheets/d/<sheet-id>/edit?API_KEY_EXCEL=<api-key>&followup=https://docs.google.com/spreadsheets/d/<sheet-id>/edit?API_KEY_EXCEL=<api-key>&ifkv=<ifkv>&ltmpl=sheets&osid=1&passive=1209600&service=wise&flowName=WebLiteSignIn&flowEntry=ServiceLogin&API_KEY_EXCEL=<api-key>'.
Details: https://accounts.google.com/v3/signin/identifier?<identifier>&API_KEY_EXCEL=<api-key>&continue=https://docs.google.com/spreadsheets/d/<sheet-id>/edit?API_KEY_EXCEL=<api-key>&followup=https://docs.google.com/spreadsheets/d/<sheet-id>/edit?API_KEY_EXCEL=<api-key&ifkv=<ifkv>&ltmpl=sheets&osid=1&passive=1209600&service=wise&flowName=WebLiteSignIn&flowEntry=ServiceLogin&API_KEY_EXCEL=<api-key>

How can i fix it?

[#2]

api key

What is OData and relative api?

Does the google sheet provide api to acheive data by formula?

Any official document available about this api?

[#3]

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

[#4]

@peiyezhu i tried to use this request using api key:

OData.Feed("https://sheets.googleapis.com/v4/spreadsheets/<sheet-id>?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 Method: spreadsheets.get  |  Google Sheets  |  Google for Developers try method window, but needs api key + oauth. I think i need to connect by google oauth in excel?

[#5]

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.

[#6]

I think i just can't get it with google sheets, it need using google oauth, it's hard for me :( i found this problem on stack overflow:
https://stackoverflow.com/questions/71818373/signing-in-to-google-sheets-from-excel-power-query
I need to somehow connecting by using oauth or it will be network forbidden...

[#7]

http://doc.yonyoucloud.com/doc/wiki/project/google-oauth-2/overview.html

https://developers.google.com/api-client-library/php/guide/aaa_oauth2_web


some sdk available

https://www.cnblogs.com/TRY0929/p/16051564.html

Try above .

https://stackoverflow.com/questions/71818373/signing-in-to-google-sheets-from-excel-power-query

I have opened this url.
but inside urls https://www.linkedin.com/pulse/accessing-api-oauth2-using-m-language-power-bi-diogenes-santos
and
https://developers.google.com/identity/protocols/oauth2

can not be visited.