Logging to Google Sheets Without a Middleman

[Tyron_Pretorius]

Have you ever tried to set up a Marketo Google Sheets Integration but have been frustrated by the need to use a 3rd party middleman like Zapier? Well, today I am going to show you a very cool and easy way that you can send Marketo data directly to a Google Sheet using a webhook and all in under 15 minutes!

Preparing Your Google Sheet

Creating Google Sheet to store Marketo Data

The first thing we are going to do is create the Google sheet where we want to log our Marketo data. In this example our sheet is going to contain 6 columns for each person who registers for our webinar:

• The timestamp that the webhook was sent from Marketo
• A hyperlink to the lead profile
• The person's first name
• The person's last name
• The person's company
• The person's email

Sample Google Sheet to log Marketo Data

Creating Google Apps Script to Catch Marketo Webhook

Next, we are going to go to Extensions > Apps Script and paste the code below into a new script. Make sure to swap in the name of the sheet you want to log to in this script.

Note that the logic used in this script is pretty simple, it loops through the items in the webhook payload and puts them one by one into the columns within a row. This means that the first field in your webhook payload will go in the first column, the second field in the second column and so on.

Unless you make the logic smarter in this script you will need to structure the payload of your webhook so that the fields are in the order you want them to appear in a row.

function doPost(e) {
  // Specify the name of the sheet (tab) you want to log data to
  var sheetName = 'YourSheetName'; // Replace 'YourSheetName' with the actual name of your sheet
  
  // Get the spreadsheet and the specific sheet (tab)
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  
  // Check if the sheet exists
  if (!sheet) {
    return ContentService.createTextOutput("Sheet not found");
  }
  
  // Parse the incoming POST data
  var params = JSON.parse(e.postData.contents);
  
  // Create an array to hold the row data
  var row = [];
  for (var key in params) {
    if (params.hasOwnProperty(key)) {
      row.push(params[key]);
    }
  }
  
  // Append the row data to the sheet
  sheet.appendRow(row);
  
  // Return a success response
  return ContentService.createTextOutput("Success");
}

 

Deploying Google Apps Script

Then to deploy this Google Apps script so it is ready to catch our webhooks from Marketo we will:

1. Click on Deploy > New Deployment
2. Click on the gear icon beside "Select type"
3. Choose "Web app"
4. Enter the name of the script in the "Description" field e.g. Marketo Webhook Listener
5. Leave "Me" selected for the "Execute as" field
6. Select "Anyone" for the "Who has access" field
   • Note that you might need to work with your IT team on the permissions of this Google sheet to ensure that the "Anyone" option is available
7. Click "Deploy"
8. Click "Authorize access"
9. Select the same Google profile that you used to create the Google sheet
10. Click "Allow"
11. Copy the "URL" value

Deploying the Google Apps script

Creating the Marketo Webhook

Now that we have deployed this Apps script successfully we will complete our Marketo Google Sheets integration by creating a webhook to send our desired information to the URL that we just copied.

Webhook to send data to Google Sheets

If it is your first time using webhooks in Marketo or you already have experience with them but want to see cool use cases then I recommend checking out the Webhook Quick Start Guide.

Now to create our Google Sheets webhook in Marketo:

1. Navigate to the Admin section in Marketo
2. Click on “Webhooks” under the “Integration” section in the navigation tree on the left-hand side
3. Click on “New Webhook” at the top of the screen
4. Name your webhook using the “Webhook Name” field
5. Enter an optional description
6. Paste the URL of the Google Apps script deployment in the “URL” field
7. Choose "POST" as the “Request Type”
8. Paste the payload below into the "Template" field
   • Notice how the payload uses lead tokens to pull in the person's information and a system token to pull in the current time
   • You will need to replace https://app-xxxx.marketo.com with the link to your own Marketo instance
   • As mentioned in the "Creating Google Apps Script to Catch Marketo Webhook" section above you need to order the fields in the webhook to be in the same order as the columns in your Google sheet
9. Leave the “Request Token Encoding” field as “None”
10. Leave the “Response type” field as “None”
11. Click “Create”

{
"timestamp": "{{system.dateTime}}",
"leadId": "https://app-xxxx.marketo.com/leadDatabase/loadLeadDetail?leadId={{lead.Id}}",
"firstName": "{{lead.First Name}}",
"lastName": "{{lead.Last Name}}",
"company": "{{company.Company Name}}",
"email": "{{lead.Email Address}}"
}

 

Logging to Google Sheets

Now that our Marketo Google Sheets integration is complete all we have to do to log our Marketo data to a Google sheet is to call the webhook from the flow of a smart campaign. In this example, we are going to put the "Call Webhook" action in the flow of our smart campaign that triggers whenever anyone registers for our webinar.

You will notice that I also included a "Campaign is Requested" trigger alongside the "Fills Out Form" trigger. This is to make testing the webhook easier so that if we want to trigger the webhook for a test person we can simply:

1. Navigate to Database > System Smat Lists > All People
2. Enter the email address of the test person
3. Highlight the test person
4. Click Person Actions > Special > Request Campaign
5. Enter the name of the smart campaign we created to integrate with Google Sheets
6. Click "Run Now"

Smart campaign triggers

Calling the Google Sheets webhook in the flow

For the next webinar, you have you can either:

• Create a new tab in the same Google sheet and change the sheet reference in the Apps Script. This way you do not need to change the URL field in your Marketo webhook
• Create a new sheet and follow the steps above to deploy the Apps script in this new sheet. Then you will need to paste the new deployment URL into your webhook in Marketo

If you have webinars in succession then you will only need 1 webhook pointing to one deployment URL to log your webinar registrants. If you have concurrent webinars then you will need multiple webhooks to send to the multiple deployment URLs of the sheets that you want to log to.

What's Next?

And there you have it, that is how easy it is to integrate Marketo with Google Sheets to start logging data from your smart campaigns.

Next up you can take a deep dive into more use cases for Marketo webhooks and tokens:

• Webhook Guide with Examples
• Great Use Cases for Tokens
• Marketo Trigger Tokens Example Usage