Automate SharePoint to SFTP transfers with Azure Logic Apps: a step-by-step guide

In the first post in this series, we explored the whys and the hows of using Azure Logic Apps to connect to SFTP To Go and applying basic operations to recently uploaded files.

Teams often need a copy of SharePoint documents outside Microsoft 365, for partners, auditors, or long-term archives. Doing it by hand doesn’t scale, and a Logic App makes it automatic. In this guide, we’ll build a Logic App that connects Microsoft SharePoint and SFTP To Go and copies new files between them.

What we’re building

The Logic App will poll a SharePoint site for new files in specific file formats (Microsoft Office files and PDF documents). Whenever there’s a new file that matches the spec, the Logic App will get the content and transfer it to SFTP To Go for storage. Please note that you can skip the step where you create the filter and build a simpler workflow without conditions.

Prerequisites

• An Azure subscription and permissions to create a Logic App
• A service account (a Microsoft 365 work account) with read access to the relevant SharePoint site
• An active SFTP To Go account with permission to create folders and credentials
• Dedicated source and destination folders
• Representative test files

Step-by-step guide

The workflow in this Logic App has four building blocks: a trigger, a condition that checks files for a matching file extension, an action that reads the file content from SharePoint, and an action that saves that file content as a new file on SFTP To Go.

Step 1: Create a new Logic App 

Log in to the Microsoft Azure portal, search for the Logic Apps section, and create a new Logic App as described in the previous guide. Use these settings:

• Hosting option: Consumption - Multi-Tenant
• Resource group: If you have an existing group with an SFTP connection to SFTP To Go, use it

Once the new Logic App is created, click Go to resource and select Development tools - Logic app designer on the left.

Step 2: Set the trigger 

Let’s create a trigger that will watch SharePoint for new files and launch a sequence of actions. The most straightforward way to discover new files on SharePoint is to use a trigger that polls the document library.

Click Add trigger and select the When a file is created (properties only) trigger from the SharePoint connector.

Select the site address and library name in the trigger’s settings. If this is the first time you connect to your SharePoint account, you will need to authenticate first. For the library name, pick Documents. 

The trigger will default to polling the library every three minutes. You can adjust that: expand the

How often do you want to check for items? section of the Parameter page and then change the interval and the frequency.

Step 3: Set the file format condition (optional)

Your team can store any files in SharePoint, but that doesn’t mean you want all of them to be archived on the SFTP server. You can restrict transfers to Word, Excel, PowerPoint, and PDF files. To do that, we’ll create a condition.

If you don’t want to create this filter, skip this step entirely and keep adding new actions under the trigger. 

Click the + button below the trigger and select Add an action in the drop-down menu, then add the Condition action from the Control group. For convenience, click on the action name and use something descriptive like Is Office file.

In the condition settings, click the first (left) value box, open the expression editor by clicking the fx icon, paste the expression below, and click Add:

or(
  endsWith(toLower(coalesce(triggerBody()?['{FilenameWithExtension}'], '')), '.docx'),
  endsWith(toLower(coalesce(triggerBody()?['{FilenameWithExtension}'], '')), '.xlsx'),
  endsWith(toLower(coalesce(triggerBody()?['{FilenameWithExtension}'], '')), '.pptx'),
  endsWith(toLower(coalesce(triggerBody()?['{FilenameWithExtension}'], '')), '.pdf')
)

Here is a quick breakdown of the expression. It lowercases the file name and checks whether it ends with one of the four extensions. coalesce() falls back to an empty string if the name is missing, so the condition returns false instead of failing.

With the expression added, keep the = (equal to) operator intact, and in the value box on the right, type true. 

Step 4: Get file content

Everything that happens next happens in the True branch of the condition above. Click the + button below True and select Add an action, then insert the Get file content action from the SharePoint connector.

In the action’s properties panel, select the same SharePoint site as the site address. For File Identifier, click the fx button, switch to the Dynamic content page, select Identifier from the trigger, and click Add.

Step 5: Transfer the file to SFTP To Go

The final step is to copy the file to your SFTP To Go account. Click the + button below Get file content, select Add an action, and add the Create file action from the SFTP-SSH connector.

If you created this app in the same resource group where you created the first Logic App in the previous guide, you should be able to select the folder path immediately after that. 

If you started anew, you will need to create a new SFTP connection first. In that case, use the Credentials page in the SFTP To Go dashboard to locate the username and copy-paste all credential information: host server address, username, and password are the bare minimum to get the ball rolling.

Click the folder icon to select the folder to save files to (/archive), or type the path.

For the file name, open the expressions editor, switch to the Dynamic contentpage, select the File name with extension property from the trigger, and click Add.

For file content, open the expression editor, switch to the Dynamic content page, select File Content from the Get file content, and click Add.

