Google Sheets Rest API Integration - Pull Static Lists | Community
Skip to main content
A
Anonymous
August 14, 2014
Question

Google Sheets Rest API Integration - Pull Static Lists

  • August 14, 2014
  • 14 replies
  • 5963 views
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.
medium.com/@paulgambill/how-to-import-json-data-into-google-spreadsheets-in-less-than-5-minutes-a3fede1a014a
bdna.com/blogs/pull-technopedia-data-google-sheets/

I am currently workign on this any thoughts or help would be greatly appreciated!
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

14 replies

A
Anonymous
August 15, 2014
It sounds interesting. Are you have trouble around mapping JSON reponse to right columns in the Google Sheet?
A
Anonymous
August 18, 2014
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.
A
Anonymous
August 18, 2014
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!
A
Anonymous
August 20, 2014
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
    A
    Anonymous
    August 20, 2014
    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. 
    A
    Anonymous
    August 20, 2014
    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.
    A
    Anonymous
    August 20, 2014
    Sounds good. I'll reach out via email.
    Brennan_McAdam2
    Brennan_McAdam2
    Level 4
    August 24, 2014
    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.
    Brennan McAdams
      A
      Anonymous
      August 25, 2014
      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.
      BDx
      BDx
      Level 4
      August 27, 2014
      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
      Show more replies
      Terms & ConditionsAccessibility statement

      Loading footer...