Export Marketo Static List Data to Google Sheet

Question

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 Answer

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.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded