How I Built a CSV to SugarCRM Lead Importer With Power Automate

I bumped into a fun scenario with one of my clients. They work in an almost criminally regulated industry and needed to work with a contractor to get leads from his work into their CRM.

Which seemed simple enough, just grab another license to the CRM and allocate it to them.

But in my line of work and life in general, things are never as simple as they should be. Given my client's line of work, they couldn't grant the contractor access to their CRM or any other resources. No SharePoint, no Google Drive, nothing like that. The only thing they were allowed to give him was an email address. So I was left with a single entry point for the data, and it couldn't be a plaintext email dumping every contact detail either. Instead I gave him a spreadsheet with specific columns to fill out, and built the rest around that.

Here's how the whole thing worked.

Five Systems, One Job Each

The workflow looks roughly like this: the contractor emails a CSV, a Power Automate cloud flow grabs the attachment and hands it off to a Power Automate Desktop flow, which parses the data, authenticates with SugarCRM, and posts each lead to /rest/v12/Leads.

It sounds like a lot laid out that way, but each piece has one specific job. The cloud flow watches Microsoft 365 and gets the data where it needs to go. Power Automate Desktop handles the part that has to happen from inside the client's network. Sugar handles the rest through its REST API.

• 01 The Contractor: fills out a spreadsheet and never so much as sees a Sugar login screen.
• 02 Email: the world's oldest API, and somehow still the right tool for the job.
• 03 Power Automate Cloud: watches the inbox, grabs the attachment, and knows better than to try doing everything itself.
• 04 Power Automate Desktop: the bot that shows up because the CRM only trusts machines already inside the building.
• 05 SugarCRM: takes the JSON, hands back a record ID, and never has to know a contractor was involved at all.

Why Power Automate Desktop Was Involved

In most cases you can run the whole automation through Power Automate Cloud, but this client's CRM required IP allowlisting, and that ruled it out. Power Automate Cloud HTTP requests don't leave Microsoft from one static IP tied to your flow. Microsoft maintains regional IP ranges and service tags for the Power Platform and its connectors, and technically you can allowlist those ranges, but for this client that wasn't realistic. Opening access to a pile of Microsoft cloud IP ranges just to make one REST call would have defeated the point of an extremely restrictive allowlist. What I actually needed was for the request to originate from a machine whose IP the CRM already trusted.

I've used an on-premise gateway to get around this kind of restriction before, but a normal cloud HTTP action can't be told to route its outbound REST call through a gateway. So the cloud flow was effectively kneecapped, which meant bringing in a bot. By bot, I mean Power Automate Desktop. This is also a good example of something I get into in RPA vs Automation vs AI vs AI Agents: I wasn't using RPA because clicking buttons is somehow better than an API call. I was using Power Automate Desktop because it gave my cloud automation a worker sitting in the right place on the network.

Controlling the Input Format Made Everything Else Easier

The contractor got a predefined spreadsheet instead of being allowed to send whatever format he wanted, and that decision made everything downstream simpler. A sanitized version of it looks something like this:

First Name,Last Name,Company,Email,Phone,Address,City,State,ZIP
John,Smith,Acme Services,john.smith@example.com,9545550101,123 Main St,Fort Lauderdale,FL,33301
Jane,Doe,Example Manufacturing,jane.doe@example.com,3055550137,456 Market Ave,Miami,FL,33101
Michael,Johnson,Demo Industries,michael.johnson@example.com,5615550199,789 Commerce Blvd,Boca Raton,FL,33432

There's nothing magical about the file, and that's the point. The contractor doesn't need CRM training, a CRM account, SharePoint access, or anything inside the company's network. He fills out the spreadsheet and emails it to a designated mailbox, and the cloud flow does the rest: find the CSV attachment, pull the content, hand it to the desktop flow. I used the cloud flow as an orchestration layer rather than trying to make it do every transformation itself, which is something I think people overcomplicate with Power Automate. Just because the cloud flow can do a bunch of transformations doesn't mean every transformation belongs there. I already needed Power Automate Desktop for the network restriction, so it made sense to let PAD handle more of the processing once the file reached it.

The Bridge Between Cloud and Desktop Is an Input Variable

