Google Sheets are used for organizing, visualizing, and calculating data in a spreadsheet. You can pull in and analyze data from other tools you use, easily share and collaborate with your team, and seamlessly connect to other Google apps. Using a Google Sheet app in Blueshift, you can update your spreadsheet from within a campaign journey.

Here are some use cases:

  • Update the spreadsheet with new leads
  • Generate a list of new subscribers during a specific time period
  • Get a list of customers who purchased a specific item and so on.

Setup overview

Here are the steps involved in setting up integration with Blueshift and updating the Google Sheet from a campaign:

  1. Complete the prerequisites.
  2. Configure the Google Sheet app in Blueshift to set up integration.
  3. Configure the payload by setting up a template.
  4. Set up a campaign journey and add a Google Sheets trigger.
  5. Launch your campaign.

Prerequisites

Before you can set up integration with Blueshift, you must complete the following prerequisites:

  • You must have the Google Sheet URL and Sheet ID for the sheet that is to be updated. For more information, see documentation for Google Sheets.
  • Share the Google Sheet that you want to update with Blueshift at the following email address. This gives Blueshift permission to add new rows to the Google Sheet.
    • Email address: blueshift-app@blueshift-cdp.iam.gserviceaccount.com
    • Role: EDITOR

  Note

Rate limits: Google has a rate limit of 300 Google Sheet API updates per minute. However, Google can increase rate limits on request.

Set up integration

To add a Google Sheets adapter in Blueshift, complete the following steps:

  1. Sign in to the Blueshift app, and click App Hub in the left navigation menu.
  2. Go to All Apps, search for, and select Google Sheets.
  3. You can also go to Data Platforms Apps and select Google Sheets.
  4. Click Configure to view all the configured adapters.
  5. Click +ADAPTER to add an adapter.
     

    googlesheets_adapter_dec23.png

  6. The API key is added to the adapter by default. 
  7. Add a Name for the adapter.  If you have multiple adapters, the adapter name helps you to identify the integration.
  8. Click Save to add the adapter.

    The adapter is now added and you can use it in your Cloud App template. 

Quick start templates

When you create a Google Sheets adapter in the App Hub, the following quick start template is added to the Cloud App templates.

  • Add a new row to a Google sheet

Consider the following points about quick start templates:

  • Quick start templates are identified by the label "QUICK START".
  • A quick start template is added only when you create the first adapter for the app in App Hub. If you have multiple adapters for an app, you can edit the Adapter details on the template Properties tab to select the required adapter.
  • You can use this template as-is in your campaigns. You cannot make any changes to the schema for a quick start template.
  • You can clone the template and then edit the schema as required.

Add a Google Sheets cloud app template

A Cloud App template is required to send a payload to Google Sheets from a campaign. You can include personalizations and recommendations in the template content. For more information, see Cloud Apps templates.

  Note

We recommend that you use the quick start template or a clone of the quick start template as these templates are pre-populated with the required values for the API Endpoint, Advanced Settings, and Content Schema. If you opt for creating a Google Sheets Cloud App template from scratch, we recommend that you copy the values from these pre-populated fields in the quick start template.

To use a quick start template or to add a Google Sheets Cloud App template, complete the following steps:

  1. Go to Templates > Cloud App.
  2. Complete one of the following actions:
    • Select the Add a new row to a Google sheet quick start template for Google Sheets from the list of templates.
    • To create a clone of the quick start template, go to the action menu for the template and click Clone.
    • To create a new template, click +TEMPLATES on the Cloud App Templates page. Select the Google Sheets app.

      Note: Only apps that support Cloud Apps templates and have atleast one adapter configured are displayed on the Cloud App Templates page.

  3. Go to the Properties tab.
  4. Specify a name for the template to identify it on the index screen.
  5. Select the adapter to use to send the payload.
  6. The API endpoint is added by default to the quick start template. If you have added a new template, copy the API endpoint from the quick start template and use it.
  7. In the Advanced Settings section, select the HTTP Method as POST. This is selected by default in the quick start template.
  8. Add tags and other relevant information.
     

    googlesheets_template_properties_dec23.png

  9. Go to the Content tab.
  10. If you are using a cloned quick start template or a new template, go to the Schema tab and configure the schema for the form.
    1. For Google Sheets, the sheet_url and sheet_id are mandatory fields. Include these fields as required fields in the schema. The data type for these fields is String.
    2. Add a row field with data type as Array of type String. The array represents the columns in the Google Sheet.
  11. Go to the Content Form tab and fill the form to specify the data to send to Google Sheets.
    1. Add the sheet_url and sheet_id for the Google Sheet you are updating.
    2. For the row fields, you can use Liquid for personalization.

    googlesheets_template_content_dec23.png

  12. In the preview tab, you can also switch to JSON and view the content in JSON format.
  13. To use attributes from an event in the content instead of user attributes, go to the Data tab > Preview Data section. 
    1. Select Event as the option and then select the event from which you want to use attributes to add to the spreadsheet.
      In this example, we are using the subscribe event.
    2. In the Show Data section, go to the bsft_event_context object. This contains the event specific data that you can use in your template.
        googleSheets_data.png
       
  14. Save the template.

Next steps

In your campaign journey, add a Google Sheets trigger. Select the template that you created.

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.