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

Show previous replies
A
Anonymous
August 27, 2014
Kyle: posted under wrong account.
A
Anonymous
August 27, 2014
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
A
Anonymous
March 28, 2018

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

BDx
BDx
Level 4
August 27, 2014
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
A
Anonymous
October 22, 2014

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
Josh_Perry1
Level 5
March 1, 2018

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.
SanfordWhiteman
SanfordWhiteman
Level 10
March 1, 2018

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.

Terms & ConditionsAccessibility statement

Loading footer...