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]

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

[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]

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]

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]

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]

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]

Sounds good. I'll reach out via email.

[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]

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.