Collecting Marketo Form Field Data - Google sheets?

[Kim_Wieczner]

We are hoping to capture form data (ideally by feeding into a Google sheet), allowing us to collect all of the form fills in one place and avoid the fields getting overwritten by multiple form fills. We have looked into using a TIBCO form integration, but that doesn't allow us to create form-specific triggers or iframe the form onto our app.

Does anyone have any tips/tricks/insights on the best way to accomplish this? Open to using 3rd party services.

Goals:

-collect all form fills in spreadsheet

-track & set triggers off of form fills

-iframe capability

[Asif10230]

Hi @SanfordWhiteman ,

I'm using one of your methods to capture the form data into Google Sheets using a Webhook.

Since the beginning, we have seen the problem Error: 401: Unauthorised access to the URL.

I was hoping you could guide me on this one.

Attaching screenshots for your reference.

[Darshil_Shah1]

It appears that Kim was also running into a similar error earlier (see pervious comments in this thread)! Did you make sure to allow public access to the Google form?

[Asif10230]

Hey @Darshil_Shah1 ,

I have already made the Google form public.
Now, the issue is that Webhook is sending only my email address to the Google Sheets.
If I try to register from another email, it's showing an Error: 404 Bad Request URL

From my email:

From different email:

Can you comment on this?

[SanfordWhiteman]

P.S. The instructions will all be up on my blog tomorrow but for now enjoy this (I hope) tantalizing video: MktoForm to Sheet​

[SanfordWhiteman]

Kim Wieczner​ now on my blog at http://blog.teknkl.com/logging-marketo-form-fills-to-a-google-sheet/

[Kim_Wieczner]

Thank you Sanford! This is so helpful. We ran across one snag - we're getting a 401 error as follows:

Error Type:

HTTP Error 401: Unauthorized access to URL.

Error String:

Server Returned code 401

Do you know what might cause an error like this? And if so, how to fix it?

Thanks again for all of your help!

[SanfordWhiteman]

401 from where?

[Kim_Wieczner]

The 401 error on the Google sheet URL webhook - is that what you're asking?

[SanfordWhiteman]

Did you make sure to allow public access to the form? (As opposed to GSuite users only.)

[Kim_Wieczner]

Thank you - unfortunately with our Corporate Google account, we can't make these accessibility changes, so we are pursuing other avenues for the moment. Thank you for all of your help! So appreciated.

[SanfordWhiteman]

Also: while Marketo webhooks (by definition) don't support OAuth flow, you can use an in-between gateway to auth to Google and add to a secure Sheet (technically in this case you're skipping the Form, although you still provision a Form-linked Sheet as in the original instructions).

FlowBoost Pro can do the OAuth, but admittedly faster for this narrow purpose is using Zapier. This Zap definition updates a secure Form/Sheet:

Then you point the Marketo webhook to the Zapier endpoint instead of directly to the Google endpoint.

In this case you don't even need to use any mapping on the JS side, since Zapier is doing the mapping. But rather than changing the code, just use the Marketo name on both sides of the map, like:

mktoSheetsTranslateMap = {
   'Email': 'Email',
   'FirstName': 'FirstName',
   'LastName': 'LastName'
}

[SanfordWhiteman]

You can't make a single form publicly accessible -- which, by the way, doesn't mean the Sheet is publicly accessible, it just means anyone can add a form fillout -- even though your Marketo form is publicly accessible? That doesn't make a lot of sense to me.

Of course the whole thing can be done with an OAuth-enticated session as well, but that's a lot more work for exactly the same amount of real security (if every post goes promptly to the Sheet without any filtering, doesn't matter that you logged into Google first).

You could of course create a separate GSuite account as well.

[SanfordWhiteman]

Kim, as I mentioned above, the Marketo API offers a feed of all form fillouts. With a little help from a dev, you can download this daily into a spreadsheet.

Another -- perhaps even more elegant (and code-free) -- way of doing this is to use a webhook to simply cross-post every form fill over to GSheets.  This is extremely easy as every GForm is a webhook-compatible endpoint that can immediately update a sheet.  Here's an example showing a few Marketo form posts mirrored to a Sheet in succession:

The sole, tiny wrinkle is you can't simply cross-post all the ​current values ​of {{lead.tokens}} to the Sheet, since the current values will include just-posted values and earlier values. (Think about what happens when a field is blocked from updating via form fillout -- it still has a value for the {{lead.token}}, but that value didn't ​come from ​the recent form post.)  So the way we work around this is by using a separate textarea field (just one field) to store only the data that came in from the last form post.  This hidden field is populated on the browser side when the form submits.  Then you pass that field value over to Sheets.

[Veronica_Holme4]

You're right, Marketo forms aren't really designed that way. They are designed to overwrite with the most recent data filled in.

Have you considered using a survey tool instead? They're designed for multiple form fills of the same questions, and if you used something like the SurveyMonkey-Marketo integration for instance, you create Custom Activities to log the form fillouts and can use that to trigger actions rather than the form fillout.

[SanfordWhiteman]

You're right, Marketo forms aren't really designed that way. They are designed to overwrite with the most recent data filled in.

On the UI level, this is true... but actually the Activity API endpoint provides an append-only (non-overwriting) feed of all Filled Out Form activities, including the actual HTTP POSTed data for each record.

While I would usually not leap to an API solution, this one is about as easy as it gets. And even if you need to hire a developer for a few hours, it's infinitely easier on your resources than a huge investment like SurveyMonkey (whose use of Custom Activities also creates a DoS vulnerability).

[SanfordWhiteman]

Please move the thread to Products and someone will answer it there. "Community" is for website feedback, not product support -- and yes, this is confusing!

[cthomas]

Hi Sanford,

Old post but hoping to get help in posting marketo form fills to google sheet for multi touch attribution. Got to the "THE FORMS 2.0 JS SIDE" section and got lost. I created the code but not sure where to put it on the form. 

Appreciate your help!