How to Import leads from spreadsheets with Pipedream

Pipedream > Workflows > New > Select Trigger > Google Sheets

Create new Pipedream workflow

Navigate to pipedream.com and click 'Create Workflow' in the top right. You'll land on a blank workflow canvas. Click 'Select Trigger' at the top of the workflow builder. Choose 'Google Sheets' from the app directory and select 'New Row Added' as your trigger event.

1. 1Click 'Create Workflow' button
2. 2Click 'Select Trigger' at the top
3. 3Search for 'Google Sheets' in the app list
4. 4Select 'New Row Added (Instant)' trigger

Google Sheets Step > Account > Connect Account

Connect Google Sheets account

Click 'Connect Account' and sign in with your Google account. Grant Pipedream permission to read your sheets. After connecting, you'll see a dropdown to select your spreadsheet. Choose the sheet where leads are added. Set the worksheet tab if your sheet has multiple tabs.

1. 1Click 'Connect Account' button
2. 2Sign in with Google credentials
3. 3Grant read permissions to Pipedream
4. 4Select your lead spreadsheet from dropdown
5. 5Choose the correct worksheet tab

Google Sheets Step > Test

Test Google Sheets trigger

Click 'Test' to verify the connection works. If your sheet has existing data, Pipedream will show the most recent row. If the sheet is empty, add a test row with sample lead data first. You should see all column headers as available fields for the next step.

1. 1Click 'Test' button in the trigger step
2. 2Review the sample data returned
3. 3Verify all expected columns appear
4. 4Note the field names for mapping

Workflow > + Add Step > Salesforce > Create Record

Add Salesforce step

Click the + button below your trigger to add a new step. Search for 'Salesforce' and select it. Choose 'Create Record' as your action. This will create new lead records in Salesforce when the workflow runs. You'll see Salesforce connection options appear in the step configuration.

1. 1Click the + button below Google Sheets step
2. 2Search for 'Salesforce' in the app directory
3. 3Select 'Salesforce' from results
4. 4Choose 'Create Record' action
5. 5Set Object Type to 'Lead'

Salesforce Step > Account > Connect Account

Connect Salesforce account

Click 'Connect Account' in the Salesforce step. Sign in with your Salesforce credentials through the OAuth popup. Grant Pipedream the necessary permissions to create and modify leads. After connecting, you'll see your Salesforce org name displayed in the account section.

1. 1Click 'Connect Account' button
2. 2Sign in through Salesforce OAuth popup
3. 3Grant required permissions
4. 4Verify connection shows your org name

Salesforce Step > Object Type > Field Mapping

Map required lead fields

Set Object Type to 'Lead' if not already selected. Map the required Salesforce fields using data from your Google Sheets trigger. LastName and Company are required - map these to the appropriate sheet columns. Click in each field and select the corresponding value from the Google Sheets step data.

1. 1Confirm Object Type is set to 'Lead'
2. 2Map LastName to your name column from sheets
3. 3Map Company to your company column
4. 4Add Email mapping if available
5. 5Set LeadSource to identify sheet imports

Salesforce Step > Add Field

Add optional field mappings

Map additional fields like FirstName, Email, Phone, Title, and Industry to provide complete lead data. Click 'Add Field' to map more properties. Reference the Google Sheets column names you noted earlier. Set Status to 'Open - Not Contacted' for new leads from sheets.

1. 1Click 'Add Field' to expand field options
2. 2Map FirstName to first name sheet column
3. 3Map Email and Phone if available
4. 4Set Status to 'Open - Not Contacted'
5. 5Add any custom fields your team uses

Workflow > + Add Step > Code (Node.js)

Add duplicate prevention step

Click + to add another step between Google Sheets and Salesforce. Add a 'Code' step to check for existing leads by email. This prevents duplicate leads when the same person appears multiple times. Paste the duplicate checking code that queries Salesforce first before creating new records.

1. 1Click + button above the Salesforce step
2. 2Select 'Code' from the step options
3. 3Choose 'Run Node.js code'
4. 4Paste duplicate prevention code
5. 5Update Salesforce step to use conditional logic

Workflow > Test Workflow

Test complete workflow

Click 'Test' on the entire workflow to verify each step works correctly. Add a new row to your Google Sheet with test data. Watch the workflow execute in real-time. Check your Salesforce leads to confirm the record was created with correct field mappings.

1. 1Click 'Test Workflow' button at the top
2. 2Add a test row to your Google Sheet
3. 3Watch each step execute in the workflow
4. 4Check Salesforce for the new lead record
5. 5Verify all mapped fields populated correctly

Workflow > Deploy

Deploy workflow

Click 'Deploy' to activate your workflow for live processing. The workflow will now automatically process new sheet rows in real-time. You'll see the deployment status change to 'Active' with a green indicator. New Google Sheets rows will trigger the workflow within 15-30 seconds.

1. 1Click 'Deploy' button in the top right
2. 2Confirm deployment in the popup
3. 3Wait for 'Active' status to appear
4. 4Test with a real lead entry

Add this Node.js code in a code step before your Salesforce action to prevent duplicate leads and normalize data formats. Paste this in the code editor that appears when you add a Code step.

import { axios } from "@pipedream/platform"
export default defineComponent({
  async run({ steps, $ }) {

import { axios } from "@pipedream/platform"

export default defineComponent({
  async run({ steps, $ }) {
    const { email, first_name, last_name, company } = steps.trigger.event
    
    // Normalize and validate data
    const normalizedEmail = email ? email.trim().toLowerCase() : null
    const cleanPhone = steps.trigger.event.phone ? steps.trigger.event.phone.replace(/[^\d+\-\(\)\s]/g, '') : null
    
    // Check for existing leads by email
    let existingLead = null
    if (normalizedEmail) {
      try {
        const searchResponse = await axios($, {
          url: `https://${steps.salesforce_auth.$auth.instance_url}/services/data/v58.0/query`,
          headers: {
            "Authorization": `Bearer ${steps.salesforce_auth.$auth.oauth_access_token}`,
            "Content-Type": "application/json"
          },
          params: {
            q: `SELECT Id, Email, Company FROM Lead WHERE Email = '${normalizedEmail}' LIMIT 1`
          }
        })
        existingLead = searchResponse.records[0]
      } catch (error) {
        console.log('Search error:', error.message)
      }
    }
    
    // Return processed data
    return {
      shouldCreate: !existingLead,
      existingLeadId: existingLead?.Id,
      processedData: {
        FirstName: first_name?.trim(),
        LastName: last_name?.trim() || 'Unknown',
        Email: normalizedEmail,
        Company: company?.trim(),
        Phone: cleanPhone,
        Title: steps.trigger.event.title?.trim(),
        LeadSource: 'Spreadsheet Import',
        Status: 'Open - Not Contacted'
      }
    }
  }
})