Forum Discussion
emiagin
May 29, 2023Copper Contributor
Import data from google sheet using api key
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 th...
peiyezhu
May 29, 2023Bronze Contributor
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?
emiagin
May 30, 2023Copper Contributor
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 https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get try method window, but needs api key + oauth. I think i need to connect by google oauth in excel?
- peiyezhuMay 30, 2023Bronze Contributorhttps://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.- emiaginJun 01, 2023Copper ContributorI 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...- peiyezhuJun 01, 2023Bronze Contributorhttp://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.