Google Sheets as a Trigger

Objective: Trigger a workflow when a Google Sheet is edited.

Setup Steps

1. Create and configure a new Google Sheet document
2. Create a Script Project and deploy a web app
3. Setup the webhook

Step 1: Create and configure a new Google Sheet document

*If you have an existing sheet you would like to use, you can skip this step

The first step is to create a new Google Sheet document. You can do this by navigating to Google Sheets and selecting the Blank option under Start a new spreadsheet. 

Step 2: Create a Script Project

This step is where the bulk of the work will be completed. In this example, we are going to trigger a webhook to Tray when a sheet is edited and include the sheet name, updated cell, new value and the user.

To create a script project, follow the steps below.

1. Open your Google Sheet.
2. Select Extensions > Apps Script.
3. At the top left of the script editor, click Untitled project.
4. Give your project a name and click Rename.

After opening the script editor, paste in the below code and select Save. 

function onEdit(e){

   var range = e.range;
   var user = e.user;

   var formData = {
     'CELL_UPDATED': range.getA1Notation(),
     'NEW_VALUE': range.getValue(),
     'SHEET':range.getSheet().getName(),
     'USER': user
};

   var options = {
     'method' : 'post',
     'payload' : formData
};

   UrlFetchApp.fetch('[YOUR_WEBHOOK_URL]', options); <-- See Step 3 below for webhook URL
}

The next step in the Apps script is to set up our triggers. Hover over the menu on the left and select Triggers.

At the bottom right of the Trigger screen, select the Add Trigger button. This will display the below screen. Ensure the options are set as below. You can choose to send the notifications immediately, hourly, daily or weekly. Select Save. 

The final step within the Apps Script console is to deploy the web app.

Select Deploy > New Deployment from the top right of the console. Choose your access level and select Deploy. 

Step 3: Setup the webhook

The final step in the configuration is to create a new workflow within Tray. Follow the steps below to complete the process. 

1. Create a new workflow with a webhook as the trigger
2. Enable the workflow to generate a public URL
3. Select the meatball menu icon and select Settings
4. Copy the Workflow public URL
5. Paste this URL into the code snippet from above and save the changes in your Apps Script project
6. Done!