Google Sheets Rest API Integration - Pull Static Lists

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

Kyle: posted under wrong account.

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

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

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.

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

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