Tuesday, June 5, 2018

Task Approval by Email Replies

This post details how to setup a script to process emails, and setup your workflows to send emails that can be processed by the script, so that end users can ultimately just reply to an email and it will get processed and close the task.

Overview

We'll setup a script as a scheduled task, that will check a designated inbox for emails to process.  It will scan the body of those emails for keywords to approve or reject.

We'll then take a workflow task process, and update the email to have an approve and a reject mailto link, which will open up an email, which when a user sends, will get processed and will update and close the task.

Step 1:  Setup Email Inbox

The first step is to create an email inbox that we'll use for all processing.  In this inbox, create three folders:
  • Email Approvals
  • Email Approvals Processed
  • Email Approvals Not Processed
Create an inbox rule that will move the item to the Email Approvals folder if the subject begins with "REPLY:"


This Email Approvals folder will be the folder our script will check to process any emails that arrive there.

Step 2:  Setup the script

Download the script from github, and change the necessary variables to local paths on your machine.  Set it up on the server that it will be running on.

Step 3:  Create Encrypted Credentials 

The script assumes there are two users, one that has the required permissions to update the task in SharePoint ($UserSharePoint), and one user for the mailbox where the emails are being sent to ($UserEmail).  Create encrypted files for each by following the instructions here, and make sure to match the name and path of the file to the $CredsFileSharePoint and $CredsFileEmail in the script.

Step 4:  Create a Scheduled Task

Setup a scheduled task to run the script, and under Actions set the following:

Program/script:  C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Add Arguments:  "F:\Scripts\EmailApprovals\Process-Email-Approvals.ps1"
Start in:  F:\Scripts\EmailApprovals\


Step 5:  Create a Workflow Task Process

Next create a workflow with a task process.  For the email option, use the following as a template:

Email to user (sent from workflow)

Subject:  ACTION REQUIRED:  PO 98451 Change Request
Body:  A new PO Change Request has been submitted and requires your approval:

Title: Requesting a new phone 
PO Number: 98451
PO Owner: Jane Doe 
Original Amount: $699.00 
New Amount: $799.00 
Description: ​Upgraded the model

Please either approve or reject the request below:

Approve         Reject

PO:  https://vivity.sharepoint.com/Finance/Lists/POChangeForm/DispForm.aspx?ID=117

Mailto Link Sample

In each email from the workflow, we have a mailto link for approving and rejecting, which should resemble the following.

email.approvals@vivityconsulting.com?subject=REPLY:%20PO%20Change%20Request&body=I%20approve%20the%20following%20PO%20Change%20Request.%0D%0A%0D%0ATitle:%20Requesting%20a%20new%20phone%0D%0APO%20Number:%2098451%0D%0APO%20Owner:Jane%20Doe%0D%0AOriginal%20Amount:%20$699.00%0D%0ANew%20Amount:%20$799.00%0D%0ADescription:%20%E2%80%8BUpgraded the model%0D%0A%0D%0A______________________________%0D%0A%0D%0AFor%20Processing%0D%0A%0D%0Aurl=https://vivity.sharepoint.com/Finance/Lists/POChangeTasks/DispForm.aspx?ID=117

Mailto Response Email

Note that the script keys off of certain words, so if “approve”, “acknowledge” or “yes” is in the body, it will be approved, if “reject” is in the body text, it will be rejected.

Subject: REPLY: PO Change Request
Body:  I approve the following PO Change Request.

Title: Requesting a new phone 
PO Number: 98451
PO Owner: Jane Doe 
Original Amount: $699.00 
New Amount: $799.00 
Description: ​Upgraded the model
______________________________
For Processing
url=https://paratekpharma.sharepoint.com/Finance/Lists/POChangeTasks/DispForm.aspx?ID=117

Design

The following should be in place for the process to work properly:
  • The link should be to a Tasks lists (as the script is updating task columns)
  • The body of the email received must contain either (case insensitive):
    ·       Approve
    ·       Acknowledge
    ·       Yes
    ·       Reject
  • The body must also contain a "url=" with a link to the task item in the format, which is typically included at the end of the email.
    url=https://vivity.sharepoint.com/IT/Lists/ManagerTasks/DispForm.aspx?ID=387
  • All processing is written out to a log to review any potential issues.  A new file is created each time the process is run, and this folder can be regularly cleaned up.


Using Encrypted Credentials in PowerShell

*** NOTE: This must be done when logged into the computer running the program, as the user you’ll be running the program as. E.g. if your Scheduled Task is running as sys_admin, you must be logged in as sys_admin on the machine that will be running the scheduled task before encrypting the credentials ***

Create the Encrypted File

First create the encrypted text file by running the following:

$cred = Get-Credential
$cred.Password | ConvertFrom-SecureString | Out-File .\adcreds.txt


