Sending Omni webhook deliveries to Google Apps Script

In this guide, you'll learn how to use Google Apps Script to catch a webhook from Omni and export data to Google Sheets. Each time the script runs, it will create a new tab in the destination sheet and use the current timestamp as part of the tab's title.

Permissions Overview

• The destination Google Sheet is private.
• The Apps Script is deployed as a webhook url which has permissions execute as=me and who has access=anyone
• The Apps Script runs as the script creator (to take action and edit the destination sheet) but can be executed by anyone with the url. This configuration is how Omni can execute the script without authenticating itself to your Google account.

warning

• The webhook url should only be used for Omni webhook deliveries to the destination sheet, If the url is used outside of this Omni integration, data could be written to the destination sheet by other sources.
• Anyone with access to the dashboard will be able to see the URL and therefore potentially use it to write data to the sheet. This may not be a good fit for sensitive or critical data sets or analyses.

Requirements​

Note: While entire dashboards can be delivered using webhooks, this example requires data in a CSV format, which isn't currently supported for entire dashboards.

To follow along, you'll need:

• An Omni dashboard with a tile configured for export.

• A Google account and an existing Google Sheet.

1. Copy the Google Apps Script template

1. Navigate to https://script.google.com/home/projects/create and press Select New Project. This will open a blank Google Apps Script project with a new Script file.
2. Rename the Script File and Project.
3. Paste the Apps Script template into the file and save.

Script Template:

const SPREADSHEET_TITLE_PREFIX = 'Omni-Export'; // Edit this Variable to Rename Sheet Title Prefix
const SPREADSHEET_ID = 'YOUR_ID_HERE'; // Edit this Variable to Link to Your Sheet


function doPost(e) {
 try {
   // Parse and validate the incoming data
   const csvData = e.postData.contents;
   if (!csvData) {
     throw new Error('No CSV data received');
   }


   // Process the data
   copyCSVToSheet(csvData);


   // Return 200 success response
   return ContentService.createTextOutput(JSON.stringify({
     status: 'success',
     message: 'Data processed successfully'
   }))
   .setMimeType(ContentService.MimeType.JSON)
   .setResponseCode(200);


 } catch (error) {
   // Return error response
   return ContentService.createTextOutput(JSON.stringify({
     status: 'error',
     message: error.message
   }))
   .setMimeType(ContentService.MimeType.JSON)
   .setResponseCode(400);
 }
}


function getTimestampFilename() {
 const now = new Date();
 return now.toISOString().replace(/:/g, '-');
}




function copyCSVToSheet(csvData) {
 const timestamp = new Date().toISOString().replace(/:/g, '-');
 const tabName = `${SPREADSHEET_TITLE_PREFIX} ${timestamp}`;
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
 const newSheet = ss.insertSheet(tabName);
  const csvContent = Utilities.parseCsv(csvData);
 newSheet.getRange(1, 1, csvContent.length, csvContent[0].length)
   .setValues(csvContent);
  console.log(`CSV data copied to "${tabName}"`);
}

2. Create the destination Google Sheet

1. Open a new Google Sheet, this will be your destination sheet.
2. Note the Sheet ID located in the URL between d/SHEET_ID/edit.

3. Configure the Apps Script

1. In the Apps Script template there are two variables:

• SPREADSHEET_TITLE_PREFIX is used to prefix the new tab that is created when the .csv is imported into the sheet. You can modify this as needed.

• SPREADSHEET_ID is the spreadsheet ID of the destination sheet

2. Copy the spreadsheet ID from the destination sheet into the SPREADSHEET_ID variable in the Apps Script template, then save the Apps Script.

4. Deploy the Google Apps Script

Note: Once deployed, any changes made to the script will require a new deployment which will generate a new Web App URL (webhook url) that will need to be updated in the Omni delivery.

1. Select Deploy, then New deployment to open the deployment dialog.
2. Press the gear icon and select Web app to set the deployment type.
3. In the configuration menu, select Execute as = Me, and Who has access = Anyone.
4. Press Deploy, then follow the prompt to authorize access.
5. On a successful deployment, you will see ‘Deployment successfully updated’ and a ‘Web app URL’. Copy the Web app URL then navigate to your Omni dashboard.

5. Finalize the workflow

In Omni:

1. Open your Omni dashboard and create a new delivery.
2. In the delivery Send menu, select the query you want to send to your Google Sheet.
3. Set Destination to 'Webhook', then open the Webhook tab below.
4. Paste the Web App URL into the ‘URL’.
5. Open the Chart tab and set the Format to ‘CSV’.
6. Test the delivery by pressing Test Now on the schedule, and check your destination sheet to confirm the integration is working. (this may take a moment to appear in your destination sheet).
7. Finalize by setting the delivery schedule, then Save.

Troubleshooting

• Verify no changes were made to the script after deploying.
• Verify the ‘Web App URL’ is correctly copied from Google Apps Script to Omni.