One thing I had to figure out was the cleanest way to pass the prospect data from the cloud flow into the desktop flow. Power Automate Desktop supports input variables that get supplied when the desktop flow is called by a cloud flow, and that becomes the bridge: cloud flow reads the CSV, passes it in as a desktop flow input variable, PAD picks it up from there.

Once I created and published the input variable in the desktop flow, it became available to the "Run a flow built with Power Automate for desktop" action on the cloud side. That's one of those details that'll make you question your sanity for a few minutes. You create the variable in PAD, jump back to the cloud flow, and wonder why it isn't there. Publish the desktop flow, then go back and refresh the cloud action. Ask me how I know.

Once the data lands in Power Automate Desktop, I can loop through the prospect records and build what Sugar needs. Each record becomes a set of fields, first name, last name, company, email, phone, and the address pieces, and from there the flow reads each prospect, extracts the fields, builds the Sugar payload, posts the lead, and moves to the next one. This part looks obvious once it works but can be genuinely frustrating the first time you're figuring out how Power Automate passes variables between environments.

Sugar's OAuth Endpoint Wants a Specific JSON Body

Before creating a Lead, Sugar requires an access token from /rest/v12/oauth2/token. The request body looks roughly like this:

{
  "grant_type": "password",
  "client_id": "sugar",
  "client_secret": "",
  "username": "api-user",
  "password": "secure-password",
  "platform": "custom_api"
}

Those values should come from your own environment, obviously. Sugar's default client ID can be sugar, though admins can set up dedicated OAuth keys. The platform value is worth paying attention to since Sugar uses it as part of the login context, and a custom API platform is useful for keeping API auth separate from a user's normal Sugar session. And don't put production credentials directly into an article, screenshot, or repo where someone can find them later.

Postman Worked, PAD Didn't: The Payload Was the Problem

My original request came back with 422 - Invalid Parameters, which isn't exactly the world's most helpful error message. The useful part was that the exact same request already worked fine in Postman. If it works in Postman but not in your application, Sugar probably isn't your problem, your request is.

I compared the working Postman request against what PAD was actually sending. The auth endpoint expected the body as raw JSON, and the fix was getting PAD to send the same raw JSON payload I'd already proven worked in Postman, instead of letting it rebuild the request in its own format. Once PAD sent Sugar what Sugar actually expected, authentication worked.

That's one of my favorite API troubleshooting rules: if it works in Postman but not in your application, stop poking at the API and diff the two requests. Headers, body, encoding, content type, auth. Something in there is different.

Only the Access Token Actually Matters

A successful auth call returns a JSON response with an access token, expiry, token type, and a refresh token. I don't need most of that, just access_token. So after PAD gets the response, it parses the JSON, pulls access_token, and stores it for the rest of the run. That token becomes the credential for every Lead request that follows, which is also why I authenticate once up front instead of re-authenticating for every single prospect in the file.

Sugar Wants Its Own Field Names, Not the Spreadsheet's

This is where knowing your CRM actually matters. The spreadsheet says "First Name," Sugar's API wants first_name. Your human-readable spreadsheet and your API don't have to speak the same language, your automation is the translation layer between them:

If you've worked with Sugar for any length of time, you know custom fields are coming eventually, usually with names ending in _c, something like contractor_source_c or external_prospect_id_c. The display name a user sees in Sugar doesn't necessarily match the field name the REST API expects, so always confirm the actual field name before you build the payload.

The POST Is the Easy Part Once the JSON Is Right

Lead creation is a straightforward POST /rest/v12/Leads with the OAuth token in the header and the Lead as the JSON body:

{
  "first_name": "John",
  "last_name": "Smith",
  "account_name": "Acme Services",
  "phone_work": "9545550101",
  "primary_address_city": "Fort Lauderdale",
  "primary_address_state": "FL",
  "primary_address_postalcode": "33301",
  "email": [
    {
      "email_address": "john.smith@example.com",
      "primary_address": true
    }
  ]
}

Sugar creates the record and hands back the resulting Lead, including its record ID. At that point a row in an emailed spreadsheet officially became a real CRM Lead, without the contractor ever getting near the CRM.

Getting authenticated was only the first hurdle though. Sending something like this isn't enough:

FirstName=John
LastName=Smith
Company=Acme

