How to unhide and unhide certain column based on cell value

Hi ItsBhatti 

Many thanks for your reply, there are something worth mentioning:

1. I have reviewed the solution you have mentioned in the discussion, and this solution is for onprem PowerShell usage, not for Azure Functions. Please note that Azure Functions don't allow popup window for authentication, but executing: Connect-AzAccount will bring up the popups, and you will end up getting the error message.

2. We will have a PowerShell option when you are trying to create a new Azure Function App. Simply use this and you don't need to install other things. This may require if you are running PowerShell in a VM, but not on Azure Functions.

3. The post doesn't cover how to setup the SFTP itself, we would assume you have already setup the SFTP server. Please have the research if you haven't prepared the SFTP server, and there are lots of articles talking about how to setup the SFTP server. The #2 step mentioned WinSCP is also an option to establish the SFTP server.

Hope the info above could be helpful, thank you.

To connect an SFTP (Secure File Transfer Protocol) server and a Storage Account using Azure PowerShell Functions, you can follow these steps:

1. Install Azure PowerShell Module (if not already installed): If you haven't already, you need to install the Azure PowerShell module. Open a PowerShell window and run the following command:

   powershellCopy code

   Install-Module -Name Az -AllowClobber -Scope CurrentUser

   If prompted to install the NuGet provider, choose "Yes".

2. Connect to your Azure Account: Run the following command and follow the prompts to authenticate with your Azure account:

   powershellCopy code

   Connect-AzAccount

3. Install WinSCP .NET Assembly: To interact with SFTP, you can use the WinSCP .NET assembly. Install it using the following command:

   powershellCopy code

   Install-Package -Name WinSCP -Source https://www.nuget.org/api/v2 -Scope CurrentUser

4. Create the PowerShell Function: Create a new PowerShell script with your desired function. Below is an example of a function that connects to an SFTP server, downloads a file, and then uploads it to an Azure Storage Account blob:

   powershellCopy code

   # Import the WinSCP .NET assembly Add-Type -Path (Join-Path $env:USERPROFILE '\.nuget\packages\winscp\5.19.2\lib\netstandard2.0\WinSCPnet.dll') # Azure Storage Account information $storageAccountName = "yourstorageaccount" $storageContainerName = "yourcontainer" $storageBlobName = "uploadedfile.txt" $storageAccessKey = "yourstorageaccesskey" # SFTP server information $sftpHost = "sftp.example.com" $sftpUsername = "yourusername" $sftpPassword = "yourpassword" # Connect to SFTP server $sessionOptions = New-Object WinSCP.SessionOptions -Property @{ Protocol = [WinSCP.Protocol]::Sftp HostName = $sftpHost UserName = $sftpUsername Password = $sftpPassword } $session = New-Object WinSCP.Session $session.Open($sessionOptions) # Download a file from SFTP server $localFilePath = "C:\LocalPath\downloadedfile.txt" $remoteFilePath = "/path/to/remote/file.txt" $session.GetFiles($remoteFilePath, $localFilePath).Check() # Close SFTP session $session.Dispose() # Upload the downloaded file to Azure Storage Account $storageContext = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccessKey Set-AzStorageBlobContent -Context $storageContext -Container $storageContainerName -Blob $storageBlobName -File $localFilePath

   Replace placeholders (like yourstorageaccount, yourcontainer, etc.) with your actual details.

5. Run the PowerShell Function: Save the PowerShell script and run it in your PowerShell window. The script will connect to the SFTP server, download the file, and then upload it to your Azure Storage Account.

Remember to handle any error scenarios and enhance security by using managed identities or better authentication mechanisms if required.