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

Using Google Cloud's Service Account via PHP

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

create-credentials.webp

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.

service-account-details.webp

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 :

email.webp

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 :

  1. Select a project.

  2. Click the email address of the service account that you want to create a key for.

  3. Click the Keys tab.

  4. Click the Add key drop-down menu, then select Create new key.

  5. 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"
}

shets-keys.webp

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.webp

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

Array
(
    [0] => Array
        (
            [0] => Title
            [1] => Description
        )

    [1] => Array
        (
            [0] => What's the difference between UI Kits and libraries
            [1] => UI Kits are copy-and-pastable components which can be accessed through our website. Purchasing a UI Kit will not grant access to it's counter library like Blade UI. Libraries are ready to use components for frameworks, these can be locally downloaded through a package manager like Composer. Purchasing a library will not grant access to it's counter UI Kit and libraries for other frameworks.
        )

    [2] => Array
        (
            [0] => Can I use this with my team?
            [1] => Yes! you can use this package with your team as long as you purchase it specifically for the team. We recommend using a team email address.
        )

    [3] => Array
        (
            [0] => Can I redistribute the components?
            [1] => No, you can't redistribute our component in any shape or form. It's also not allowed to build other UI kits based on Razor UI.
        )

    [4] => Array
        (
            [0] => Can I use the components for open-source projects?
            [1] => Yes, as long as the main purpose of the project isn't something like a page builder or an admin panel package. It's completely okay to use our components as a part of a bigger and unrelated open-source project.
        )

    [5] => Array
        (
            [0] => Can I make a (paid) tool with the components?
            [1] => Same as the open-source projects. You can use Razor UI to build a paid tool as long as the tool isn't a page builder or an admin panel builder.
        )

    [6] => Array
        (
            [0] => Can I use this for client work?
            [1] => Same as the open-source projects. You can use Razor UI for client work, as long as the client project isn't a UI kit or a tool for building a page builder or an admin panel builder.
        )

    [7] => Array
        (
            [0] => Is there a project limit?
            [1] => No. You can use Razor UI for as many projects as you like.
        )

    [8] => Array
        (
            [0] => Will I get upcoming UI Kits and libraries for free?
            [1] => No, they're considered different products. You'll need to purchase a new license. However, customers get an exclusive discount on new releases.
        )

    [9] => Array
        (
            [0] => How much do updates cost?
            [1] => Updates for each package are forever free, at no additional cost.
        )

    [10] => Array
        (
            [0] => Do I get discounts on new packages?
            [1] => Yes. We appreciate our customers, so we reward them with discounts on any Razor UI packages we release in the future.
        )

    [11] => Array
        (
            [0] => Are there Vue.js and React.js examples?
            [1] => No. The templates use Alpine.js because it's part of the template, and easy to port to other frameworks. You'll find the code intuitive regardless of your coding background.
        )

    [12] => Array
        (
            [0] => Can I provide suggestions?
            [1] => We appreciate them! As part of your purchase, you'll get access to our Discord server where you can provide suggestions and we'll gladly discuss them.
        )

    [13] => Array
        (
            [0] => Is there support?
            [1] => We only offer support for payment-related issues. However, in the Discord community - that's part of your purchase - you can talk to us and other customers. The odds are likely someone will be able to help you out.
        )

    [14] => Array
        (
            [0] => What are libraries?
            [1] => Libraries are implementations with a complete API for a specific framework. Instead of HTML, you get ready to use building blocks. You can see their API before you purchase them.
        )

)

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.

Did you find this article valuable?

Support Anjanesh Lekshminarayanan by becoming a sponsor. Any amount is appreciated!