Retrieving data from a privately owned Google Spreadsheet via API without OAuth

This is a sequel to my previous blog post on Retrieving data from a public Google Spreadsheet and display it on a webpage. Only, this time, we're using a server-side code (PHP) to fetch the data from the Google Spreadsheet since : not only do we not want the end-user to 'know' the Spreadsheet URL, we can't expose the Google Cloud API details in the browser to fetch.

Goto : console.cloud.google.com/apis

Create a project or select an existing project > APIs & Services > Enable APIs and Services > Google Sheets > Enable

Create Credentials > Service Account

Now, goto Keys and create a new key.

Goto https://console.cloud.google.com/iam-admin/serviceaccounts?walkthrough_id=iam--create-service-account-keys&start_index=1#step_index=1 as mentioned here :

Select a project.
Click the email address of the service account that you want to create a key for.
Click the Keys tab.
Click the Add key drop-down menu, then select Create new key.
Select JSON as the Key type and click Create.

Clicking Create downloads a service account key file. After you download the key file, you cannot download it again.

The downloaded key has the following format, where PRIVATE_KEY is the private portion of the public/private key pair:

{
  "type": "service_account",
  "project_id": "PROJECT_ID",
  "private_key_id": "KEY_ID",
  "private_key": "-----BEGIN PRIVATE KEY-----\nPRIVATE_KEY\n-----END PRIVATE KEY-----\n",
  "client_email": "SERVICE_ACCOUNT_EMAIL",
  "client_id": "CLIENT_ID",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/SERVICE_ACCOUNT_EMAIL"
}

Create Private Key and download the JSON file - save it as `credentials.json` - This JSON file cannot be recovered later on at any point of time, if lost. So, store this carefully.

php composer.php require google/apiclient

touch google-api-sheets.php

Edit google-api-sheets.php : (developers.google.com/sheets/api/quickstart..)

<?php
require __DIR__ . '/vendor/autoload.php';
use Google\Client;

$client = new Google\Client();
$client->setApplicationName('Google Sheets API Service Account');
$client->setScopes('https://www.googleapis.com/auth/spreadsheets');
$client->setAuthConfig('credentials.json'); // This the location of your JSON file downloaded from Google Cloud's Service Account Credentials

$service = new Google\Service\Sheets($client);

// https://docs.google.com/spreadsheets/d/1zwblTR5DWzgOiVxsDYJJkP5Gcmi4OGiCtldI0nyxZGo/edit
try {
    $spreadsheetId = '1zwblTR5DWzgOiVxsDYJJkP5Gcmi4OGiCtldI0nyxZGo';

    //  This is the sheet name with cell-values range for example $range = 'content-pages!A1:B15';
    $range = 'Sheet1';

    $response = $service->spreadsheets_values->get($spreadsheetId, $range);
    $values = $response->getValues();

    if (empty($values)) {
        print "No data found.\n";
    } else {
        print_r($values);
    }
}
catch(Exception $e) {
    // TODO(developer) - handle error appropriately
    echo 'Message: ' .$e->getMessage();
}
?>

php google-api-sheets.php

PS: The Google Spreadsheet mentioned here is a publicly shared one and not a private one because I used this same spreadsheetId is the previous blog post. You can update the $spreadsheetId with your private Google Spreadsheet ID in the code above. It should still work.

Anjanesh

Anjanesh

Retrieving data from a privately owned Google Spreadsheet via API without OAuth

Using Google Cloud's Service Account via PHP

Create Credentials > Service Account

Now, goto Keys and create a new key.

Create Private Key and download the JSON file - save it as `credentials.json` - This JSON file cannot be recovered later on at any point of time, if lost. So, store this carefully.

Retrieving data from a privately owned Google Spreadsheet via API without OAuth

Using Google Cloud's Service Account via PHP

Create Credentials > Service Account

Copy the service account email and share the privately owned Google Spreadsheet with this email address - this is the most important point in this article.

Again, its the newly created Service Account's Email Address that needs to be copied and share this Email Address (circled in red) with the your private Google Sheet document :

Now, goto Keys and create a new key.

Create Private Key and download the JSON file - save it as credentials.json - This JSON file cannot be recovered later on at any point of time, if lost. So, store this carefully.

Create Private Key and download the JSON file - save it as `credentials.json` - This JSON file cannot be recovered later on at any point of time, if lost. So, store this carefully.