🔧 Get json from a spreadsheet using GoogleSheetsAPI v4.

Google Console

Access to Google API Console

Search "spreadsheet"

Click to enable this API

Transition to the API details page.

Click "Credentials"

Application restrictions: HTTP referrers (web sites)

Website Restrictions: Add your domain

API restrictions

Select Google Sheets API



"SHOW KEY" and copy.

Spreadsheet

Make Data & Change viewer auth "Anyone with the link"

check id
https://docs.google.com/spreadsheets/d/{id}/edit#gid=0

API Endpoint

https://sheets.googleapis.com/v4/spreadsheets/{id}/values/{sheet name}?key={API Key}
If you want to try it directly from your browser, delete "Website Restrictions" domains.
And enter the API endpoint directly in the browser and try to access.
You can get data

{
  "range": "data!A1:Z1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "id",
      "name"
    ],
    [
      "1",
      "white"
    ],
    [
      "2",
      "black"
    ],
    [
      "3",
      "blue"
    ],
    [
      "4",
      "pink"
    ]
  ]
}

Query Params

 "majorDimension": "COLUMNS",

{
  "range": "data!A1:Z1000",
  "majorDimension": "COLUMNS",
  "values": [
    [
      "id",
      "1",
      "2",
      "3",
      "4"
    ],
    [
      "name",
      "white",
      "black",
      "blue",
      "pink"
    ]
  ]
}

Error

If you don't specify a API key. API key not valid. Please pass a valid API key.

{
  "error": {
    "code": 400,
    "message": "API key not valid. Please pass a valid API key.",
    "status": "INVALID_ARGUMENT",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.ErrorInfo",
        "reason": "API_KEY_INVALID",
        "domain": "googleapis.com",
        "metadata": {
          "service": "sheets.googleapis.com"
        }
      }
    ]
  }
}

Ref.

query params