Import data from google sheet using api key

emiagin
Copper Contributor
May 30, 2023
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?
- peiyezhu
  Bronze Contributor
  May 30, 2023
  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.
  - emiagin
    Copper Contributor
    Jun 01, 2023
    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...
emiagin
Copper Contributor
May 30, 2023
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

Forum Discussion

Import data from google sheet using api key

Resources