Logging to Google Sheets Without a Middleman

Tyron_Pretorius
Level 9 - Champion Level 9 - Champion
Level 9 - Champion

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 DataSample 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 scriptDeploying 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 SheetsWebhook 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 triggersSmart campaign triggers

 

Calling the Google Sheets webhook in the flowCalling 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:

 

1811
5
5 Comments
SanfordWhiteman
Level 10 - Community Moderator

That webhook payload will break on any character that requires JSON-encoding. Observe what happens when you send a Notes/Comments field, for example.

 

(Also, you can post to a Google Form via webhook, and that form will automatically update a Google Sheet. No Zapier needed, wrote about that 6-7 years ago.)

Tyron_Pretorius
Level 9 - Champion

@SanfordWhiteman there is the option to JSON encode the webhook payload in the settings to handle characters in Notes/Comments fields that require encoding, I am not sure what the encoded notes field would look like in the Google Sheet but I am guessing the notes field could be decoded in the Google Apps script before pushing to the sheet

 

Could you please share the post about sending to a Google Form via webhook? I think that would be good for the community to access here from this post as an alternative

SanfordWhiteman
Level 10 - Community Moderator
Tara_Dacci
Level 4 - Champion Alumni

Thanks for posting this @Tyron_Pretorius !  The video walk through is great!

Tyron_Pretorius
Level 9 - Champion

Thanks Tara, I'm glad you found it helpful!