Step 6: Test the workflow

Let’s test the workflow. Click Publish to save and activate the workflow you have just created.

Now, upload a file to your SharePoint account. Use a file that has one of the extensions you added in the Condition action.

After the next poll, you should see the file turn up in the SFTP folder:

Now, try using a file with some other extension such as .png. The file shouldn’t be copied, but the workflow will still run, and if you open it in the Run history section, you will see that the execution succeeded, bypassing the steps that involve getting file content and copying it to the SFTP server.

Basically, the Condition action looked at the file extension, found that it didn’t match one of the predefined ones, and skipped all other actions in the workflow.

Further refinements

There are several ways you can improve this workflow. Let’s quickly review the most useful ones.

Automatic conversion

For long-term archives, a PDF is more stable than an Office file because it renders the same regardless of software version, so you may want automatic conversion of Office files to PDF. 

To do that, you can use the Convert file action from the OneDrive for Business connector. You will need to create a temporary file on OneDrive, run the conversion, copy the resulting file to your SFTP folder, and clean up the temporary file. The workflow will also need to omit PDF files, so route only the Office files through the conversion branch, for example with a second condition that checks the extension isn’t .pdf.

Automatic routing

The example we’re using in this guide is rather simplistic. Whatever files match the spec (certain extensions), they end up in the archive folder on SFTP. But the files themselves can be anything: contracts, invoices, reports, budgets, slides, etc. You probably don’t want those all dumped into a single folder.

One thing you can do here is automatically route files based on their filename, the path, or the combination of the two. For purely filename-based routing, you can skip the Logic App step and do it with automations on the SFTP To Go’s side. However, if you need to process the folder name, use the Compose action to extract the Folder Path value from the trigger and reuse it when saving to SFTP.

Encryption

It’s fairly common to encrypt files before placing them in storage. Just like with automatic routing, you can do it in the Logic App or using an SFTP To Go automation. The former assumes either using a third-party connector or writing an Azure Function that uses Azure Key Vault to store a secret used in encryption.

It’s much easier to do that with a native automation on the SFTP To Go side. Just make the automation watch a folder for incoming files and apply PGP encryption. Unlike Azure, SFTP To Go doesn’t bill you on every encryption run, so you’re not paying extra.

Safeguards

The workflow in this guide is deliberately minimal. A few things can trip it up once it runs unattended. Here are some of the things you may want to consider when designing a production-grade Logic App.

• File modifications. The workflow picks up new files, but not modified files. A quick fix is to use the When a file is created or modified (properties only) trigger instead. However, that means that you’re now at risk of creating unwanted overwrites. You may want to use timestamps in file names when copying data to SFTP To Go. Please check out the previous guide where we discussed that at length.
• Least privilege. Give each connection only the access it needs. For SharePoint, create a service account not tied to a specific team member and grant it access only to a specific library with read-only permissions. Likewise, for SFTP To Go, create a dedicated user and set up virtual folders to restrict the user’s access to the target folder with limited permissions. For Azure, assign access to the Logic App through narrowly scoped roles rather than broad Contributor or Owner roles.
• Data retention. Run history stores the inputs and outputs of each action, which can include file names and sometimes file content. Use Secure Inputs and Secure Outputs on actions that handle sensitive data to hide it. If you have a data retention policy to conform to, set up lifecycle or cleanup rules for source and target storage accordingly.
• Issue notification. When a run fails, it just sits in the Run history section and awaits someone’s attention. For anything business-critical, add a notification step that runs after a failure. A typical solution is to send an email (Send an email (V2) from the Office 365 Outlook connector) or a Teams message (Post message in a chat or channel from the Microsoft Teams connector). Whichever you choose, set it to fire only on failure: click ... on the action, select Configure run after, untick is successful, and tick has failed. Otherwise, you’ll get a notification on every successful run. This only covers failed runs. To catch trigger failures, such as an expired connection, set up an Azure Monitor alert on the Logic App.

Wrapping up

You now have a Logic App that watches a SharePoint library, takes only the file types you care about, and saves them to SFTP To Go automatically.

The next decision is how far to take it. Routing and encrypting can each be handled either in the Logic App or by an SFTP To Go automation. It’s worth deciding early which service owns which step. 

Filename-based routing and PGP encryption are simpler to set up on the SFTP To Go side, while anything that depends on SharePoint metadata, such as the folder path, has to happen in the Logic App. 

Before you rely on the workflow in production, add the safeguards above, especially failure alerts and a service account. If you haven’t connected Azure Logic Apps to SFTP To Go before, the first guide in this series covers the basics.

If you don't have an SFTP To Go account yet, sign up for a trial and try the workflow with a few test files.

Frequently asked questions