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 : https://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

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.

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

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 : (https://developers.google.com/sheets/api/quickstart/php)

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