[Request Google Sheets JSON API v4 with PHP] #json #google-sheets

_sheets.php

<?php

// Migrate to v4: https://developers.google.com/sheets/api/guides/migration#retrieve_row_data
// Form: https://forms.gle/P1hyew16yUSx7GgH8
// Table: https://docs.google.com/spreadsheets/d/1TlhxvW4GxayktKdjoWKt620qTzysEquC4UPGmOlGxb0/edit?usp=sharing
// Get API_KEY: https://developers.google.com/sheets/api/guides/authorizing#APIKey

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

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

Request Google Sheets JSON API v4 with PHP.md

Request Google Sheets JSON API v4 with PHP #json #googlesheets

<?php

// Migrate to v4: https://developers.google.com/sheets/api/guides/migration#retrieve_row_data
// Form: https://forms.gle/P1hyew16yUSx7GgH8
// Table: https://docs.google.com/spreadsheets/d/1TlhxvW4GxayktKdjoWKt620qTzysEquC4UPGmOlGxb0/edit?usp=sharing
// Get API_KEY: https://developers.google.com/sheets/api/guides/authorizing#APIKey

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

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

// 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"
    ]
  ]
}

result.json

// 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"
    ]
  ]
}

<?php
define("API_KEY", "XXXXXXXXXXXX");

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

   <div class="container mt-4">
        <div class="row">  
            <div class="col-12">             
                <table class="table table-bordered table-hover">
                    <thead>
                        <tr>                            
                            <th class="text-center">Title</th>
                            <th class="text-center">Author</th>
                            <th class="text-center">Status</th>
                        </tr>
                    </thead>
                    <tbody>                    
                        <?php foreach($rows as $row){
                          echo '<tr>';                                
                                  $title = $row[0];
                                  $author = $row[1];                                
                                  $status = $row[2];                            
                              echo "<td class='text-center'>" . $title . '</td>';
                              echo "<td class='text-center'>" . $author . '</td>';
                              echo "<td class='text-center'>" . $status . '</td>';                                                       
                          echo '</tr>';
                        }?>                    
                    </tbody>
                </table>  
            </div> 
        </div>
    </div>