Request Google Sheets JSON API v4 with PHP

I like Google Forms, to create an easy interface to collect data in a spreadsheet (via Google Sheets) and work with that.

Unfortunately, to this day, there is no clear guideline (not to my knowledge) about how to use those services commercially and in compliance with the GDPR, without consulting a lawyer and exchange documents via fax.

But let's say you have figured it out¹, or you use this just for yourself to feed data in a spreadsheet and then display that data on your website.

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 v4².

TL;DR

Here is how:

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

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

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

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

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",

      "Spalte 1",

      "Spalte 2",

      "Spalte 3"

    ],

    [

      "01.05.2020 18:07:17",

      "A Lot",

      "Marc",

      "It's raining",

      "Spalte 2",

      "Spalte 1",

      "Spalte 3"

    ],

    [

      "01.05.2020 18:07:39",

      "Nothing",

      "Maria",

      "Still raining",

      "Spalte 2, Spalte 3",

      "",

      "Spalte 1"

    ]

  ]

}

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