Using the Encrypted Credentials

To use the credential file in powershell you can use:


$User = "sregan@vivityconsulting.com"
$File = "C:\Development\Vivity\adcreds.txt"
$cred = New-Object -TypeName System.Management.Automation.PSCredential `
-ArgumentList $User, (Get-Content $File | ConvertTo-SecureString)


Notes

If you change the machine or account that this runs as, you will have to recreate this file!


Reference

https://practical365.com/blog/saving-credentials-for-office-365-powershell-scripts-and-scheduled-tasks/

Thursday, May 31, 2018

Using jQuery for Validation

If you want to make a column required based on non-standard column validation, you can do so via jQuery by creating a function to make the field red and disable the Save button like so:

function MakeFieldRequired(fieldId) {
    $("input[value$='Save']").attr('disabled', true);
    $("span[id^='" + fieldId+"']").css('color','red').css('font-weight','bold');
}


Tuesday, April 10, 2018

Updating a SharePoint Survey View

If you want to update the view of a SharePoint survey, like say add a column to view or change the sort order, you have to open up the view in SharePoint Designer, like All Responses:


then find the "View" xml and update accordingly (sorting is in the OrderBy CAML, and you can add FieldRefs to show more columns, etc.)

<View Name="{D1928BFA-8AFA-4B0D-A5F2-9C5748292299}" Type="HTML" TabularView="FALSE" DisplayName="All Responses" Url="/Lists/Statistics/AllItems.aspx" Level="1" BaseViewID="2" ContentTypeID="0x" ImageUrl="/_layouts/15/images/survey.png?rev=44" ><Query><OrderBy><FieldRef Name="ID" Ascending="FALSE"/></OrderBy></Query><ViewFields><FieldRef Name="DisplayResponse"/><FieldRef Name="Author"/><FieldRef Name="Modified"/><FieldRef Name="Active"/><FieldRef Name="Completed"/></ViewFields><RowLimit Paged="TRUE">30</RowLimit><JSLink>clienttemplates.js</JSLink><XslLink Default="TRUE">main.xsl</XslLink><Toolbar Type="Standard"/></View>

and you'll get an updated view:


Friday, April 6, 2018

SharePoint Designer: Workflow stuck - Invalid Text Value

If you have a workflow that gets stuck in a state where it says:

"Invalid text value.  A text field contains invalid data.  Please check the value and try again"



You likely have more than 255 characters in a "Log to the Workflow History List", which throws this error.


SharePoint Designer Workflow: Passing HTML in url parameters

If trying to pass some html as a url parameter in a link in a SharePoint Designer workflow like below,



if you just add it to a link parameter like so:

&msg=<h2>Thank you [% Current Item:Assigned To %] for approving<br/><br/>You may now close this tab.</h2>


it gets encoded so your < will become %3C, etc.  To get around this, if you create a variable and encode it:


like so:

%3Ch2%3EThank%20you%20[% Current Item:Assigned To %]%20for%20approving%20your%20direct%20reports%20for%20this%20fiscal%20quarter.%3Cbr%2F%3E%3Cbr%2F%3EYou%20may%20now%20close%20this%20tab.%3C%2Fh2%3E


then you can just use the variable in the link and it will display as you'd like it:


&msg=<h2>Thank%20you%20Sean%20Regan%20for%20approving.<br%2F><br%2F>You%20may%20now%20close%20this%20tab.<%2Fh2>


SharePoint Survey: Creating a Friendly Error Message

If you have a SharePoint survey that is set to only allow a single submission per user, if a user tries to submit again they get a nasty system error:

Sorry, something went wrong
You are not allowed to respond again to this survey



To create a more user friendly error message, you can create a new page, add some javascript in it, and display either a friendly message like:




Or if they haven't taken the survey it will redirect them there.  

Simply send out a link to your new page with a (list) path and a (list) title parameter like so:

https://vivity.sharepoint.com/Pages/Survey-Check.aspx?path=/Lists/Survey/NewForm.aspx&title=Survey

The code is in my github below, just link the javascript into a Content Editor Web Part:


And below:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script type="text/javascript" src="/_layouts/15/sp.runtime.js"></script>
<script type="text/javascript" src="/_layouts/15/sp.js"></script>

<script type="text/javascript">

function getParameterByName(name) {
var url = window.location.href;
name = name.replace(/[\[\]]/g, "\\$&");
var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)"),
results = regex.exec(url);
if (!results) return null;
if (!results[2]) return '';
return decodeURIComponent(results[2].replace(/\+/g, " "));
}

function readSurveyVotes(cbSurveyResult)
{
var listTitle = getParameterByName("title");

        var context = new SP.ClientContext.get_current();
        var web = context.get_web();
        var list = web.get_lists().getByTitle(listTitle);
        var viewXml = '<View><Where><Eq><FieldRef Name="Author"/><Value Type="Integer"><UserID Type="Integer"/></Value></Eq></Where></View>';
        var query = new SP.CamlQuery();
        query.set_viewXml(viewXml);
        var items = list.getItems(query);
        context.load(items);
        context.add_requestSucceeded(onLoaded);
        context.add_requestFailed(onFailure);
        context.executeQueryAsync();
        function onLoaded() {
            var voteCount = items.get_count();
            cbSurveyResult(voteCount)
        }
        function onFailure() {
            cbSurveyResult(null);
        }
}

$(document).ready(function() {
var listPath = getParameterByName("path");
var listTitle = getParameterByName("title");

    //Read survey for current user to find out if he have already voted   
    readSurveyVotes(function(votesCount){
        //if voted then display custom message 
        if(votesCount > 0) {
            survey-check-message.innerHTML = "<h2>You already took the " + listTitle + " survey, thank you for checking!</h2>";
        }
        //if not, call original function for opening response form
        else {
            survey-check-message.innerHTML = "<h2>Redirecting to " + listTitle +  " survey...</h2>";
            window.location.href = listPath;
        } 
    });
  
});
        
</script>

<div id="survey-check-message" />

I based my code from the following:



Friday, March 16, 2018

Inserting text/html into a list form

If you want to insert text or html into a list form, you can use the following jquery, where you basically target the ID of a control, and insert a row above it:

<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$('<tr><td colspan="2"><br/>Please add your notes here:</td></tr>').insertBefore($("input[id^='Body']").closest('tr'));
});
</script>



Friday, March 9, 2018

Creating a New Hire Workflow...Using Tasks Created Dynamically from a SharePoint List

The following describes how to create a workflow that uses tasks that are updated in a regular SharePoint list, allowing your end users to create/edit/remove/assign tasks in a SharePoint list, so they never have to open up SharePoint Designer.

It's a process I like to use for a new hire process or for offboarding, or any situation where tasks tend to change regularly.

It essentially involves building:
  • New Hire list - this is the list your users will be filling out to kick off the new process, and contain all the information about the new hire
  • Task Administration list - Create a custom list that will store the tasks we want to be created when the workflow runs
  • Tasks list - that will store the tasks that will get created and assigned to users
  • Workflow - loops through each item and assigns the tasks to the users at runtime

and the process is:
  • Somebody fills out a form to add a new hire
  • This kicks off the workflow
  • The workflow loops through the Task Administration list, creating tasks in the Tasks list
  • Everyone assigned a task gets an email at the end

New Hire List List

The new hire list collects the information about the new hire, and kicks off the process.  


Task Administration List

The Task Administration list is just a custom list with the following fields: 
  • Title - this will be the name of the task that will get created, e.g. "Create AD Account"
  • Assigned To - this is the person or group the task will be assigned to
  • Task Type - [Task or Notification] - I use this to distinguish if I should create a task, or just send an email notification
  • Task Active For - [Employees & Non-Employees, Employees or Non-Employees] - this determines whether the task should get assigned for different categories of users


Tasks List

The Tasks list is just an OOB Task list:

Workflow

The workflow essentially just queries our Task Admin List, loops through each item, creates a task, and keeps a running list of who to email at the end (so everyone gets just one email no matter how many tasks are create for them).

In this example I also added two extra bits of functionality:
  • I added a "Task Active For" column in the Task Admin list, which specifies if a task is needed for Employees, Non-Employees, or both.  The workflow checks this and assigns tasks accordingly
  • I also added a "Task Type" column in the Task Admin list.  If marked as a Task, a task gets created.  If a Notification, it just sends an email to the users assigned, and no task is created.

Wednesday, February 7, 2018

Display all Sites and Documents a user can access in SharePoint

Summary


This post walks you through the steps of creating a page that will display every shared document, folder and site that's been shared with a user, avoiding the reliance on email links when items are shared.  The end result looks something like below.



Solution


Search can bring back everything a user has access to.  So by modifying the Search Content Web Part, I brought back all files, folders and sites.  I updated the Display Templates to show the sites that the documents are in, and added a hover panel that has a document preview and shows the path where the document lives.

I added a Search Refinement Web Part, that displays all of the sites along with the item count under each site.  This way a user can easily drill down into different areas.

And lastly I added a search box so that users can perform a search on the results to aid in finding specific items.

Setting up the  web parts


Search Content Web Part


First I created a page and added a Search Content Web Part.  In order to bring back all files/folders/sites, so I changed the Query text to be:

Path:"https://vivity.sharepoint.com" -Path:"https://vivity.sharepoint.com/SiteAssets/" ContentTypeId:0x0120* -ProgID:OneNote.Notebook    -ContentTypeId:0x012002* -ContentTypeId:0x012000C0* -ContentTypeId:0x0120001928* ContentTypeId:0x010100* {QueryString.searchTerm}

Which searches my site, leaves out SiteAssets and OneNote Notebooks, and brings back derivatives of the contenttypes I want (files, folders and sites).  I am also searching for the querystring parameter "searchTerm", so a wildcard is thrown in the url as a default, which is replaced if a user submits a search.


I also mapped the ows_SiteName crawled property to RefineableString02 and am sorting by that.

Next, I wanted to update my Display Templates (/_catalogs/masterpage/Display Templates/Content Web Parts).  I started out with the Control_ListWithPaging.html and the Item_TwoLines.html and modified those.  

Control_ListWithPaging became Control_GroupedSites_WithHover, and I added some paging logic from here.  I also added a javascript file that sets some variables I'll be using, and adds some styling to my display template.

Item_TwoLines became Item_GroupedSites_WithHover, and my goal here was to display the documents, and added some code to make the Site Title be the header for every set of documents.

I also wanted more contextual information about each document, so I added a hover panel, which I updated to display the folder path and a document preview.

To show the hover panel, I started with the /_catalogs/masterpage/Display Templates/Item_Default_HoverPanel.html file, and merged it with the Item_Word_HoverPanel.html file, making some slight modifications to make it more generic and show the folder path so we have some context of where the document lives.  I named it Item_Default_HoverPanel_SharedDocuments.html.

Search Refiner Web Part


Additionally, users usually have a sense of where a document lives that they're searching for, so I added a Search Refinement web part that lists all of the sites that are returned an displays them so you can drill down easily.



and when you select a site, it will only bring back those items:


I updated the refiner templates so that they display in a table format, and you can find these updates in  /_catalogs/masterpage/Display Templates/Filters/Control_Refinement_SharedDocuments.html and Control_Refinement_SharedDocuments.html.

Search


Lastly I wanted a way for users to be able to search at any point, so I added a Content Query Web Part with some html/javascript that simply adds a querystring parameter and resubmits the page, and the the Content Search Web Part uses as a search term.

Code


All the code, templates and web parts are up on my github:  https://github.com/sregan1/SharePoint-Office365/tree/master/Shared%20Documents

Monday, January 29, 2018

Refresh Mapped SharePoint Folders & Fix the "Not Accessible" Error Message

PROBLEM

If you have mapped network drives to SharePoint document libraries, when accessing it from File Explorer you'll occasionally get the error:

[path to your folder] is not accessible.  You might not have permission to use this network resource.  Contact the administrator of this server to find out if you have access permissions.

Access Denied.  Before opening files in this location, you must first add the website to your trusted sites list, browse to the web site, and select the option to login automatically.
There doesn't seem to be any way you can make this completely go away, but by using some javascript to mimic the action, you can create an easy way for your end users to correct that problem.

SOLUTION

Our solution was to create a desktop shortcut, which users can double-click, that will reauthenticate the user and remedy the issue.

When clicked, an instance of IE will be opened, to a page where we will mimic the "Open with Explorer" functionality, and ultimately the File Explorer will be displayed.

So let's get started.

If you create a page on your site (e.g. OpenWithExplorer.aspx), add a content web part, and in the html add the following code:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script type="text/javascript">
function getParameterByName(name, url) {
    if (!url) url = window.location.href;
    name = name.replace(/[\[\]]/g, "\\$&");
    var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)"),
        results = regex.exec(url);
    if (!results) return null;
    if (!results[2]) return '';
    return decodeURIComponent(results[2].replace(/\+/g, " "));
}

$(document).ready(function() {
// This is the default url to open if no parameter is passed
var url = 'https://vivity.sharepoint.com/Documents';

// See if there is a url parameter, if so grab it.
var qsUrl = getParameterByName("url", window.location.href);

// If it exists, change the default url
if (qsUrl != null)
{
url = qsUrl;
}

// Open up Explorer with our url
CoreInvoke('NavigateHttpFolder', url, '_blank');
});
</script>

Then create a desktop shortcut (right-click on the Desktop, select New -> Shortcut), and set the target to:

"C:\Program Files\Internet Explorer\iexplore.exe" https://vivity.sharepoint.com/Pages/OpenWithExplorer.aspx?url=https://vivity.sharepoint.com/sites/HR/Shared%20Documents

with the url parameter the path to the folder you want to open.

and set the Start in to:  "C:\Program Files\Internet Explorer", and optionally change the icon.


You'll then have an icon on your desktop, which when you double-click it will:

      1) Open up an instance of IE
      2) Open up the library in File Explorer


Or you can just as easily create a powershell script and run it with a hidden IE window:

$ie = new-object -com "InternetExplorer.Application"
$ie.navigate("https://vivity.sharepoint.com/Pages/OpenWithExplorer.aspx")