DEV Community 👩‍💻👨‍💻

Cover image for Request Google Sheets JSON API v4 with PHP
Marcus
Marcus

Posted on

Request Google Sheets JSON API v4 with PHP

Via the Google API, it's really easy to GET your spreadsheet as JSON and then work with it in whichever way you like.

Since the API v3 is going to go away in September 2020 it renders many tutorials useless, so here is how you do it with API v41.

  1. Set up a Google Form, a spreadsheet will be created automatically or you can point to an existing spreadsheet. (Help)

Forms to Sheet

  1. Add entries and check if they appear in your spreadsheet

  2. Acquire an API key to authorize the access to the API.

  3. Share the spreadsheet and save the spreadsheetId and the sheetName

Share Sheet to Public

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{sheetName}

For more in-depth information about how to query the spreadsheet, check out the docs.

defined('API_KEY','XXX');

$url = sprintf('https://sheets.googleapis.com/v4/spreadsheets/1TlhxvW4GxayktKdjoWKt620qTzysEquC4UPGmOlGxb0/values/Formularantworten%203?key=%s', API_KEY);
$json = json_decode(file_get_contents($url));
$rows = $json->values;

foreach($rows as $row) {
    var_dump($row);
}

Result

// https://sheets.googleapis.com/v4/spreadsheets/1TlhxvW4GxayktKdjoWKt620qTzysEquC4UPGmOlGxb0/values/Formularantworten%201?key=xxxx

{
  "range": "'Formularantworten 1'!A1:M104",
  "majorDimension": "ROWS",
  "values": [
    [
      "Zeitstempel",
      "What's up",
      "Short Text",
      "Long Text",
      "Matrix [Row 1]",
      "Matrix [Row 2]",
      "Matrix [Row 3]"
    ],
    [
      "01.05.2020 18:06:54",
      "Nothing",
      "Diana",
      "My Answer",
      "Col 1",
      "Col 2",
      "Col 3"
    ],
    [
      "01.05.2020 18:07:17",
      "A Lot",
      "Marc",
      "It's raining",
      "Col 2",
      "Col 1",
      "Col 3"
    ],
    [
      "01.05.2020 18:07:39",
      "Nothing",
      "Maria",
      "Still raining",
      "Col 2, Col 3",
      "",
      "Col 1"
    ]
  ]
}

For more complex tasks, you might want to check out google's api wrapper libraries.

This appeared first on my blog: Request Google Sheets JSON API v4 with PHP


  1. It's much easier to work with v4! JSON result of v3 was a mess. Migrate to v4 

Top comments (0)

Build Anything...


Use any Linode offering to create something for the DEV x Linode Hackathon 2022. A variety of prizes are up for grabs, inculding $1,000 USD. 👀

Join the Hackathon <-