How to use Google Sheets as a Database for your PHP App

Time to read: 5 minutes

January 16, 2019

Written by

Contributor

How to use Google Sheets as a Database for your PHP App

Introduction

Twelve years ago, Google had, in my opinion, the ambitious idea of releasing a product that would be in direct competition with Excel. Google Sheets has since gained popularity over the years and become much more than just a spreadsheet. Being an online application, it’s a lot easier to integrate it with different online tools that you use. In this tutorial, we are going to test the flexibility of Sheets by fetching data from Order Desk’s API, updating a Google Sheet and sending a Twilio confirmation via SMS. We will use Order Desk to simulate an online store fulfillment system that backs up it's data into a Google Sheet.

Tutorial Requirements

For this tutorial, you will need:

A PHP development environment
A global installation of Composer
A Twilio Account
A Google Account
An Order Desk Account

Setup our Development Environment

To start off, let’s create a new project directory and create a .env file in it.

Installing Dependencies

For this project, we need three dependencies:

PHP Dotenv, which makes it possible for us to load environment variables from the .env file.
Google API Client Library for PHP, which enables us to work with Google APIs. For this tutorial we will use the Sheets API.
Twilio SDK for PHP, which makes it possible to interact with the Twilio API.

From the terminal, inside the project directory, run the following command:

$ composer require vlucas/phpdotenv google/apiclient twilio/sdk

The next step is to create a .env file and copy these variables into it:

ORDER_DESK_API_KEY=
ORDER_DESK_STORE_ID=

TWILIO_TOKEN=
TWILIO_SID=
TWILIO_NUMBER=

GOOGLE_APPLICATION_CREDENTIALS=./credentials.json
GOOGLE_API_KEY=

GOOGLE_SERVICE_ACCOUNT_NAME=
GOOGLE_SERVICE_KEY=
SPREADSHEET_ID=

We are going to work step by step to get the values for the above variables.

Order Desk Credentials

If you don’t have an order desk account, go ahead and create one. From the dashboard menu, under Settings, click on Store Settings. On the far right of the store settings menu is the API tab. Click on that. Then create your API key. Let’s go ahead and copy the store ID and API key in our .env file.

Twilio Credentials

In your Twilio account, create a project under "Programmable SMS". When you project is setup, from the right hand side, copy your Account SID and Auth Token. To get your phone number, navigate to Phone Numbers. Copy and paste it to the .env.

Google Credentials

Next we need to create a Google Project and enable the Google Sheets API. For our project, we have server-to-server interactions, therefore we need a service account in order to use OAuth 2.0. For more information on this, please read the documentation.

From your project dashboard, click on the hamburger menu on the left. Navigate to IAM & admin -> Service Accounts.

Then click "Create Service Account". On the first step, you need to enter your Service Account Name. I named mine twilio-sheets. Enter the required details and feel free to skip the optional prompts. On the third step however, make sure to click "Create Key". By default, the key will be created as a JSON file and downloaded to your computer. Copy that file to the root directory of your project and rename it to credentials.json.

Note: This is a sensitive file and it should not be pushed to a public repository.

After clicking Done, copy the Key ID to the GOOGLE_SERVICE_KEY variable in the .env file. Similarly, copy the email and save it as the value for the GOOGLE_SERVICE_ACCOUNT_NAME variable. Next, we need to navigate to Credentials. Click "Create Credentials" and create an API key. Once that’s done, copy and paste it as the value of the GOOGLE_API_KEY variable.

Lastly, from Google Drive, create a new Google Sheet and give it any name. I named mine "Order Desk Data". On the top right of your sheet, click Share and give it the permission of “Anyone with link can edit”. The URL should look like this:

https://docs.google.com/spreadsheets/d/1Io5ko9zaOm7nnEKVpz84NLdFdrtdVnr9TQFRrGVEuvM/edit#gid=0

The section 1Io5ko9zaOm7nnEKVpz84NLdFdrtdVnr9TQFRrGVEuvM is our sheet ID. Copy your ID and save it in the .env file. At this point, all of your environment variables should be populated.

On the first row add the following headers in the first six columns:

First Name
Last Name
Email
Shipping Method
Payment Type
Order Total

These are the columns we will be updating using our code.

The Code

Order Desk API Client

Order Desk provides a helper client for PHP development available on Github. Let’s create a file called OrderDeskClient.php and copy the following code into it:

<?php

class OrderDeskApiClient
{
    private $store_id;
    private $api_key;
    private $base_url = "https://app.orderdesk.me/api/v2";
    public $last_status_code = "";

    public function __construct( $store_id, $api_key ) {
        $this->store_id = $store_id;
        $this->api_key = $api_key;
    }

    public function get( $url = "", $post = null ) {
        return $this->go( "GET", $url, $post );
    }

