Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PHP for parsing the JSON output a published Google spreadsheet and displaying columns from each row.
<?php
// Parsing this spreadsheet: https://spreadsheets.google.com/pub?key=0Ah0xU81penP1dFNLWk5YMW41dkcwa1JNQXk3YUJoOXc&hl=en&output=html
$url = 'http://spreadsheets.google.com/feeds/list/0Ah0xU81penP1dFNLWk5YMW41dkcwa1JNQXk3YUJoOXc/od6/public/values?alt=json';
$file= file_get_contents($url);
$json = json_decode($file);
$rows = $json->{'feed'}->{'entry'};
foreach($rows as $row) {
echo '<p>';
$title = $row->{'gsx$title'}->{'$t'};
$author = $row->{'gsx$author'}->{'$t'};
$review = $row->{'gsx$review'}->{'$t'};
echo $title . ' by ' . $author . '<br>' . $review;
echo '</p>';
}
// See this here: http://imagine-it.org/google/spreadsheets/showspreadsheet.php
?>
@vikas5914

This comment has been minimized.

Copy link

@vikas5914 vikas5914 commented Apr 26, 2016

Thanks . .

@LucAwater

This comment has been minimized.

Copy link

@LucAwater LucAwater commented Dec 29, 2016

Awesome! How do I get data from the other sheets?

@LucAwater

This comment has been minimized.

Copy link

@LucAwater LucAwater commented Dec 29, 2016

Apparently, you can use 1, 2, 3, 4... However, for the first worksheet you would still need to use 'od6'

@danielkiz

This comment has been minimized.

Copy link

@danielkiz danielkiz commented Apr 22, 2017

So when i have it i can reload from google table something to json?

@shalinipulp

This comment has been minimized.

Copy link

@shalinipulp shalinipulp commented Sep 23, 2018

When i run this program it give me an error, can you please tell me any solution

Error are:

Notice: Trying to get property 'feed' of non-object in D:\xampp\htdocs\spreadsheet\test.php on line 5

Notice: Trying to get property 'entry' of non-object in D:\xampp\htdocs\spreadsheet\test.php on line 5

Warning: Invalid argument supplied for foreach() in D:\xampp\htdocs\spreadsheet\test.php on line 6

@Schokgolf

This comment has been minimized.

Copy link

@Schokgolf Schokgolf commented Apr 10, 2020

hey there,

just came across this feature i want to experiment with myself.
could you perhaps show how you would add a 4th entry, for example $genre = $row->{'gsx$genre'}->{'$t'};
and group the rows by genre?
(so they would all sit in the same div from the echo)

thank you!

@marcus-at-localhost

This comment has been minimized.

Copy link

@marcus-at-localhost marcus-at-localhost commented May 1, 2020

V3 of the API will go away later this year, V4 is much easier to use, but needs an API Key

<?php
$url = 'https://sheets.googleapis.com/v4/spreadsheets/{sheetId}/values/{sheetName}?key={API_KEY}';
$json = json_decode(file_get_contents($url));
$rows = $json->values;

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

https://gist.github.com/marcus-at-localhost/1886c1f35647ff682752c19880bd9dd1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment