Harness Cloudhooks for real-time Google Sheets updates: From sales to spreadsheets

In this series, we'll show you how to integrate Cloudhooks with popular SaaS products.

The first product in the series is Google Sheets - many Shopify store owners want to integrate with Sheets.

How to add a row to a spreadsheet

We'll cover the use case of how to append a row with customer and purchase data to a sheet for every order.

The sheet will have four columns: name, email, purchase date, and gross purchase amount.

Before we jump into the implementation, it's important to understand the different ways we can interact with Google Sheets.

Google Sheet integration options

Google Sheets offers a client-side javascript API, an Apps Script API, and a server-to-server integration.

Cloudhooks runs hooks on the server side, so it can't use the client-side Google Sheets javascript API. The server-to-server Google Sheets API requires OAuth, but we'd like to keep the integration simple. This leaves us with the Google Apps Script integration, which is surprisingly easy.

Having explored our options, let's focus on the most suitable approach for our needs: using Google Apps Script.

Integration steps using Google Apps Script

Did you know that a Google Apps Script can act as an API endpoint to receive HTTP GET or POST requests? It can also take advantage of Sheets’ client-side API which will make our job really simple.

Our integration will look like this:

1. In Cloudhooks, the hook sends the payload in a HTTP POST request to a Google Apps Script endpoint.
2. In Google Apps Script, the HTTP POST endpoint will:
   1. Receive the POST request.
   2. Authenticate the request with an API key.
   3. Extract data from the payload.
   4. Insert a new row in the spreadsheet.

With our strategy in place, it's time to roll up our sleeves and start building our integration.

Let’s start implementing

Create a new spreadsheet

Open Google Sheets and create a new spreadsheet. Name the first sheet “Orders”, and add name, email, date, and gross amount columns:

Take note of the spreadsheet ID found in the document URL (it’s the selection on the screenshot):

Create a Google Apps Script project

In your spreadsheet:

1. Select Extensions ➞ Apps Script from the menu to open a project.
2. Rename the project to “Shopify Order Tracker”.

Implement the Google Apps Script

Replace the contents of the script editor with this code:

const API_KEY = 'YOUR_API_KEY_HERE'
const SPREADSHEET_ID = 'YOUR_GOOGLE_SPREADSHEET_ID_HERE'

const SHEET_NAME = 'Orders'

function authenticate(parameter) {
  if (!parameter || !parameter.apiKey || parameter.apiKey !== API_KEY) {
    return false
  }

  return true
}

function generateAccessDeniedResponse() {
  return ContentService
          .createTextOutput('{"success": false, "errorCode": "401", "errorMessage": "Access denied."}')
          .setMimeType(ContentService.MimeType.JSON)
}

function generateSuccessResponse() {
  return ContentService
      .createTextOutput('{"success": true}')
      .setMimeType(ContentService.MimeType.JSON)
}

function doPost(e) {
  if (!authenticate(e.parameter)) {
      return generateAccessDeniedResponse()
  }

  const payload = JSON.parse(e.postData.contents)

  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = spreadsheet.getSheetByName(SHEET_NAME);
  sheet.appendRow([
    payload.customer.first_name + ' ' + payload.customer.last_name,
    payload.customer.email,
    new Date(payload.created_at).toLocaleDateString(),
    payload.current_total_price
  ])

  return generateSuccessResponse()
}

‍

Replace the spreadsheet id constant with a real value, and create an API key with random alphanumeric characters.

Note that the API key is sent as a query parameter, so using special characters might cause unwanted side effects.

Now that we have our script ready, the next step is to make it accessible as a web app so Cloudhooks can send data to it.

Deploy the script as a web app

Deploy the script to receive requests by following these steps:

1. Click Deploy ➞ New Deployment
2. Select “Web app” as the type
3. Set the following options
   1. Execute as: “Me”
   2. Who has access: “Anyone”
4. Click “Deploy”

Click “Authorize access” to grant access to the script to your spreadsheet.

Copy the web app URL from the deployment screen:

‍

With our Google Apps Script deployed, we're ready to configure Cloudhooks to send data to our new endpoint.

Set up the Cloudhooks integration

Create a new hook for the “Order created” event in your Cloudhooks dashboard.

Paste the following code in the “Hook” tab:

module.exports = async function(payload, actions, context) {
  const API_KEY = 'YOUR_SECRET_API_KEY_HERE';
  const SCRIPT_URL = 'YOUR_GOOGLE_SCRIPT_DEPLOYMENT_URL_HERE';
   
  const result = await actions.http.post(`${SCRIPT_URL}?apiKey=${API_KEY}`, payload);
  console.log('Result: ', result.data);
}

Replace the constants for the API key and the deployed script URL.

Our setup is complete, but before we celebrate, let's make sure everything is working as expected.

Test your integration

You can perform end-to-end testing in Cloudhooks. Open your hook, go to the “Hook” tab, and press the “Run test” button:

If your integration works, then:

• A new row should appear in the spreadsheet.
• The API call should return a JSON object with {success: true}.

Conclusion

Congratulations! You've successfully integrated Cloudhooks with Google Sheets to automatically track Shopify orders. This powerful combination allows you to:

1. Capture real-time order data from your Shopify store
2. Automatically populate a Google Sheet with customer and purchase information
3. Create a centralized, easily accessible record of all your orders

Remember, this is just one example of how Cloudhooks can enhance your e-commerce operations. As you become more comfortable with the platform, you can explore additional integrations and automations to further streamline your business processes.

Possible improvements

While the integration we've built is functional, there are always ways to enhance security and efficiency. Here are some suggestions for improvement:

Enhance API key security

It's crucial to keep your API key secret and never share it publicly. For production use, consider implementing a more secure method of storing and accessing your API key. One excellent option is to use Google Secret Manager.

Why use Google Secret Manager?

Google Secret Manager offers several benefits for managing sensitive information like API keys:

1. Centralized storage: It provides a central, secure location to store all your secrets.
2. Version control: You can manage different versions of your secrets.
3. Fine-grained access control: You can precisely control who or what has access to your secrets.
4. Audit logging: It maintains logs of all access to your secrets, enhancing security and compliance.

How to implement Google Secret Manager

To use Google Secret Manager with your Google Apps Script:

1. Store your API key in Google Secret Manager.
2. Update your Apps Script to retrieve the API key from Secret Manager instead of hardcoding it.

This Stack Exchange article provides a detailed explanation of how to access Secret Manager from Google Apps Script.

As your integration needs grow and evolve, continually reassess and improve your security measures to protect your data and your customers' information.

‍