How to Export HubSpot Contacts to Sheets with Pipedream

Pipedream Dashboard > New > Workflow

Create New Workflow

Log into pipedream.com and click the blue 'New' button in the top navigation. Select 'Workflow' from the dropdown. You'll land on the workflow builder with an empty trigger step. Name your workflow something descriptive like 'HubSpot Contacts Export'.

1. 1Click 'New' in the top navigation bar
2. 2Select 'Workflow' from the dropdown menu
3. 3Click the pencil icon to rename your workflow
4. 4Type 'HubSpot Contacts Export' as the name

Workflow Builder > Trigger Step > Schedule

Set Up Schedule Trigger

Click on the trigger step and select 'Schedule' from the built-in apps section. Choose 'Cron' for the event type. Set your cron expression — use '0 9 * * *' for daily at 9 AM UTC. Click 'Create Source' to activate the trigger.

1. 1Click on the trigger step box
2. 2Search for 'Schedule' in the app selector
3. 3Select 'Cron' as the event type
4. 4Enter '0 9 * * *' in the cron expression field
5. 5Click 'Create Source'

Workflow Builder > Add Step > HubSpot

Add HubSpot Connection

Click the '+' button below your trigger to add a new step. Search for 'HubSpot' and select it. Choose the 'Get All Contacts' action from the list. Click 'Connect Account' and authorize Pipedream to access your HubSpot data using OAuth.

1. 1Click the blue '+' button below the trigger
2. 2Search for 'HubSpot' in the app list
3. 3Select 'Get All Contacts' action
4. 4Click 'Connect Account'
5. 5Complete OAuth authorization in the popup

HubSpot Step > Configuration

Configure Contact Filters

In the HubSpot step configuration, set up your filtering criteria. Use the 'Properties' field to specify which contact properties you want to retrieve. Add filters like 'lastmodifieddate' is after yesterday to get recent contacts only. Set a reasonable limit like 100 contacts per run to avoid timeouts.

1. 1Scroll to the 'Properties' field
2. 2Add properties: firstname,lastname,email,company,phone,lifecyclestage
3. 3Click 'Add Filter' under Filters section
4. 4Set 'lastmodifieddate' > 'yesterday'
5. 5Set Limit to 100

HubSpot Step > Test

Test HubSpot Connection

Click the 'Test' button at the bottom of the HubSpot step to run a test query. Pipedream will fetch contacts matching your criteria and display the results in the step output. Review the data structure to understand how contact properties are formatted.

1. 1Click the 'Test' button
2. 2Wait for the test to complete
3. 3Click 'View Test Event Data'
4. 4Expand the results to see contact objects

Workflow Builder > Add Step > Google Sheets

Add Google Sheets Connection

Add another step below HubSpot and select Google Sheets. Choose the 'Add Multiple Rows' action since you'll be writing multiple contacts at once. Connect your Google account through OAuth authorization. This action is more efficient than adding rows one by one.

1. 1Click '+' below the HubSpot step
2. 2Search for and select 'Google Sheets'
3. 3Choose 'Add Multiple Rows' action
4. 4Click 'Connect Account'
5. 5Complete Google OAuth authorization

Google Sheets Step > Configuration

Select Target Spreadsheet

In the Google Sheets configuration, select your target spreadsheet from the dropdown. If you don't have one yet, create a new Google Sheet with headers like 'First Name', 'Last Name', 'Email', 'Company', 'Phone', 'Lifecycle Stage'. Choose the correct worksheet name — usually 'Sheet1' by default.

1. 1Click the 'Spreadsheet' dropdown
2. 2Select your target spreadsheet
3. 3Choose the worksheet (usually 'Sheet1')
4. 4Verify the spreadsheet has proper column headers

Workflow Builder > Add Step > Code

Add Data Transformation Code

Before mapping to sheets, add a code step to transform HubSpot contact data into the format Google Sheets expects. Click '+' between HubSpot and Google Sheets steps, then select 'Code'. This step will convert the contact objects into an array of row arrays that Google Sheets can consume.

1. 1Click '+' between HubSpot and Google Sheets
2. 2Select 'Run Node.js Code'
3. 3Delete the default code in the editor
4. 4Paste the transformation code

This Node.js code transforms HubSpot contact objects into Google Sheets row format and handles null values gracefully. Paste it into the code step between HubSpot and Google Sheets.

export default defineComponent({
  async run({ steps, $ }) {
    const contacts = steps.hubspot.contacts || [];

export default defineComponent({
  async run({ steps, $ }) {
    const contacts = steps.hubspot.contacts || [];
    
    // Transform contacts into rows for Google Sheets
    const transformedRows = contacts.map(contact => {
      const props = contact.properties;
      
      return [
        props.firstname?.value || '',
        props.lastname?.value || '',
        props.email?.value || '',
        props.company?.value || '',
        props.phone?.value || '',
        // Convert lifecycle stage to readable format
        props.lifecyclestage?.value?.replace(/([A-Z])/g, ' $1').trim() || '',
        // Format date nicely
        props.lastmodifieddate?.value ? 
          new Date(props.lastmodifieddate.value).toLocaleDateString() : '',
        props.hs_analytics_source?.value || ''
      ];
    });
    
    // Filter out contacts with no email (invalid records)
    const validRows = transformedRows.filter(row => row[2] !== '');
    
    console.log(`Processed ${contacts.length} contacts, ${validRows.length} valid`);
    
    return { transformedRows: validRows };
  }
});

Google Sheets Step > Rows Configuration

Configure Sheet Row Mapping

In the Google Sheets step, set the 'Rows' field to reference your code step output. Use the expression editor to select the transformed data array from your code step. This tells Google Sheets exactly which data to write and in what format.

1. 1Click in the 'Rows' input field
2. 2Click 'Use expression' toggle
3. 3Select your code step from the dropdown
4. 4Reference the 'transformedRows' property
5. 5Click 'Save' to confirm the mapping

Workflow Builder > Test

Test Complete Workflow

Run a full test of your workflow by clicking 'Test' at the top right of the workflow builder. This will execute all steps in sequence — schedule trigger fires, HubSpot fetches contacts, code transforms data, and Google Sheets adds rows. Check your spreadsheet to verify the data appears correctly.

1. 1Click 'Test' button at top right
2. 2Wait for all steps to complete
3. 3Check each step for green success indicators
4. 4Open your Google Sheet to verify data
5. 5Click 'Deploy' to activate the workflow