Written by Sean Behan on Thu Oct 11th 2018

This post assumes you've created a project with Google's web console and downloaded a client_secret.json file from the credentials page. If not, more info is here..

https://www.twilio.com/blog/2017/03/google-spreadsheets-and-php.html

Note: The tutorial linked to above is great and takes you 90% of the way there. However, they don't cover writing and reading to a single cell. The problem is that if a cell is empty, the "update" call used in their tutorial will fail. It's kind of unfortunate that the client library doesn't consider this and populate when the object is null.

Anyway, after you're setup to authenticate with Google, you'll need to install the client libraries with Composer from the command line

composer require google/apiclient:"^2.0"
composer require asimlqt/php-google-spreadsheet-client:"3.0.*"

And here is the code.

    <?php

    require __DIR__ . '/vendor/autoload.php';
    use Google\Spreadsheet\DefaultServiceRequest;
    use Google\Spreadsheet\ServiceRequestFactory;

    putenv('GOOGLE_APPLICATION_CREDENTIALS=' . __DIR__ . '/client_secret.json');

    $client = new Google_Client;
    $client->useApplicationDefaultCredentials();
    $client->setApplicationName("My App");
    $client->setScopes(['https://www.googleapis.com/auth/drive','https://spreadsheets.google.com/feeds']);
    if ($client->isAccessTokenExpired()) {
        $client->refreshTokenWithAssertion();
    }
    $accessToken = $client->fetchAccessTokenWithAssertion()["access_token"];
    ServiceRequestFactory::setInstance(
        new DefaultServiceRequest($accessToken)
    );
    $service = new Google_Service_Sheets($client);

    $sheet_id = "[The ID (in the URL) of the spreadsheet in question]";
    $range = "Sheet1!A1"; /// the first cell on the first sheet.. (it's really titleOfSheet! but it's called Sheet1 by default)
    $myValue = "Hello World";

    $updateBody = new Google_Service_Sheets_ValueRange([
        'range' => $range,
        'majorDimension' => 'ROWS',
        'values' => [
          [$myValue]
        ]
    ]);
    $service->spreadsheets_values->update(
        $sheet_id,
        $range,
        $updateBody,
        ['valueInputOption' => "RAW"]
    );
    $results = $service->spreadsheets_values->get(
      $sheet_id, $range
    );

    var_dump($results->getValues());

Using the code above you can obviously write a range of values, but that use case is covered in more depth elsewhere.


Tagged with..
#php #google #api #sheets #cells #spreadsheets

Just finishing up brewing up some fresh ground comments...