Scheduling data delivery to Google sheets

[conor (Conor O'Mara)]

Google sheets is a great product that many customers use for their spreadsheet solutions. While Omni has built in spreadsheet support for calculations, tables and XLOOKUPs. There may be cases when you want to schedule exports of data to Google sheets. Omni natively supports sending content to 3 different destinations:

1. Email
2. Slack
3. Webhooks

In this guide, we’ll demonstrate how to use Omni’s webhook delivery method in conjunction with Zapier to send schedules and alerts to Google Sheets. You can configure Google Sheets in two different ways:

1. Overwrite a tab in your Google sheet on each delivery: Zapier template (Overwrite tab)
2. Create a new tab in your Google sheet on each delivery (using timestamp in tab name): Zapier template (timestamped tabs)

The first half of both workflows are the same and they differ in the google sheets configuration.

Overwrite a tab in your Google sheet on each delivery

1. Set up your Omni dashboard with a tile you want to export as either a Schedule or an Alert. The destination should be Webhook, set the format as csv, for now the Webhook URL will be left blank. You can follow the Loom for step 1
   

   

2. Switch to Zapier and create a new workflow. For the first step, select Webhooks by Zapier and set the event to Catch Raw Hook. Select continue and Test Trigger. Copy the webhook URL and switch back to Omni and paste it as the webhook URL for the schedule. In Omni select Test Now to send the test to Zapier. Save the schedule/alert in Omni. Switch back to Zapier and select test trigger and you should see the results of the POST request from Omni. You will see the csv data in the Raw Body key. Scrolling you will see over values such prefixed with headers__<words>. Loom for step 2.

3. Add another step to your Zapier workflow called Files by Zapier. Set the event to Line Items From CSV Rows, then click Continue. In the Action configuration page, set the CSV file to the Raw Body form the Catch Raw Hook stage. Set has header row to true and delimiter as Comma. Press Continue. Press Test Step and check the contents of the parsed csv have your header names and values. Loom for step 3

4. Add another step to the Zapier workflow. Set Event to Create Worksheet. Choose the Google drive account to use, select continue. In Action, choose the Drive, Spreadsheet and sheet title. To insert the data, manually type in the names of each column from your export as they appear in the results tab in Omni. Select Overwrite True, press Continue. Press Test Step and navigate to your google sheet. There should now be a tab created with the configured name and the header columns populated. Loom for step 4

5. Add a new google sheets step to the Zapier workflow. Set Event to Create Multiple Spreadsheet Rows. Choose your account, drive, spreadsheet and worksheet to match the same step as before. For the Rows, select the columns from the Files by Zapier step, selecting the columns in the same order as the header columns have been setup. In this example it’s (Status, Complete, Processing, Shipped). Test this step and Zapier will send 10 rows into the worksheet. Check the google sheet to confirm it’s working. Loom for Step 5.

6. Publish the Zapier workflow, give it a name and version, and ensure it’s active. Then navigate back to your Omni dashboard, find your schedule/alert and select the three dots and select Send Now. Wait a few seconds and navigate back to your google sheet’s worksheet and it should be updated with all the data in your workbook and tada your data should be there up to date. Loom for Step 6. This will now work with your schedules/alerts.

   

7. You can use the Zapier template (Overwrite tab) as a starting point.

Create a new tab in your Google sheet on each delivery

1. [Follow steps 1-3] as above.
2. In this example I’m using a different dataset and tile in my dashboard (just 2 columns). Add the Google Sheets Create Worksheet event to your Zapier workflow. Configure the account, drive, spreadsheet. Name the Title of googlesheet what you like but append in -{{zap_meta_human_now}} to include a timestamp in the tab name. Set overwrite to False. Define the headers (in this example they are Email Domain and Users Count). Select continue. Test the step and a new worksheet should be in your googlesheet. Loom for Step 2 to configure creating a new google sheet worksheet per run
3. Add a new step to your Zapier workflow using Google sheets to Create Multiple Spreadsheet Rows. Conifgure the account, drive, spreadsheet. For the Worksheet, select the Custom option and choose the ID option generated as part of the Create Worksheet step to ensure you are inserting data into the tab created in the previous step. For the Rows, select the columns from the Files by Zapier step, selecting the columns in the same order as the header columns have been setup. In this example it’s (Email Domain and Users Count). Test this step and Zapier will send 10 rows into the worksheet. Check the google sheet to confirm it’s working. Loom of this step
4. As in the final step for the overwrite workflow. Publish the Zapier workflow, give it a name and version, and ensure it’s active. Then navigate back to your Omni dashboard, find your schedule/alert and select the three dots and select Send Now. Wait a few seconds and navigate back to your google sheet’s worksheet and it should be updated with all the data in your workbook and tada your data should be there up to date. This will now work with your schedules/alerts.
5. You can use the Zapier template (timestamped tabs) as a starting point.

Common troubleshooting

• It you are exporting a table from Omni, it might be best to turn off totals for the export.

Conclusion

This guide provides two ready-to-use Zapier templates for integrating Omni with Google Sheets: one for overwriting existing tabs and another for creating new timestamped tabs. These templates streamline the process of automating your data exports, ensuring your team always has access to up-to-date information for analysis and reporting. Feel free to customize these templates to fit your specific needs, and don’t hesitate to reach out to our support team if you need any assistance.