Microsoft Flow is fantastic for designing business logic workflows in a simple to use interface. It also has a bunch of connectors that allow us to make use of some popular tools, including those within Office 365. When we encounter a step that Microsoft Flow can’t handle, such as connecting to a service that doesn't have an existing Microsoft Flow connector, we can start an Azure Function from within our Flow. The Azure function can do the technical work, and send the data back.
This exact scenario won't apply to the majority. However, hopefully it demonstrates the power of using Microsoft Flow with Azure Functions and SharePoint Online to automate your business processes. If you happen to be using Gravity Forms, ConnectWise and Office 365, and you'd like to experiment with Flow and Functions, you can implement this solution yourself using the guide below.
This example solution uses the following services:
Microsoft Flow
Microsoft Flow gives us an easy way to coordinate the flow of data between our other services. It authenticates easily against Office 365 services, and is the backbone of the solution
Azure Functions
Azure Functions let us run code in the cloud without a server. Azure Functions do the technical work, such as interfacing with an API that doesn't have a built in connector for Flow. This solution uses 3 Azure Functions - one runs every four hours to start the Flow, the other two are triggered within the Flow.
Gravity Forms
Gravity forms is the forms plugin that we use on our Wordpress Site to collect our customer's referrals
ConnectWise
We're an IT managed service provider that uses ConnectWise to run our business, it’s a professional services automation (PSA) tool that helps us keep track of open tickets, billing, sales etc.
SharePoint Online
We're using SharePoint to keep a register of referrals. We can use this later to track whether our customers have been rewarded for providing referrals.
Our solution works like this:
Create Gravity Form and page in WordPress Site to receive referrals
Set up ConnectWise Ticket completion notifications
<a href="https://gcits.com/who-else-can-we-help?contactrecordid=[contactrecordid]&contactfirstname=[contactfirstname]&companyrecordid=[companyrecordid]"><strong>let us know here</strong></a>
Enable the Gravity Forms API to retrieve the form info
Make a note of all the fields in the form. We’ll be adding these as columns to a SharePoint List.
Create a SharePoint List to receive the form data
Create a Function App in Visual Studio 2017
Azure Functions can be created and tested online via the Azure Portal or locally on your own computer. We'll be using Visual Studio to test and deploy the C# Azure Function that pulls the form entries from Gravity Forms.
Go to File, New Project, Visual C#, Cloud, Azure Functions then create a Function App and give it a name.
using System; using Microsoft.Azure.WebJobs; using Microsoft.Azure.WebJobs.Host; using System.Net.Http; using System.Threading.Tasks; using System.Web; using System.Security.Cryptography; using System.Net.Http.Headers; using System.Text; namespace GCITSFunctions { public static class GravityForms_Referrals { [FunctionName("GravityForms_Referrals")] public static void Run([TimerTrigger("0 0 */4 * * *")]TimerInfo myTimer, TraceWriter log) { //Change the Timer Trigger above to "0 * * * * *" when debugging to avoid waiting too long for it to execute. log.Info($"C# Timer trigger function executed at: {DateTime.Now}"); string content = RunAsync().Result; // Remove the comments from the below four lines once you've retrieved the HTTP POST URL from Microsoft Flow and added it in. //HttpClient client = new HttpClient(); //HttpContent jsoncontent = new StringContent(content, Encoding.UTF8, "application/json"); //string flowRequest = "<Enter flow HTTP POST URL HERE>"; //var result = client.PostAsync(flowRequest, jsoncontent).Result; } static async Task<string> RunAsync() { HttpClient client = new HttpClient(); // Add the public and private keys for Gravity Forms string publicKey = "<Enter Gravity Forms Public API Key>"; string privateKey = "<Enter Gravity Forms Private API Key>"; string method = "GET"; // Specify the form ID of the form you're retrieving entries for string formId = "1"; string route = string.Format("forms/{0}/entries", formId); /* Paging specifies the number of entries that will be retrieved from your form in this call, eg. 1000. You can make this higher or lower if you like. It will retrieve the most recent entries first. */ string paging = "&paging[page_size]=1000"; string expires = Security.UtcTimestamp(new TimeSpan(0, 1, 0)).ToString(); string signature = GenerateSignature(publicKey, privateKey, method, route); /* Replace gcits.com with your own domain name. If the call doesn't work initially, you may need to make sure that 'pretty' permalinks are enabled on your site. See here for more information: https://www.gravityhelp.com/documentation/article/web-api/ */ string url = string.Format("//gcits.com/gravityformsapi/{0}?api_key={1}&signature={2}&expires={3}{4}", route, publicKey, signature, expires, paging); client.BaseAddress = new Uri(url); client.DefaultRequestHeaders.Accept.Clear(); client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json")); var response = await client.GetAsync(client.BaseAddress); string content = response.Content.ReadAsStringAsync().Result; return content; } public static string GenerateSignature(string publicKey, string privateKey, string method, string route) { string expires = Security.UtcTimestamp(new TimeSpan(0, 1, 0)).ToString(); string stringToSign = string.Format("{0}:{1}:{2}:{3}", publicKey, method, route, expires); var sig = Security.Sign(stringToSign, privateKey); return (sig); } } public class Security { public static string UrlEncodeTo64(byte[] bytesToEncode) { string returnValue = System.Convert.ToBase64String(bytesToEncode); return HttpUtility.UrlEncode(returnValue); } public static string Sign(string value, string key) { using (var hmac = new HMACSHA1(Encoding.ASCII.GetBytes(key))) { return UrlEncodeTo64(hmac.ComputeHash(Encoding.ASCII.GetBytes(value))); } } public static int UtcTimestamp(TimeSpan timeSpanToAdd) { TimeSpan ts = (DateTime.UtcNow.Add(timeSpanToAdd) - new DateTime(1970, 1, 1, 0, 0, 0)); int expires_int = (int)ts.TotalSeconds; return expires_int; } } }
Add a reference to your function for System.Web by right clicking on your project and choosing Add, Reference.
Once you’ve got the Connection string, paste it in the AzureWebJobsStorage value of the local.set
That's it for part 1. In part 2, we set up the Azure Functions required to connect to Gravity Forms and ConnectWise. In Part 3, we bring it all together in a single Microsoft Flow.
Elliot Munro is an Office 365 MCSA and Partner at GCITS - See the GCITS knowledge base for scripts and articles on administering Office 365 for managed service providers.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.