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.
- Set up a Google Form, a spreadsheet will be created automatically or you can point to an existing spreadsheet. (Help)
Add entries and check if they appear in your spreadsheet
Acquire an API key to authorize the access to the API.
Share the spreadsheet and save the
spreadsheetIdand thesheetName
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
-
It's much easier to work with v4! JSON result of v3 was a mess. Migrate to v4 ↩
Top comments (0)