Search code examples
google-apihttprequestgoogle-sheets-api

How to use google spreadsheet api?

So I would like a link that gives me data without having to login similar to this it is a tutorial for the v3 API.

On the docs it says what I want is a http request which is https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range} but it gives me this error

{ "error": { "code": 403, "message": "The request is missing a valid API key.", "status": "PERMISSION_DENIED" } }

Solution

  • In the case of Sheets API, at least, an API key is required to be used even when the Spreadsheet is publicly shared. This is the current specification. So when you can use the API key and the Spreadsheet is publicly shared, you can retrieve the values from Spreadsheet using Sheets API.

    But, if you cannot use the API key, how about the following endpoint?

    Endpoint 1:

    https://docs.google.com/spreadsheets/d/{spreadsheetId}/pub?gid={sheetId}&output=csv&range={range}

    Endpoint 2:

    https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq?gid={sheetId}&tqx=out:csv&range={range}
    • At the above endpoints, the API key is not required to be used.
    • In this case, please encode the value of range with the URL encode.

    Added:

    About your following situation.

    Endpoint 1 gives me an error and endpoint 2 works but it downloads a .txt for me which I have no idea how to use.

    In above endpoints, the sheet of the Google Spreadsheet is retrieved as the CSV data.

    About your following new question

    I would use an API key but I don't know how like I would guess I should put it in the link somewhere but I'm not sure how.

    When you can use the API key,

    https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}?key={yourAPIkey}