Author Archives: bexgordon

Copy new Email and Details From Outlook 365 to SharePoint Online Document Library, Microsoft Flow: Mail Enable Office 365 SharePoint Online

Parent Post – Requirements and InformationCopy new Email and Details From Outlook 365 to SharePoint Online Document Library, Microsoft Flow and Logic Apps: Mail Enable Office 365 SharePoint Online

Related Post – Logic AppsCopy new Email and Details From Outlook 365 to SharePoint Online Document Library, Logic Apps: Mail Enable Office 365 SharePoint Online

How to create a Microsoft Flow to pass an email from Outlook 365 to a SharePoint Online Document Library

  1. Go to your SharePoint Online site
  2. Create a document library to receive the emails
  3. In your document library, create a flow (in the modern UI click Flow -> Create a Flow, click "See more templates", click "create a flow from blank"). If you are using classic view, you can simply go to https://us.flow.microsoft.com/manage, login and under My Flows click "Create from blank"
  4. In your new flow, the below should be displayed. Click "Office 365 Outlook"
  5. Click "Office 365 Outlook – When a new email arrives" trigger
  6. You will need to choose which email address you want the trigger to watch, mine has automatically used the email address I am signed in as. To change this click "Add new connection" and then make sure is it ticked under "My connections". MAKE SURE you set "Include Attachments" to yes, or the attachments part will error
  7. I have left all of the other options default, but you can add other filters for the trigger to look for such as a subject keyword
  8. Click "New Step" -> "Add an Action"
  9. Type in "Content Conversion" and click on "Content Converstion – Html to text" – NOTE: we are adding this step as generally emails are in HTML and this shows as HTML markup when you add the Body of the email into a .txt file in SharePoint. Converting to plain text means we can add just the text and not HTML markup from the Body
  10. In the "Content" field, enter "Body" by double clicking the value "Body" under "When a new email arrives -> Body" as seen below
  11. Click "New Step" -> "Add an Action"
  12. Click on SharePoint
  13. Click "SharePoint – Create file"
  14. Enter the Site Address for your site collection or subsite, then select the Folder Path as your document library
  15. NOTE: as with Outlook, SharePoint also has "My connections" on the "…" menu, make sure you use the user you want to add the items to SharePoint. I have used the user I am logged in as
  16. For the File Name we will enter the From field "_" and the utcNow() function. This will make the file name easy to identify by who it is from but also unique based on the utc date and time. You will find all of the email fields under "Dynamic content" and functions such as utcNow() under "Expression". Scroll down to "Date and time" section, double click on utcNow() which will enter it into the expression field, then click OK
  17. Ensure you type in ".txt" at the end of the file name. The type of file is not decided for you and .docx does not work (throws an error that it can't open the document)
  18. For the "File Content" field you can enter a mixture of dynamic and static content. I have entered a number of the email fields due to the fact that I can't assign these to metadata. Note that "Dynamic content" pop up box groups available information by the action it comes from, e.g. below we have "Html to text" and "When a new email arrives". You'll also notice there is a "See more" button which will show more hidden fields. You can also just search dynamic content and it will filter the list so you can quickly find values
  19. Below you can see what I have chosen for the File Content. Notice that I have used "The plan text content." from the "Html to text" group instead of the Body field
  20. Now we need to add any attachments from the email. Click "New Step" -> "Add an Action"
  21. Click "SharePoint"
  22. Click "SharePoint – Create File" action
  23. Enter the site URL and the document library. Under file name, click "Name (Attachment name)"
  24. This will convert your create file action into an "Apply to each" action as it will need to loop through each attachment
  25. Let's make the "File Name" more useful, add the From field, "_ATT_" (to signify it's an attachment), utcNow() and Name field. When the item is added to the document library it should now look similar to the main email and group better, especially if you set your default view to sort by created date descending and then by File Name, you will be able to tell quickly that the email has attachments associated with it
  26. For the "File Content" field, enter "Content (Attachment content)"
  27. Your final attachment loop should look like the above
  28. Update your flow, you're now ready to test it!
  29. Create an email and add a couple of attachments. Send this to the email address you setup for the email trigger to watch
  30. It will take a few moments for the email to get picked up. You can view the run history and other details on your flows main screen, you should be taken here once you update your Flow and click "Done"
  31. Once it has successfully run, I can see my email plus two attachments in my document library
  32. If we open and view the txt file

Copy new Email and Details From Outlook 365 to SharePoint Online Document Library, Microsoft Flow and Logic Apps: Mail Enable Office 365 SharePoint Online

Related Post – Logic AppsCopy new Email and Details From Outlook 365 to SharePoint Online Document Library, Logic Apps: Mail Enable Office 365 SharePoint Online

Related Post – Microsoft FlowCopy new Email and Details From Outlook 365 to SharePoint Online Document Library, Microsoft Flow: Mail Enable Office 365 SharePoint Online

 

I've been working with a customer to figure out a way to email enable lists or libraries in Office 365/SharePoint Online. After deciding that a third party tool wasn't ideal, we looked at Logic Apps and Microsoft Flow. I found a few articles but they were out of date and not fully functional.

I'm writing this article to help fill the gaps until more funtionality is available for the SharePoint connectors and also to help clear up some of the things that took a bit of meddling to figure out. 

Here are the requirements and some comments on current restrictions. Restrictions apply to connectors for both Logic apps and Microsoft Flow as of 01/08/2017. If you are looking at this in September or beyond have a search around to see what Microsoft have implemented since this post. 


Requirement 1

Recieve an email to an email address. Once email received, it is picked up and processed into a document library

Comments

The reason we ended up using a document library instead of a list is currently there is no way to add attachments to a list item (this should come eventually), which means we can't add the BODY content as an attachment or the attachments in the email as attachments.

Ideally we wanted to grab the whole email, attachments and all and add the .msg file to the document library. This isn't possible (but also on the radar from Microsoft).

To work around this we will be creating a .txt file for all of the email details (from, subject, date received, has attachments?, body) and additional files for each attachment.

Requirement 2

Add metadata to the item for email fields

Comments

Sadly this isn't currently possible but it IS on the radar for development. You can only add metadata to a list item and not a document library. Adding metadata to list items also isn't ideal due to the fact you are restricted to 255 characters for string fields, not enough for the BODY content. 

For now the workaround is to pass all of these fields into the document we create in the document library.

I'll cover how to achieve this in both logic apps and flow, though both have pretty similar steps. NOTE: Flow is built ontop of Logic Apps, they are fairly similar but FLOW is accesable from SharePoint and more power user focused, where Logic Apps is more developer focused. 

Instructions for building mail enabled libraries in Office 365

I have split Logic Apps and Microsoft Flow into two seperate posts to reduce size

Logic AppsCopy new Email and Details From Outlook 365 to SharePoint Online Document Library, Logic Apps: Mail Enable Office 365 SharePoint Online

Microsoft FlowCopy new Email and Details From Outlook 365 to SharePoint Online Document Library, Microsoft Flow: Mail Enable Office 365 SharePoint Online

Other notes

I noticed over night that random spam from Microsoft processes made it's way into my document library. You may want to trigger the email received action based on a specific folder and route emails that match criteria to the folder. 

 

Remove custom actions for SharePoint Online in CSOM/C#

Snippet of code to remove specific user actions on SharePoint online / Office 365. Handy if you decide later you don't want a script anymore. Make sure you scope it to web / site / list, in my example I was deploying the actions at site collection level and needed to remove it as it was slowing down the page load too much. 

 private static void RemoveCustomActions(ClientContext context)
        {
            Site site = context.Site;
            context.Load(site, x => x.UserCustomActions);
            context.ExecuteQuery();

            for (int i = 0; i < site.UserCustomActions.Count; i++)
            {
                if (site.UserCustomActions[i].ScriptSrc != null && site.UserCustomActions[i].ScriptSrc.Contains("bundle.js"))
                {
                    site.UserCustomActions[i].DeleteObject();
                    break;
                }
            }

            context.ExecuteQuery();
        }

Some of the example taken from: http://sharepoint.stackexchange.com/questions/164745/unable-to-unregister-usercustomactions-added-at-location-scriptlink-using-csom, this post also has a JScript example. 

PowerShell SharePoint 2007 get all sites, lists, item counts, last modified

I needed to run a script on a MOSS SharePoint 2007 server to get an idea of the amount of stuff to migrate. Most of the scripts I found online were useless. This code has been run in proper environments and works well. 

#clear the PowerShell window
cls

#Set or reset default properties
$ReportPath = "C:\SPSiteReport.csv"
$webAppURL = ""
$Result=""
$siteColURL=""

# Exclude the following libaries / lists
$excludeLists = @("Master Page Gallery",
                "User Information List",
                "TaxonomyHiddenList",
                "Theme Gallery",
                "Style Library",
                "Solution Gallery",
                "Reusable Content",
                "Reporting Templates",
                "Reporting Metadata",
                "Long Running Operation Status",
                "List Template Gallery",
                "Content and Structure Reports",
                "Cache Profiles",
                "wfpub",
                "Workflows",
                "Workflow History",
                "Web Part Gallery",
                "Form Templates",
                "Workflow Tasks",
                "Suggested Content Browser Locations",
                "Site Template Gallery",
                "fpdatasources",
                "Variation Labels",
                "Content type publishing error log",
                "Converted Forms"
                )

#Load the PowerShell commandlets for SharePoint using PowerShell 1.0 and MOSS 2007 compatibility
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

#Print the header in the resulting file
$Header = "Site Collection URL`tWeb Title`tWeb URL`tWeb Template`tList Title`tList Default View URL`tList Type`tItem Count`tList Last Modified"
Out-File -inputobject $Header -filepath $ReportPath -Force

function RecurseWeb($inSite) {
     foreach ($web in $inSite.AllWebs) {         
			foreach ($list in $web.Lists)
            {
                if ($excludeLists -notcontains $list.Title)
                {
    				#Get the count of items in the current list
                    $itemCount = $list.Folders.Count + $list.Items.Count
    				Write-Progress -activity "Inventorying items. . ." -status "Processing items in Site> $web - List> $list"
                
    			    #Output the result of the current list
    				$Result = $siteColURL + "`t" + $web.Title + "`t" + $web.URL + "`t" + $web.WebTemplate + "`t" + $list.Title + "`t" + $($web.URL + "/" + $list.DefaultView.Url) + "`t" + $list.BaseType  + "`t" + $itemCount + "`t" + $list.LastItemModifiedDate
    				Out-File -inputobject $Result -filepath $ReportPath -append
                }
            }
            $web.Dispose()
        }
}

   $webApp = [Microsoft.SharePoint.Administration.SPWebApplication]::Lookup($webAppURL)

   foreach ($site in $webApp.Sites) {
        $siteColURL = $site.Url
        RecurseWeb $site
        $site.Dispose()
    }
    
    

 

SharePoint Add-in Client Side check if current user is in a group

Wanted to add some client side validation for my SharePoint hosted add-in (app, addin) to check whether a user was in an HR group. If the user is in the group then the proper interface will show, otherwise a message will display. Obviously you should do server side validation on your API calls or server side code as well if it's sensitive information. I've only tested this with side loading an app, it will probably require a certain level of permissions on app install.. Will update when I get that far 🙂 

var context;
var currentUser = null;
var userGroups;
var permissionsToView = false;

$(document).ready(function () {
    getCurrentUser();
});

function getCurrentUser() {
    var context = new SP.ClientContext.get_current();
    var website = context.get_web();
    currentUser = website.get_currentUser();
    userGroups = currentUser.get_groups();
    context.load(currentUser);
    context.load(userGroups);
    context.executeQueryAsync(Function.createDelegate(null, onQuerySucceeded), Function.createDelegate(null, onQueryFailed));
}

function onQuerySucceeded(sender, args) {
    var groupsEnum = userGroups.getEnumerator();
    while (groupsEnum.moveNext()) {
        var group = groupsEnum.get_current();
        if (group.get_title() == "My Group Name") {
            permissionsToView = true;
        }
    }

    if (permissionsToView) {
        // yay can see!
    }
    else {
        $('.no-permissions').show(); // show some sort of no permissions message
    }
}

function onQueryFailed(sender, args) {
    $(".error").html('Request failed ' + args.get_message() + '\n' + args.get_stackTrace());
    $(".error").show();
}

 

Dynamically create subscribe to alert link JavaScript SharePoint

Code for creating an alert for a list dynamically on a page. I have created the following HTML file, upload it somewhere in your site collection and link to it from a content editor web part, change the title of the list you want people to subscribe to and the message 🙂 

Note that this is sitting on a discussion site home page therefore is looking for /sitepages – if you want it to look for /pages change this, or if you want to hard code the link to a root level list for subscription change it even more 😛 

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
</head>

<body>
<script type="text/javascript">
	$().ready(function () {
	    SP.SOD.executeFunc('sp.js', 'SP.ClientContext', function () { });
	    SP.SOD.executeOrDelayUntilScriptLoaded(initiateDiscussionsAlerts, "sp.js", "SP.ClientContext");
	})

	var discussionList;
	var discussionWeb;
	function initiateDiscussionsAlerts() {
		// load web
		clientContext = new SP.ClientContext.get_current();                    
	    discussionWeb = clientContext.get_web();
	    clientContext.load(discussionWeb);
	    
	    // load discussions list and ID
	    discussionList = discussionWeb.get_lists().getByTitle("Discussions List");
	 	clientContext.load(discussionList); 
	
		clientContext.executeQueryAsync(Function.createDelegate(this, onQuerySucceeded), Function.createDelegate(this, onQueryFailed));
	}
	  
	function onQuerySucceeded() {
		var webUrl = window.location.href;
		if (webUrl.toLowerCase().indexOf('/sitepages') != -1) {
			var listGuid = discussionList.get_id();
		    webUrl = webUrl.substr(0, webUrl.toLowerCase().indexOf('/sitepages'));
		    
		    $('.discussion-alert > a').attr('href', webUrl + '/_layouts/SubNew.aspx?List=' + listGuid + '&Source=' + webUrl);
		}
	}
	             
	function onQueryFailed(sender, args) {
		alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
	}
</script>

	<div class="discussion-alert"><a href="">Subscribe to Alerts for this Discussion Board</a></div>
</body>

</html>

 

Reporting Services SSRS in SharePoint Error ‘New Document’ requires a Microsoft SharePoint Foundation-compatible application and web browser…..

Adding SSRS content types seems to be a bit flaky.. We couldn't get the content types automatically added by activating the feature in one web application – so we manually added the content types into the site collection as specified in here (http://sharepoint.stackexchange.com/questions/111619/sharepoint-2013-sp1-installation-issue-ssrs-content-type-missing-for-new-sc) – this post also had some other good suggestions.

So we add another web app for another purpose – again same issue with the content types not adding, so we add them manually again and get the following error when trying to create an item in the document library for "Report Data Source", "Report Builder Report" and "Report Builder Model" – 'New Document' requires a Microsoft SharePoint Foundation-compatible application and web browser. To add a document to this document library, click the "Upload Document" button."

We had added them manually correctly and obviously it was installed correctly as another web app was working..

We came across this post which worked for us! https://chrishattonnzlsp.wordpress.com/2015/07/21/reporting-services-new-document-requires-a-microsoft-sharepoint-foundation-compatible-application-and-web-browser/

Running rsSharePoint.msi fixed the issue. We had to delete the content types from lists and the site collection and then deactivated and reactivated the reporting services feature, the content types were there this time and we could add the proper ones into the libraries.

Tenant Administration Delete Root Site Collection and Re-Create Doesn’t Properly Delete From Recycle Bin

I came across a strange issue when re-creating the root level tenant site collection from the interface. It's like it wasn't actually deleting the site collection – the wiki page content was on my new home page and the features activated was all wrong. Here's the steps I followed in the interface to replicate the issue:

We need to remove the root level site collection

  • Navigate to the site collections screen adminurl.sharepoint.com/_layouts/15/online/SiteCollections.aspx
  • Check the root level site site.sharepoint.com
  • Click Delete
  • Select “I understand that nobody can access SharePoint until I create a new site collection at https://site.sharepoint.com.”
  • Click Delete
  • Wait until the site is removed before continuing the release.

Create site collection

  • Click on create new private site collection in admin adminurl.sharepoint.com/_layouts/15/online/SiteCollections.aspx
  • Enter the following details:

    • Title: Title
    • Web Site Address: this should default to the root level site
    • Template Selection: Publishing -> Publishing Portal
    • Time Zone: (UTC + 12:00) Auckland, Wellington
    • Administrator: admin username
    • Server Resource Quote: leave at 300
    • Click OK
  • You will get a warning "Permanently delete the site collection from the recycle bin and continue."
  • Check the box and click OK

It obviously doesn't actually properly delete the site collection from the recycle bin. 

So I contacted Microsoft as this was annoying the hell outa me and I couldn't deploy my site into the test environment. They gave me the following instructions to get PowerShell to do the job, which is cool but slightly annoying as the release didn't require any installation of extra software and now it does, but in saying that I guess anyone who works with SharePoint should have this installed anyway :P. M$ didn't give any indications of fixing and ignored my comments regarding fixing the interface to work. 

Access Denied deploying files to SharePoint Online as Global Administrator

So I went to deploy to test and for some reason kept getting access denied errors, but I'm global admin!! and Site collection admin!! What. It died on the code where I am uploading to the master page gallery. 

The problem was I had been deploying to /sites/site and not to the root web in other tests. The root web has a setting on it called DenyAddAndCustomizePagesStatus which is set to true but on the /sites/site sites, it's set to false :|. Even more annoying, I had also deployed to a demo tenant under the root web and this works, so I can only assume since it's a demo tenant with content that Microsofts code actually does this for you… https://support.office.com/en-us/article/Turn-scripting-capabilities-on-or-off-1f2c515f-5d7e-448a-9fd7-835da935584f?ui=en-US&rs=en-US&ad=US

Here's the code for deactivating on your site collection. Thanks to Ronny Lewandowski and SPMeta2 for helping me to solve this issue :). 

static void Main(string[] args)
{
            string siteUrl = ConfigurationManager.AppSettings["SiteUrl"];
            string adminUrl = ConfigurationManager.AppSettings["AdminUrl"];
            string userName = ConfigurationManager.AppSettings["AdministratorUserName"];
            string password = ConfigurationManager.AppSettings["AdministratorPassword"];
            var securePassword = new SecureString();
            foreach (var c in password) { securePassword.AppendChar(c); }

           Console.WriteLine("Start: Allow customisations...");
           DisableDenyAddAndCustomizePages(adminUrl, siteUrl, userName, securePassword);
            Console.WriteLine("End: Allow customisations... \n");
}

public static void DisableDenyAddAndCustomizePages(string adminUrl, string siteUrl, string userName, SecureString securePassword)
        {
            using (var ctx = new ClientContext(adminUrl))
            {
                ctx.Credentials = new SharePointOnlineCredentials(userName, securePassword);

                Tenant tenant = new Tenant(ctx);
                var siteProperties = tenant.GetSitePropertiesByUrl(siteUrl, true);
                ctx.Load(siteProperties);
                ctx.ExecuteQuery();

                siteProperties.DenyAddAndCustomizePages = DenyAddAndCustomizePagesStatus.Disabled;
                var result = siteProperties.Update();
                ctx.Load(result);
                ctx.ExecuteQuery();
                while (!result.IsComplete)
                {
                    Thread.Sleep(result.PollingInterval);
                    ctx.Load(result);
                    ctx.ExecuteQuery();
                }
            }
 }

ALSO, here is the code in PowerShell if you just want to quickly run it once:

Clear-Host
$spOnlineUser = ''
$spOnlinePassword = ''
$spOnlineSiteAdminUrl = ''
$spOnlineSiteUrl = ''
 
$cred = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $spOnlineUser, $(convertto-securestring $spOnlinePassword -asplaintext -force)
Connect-SPOService -Url $spOnlineSiteAdminUrl -Credential $cred
Write-Host "Connecting to " $spOnlineSiteUrl
 
$site = Get-SPOSite -Identity $spOnlineSiteUrl
Write-Host "Connection Successful!!" -ForegroundColor "Green"

Set-SPOSite -Identity $spOnlineSiteUrl -DenyAddAndCustomizePages 0

Write-Host "Finished!!" -ForegroundColor "Green"