Google Sheets Integration Guide

This guide will show you how you can send and retrieve data with Google Sheets.

Overview

We'll cover the following topics:

1. Requirements for Integration
2. Set up an API for Google Sheets
3. Add a row to a Google Sheet
4. Get data from a Google Sheet row

Requirements for Integration

• A Google Sheet to send and retrieve data with
• Permission to use Google App scripts
• Basic Understanding API calls and JavaScript
• Access to the CueDesk Flow Builder

Set up an API for Google Sheets

1. Open your Google Sheet
2. from the top the top menu, navigate to Extensions → Apps Script
3. Insert the code for your Apps Script (see code samples below)
4. Click Deploy → Manage deployments.
5. Click New deployment.
6. Under Select type, choose Web App.
7. Set the description for your script
8. Set Execute as to Me
9. Set Who has access to Anyone with the link
10. Click Deploy
11. Authorise the script when prompted
12. Copy the Web App URL – this is the API endpoint URL you will use when making requests from your flow in Cue.

Once you've done this you will now have a URL that you can make requests to from the Cue Flow Builder.

Add a row to a Google worksheet

For this example, let's assume we are going to save enquiries made via WhatsApp to our Google Sheet.

Update Google Apps Script

We want to update our our Apps Script code to do the following:

1. Receive and handle a POST request
2. Check that the API key is correct
3. Extract name, email and enquiry from the request body
4. Save a new row to the "Enquiries" sheet in our spreadsheet that contains name, email, enquiry and current date and time.

Below is a sample of code you can add to your Apps Script to perform the above actions. Remember to replace the API_KEY and SHEET_NAME with your own values

// Update with your own API key and sheet name below.
const API_KEY = "<your_token_here>";
const SHEET_NAME = "Enquiries"

function doPost(e) {
  
  // Parse the incoming POST data
  const data = JSON.parse(e.postData.contents);
  const apiKey = data.api_key;
  const type = data.type;

  // Reject request if API_KEY is incorrect.
  if (apiKey !== API_KEY) {
    return ContentService.createTextOutput(JSON.stringify({ status: "error", message: "Invalid API key" }))
                         .setMimeType(ContentService.MimeType.JSON);
  }

  // Add data from the request to a new row
  if (type === "add") {
    // Access the "Enquiries" sheet
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);

    // Extract data from the request (use empty string if no data)
    const name = data.name || "";
    const email = data.email || "";
    const enquiry = data.enquiry || "";
    const date = new Date(); // Get current date/time

    // Append a new row with the enquiry data
    sheet.appendRow([date, name, enquiry, email]);

    // Return success response
    return ContentService.createTextOutput(JSON.stringify({ status: "success", message: "Data saved" }))
                         .setMimeType(ContentService.MimeType.JSON);
  }

Remember to save and deploy your updated Apps Script after making the above changes.

Call the API from your Cue flow

In your flow you can now add an HTTP Request step to make a POST request to the web app URL you were given for your Google Apps script:

Then add the body to your request:

Once you've added this step to your flow, publish your flow again and when you reach this step, a request will be submitted to your Google Sheet and the Enquiries sheet will be updated with the new entry.

Get data from a Google Worksheet

For this example, let's assume we want to look up a person saved in a spreadsheet based on their phone number.

Update Google Apps Script

We want to update our our Apps Script code to do the following:

1. Receive and handle a POST request
2. Check that the API key is correct
3. Extract contact value from the request
4. Find the contacts column and search through it until we find a match on contact
5. Return the entire row in the response

Below is a sample of code you can add to your Apps Script to perform the above actions. Remember to replace the API_KEY and SHEET_NAME with your own values

```
// Update with your own API key and sheet name below.
const API_KEY = "<your_token_here>";
const SHEET_NAME = "Contacts"

function doPost(e) {
  
  // Parse the incoming POST data
  const data = JSON.parse(e.postData.contents);
  const apiKey = data.api_key;
  const type = data.type;

  // Reject the request if API_KEY is not correct
  if (apiKey !== API_KEY) {
    return ContentService.createTextOutput(JSON.stringify({ status: "error", message: "Invalid API key" }))
                         .setMimeType(ContentService.MimeType.JSON);
  }


  // Look for a contact in workskeet
  if (type === "lookup") {
    
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
    const contact = data.contact;

    // Ensure contact value is provided
    if (!contact) {
      return ContentService.createTextOutput(JSON.stringify({ status: "error", message: "Missing contact value" }))
                           .setMimeType(ContentService.MimeType.JSON);
    }

    // Get all rows and column headers
    const rows = sheet.getDataRange().getValues();
    const headers = rows[0];

    // Find the index of the "contact" column
    const contactIndex = headers.indexOf("contact");

    // If "contact" column is missing, return error
    if (contactIndex === -1) {
      return ContentService.createTextOutput(JSON.stringify({ status: "error", message: "No 'contact' column found" }))
                           .setMimeType(ContentService.MimeType.JSON);
    }

    // Search for a row where the contact matches
    const match = rows.slice(1).find(row => String(row[contactIndex]) === contact);

    // If a match is found, convert it to an object using headers as keys
    if (match) {
      const result = headers.reduce((obj, key, i) => {
        obj[key] = match[i];
        return obj;
      }, {});

      // Return the matched contact's details
      return ContentService.createTextOutput(JSON.stringify({ status: "success", data: result }))
                           .setMimeType(ContentService.MimeType.JSON);
    } else {
      // If no match found, return not found message
      return ContentService.createTextOutput(JSON.stringify({ status: "error", message: "Contact not found" }))
                           .setMimeType(ContentService.MimeType.JSON);
    }
  }
}
```

Call the API from your Cue flow

In your flow you can now add an HTTP Request step to make a POST request to the web app URL you were given for your Google Apps script:
Below is an example of the JSON payload:

Below is a screenshot from our Contacts Sheet showing that 27734378471 is a valid contact. As a result, the request will be successful and return all the details associated with that contact

Example of a successful response to this request:

Need Help?
Need a hand setting it up? Reach out to us  at support@cuedesk.com and we'll help you out.