    public function post( $url, $post = null ) {
        return $this->go( "POST", $url, $post );
    }

    public function put( $url, $post = null ) {
        return $this->go( "PUT", $url, $post );
    }

    public function delete( $url, $post = null ) {
        return $this->go( "DELETE", $url, $post );
    }

    public function go( $method, $url, $post ) {
        if ( ! is_array( $post ) ) {
            $post = null;
        }
        if ( ! $url ) {
            throw new \Exception( "Please enter a destination url" );
        }

        $url =  $this->base_url . "/" . $url;
        $headers = $this->getHeaders();

        //GET Override
        if ( $method == "GET" && $post !== null ) {
            $url .= (strpos($url, "?") === false ? "?" : "") . http_build_query( $post );
            $post = "";
        }

        //Setup cURL
        $ch = curl_init();
        curl_setopt( $ch, CURLOPT_URL, $url );
        curl_setopt( $ch, CURLOPT_HTTPHEADER, $headers );
        curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, $method );
        if ( $post ) {
            curl_setopt( $ch, CURLOPT_POSTFIELDS, json_encode( $post ) );
        }
        curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true );
        curl_setopt( $ch, CURLOPT_CONNECTTIMEOUT, 10 );
        curl_setopt( $ch, CURLOPT_TIMEOUT, 10 );
        curl_setopt( $ch, CURLOPT_USERAGENT,  "orderdesk/orderdesk_client" );

        //Send To Order Desk and Parse Response
        $response = trim( curl_exec( $ch ) );
        $info = curl_getinfo( $ch );
        $json = json_decode( $response, 1 );

        if ( ! is_array( $json ) ) {
            return $response;
        }

        $this->last_status_code = $info[ 'http_code' ];
        return $json;
    }

    //Get auth headers for this call
    public function getHeaders() {
        return array(
            "ORDERDESK-STORE-ID: {$this->store_id}",
            "ORDERDESK-API-KEY: {$this->api_key}",
            "Content-Type: application/json",
        );
    }

    //Check Post JSON
    public function validatePostedJson() {

        if ( ! isset( $_POST[ 'order' ] ) ) {
            header( ':', true, 400 );
            die( 'No Data Found' );
        }

        //Check Store ID
        if ( ! isset( $_SERVER[ 'HTTP_X_ORDER_DESK_STORE_ID' ]) || $_SERVER[ 'HTTP_X_ORDER_DESK_STORE_ID' ] != $this->store_id ) {
            header( ':', true, 403 );
            die( 'Unauthorized Request' );
        }

        //Check the Hash
        if ( ! isset( $_SERVER[ 'HTTP_X_ORDER_DESK_HASH' ]) || hash_hmac( 'sha256', rawurldecode( $_POST[ 'order' ] ), $this->api_key ) != $_SERVER[ 'HTTP_X_ORDER_DESK_HASH' ] ) {
            header( ':', true, 403 );
            die( 'Unauthorized Request' );
        }

        //Check Order Data
        $order = json_decode( $_POST[ 'order' ], 1 );
        if ( ! is_array( $order ) ) {
            header( ':', true, 400 );
            die( 'Invalid Order Data' );
        }
    }
}

Google Sheets Client

Using the spreadsheets.values.update API, we can create a Google Sheets Client. Create a file and name it GoogleSheetsClient.php and copy the following code:

<?php

require __DIR__ . '/vendor/autoload.php';

class GoogleSheetsClient
{
   protected $service;

   /**
    * GoogleSheetsClient constructor
    */
   public function __construct()
   { 
       $apiKey = getenv( 'GOOGLE_API_KEY' );

       $client = new Google_Client();
       $client->setAccessType( 'offline' );
       $client->useApplicationDefaultCredentials();
       $client->setDeveloperKey( $apiKey );
       $client->setSubject( getenv( 'GOOGLE_SERVICE_ACCOUNT_NAME' ) );
       $client->setScopes( [ 'https://www.googleapis.com/auth/spreadsheets' ] );

       $this->service = new Google_Service_Sheets( $client );
   }

   /**
    * Creates an event
    *
    * @param array $eventDetails event details e.g summary, start, end, attendees, e.t.c
    *
    * @return array $user of a user
    */
   public function updateSheet( $values, $range, $spreadsheetId )
   {

       $requestBody = new Google_Service_Sheets_ValueRange( [
           'values' => $values
       ] );

       $params = [
           'valueInputOption' => 'USER_ENTERED'
       ];

      return $this->service->spreadsheets_values->update( $spreadsheetId, $range, $requestBody, $params );
   }
}

In the constructor, we first authenticate using the credentials we got earlier from Google. The updateSheet method has the logic that updates a sheet based on the parameters passed to it.

