Skip to content

Instantly share code, notes, and snippets.

@jarodthornton

jarodthornton/gsheets-json.php

Last active April 16, 2021 13:51
Show Gist options
  • Save jarodthornton/74a883ed470db9b9d56b2f1890a3a889 to your computer and use it in GitHub Desktop.
Save jarodthornton/74a883ed470db9b9d56b2f1890a3a889 to your computer and use it in GitHub Desktop.
Download ZIP
Google Spreadsheets to HTML via JSON
Raw
gsheets-json.php
<html>
<head>
<title>GameWeek Winners</title>
<style>
</style>
</head>
<body>
<div class="header">
<h1>GameWeek Winners</h1>
</div>
<div class="conatiner">
<?php
// Create a spreadsheet in Google Drive.
// Go to File > Publish to Web THEN Share > Anyone with the Link
// Set the GSID and WID
// GSID is the Google Sheets ID - it's the part of the URL like this > https://docs.google.com/spreadsheets/d/THSIS_PART_IS_THE_ID/edit#gid=0.
$gsid = '1nXuNav34vwK2lmYalL1paYMQkW0bMljeseLynCf3aTY';
// WID is the Worksheet ID - by default the first sheet is "1"
$wid = '1';
// Don't edit these parts
$url = "https://spreadsheets.google.com/feeds/list/$gsid/$wid/public/values?alt=json";
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, $url );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true );
$response = curl_exec( $ch );
if ( $response === false) {
$curl_result = curl_error( $ch );
return( $curl_result );
}
curl_close($ch);
$json = json_decode($response);
if(!$json){
return "GID is invalid.";
}
$rows = $json->{'feed'}->{'entry'};
foreach($rows as $row) {
// These are the variables pulled from the sheets column header.
// Make these whatever, just be sure they match.
$gw = $row->{'gsx$gw'}->{'$t'};
$A = $row->{'gsx$a'}->{'$t'};
$ScoreA = $row->{'gsx$scorea'}->{'$t'};
$B = $row->{'gsx$b'}->{'$t'};
$ScoreB = $row->{'gsx$scoreb'}->{'$t'};
$C = $row->{'gsx$c'}->{'$t'};
$ScoreC = $row->{'gsx$scorec'}->{'$t'};
$D = $row->{'gsx$d'}->{'$t'};
$ScoreD = $row->{'gsx$scored'}->{'$t'};
$E = $row->{'gsx$e'}->{'$t'};
$ScoreE = $row->{'gsx$scoree'}->{'$t'};
$F = $row->{'gsx$f'}->{'$t'};
$ScoreF = $row->{'gsx$scoref'}->{'$t'};
$G = $row->{'gsx$g'}->{'$t'};
$ScoreG = $row->{'gsx$scoreg'}->{'$t'};
$H = $row->{'gsx$h'}->{'$t'};
$ScoreH = $row->{'gsx$scoreh'}->{'$t'};
$I = $row->{'gsx$i'}->{'$t'};
$ScoreI = $row->{'gsx$scorei'}->{'$t'};
$J = $row->{'gsx$j'}->{'$t'};
$ScoreJ = $row->{'gsx$scorej'}->{'$t'};
$K = $row->{'gsx$k'}->{'$t'};
$ScoreK = $row->{'gsx$scorek'}->{'$t'};
$L = $row->{'gsx$l'}->{'$t'};
$ScoreL = $row->{'gsx$scorel'}->{'$t'};
// The next part also depends on the above variables. Make sure they match.
?>
<div class="contentBlock">
<strong>GameWeek</strong> <?=$gw?>
<strong>A</strong> <?=$A?> - <?=$ScoreA?> |
<strong>B</strong> <?=$B?> - <?=$ScoreB?> |
<strong>B</strong> <?=$C?> - <?=$ScoreC?> |
<strong>B</strong> <?=$D?> - <?=$ScoreD?> |
<strong>B</strong> <?=$E?> - <?=$ScoreE?> |
<strong>B</strong> <?=$F?> - <?=$ScoreF?> |
<strong>B</strong> <?=$G?> - <?=$ScoreG?> |
<strong>B</strong> <?=$H?> - <?=$ScoreH?> |
<strong>B</strong> <?=$I?> - <?=$ScoreI?> |
<strong>B</strong> <?=$J?> - <?=$ScoreJ?> |
<strong>B</strong> <?=$K?> - <?=$ScoreK?> |
<strong>B</strong> <?=$L?> - <?=$ScoreL?> |
</div>
<?php } ?>
</div>
<div class="footer">
<h5>Footer of page</h5>
</div>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment