Category Archives: Workflows

Saving Tweets to Excel using Microsoft Flow

Microsoft Flow is the new tool integrated with Office 365 which allows different services to interact. Such as social media providers like Twitter and Facebook or file sharing platforms like Dropbox, OneDrive and Google Drive, and many more services.

I wrote a blog post earlier this year about integrating machine learning tools and another post about syncing Google calendar data into SharePoint using Flow processes, but I thought I would get back to basics and provide a clear easy tutorial for Microsoft Flow newcomers.

In this example, I’m going to setup a Flow to put all the tweets made from the company (my) twitter account into an excel spreadsheet. This is a simple process but might be useful for keeping track of marketing or even leaving an audit trail of marketing activity by the social media owners within an organisation. The excel spreadsheet will sit in OneDrive for business but you can determine where you would like it stored.

To start, we are going to create a new excel spreadsheet with a table. In OneDrive for business, create a new excel spreadsheet called “tweets”.

Microsoft Excel Online

Add the following column headers:

  • Tweet Text
  • Location
  • Time

Select the column headers and in the insert menu, select “table”. This will make a new table in Excel which Flow can access to add rows.

Open Microsoft Flow. The Flow icon should be available in your Office 365 App Launcher if you have the licence enabled (alternatively you can just go to the website).

Search the templates for “Twitter”, and select the “Save tweets to an Excel file” template.

Microsoft Flow Templates

You will then be required to login to Twitter and Excel. When logging into Excel, make sure that you sign into OneDrive for Business with the correct account (otherwise you will get unauthorised access when trying to add rows). When both services are signed in, press continue.

Twitter trigger

In the twitter trigger, add the twitter account name to the search text box. This will fire off the process when that account tweets.

Excel action

In the insert row action, find the tweets excel spreadsheet in your OneDrive for Business by using the folder icon. The table will appear automatically under the Table name drop down. You can then select each column and add the appropriate tweet field.

Save the Flow and check that it is enabled.

In twitter, send a tweet from your account.

Twitter tweet

The Flow will run every 60 seconds, you can check the runs by clicking on the “i” icon next to the Flow and then filtering by “Checks (no data)”. After a couple of minutes, your spreadsheet should be updated with the tweet data!

Excel populated with tweets from MS Flow



Machine Learning with Cognitive Services API in Microsoft Flow

I recently attended a fascinating talk on machine learning by Martin Kearn and an implementation of this in Microsoft Flow by Mark Stokes. Did you know that there are APIs to access machine learning functionality via Microsoft’s Cognitive Services API? Did you also know that you can easily connect to these services in Office 365?

Microsoft Flow

Take a look at the available APIs and try them for free here.

The APIs give you access to computer vision, face emotion, face detection, text analytics, translators and much more. Microsoft have really opened up machine learning to the average developer (or non-developer)!

In this blog post, I’m going to show you how to use the text analytics cognitive API in Microsoft Flow without any code at all. If you are not familiar with Flow, check out some of my earlier posts.

Starting in Microsoft Flow, search the templates for “Text analytics”. Some interesting starter templates appear including analysing text for positive or negative sentiment via Twitter, Yammer and email. The returned sentiment value can then be used to start an action or chain of events. Have you ever wanted to be alerted to a negative email from the boss? Well now there is a template for it!

Microsoft Flow

In this example, I’m going to use the “Get emails for positive tweets” template. I want to get notified by email when I receive tweets which are very positive in sentiment.

Microsoft Flow

From this screen, you can see that the services involved in this Flow are Twitter, Text Analytics and Mail. Select “Use this template” and sign into each service.

When you get to the Text Analytics service, you will be required to enter a connection name and account key. To get this go to the Azure Text Analytics site and sign up for a free account (if you don’t already have one) by going to Getting started.

Once you have the account setup, navigate to your account keys in the Azure portal (as shown below).

Microsoft Azure Sentiment API

Copy and paste the account name and key 1 into the Flow connector.

Microsoft Flow connectors

In my Flow, I have used the hashtag #msflowtextanalytics as my trigger. If anyone uses that hashtag on Twitter, it will start the Flow.

Microsoft Flow

I’ve left the condition as 0.7. This means if the API returns a 70% positive sentiment rating, it will send the email.

Microsoft Flow Email properties

Finally, fill in the email details to complete the Flow. The template email will show who sent the positive tweet and the sentiment score from the API.

Publish the Flow and go to Twitter to test it out.

Twitter hashtag tweet

The Flow can take around 2 minutes to run but you should eventually receive an email with the name of the person sending the tweet and the score from the sentiment API.

Microsoft Flow Email

There are many uses for this in real world scenarios. Marketing and PR departments could use a very similar template to detect any negative tweets. That could then trigger an email for manual intervention or you could automatically direct message the user and open a support ticket. It could save huge amounts of time watching and sorting through thousands of tweets manually.

There are connectors for SharePoint, Instagram, Office 365 Email, and much much more. Imagine integrating all of these separate systems into one workflow (you couldn’t do that in SharePoint Designer).

Exciting times for machine learning and very powerful tools for those of us who are not developers or data scientists.

Integrating Google Calendar Data with SharePoint using Microsoft Flow

It can be time consuming to update multiple calendars. You can now setup custom flows using Microsoft Flow to copy and edit data between the two calendars.

Google Calendar to SharePoint

In the example below, I create a Microsoft Flow to detect when a new Google Calendar item is added and then create a new item in a SharePoint calendar.

First of all, open Microsoft Flow.

Microsoft Flow

Select “Create from blank” to open the flow editor

Search for the correct trigger by typing “Google” into the search box. You should see “Google Calendar – When an event is added to a calendar”. We will use this trigger to detect when a new item is added to the Google calendar.

Google Calendar trigger

When prompted, sign into your Google account and allow access to the calendar.

We are now going to add another step to the flow to add the items into our SharePoint calendar. Click “New Step” and then Add an action.

New flow step

Search for “SharePoint – Create item”, add the URL and list name. This should load up all the calendar new item fields.

Click in each field and select the output from the previous step (the google calendar data). You may wish to populate the Title, Start Time, End Time, Location and Description.

New SharePoint calendar item

Select “Create flow” when you are ready to publish the new task. It can take a few seconds for the task to run once the Google calendar item has been added. You can check the progress and status of the task from the Flow site.

There are other things to consider when setting this up such as all day events, recurrence, editing items and deleting items. However, you should be able to extend the logic in the flow to handle these data types. Below is a video guide going through the process.



Creating a simple Microsoft Flow for a SharePoint list

For anyone using the new style SharePoint lists, there is now a new action for Microsoft Flow integration. It’s a really cool product that integrates all the Office 365 products (and more) into your workflow.

SharePoint Designer workflows still have their place but the Microsoft Flow interface offers rich functionality and is easy to view and structure workflows.

One downside to using Microsoft Flow is the error messages. They come back as error messages from the REST API as headers which can be very confusing for non-technical users. SharePoint Designer errors were much clearer and easy to understand for general users.

In the video below, I go through quickly creating a Microsoft Flow from a new style SharePoint list in an Office 365 group site.



Creating a simple SharePoint Workflow to send an email

I have made the video below on how to create a basic SharePoint workflow to send emails. This could be used for sending out remittance advice (like in the example below) but it could also be used with additional workflow logic for sending out help desk emails, renewal reminders, etc.

Hope you find it useful, please feel free to contact me at Cloud Design Box if you have any further questions. I’ll try and make a full blog post on this subject with screenshots as soon as I get chance.



How to create a basic content type

I’ve just created a video guide on creating a simple content type and attaching it to a document library.

You can add custom metadata to a SharePoint list by:

  • Adding columns directly onto the list
  • Using Site Columns (can be reused with other lists)
  • Content Types (can be reused and keeps a set of custom columns together in a content type)

If you decide to use a content type, you will also get the benefits of being able to apply a workflow to the content type (rather than to each list individually). If you are thinking of developing search, content types can be a great way to filter and search for specific types of data in a list. You can also use multiple content types in a list (each with different columns), for example an invoice and a receipt.



How to add the content query web part to a page layout in SharePoint 2013 and SharePoint Online

Content queries in page layouts can be quite useful when wanting to generate your own navigation based on page content type. There isn’t a straight forward way to adding these into your current page layouts so I thought it would be useful to share this method.

Open Design Manager from the menu.

SharePoint menu

Select “Edit Page Layouts” and create a new temporary page layout (as shown below).

SharePoint Design Manager

Select “Conversion successful” to go to the preview page. In the top right select “Snippets” to open up the snippet code generator.

Code Snippet

Select “Other Web Parts” drop down menu and click “Content Query”.

Content Query

Configure the web part including the appearance, title and queries. Select the update button to generate the custom code.

Web Part Properties

Copy the custom code generated, this code needs to be pasted into the HTML version of the temporary page layout via SharePoint Designer 2013. Once this code has been copied into the page layout HTML, in this case testttt.html, save the file.

SharePoint will then automatically convert this code to aspx in the file testttt.aspx. Open this file to grab the content query code which can be used in any page layout.

Page layout with content query

Of course you can use the design manager to create your actual page layout (rather than just using it to convert code) but many people find this restrictive and already have good page layouts ready to deploy.

If you require any help or training, check out my consultancy company Cloud Design Box which provides this and other services for SharePoint on-premises and Office 365.