Export Marketo Static List Data to Google Sheet

[Mkhanna0510]

Hi,

I am trying to pull Marketo static list data to Google Sheets with the help of Google App Script. However, I have observed only 300 records are getting pulled from Marketo. Can someone help me to find a solution for this issue? Sharing the code for the reference.

function updateData() {
   const ACCESS_TOKEN = "";
   const API_URL = "";
   const LIST_ID = "1873"; // Replace with your actual List ID
   const PAGE_SIZE = 200; // Number of records per page
   let currentPage = 1;
   let allLeads = [];
   let leads = []; // Initialize leads as an empty array

   do {
       const DATA_ENDPOINT = `/rest/v1/list/${LIST_ID}/leads.json?maxReturn=${PAGE_SIZE}&page=${currentPage}`;
       
       const response = UrlFetchApp.fetch(API_URL + DATA_ENDPOINT, {
           headers: {
               "Authorization": "Bearer " + ACCESS_TOKEN
           }
       });

       const content = JSON.parse(response.getContentText());
       leads = content.result || []; // Use an empty array if content.result is undefined

       allLeads = allLeads.concat(leads);

       currentPage++;
   } while (leads.length === PAGE_SIZE);

   const HEADERS = ['Lead ID', 'First Name', 'Last Name', 'Email'];
   const spreadsheet = SpreadsheetApp.getActive();
   const sheet = spreadsheet.getSheetByName('Sheet1');
   sheet.clear();
   sheet.appendRow(HEADERS);

   allLeads.forEach(lead => {
       const leadId = lead.id;
       const firstName = lead.firstName;
       const lastName = lead.lastName;
       const email = lead.email;

       sheet.appendRow([leadId, firstName, lastName, email]);
   });
}

**The Access token and API URL has been removed but the code is working fine with them for 300 records pull.

[SanfordWhiteman · Accepted solution]

I’d venture that you’re going in the wrong direction here.

The paginated Bulk Export API isn’t the right tool. If you want to use it correctly from Apps Script, you’d need to (a) correctly manage access_token expiration — which you’re not doing now — and (b) get 300 records at a time, recursively, using the nextPageToken. Apps Script is not good at such large tasks and you’ll eventually hit timeouts even when you’re using the tightest possible code.

You should instead use the Bulk Extract API and import the resulting CSV into the Sheet when it’s ready.

Alternately, you could simply keep the Sheet updated using a Marketo webhook, although that’s not suitable if you regularly add thousands of Marketo records at once. If you only add people on a triggered basis but currently have a large list, import the list into the Sheet manually once to “rebase” it, then use the triggered webhook for later updates.

[Mkhanna0510]

Thank you @SanfordWhiteman for sharing the information. Can you share a guide for step-wise instructions to pull Marketo static list data on a button click in Google Sheets? I am not able to find the right information for this. Also, some sample code might help for me to get going.

[SanfordWhiteman]

What have you tried? The API docs are quite good, showing the Create-Enqueue-Poll-Download process.