How to Export Pipeline to Sheets with Pipedream

Workflows > New > New Workflow

Create new workflow

Go to pipedream.com and click the purple 'New' button in the top right. Select 'New Workflow' from the dropdown menu. You'll see a blank workflow canvas with a 'Select a trigger' prompt in the center. Click on it to open the trigger selection panel.

1. 1Click 'New' in the top navigation bar
2. 2Select 'New Workflow' from the dropdown
3. 3Click 'Select a trigger' in the workflow canvas

Trigger > Schedule > Custom Interval

Set up schedule trigger

In the trigger panel, search for 'Schedule' and select the 'Schedule' app. Choose 'Custom Interval' as your trigger type. Set the interval to run daily at your preferred time (like 7:00 AM). This creates a cron-based trigger that will fire every morning to fetch fresh pipeline data.

1. 1Type 'Schedule' in the search box
2. 2Click on the 'Schedule' app
3. 3Select 'Custom Interval' trigger
4. 4Set schedule to '0 7 * * *' for 7 AM daily

Add Step > Salesforce > Execute SOQL Query

Add Salesforce step

Click the '+' button below your trigger to add a new step. Search for 'Salesforce' and select it from the app list. Choose 'Execute SOQL Query' as the action since you need to query for open opportunities. This action lets you write custom SOQL to get exactly the fields you need.

1. 1Click the '+' button below the trigger
2. 2Search for 'Salesforce' in the app list
3. 3Select 'Salesforce' app
4. 4Choose 'Execute SOQL Query' action

Salesforce Step > Connect Account

Connect Salesforce account

In the Salesforce step configuration, click 'Connect Account' to authenticate with your Salesforce org. You'll be redirected to Salesforce login. Enter your credentials and authorize Pipedream to access your data. The connection needs read permissions for Opportunity, Account, and User objects to pull complete pipeline data.

1. 1Click 'Connect Account' in the step config
2. 2Log in to your Salesforce org
3. 3Click 'Allow' to authorize Pipedream
4. 4Return to Pipedream workflow

Salesforce Step > SOQL Query

Write SOQL query

In the SOQL Query field, enter a query to fetch open opportunities with the fields you need in your sheet. Include Opportunity name, stage, amount, close date, account name, and owner. The query should filter for open opportunities using 'WHERE IsClosed = false' to exclude won/lost deals.

1. 1Click in the SOQL Query text area
2. 2Enter the SOQL query for open opportunities
3. 3Include relevant fields like Name, StageName, Amount
4. 4Add WHERE clause to filter for open deals

Salesforce Step > Test

Test Salesforce query

Click 'Test' at the bottom of the Salesforce step to run your SOQL query and see the returned data. You'll see a JSON response with your opportunity records. Review the field names and data structure since you'll need these exact field names when mapping to Google Sheets. Make note of any null values or formatting issues.

1. 1Click the 'Test' button at bottom of step
2. 2Wait for the query to execute
3. 3Review the returned opportunity data
4. 4Note the field names in the JSON response

Add Step > Google Sheets > Add Multiple Rows

Add Google Sheets step

Click '+' below the Salesforce step to add Google Sheets. Search for 'Google Sheets' and select it. Choose 'Add Multiple Rows' as the action since you're writing multiple opportunity records. This action handles batch inserts efficiently rather than creating individual rows one by one.

1. 1Click '+' below the Salesforce step
2. 2Search for 'Google Sheets'
3. 3Select 'Google Sheets' app
4. 4Choose 'Add Multiple Rows' action

Google Sheets Step > Connect Account

Connect Google Sheets

Click 'Connect Account' in the Google Sheets step to authenticate with your Google account. You'll go through Google's OAuth flow to grant Pipedream access to your Google Sheets. Make sure you're logged into the Google account that owns or has edit access to your target spreadsheet.

1. 1Click 'Connect Account' in the step
2. 2Sign in to your Google account
3. 3Grant Pipedream access to Google Sheets
4. 4Return to the workflow

Google Sheets Step > Spreadsheet > Worksheet

Select spreadsheet and worksheet

In the Spreadsheet field, choose your target Google Sheet from the dropdown list. If you don't see it, make sure it's accessible to your connected Google account. Then select the specific worksheet tab where you want the opportunity data written. The action will append new rows to this sheet each time it runs.

1. 1Click the Spreadsheet dropdown
2. 2Select your target Google Sheet
3. 3Choose the worksheet tab from the dropdown
4. 4Verify the correct sheet is selected

Google Sheets Step > Rows > Add Row Mapping

Map opportunity fields

In the Rows section, you'll map Salesforce opportunity fields to your Google Sheet columns. Click 'Add Row Mapping' and use the reference picker to select fields from your Salesforce query results. Map key fields like opportunity name, stage, amount, close date, and account name to corresponding sheet columns.

1. 1Click 'Add Row Mapping' button
2. 2Use the reference picker to select Salesforce fields
3. 3Map Name, StageName, Amount, CloseDate
4. 4Add mappings for Account.Name and Owner.Name

Workflow > Test

Test complete workflow

Click 'Test' at the top of the workflow to run the entire pipeline end-to-end. This will execute your SOQL query, fetch the opportunity data, and write it to your Google Sheet. Check your Google Sheet to verify the data appears correctly formatted. Look for any missing values or formatting issues that need adjustment.

1. 1Click the 'Test' button at workflow level
2. 2Wait for all steps to complete execution
3. 3Check your Google Sheet for new data
4. 4Verify field mappings worked correctly

This code step goes between Salesforce and Google Sheets to clean up the data format and add calculated fields. Paste it as a Node.js code step after your SOQL query.

export default defineComponent({
  async run({ steps, $ }) {
    const opportunities = steps.salesforce.$return_value.records;

export default defineComponent({
  async run({ steps, $ }) {
    const opportunities = steps.salesforce.$return_value.records;
    
    // Clean and format the opportunity data
    const formattedOpps = opportunities.map(opp => {
      // Format currency to 2 decimal places
      const amount = opp.Amount ? Math.round(opp.Amount * 100) / 100 : 0;
      
      // Convert Salesforce date to readable format
      const closeDate = opp.CloseDate ? 
        new Date(opp.CloseDate).toLocaleDateString('en-US') : '';
      
      // Calculate days to close
      const daysToClose = opp.CloseDate ? 
        Math.ceil((new Date(opp.CloseDate) - new Date()) / (1000 * 60 * 60 * 24)) : null;
      
      // Add stage emoji for visual scanning
      const stageEmoji = {
        'Prospecting': '🔍',
        'Qualification': '❓', 
        'Needs Analysis': '📋',
        'Value Proposition': '💡',
        'Proposal/Price Quote': '📄',
        'Negotiation/Review': '🤝',
        'Closed Won': '✅',
        'Closed Lost': '❌'
      };
      
      return {
        name: opp.Name || '',
        stage: `${stageEmoji[opp.StageName] || ''} ${opp.StageName || ''}`,
        amount: `$${amount.toLocaleString()}`,
        closeDate: closeDate,
        daysToClose: daysToClose,
        accountName: opp.Account?.Name || '',
        ownerName: opp.Owner?.Name || '',
        probability: opp.Probability ? `${opp.Probability}%` : '',
        nextStep: opp.NextStep || 'Not specified'
      };
    });
    
    // Sort by close date (earliest first)
    formattedOpps.sort((a, b) => new Date(a.closeDate) - new Date(b.closeDate));
    
    return formattedOpps;
  }
});