Google Sheets Rest API Integration - Pull Static Lists

[Anonymous]

I am new to Marketo so I do not know if this has been done yet.

I am trying to take a static list say registrations for an event and pull the leads into a Google Spreadsheet.
The sheet needs to be able to dynamically refresh for anyone that it is shared with so that they always have an up to date list of registrations.

Using Postman REST Client I am able to get this to work on the Marketo REST API end.
http://developers.marketo.com/documentation/rest/

Use "Get Multiple Lists" to determine the id of the list that you want to pull.
Then use "Get Multiple Leads by List Id" to pull the leads from that list back
These results come back in a JSON fomat

There is a script for Google sheets to allow you to import JSON into Google Sheets via a function based upon specified parametersin the function.

ImportJSON()     ImportJSONViaPost()     ImportJSONAdvanced()
http://blog.fastfedora.com/projects/import-json
https://github.com/fastfedora/google-docs
 
Thus theoretically you could specify the name/id of the static list as a variable in the ImportJSON function in Google Sheets and then you would have a live updating list of leads by passing that to the request.
 
If you wanted to get fancy you could use the "Get Multiple Leads by List Id" to get the lead ids and then use "Get Multiple Leads by Filter Type" to get a list of leads with further details about each lead.
 
My coding limitation is that I do not know how to process the returned JSON since it is not a hosted file but a returned result.
Also, I am unsure of how to cycle the requests to continue until you reach the last page of results.
 
I am not good enough at Java Script to be able to make the final connection but I know it is close.
 
Here are two blog posts which illustrate what I mean.
https://medium.com/@paulgambill/how-to-import-json-data-into-google-spreadsheets-in-less-than-5-minu...
http://www.bdna.com/blogs/pull-technopedia-data-google-sheets/

I am currently workign on this any thoughts or help would be greatly appreciated!

[Anonymous]

Kyle, thanks for the gret work. I am able to run the script in Google sheets but I get an error message saying "You have not configured your rest API credentials" I am guessing this is from the isConfigured() function validation that you do on line 112 of the code. I have checked the API credentials and they look fine. Moreover, I am able to retrieve data via the REST client on postman as well as via the browser. So the credentials look fine. Only difference is that I am providing the token in postman while you pull it dynamically and cache it. Any specific areas that you think I should look at to troubleshoot this?
 

[Josh_Perry1]

I just tried this. At first, I ran into the same issues. Here are a couple of tips:

1. After you add the Rest API endpiont url and Identity Url, make sure you have a /
2. Do not update the bottom replace me area. Only do it at the top area.

[Anonymous]

Were you able to pull any other fields other than the 3 originally outlined. I have been trying to figure that out, wanted to see if you have tried/had any luck.

Thanks!

[SanfordWhiteman]

Isn't it easier to just push the registrations to a Sheet in real-time?  You can also leave the Sheet read-only for users this way, as they don't need to update it themselves.

[Brice_Dunwoodie]

Hi Kyle,

Thanks for confirming that. Though, I have to say that it is a bit of a shame that our holy grail is effectively a SQL query that Larry Ellison wrote back in 1979 :/.

Thanks again for your work here. It's a valuable contribution.

-Brice

[Anonymous]

Hi Brice,

Unfortunately, you are absolutely correct; none of the Marketo API's permit fetching Smart Lists, only Static. Although it is brittle, as you say, copying a Smart List to a Static List on a schedule does seem like the simplest way to achieve this functionality.

My Spreadsheet integration is still pretty immature, but will eventually permit you to keep a Static List synchronized with Marketo. Finishing this is more of a UI hurdle for me than anything, at this point; I could just have the open sheets update themselves when the document is opened, but that is a great way to run out of API quota, so I think I'm going to require a manual refresh through the UI.

Getting an up-to-date copy of a Smart List outside of Marketo is a sort of community grail, and as soon as they permit API access, I will be working on building a web interface for pulling and sharing them.

Hope you can get this to work for your needs; please let me know if you have any feedback for the Spreadsheet component, as it is under active development.

Best,
Kyle

[Anonymous]

Thanks for this! I just built it out and it works great. Is there a way to pull a few other fields?

[Anonymous]

Kyle: posted under wrong account.

[Brice_Dunwoodie]

