18 Replies Latest reply on Mar 27, 2018 9:55 PM by Bethanie Kollbaum

    Google Sheets Rest API Integration - Pull Static Lists

      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-minutes-a3fede1a014a
      http://www.bdna.com/blogs/pull-technopedia-data-google-sheets/

      I am currently workign on this any thoughts or help would be greatly appreciated!