Catch webhook with Google Sheet
This short guide will help you catch a webhook and write its data to a google sheet.
Use Cases
- Save Document details to Google Sheet for custom reporting
- Save token & field data
- Report on products sold for Inventory tracking in Google Sheets
Target Audience
This is designed to help developers understand how to process PandaDoc webhook data and generate a signature for verification. It is recommended to have some background in programming in case the google sheet does not meet your needs "as-is"
No Development Support
PandaDoc does not provide development/coding support. This example is "as-is" and PandaDoc support will be unable to assist outside of setting up the webhook.
A new blank sheet is recommended but not required. Click here to create a new Google sheet.
Sheet Name
Don't forget to give your sheet a good name.
Open script editor
- Click Tools from the menu bar
- Select "Script editor" subitem
Add script code
The below code can replace the default Code.gs file
- sharedKey on line 14 will need to be replaced with your shared key.
- sheetName on line 17 will need to match your sheet name
// Required function for publishing Google script app
// This funciton fires when a GET message is recieved
function doGet(e) {
return HtmlService.createHtmlOutput("doGet request");
}
// Required fucntion for publishing Google script app
// This fucntion fires when a POST message is recieved
function doPost(e) {
// Pandadoc Shared key found on Pandadoc webhook configratuion page.
// https://app.pandadoc.com/a/#/settings/integrations/webhook
var sharedKey = "[Your Shared Key]";
//Name of Sheet to be used.
var sheetName = "Sheet1";
try {
// signature sent from PandaDoc
var pandadocSignature = e.parameter.signature;
// payload to be used to genreate signature for verification
var input = e.postData.contents;
// genreated signature from input & key
var byteArraySig = Utilities.computeHmacSha256Signature(input, sharedKey);
// convert byte array into hex string
var generatedSignature = byteArraySig.reduce(function(str,chr){
chr = (chr < 0 ? chr + 256 : chr).toString(16);
return str + (chr.length==1?'0':'') + chr;
},'');
// compare generated signature with pandadoc signature.
// if they do not match the key has changed or payload has been modified
if ( pandadocSignature != generatedSignature )
{
throw new Error( "Webhook signature does not match. Key changed or payload has been modified!" );
}
// convert payload into js object
// webhook events are only for one document so we can just extract the first array object
var data = JSON.parse(e.postData.contents)[0].data;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
// write some data to Google sheet. There is other data that can be used but these are common data point for every document
sheet.appendRow([data.id, data.name,data.status, data.date_created,data.date_modified, data.expiration_date,
data.created_by.email,data.grand_total.currency, data.grand_total.amount
]);
} catch (error) {
// if something goes wrong we will append message to google sheet so it can be easily found
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
sheet.appendRow([error])
}
return HtmlService.createHtmlOutput("doPost received");
}
Save project and give it a name
Publish web App
- Click Publish from the menu bar
- Click "Deploy as a web app..."
- Give the project a version number
- Set app access to "Anyone, even anonymous"
Authentication - Signature Verification
PandaDoc sends a Signature with each webhook post. This signature in conjunction with the shared key found in PandaDoc webhook settings. Can be used to verify the webhook came from PandaDoc and the payload has not been modified.
If prompted review and authorize permissions.
Save Webhook URL
Copy web app URL from Success prompt. This will be used to create your PandaDoc webhook.
It should look like https://script.google.com/macros/s/[Some-Long-ID]/exec
Create PandaDoc Webhook
Enterprise plan
Webhooks are a feature of the PandaDoc Enterprise Plan if you are unable to create a webhook please verify your plan Here and selecting "Account" on the left panel.
Create Webhook
To create a new Pandadoc webhook open https://app.pandadoc.com/a/#/api/configuration and use the βCreate webhookβ button in the Webhooks section.
Give the webhook a name and copy the URL from your google sheet into the "Webhook endpoint URL" Click Save to create the webhook.
After webhook is created reopen the settings to get your SharedKey.
Copy the SharedKey and pasted it into line 14 of your Google Sheet Code.gs file
Update Sheet Name
If your sheet is not named "Sheet1" you will need to update the sheet name in the Code.gs file. This can be found at line 17 the variable "sheetName".
Publish GoogleSheet with updated Key
- Publish from menu bar
- Click "Deploy as web app..."
- Select "New" from Project version dropdown
- Enter a new version
- Click Update
Publish new code
Each time a change is made to your Code.gs file it will need to be published with a new version before it will be applied.
Test
The expected result is for a new row to be added to the Google Sheet everytime a document is created, sent, viewed, completed, or paid.
To test this start by creating a new document in PandaDoc. A new row should appear at the end of your Google Sheet. It should look similar to the data below.
Common Errors
If the shared key does not match what is found in Pandadoc you will receive the error 'Error: Webhook signature does not match. Key changed or payload has been modified!'
Updated 12 months ago