Hi all,

Nice and interesting work. 

I have a question that is slightly out of place, but this thread has been pointed to as a possible work around for Marketo's lack of a "subscribe to smartlist" feature, which keeps getting kicked down the road (currently to 2015) by Marketo product management.

See here:
https://community.marketo.com/MarketoIdeaDetail?id=08750000000HAX9AAO

I've looked at what you've done with the Google Script and Marketo's REST API and at the APIs, and there is no way to query a smartlist as far as I know. So the only way this solution could possibly work is if you created campaigns that populated static lists from smartlists on a nightly basis, then used this scripting to query the static lists and update the Google Spreadsheet from there.

Does that sound right? Am I missing anything?

Thanks for your thoughts.

-Brice

[Anonymous]

Brennan,

Glad you found it useful! Just a note to you and anyone else using this: it is about a week old, and changing rapidly. If you end up playing with it, don't forget to check the repo for updates.

For instance, the insert function will soon be much faster, configuration will eventually have a GUI, and existing sheets will eventually have the option to be updated.

[Brennan_McAdam2]

Hi Kyle,

I found your code yesterday and it was exactly what I needed.  Still very experimental and with some modifications I now can pull the list directly into a google spreadsheet for a specific list I have defined as well as grab other fields (hard coded at the moment). 

Anyway, I wanted to say great work.

[Anonymous]

Sounds good. I'll reach out via email.

[Anonymous]

Sure thing! You can contact me outside these forums at kyle@elixiter.com.

It is certainly in the exploratory phase, but that is the best stuff to blog about.

[Anonymous]

This is awesome. 

I added your repo to our list of example integrations:
https://github.com/Marketo/Community-Supported-Client-Libraries/blob/master/README.md

Would you interested in doing a guest blog post on Marketo developers blog about this? Basically, a short post with you have written up in this thread, with more screenshots and more details on how to use it. 

[Anonymous]

Hi all,

I got a little time to mess with this last night, and here's what I came up with. I tried to make ImportJSON work, and eventually got a little more ambitious: I decided that it is simple enough to write my own functions, and not rely on ImportJSON. Beware... this is a work-in-progress, and does not do exactly what you requested.



I wrote some Apps Script (practically identical to Javascript) to create a sidebar that populates with names of lists pulled from the REST API. It also has "Insert" buttons that currently append the first 100 entries of the specified list (the first API call of a paginated result set) to the end of the active spreadsheet. I have posted this code in a public GitHub repository under the MIT license, so feel free to grab it here: https://github.com/elixiter/mkto_google-spreadsheet

I know you are looking for some sort of real-time synchronization, and that is totally possible... this is just a simple proof-of-concept. You can certainly fork the project and improve it yourself, or Watch the repo for updates.

If you intend to try this code yourself, be sure to read the configuration section of the readme, also embedded in the code as a comment.

Some caveats:
1. Neither the code nor the interface is polished in any sense.
2. It currently only pulls 4 fields: id, email, firstName, lastName
3. If you (or I) end up adapting this code to pull more than 100 records at a time, we may quickly hit the 20s API limit of 100 calls (that would definitely happen with a list of 10k+ leads).
4. This must be run by a priveleged user. The user would be able to see your REST API secret key. You really might as well just give these users Marketo logins and send them links to the lists, and not murder your API quota.

Happy fiddling!
Kyle

[Anonymous]

Interesting, indeed. Did you see Google's own documentation?

https://developers.google.com/apps-script/guides/services/external

Unfortunately, I have never scripted Google Apps, but it looks pretty straightforward to write your own function to call the API and insert the data into the sheet, and doing so has the added benefit of letting you create a menu with a button to call the API, giving you more control over when calls are made (REST limits are 100/20seconds and 10,000/day).

I don't know if you wanted to get that deep into it, but I will hack on this when I get the chance, and post what I find!

[Anonymous]

That is what the ImportJSON function does so that is not the issue.
It is how to take response data and use that in conjunction with ImportJSOn as it is designed to use with a hosted JSON file.
The other option is to use the ImportJSONviaPost function, but I am not sure how to do that with the Oauth authentication.

[Anonymous]

It sounds interesting. Are you have trouble around mapping JSON reponse to right columns in the Google Sheet?