It sounds interesting. Are you have trouble around mapping JSON reponse to right columns in the Google Sheet?
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.
Interesting, indeed. Did you see Google's own documentation?
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!
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 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.
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.
This is awesome.
I added your repo to our list of example integrations:
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.
Sounds good. I'll reach out via email.
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.
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.
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.
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.
Kyle: posted under wrong account.
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.
Thanks for this! I just built it out and it works great. Is there a way to pull a few other fields?
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.
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?
I just tried this. At first, I ran into the same issues. Here are a couple of tips:
- After you add the Rest API endpiont url and Identity Url, make sure you have a /
- Do not update the bottom replace me area. Only do it at the top area.
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.