Sugar expects JSON with Sugar's own field names. The same goes for anything more complicated than a flat string. Email specifically isn't just this:

{
  "email": "john.smith@example.com"
}

Sugar treats email as a collection, so it has to be built as an array with email_address and primary_address keys, the way it's shown above. Once the request body matched Sugar's expected structure, Lead creation worked and the hard part was over.

Get One Record Working Before You Loop

Once a single Lead can be created successfully, the rest of the workflow is just a loop: read the prospect, format the fields, build the JSON, post it, capture the response, move to the next one. I didn't start by trying to process an entire contractor file. I got one prospect working first, and only after that worried about looping through the rest. Trying to debug authentication, JSON formatting, CSV parsing, and looping all at once is a great way to lose an afternoon questioning your career choices. Make one work, then loop it.

Track Every Record, Not Just the Total

Bulk imports need real error handling. If prospect 37 fails, I don't want prospects 1 through 36 recreated just because someone reruns the process, and I definitely don't want the automation to quietly stop on row 37 while everyone assumes all 100 records went through. For every request I capture the HTTP status code, the Sugar response, the original CSV row, whether the Lead was actually created, the returned Sugar record ID, and the failure reason if there was one. That's the difference between knowing you got "Success: 147, Failed: 3, Total: 150" and just seeing "Flow completed," which are two very different definitions of the word completed.

Check for Duplicates Before You Create the Record

Any automation that creates CRM records eventually has to answer this. What happens if the contractor sends the same person twice? The safe version searches before creating, checking email, an external prospect ID, or some combination of first name, last name, and company. If Sugar already has the record, the flow can skip it, update it, or flag it for review depending on the business need. I generally prefer a stable external identifier when the source system can provide one, since names aren't unique, companies aren't unique, and even email addresses change. A source ID gives you something deterministic to key off of.

Why Email Was Actually the Right Interface

It's easy to think of email as the throwaway part of this build. It isn't. Email solved a specific security problem: the contractor needed a way to give us data without needing access to our systems, and those are not the same requirement. So instead of standing up another portal or licensing another system, email became the controlled entry point, and everything after the email was automated. Sometimes the best interface for an internal automation is the one your user already knows how to use.

What I'd Change in Version 2

The first version solves the business problem, but there's more I'd do here.

Reject bad records before they hit the API. Things like an empty last name, an invalid email, a state that isn't a real abbreviation, or a ZIP with the wrong number of digits shouldn't need an API call to catch. Validate before you ever touch Sugar.

Search before you create. Check Sugar for an existing match on email, phone, or company plus name before creating a new Lead, using whichever identifier is most reliable for the business.

A simple log beats another app. A lightweight audit trail with import ID, received date, contractor, total records, successes, failures, the resulting Sugar record IDs, and error messages is enough. The client doesn't need a whole second application to get that visibility.

Stop re-sending the password for every token. Sugar returns both an access token and a refresh token during authentication. A more mature version of this should use the refresh token instead of repeatedly sending the username and password to get a new access token.

This Was Never Really About the CSV

It's about recognizing when a small internal tool is enough. The company already had a contractor, email, a CSV, SugarCRM, Microsoft 365, Power Automate, and a machine sitting inside the trusted network. Nobody needed another platform, just something to connect the pieces that already existed. Nobody's going to buy a billboard for a CSV-to-SugarCRM importer or call it a digital transformation initiative. It just quietly does the job it was built for, and that's enough.

A small internal tool is worth building when the same process happens repeatedly, when people are copying data between systems by hand, when the input and output are both reasonably predictable, when the destination has an API, when security rules rule out the obvious integration, or when the manual process is just annoying enough that a full SaaS product would be overkill for it. If you can describe a workflow as take this, do this to it, put it here, it probably belongs in your toolbox.

The contractor's process stayed dead simple: fill out the spreadsheet, send the email. Everything else is mine to handle, and that's exactly how I want it.

Not every automation needs to become a major application. Sometimes the most valuable thing you can build is a small tool that removes one annoying step from someone's workday. This one takes a CSV from an outside contractor and turns the prospects into CRM Leads while respecting a security model that wouldn't let the contractor anywhere near the CRM itself. Nothing more, nothing less, and that's exactly why it works.