Order Class

So far we have created clients that make it easier for us to use both Order Desk and Google Sheets. Now we need to create a class that will inject the clients, fetch data from Order Desk, update the Google Sheet and send a Twilio SMS.

Create a file called Order.php and copy the following code:

<?php

class Order
{
  private $googleSheets;
  private $orderDesk;
  private $twilio;

  public function __construct( GoogleSheetsClient $googleSheets, OrderDeskApiClient $orderDesk, Twilio\Rest\Client $twilio )
  {
      $this->googleSheets = $googleSheets;
      $this->orderDesk    = $orderDesk;
      $this->twilio       = $twilio;
  }

  /**
   * Gets orders from OrderDesk
   *
   * @return void
   */
  public function getOrders()
  {
      $response = $this->orderDesk->get( "orders" );
      $orders   = $response[ "orders" ];
      $values   = [];

      if (!empty($orders)) {
           foreach ( $orders as $order ) {

               $value = [
                   $order["shipping"]["first_name"],
                   $order["shipping"]["last_name"],
                   $order["email"],
                   $order["shipping_method"],
                   $order["payment_type"],
                   $order["order_total"]
               ];
  
               array_push( $values, $value );
           }
  
           $this->updateGoogleSheets( $values );
       } else {
           return $response['message'];
       }
  }

  /**
   * Updates Google Sheets
   *
   * @param array $values - values of the fields that need
   * to be updated on Sheets
   *
   * @return void
   */
  public function updateGoogleSheets( $values )
  {
  
      $spreadsheetId = getenv( "SPREADSHEET_ID" );
      $range         = 'Sheet1!A2:F2';

      $response = $this->googleSheets->updateSheet( $values, $range,$spreadsheetId );

      if ( $response->updatedRows ) {
          $this->sendSms( $response->updatedRows );
          return "Congratulations, $response->updatedRows row(s) updated on Google Sheets";
      }
  }

  /**
   * Sends an SMS about the update on Sheets
   *
   * @param integer $numberOfRows - number of rows
   *
   * @return void
   */
  public function sendSms( $numberOfRows )
  {
      $myTwilioNumber = getenv( "TWILIO_NUMBER" );

      $this->twilio->messages->create(
          // Where to send a text message
          "INSERT VERIFIED NUMBER",
          array(
             "from" => $myTwilioNumber,
             "body" => "Hey! $numberOfRows row(s) updated on Google Sheets!"
         )
      );
  }
}

Make sure to update the above code with a number you would like the SMS sent to. If you’re using a trial account, you can only send an SMS to a verified account.

Putting It All Together

In order for the files we’ve created to work, we need to link them. Let’s create a file called index.php and copy the following code:

<?php
require __DIR__ . "/vendor/autoload.php";

require "GoogleSheetsClient.php";
require "OrderDeskClient.php";
require "Order.php";

use Twilio\Rest\Client as TwilioClient;

$dotenv = new Dotenv\Dotenv(__DIR__);
$dotenv->load();


$apiKey    = getenv( "ORDER_DESK_API_KEY" );
$storeId   = getenv( "ORDER_DESK_STORE_ID" );
$orderDesk = new OrderDeskApiClient( $storeId, $apiKey );

$twilioAccountSid = getenv( "TWILIO_SID" );
$twilioAuthToken  = getenv( "TWILIO_TOKEN" );
$twilio           = new TwilioClient( $twilioAccountSid, $twilioAuthToken );

$googleSheets = new GoogleSheetsClient();

$order = new Order( $googleSheets, $orderDesk, $twilio );

$result = $order->getOrders();

echo $result;

When we start our application, this file creates the objects and calls the getOrders() function in the Order class.

Note: It is important to note that this format is only ideal for a small application. As an application grows, more objects will need to be created when it starts and this could affect your application’s performance. This can be solved by getting a dependency injection application that reads an XML file. The objects would be serialized and written to a file. The index.php file would then simply read that file and directly create the objects.

Test Our Application

Before we can test the application, we need to create an order on Order Desk. From the dashboard create a new order and save.

In the project directory, run the command:

php index.php

Your sheet should now be updated and you will receive an SMS confirming a successful update.

Conclusion

Congratulations! You have successfully updated a Google sheet with data from Order Desk’s API and sent a confirmation SMS using Twilio. You can make this application even better by setting up a cron job to check for orders at given intervals.

I look forward to hearing about the amazing application you build. You can find the complete code on Github. You can reach me on:

Email: odukjr@gmail.com
Github: charlieoduk
Twitter: @charlieoduk

Related Resources

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.

How to use Google Sheets as a Database for your PHP App

Related Posts

Related Resources

From APIs to SDKs to sample apps

The latest ebooks, industry reports, and webinars

Twilio's developer community hub