Second Life of a Hungarian SharePoint Geek

June 25, 2017

Copying Hierarchical Lookup Table Entries via the Managed Object Model

After I’ve described, how to copy flat lookup tables via the Project Server managed object model, this time I will go a step forward, and show how you can copy lookup tables with hierarchy, like the RBS (resource breakdown structure) table.

The complexity of the task (comparing to the flat lookup tables) comes to the fact, that child entries are bound to their respective parent entries not via the IDs (like having a property called ParentId) but simply via the FullValue property. See the properties of the LookupEntry class in the documentation. For example (assuming the separator character used in the code mask is the period “.”), the parent entry of a child entry having its FullValue property like “Division.Subdivision.SubSubdivision” is the entry having a FullValue property like “Division.Subdivision”. Furthermore, the parent entry should be already included in the lookup table, as we inserts its child items, but it seems to be fulfilled by the standard Project Server behavior, as it returns entries in the correct order (parent entries first, their child entries next) for a simply request.

As in the case of the flat tables, we should copy the target entries one by one, by adding new LookupEntryCreationInformation instances to the existing Entries property (of type LooupEntryCollection) of the target lookup table.

Just to make our life a bit harder, in contrast to the LookupEntry class the LookupEntryCreationInformation class has a property ParentId, but no FullValue property at all. It has, however a Value property that you should to set to the value of the child entry, without the joined values of the parent entries. You should set the ParentId property to the value of the Id of the parent entry only if there is a parent entry, otherwise you mustn’t set this property (for example, to null). You can append the LookupEntryCreationInformation instance to the target LooupEntryCollection instance via Add method.

If you would like to get the Id of the parent entry, it would be nice to split the last tag from the FullValue of the current LookupEntry instance to first get the full value of the parent entry (like by splitting SubSubdvision from “Division.Subdivision.SubSubdivision” we would get “Division.Subdivision”, the FullValue of the parent entry), and make a query for the LookupEntry having the same value in the collection of already appended entries afterwards, like this:

parentId = ltTargetEntries.First(e => e.FullValue == parentFullValue).Id;

If you try that, you get the very same exception, that you receive if you try to access a property that you have not explicitly or implicitly requested in the client object model:

An unhandled exception of type ‘Microsoft.SharePoint.Client.PropertyOrFieldNotInitializedException’ occurred in Microsoft.SharePoint.Client.Runtime.dll
Additional information: The property or field ‘FullValue’ has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.

You could request the entire entry collection including the FullValue property of the entries after each update, but it would not be very efficient. Instead of this, we create a dictionary object of type Dictionary<Guid, string> to store a local mapping of the Id – FullValue pairs, and use this mapping to look up the parent entries.

This method assumes the target lookup table already exists, and both of the source and target tables have the same depth / code mask and the period character “.” as separator:

  1. private void CopyHierarchicalLookupTableValues(string sourcePwa, string sourceTable, string targetPwa, string targetTable)
  2. {
  3.     var separator = '.';
  4.  
  5.     LookupEntryCollection ltSourceEntries = null;
  6.     using (var pcSource = new ProjectContext(sourcePwa))
  7.     {
  8.         pcSource.Load(pcSource.LookupTables, lts => lts.Where(lt => lt.Name == sourceTable).Include(lt => lt.Entries.Include(e => e.FullValue, e => e.Id, e => e.SortIndex)));
  9.         pcSource.ExecuteQuery();
  10.  
  11.         if (pcSource.LookupTables.Any())
  12.         {
  13.             ltSourceEntries = pcSource.LookupTables.First().Entries;
  14.         }
  15.         else
  16.         {
  17.             Console.WriteLine("Source table '{0}' not found on PWA '{1}'", sourceTable, sourcePwa);
  18.         }
  19.     }
  20.  
  21.     if (ltSourceEntries != null)
  22.     {
  23.         using (var pcTarget = new ProjectContext(targetPwa))
  24.         {
  25.             pcTarget.Load(pcTarget.LookupTables, lts => lts.Where(lt => lt.Name == targetTable).Include(lt => lt.Name));
  26.             pcTarget.ExecuteQuery();
  27.  
  28.             // target table exist
  29.             if (pcTarget.LookupTables.Any())
  30.             {
  31.                 var ltTargetEntries = pcTarget.LookupTables.First().Entries;
  32.                 var localIdToFullValueMap = new Dictionary<Guid, string>();
  33.  
  34.                 // we cannot assign the FullValue property the value that includes the separator characters
  35.                 // to avoid LookupTableItemContainsSeparator = 11051 error
  36.                 // we should  split the value at separator characters and assign the last item to the Value property and if there is a parent item
  37.                 // set the ParentId property as well, see later
  38.                 // https://msdn.microsoft.com/en-us/library/office/ms508961.aspx
  39.                 ltSourceEntries.ToList().ForEach(lte =>
  40.                 {
  41.                     var value = lte.FullValue;
  42.                     Console.WriteLine("FullValue: '{0}'", value);
  43.                     Guid? parentId = null;
  44.                     var parentFullValue = string.Empty;
  45.  
  46.                     var lastIndexOfSeparator = value.LastIndexOf(separator);
  47.                     if (lastIndexOfSeparator > -1)
  48.                     {
  49.                         parentFullValue = value.Substring(0, lastIndexOfSeparator);
  50.                         value = value.Substring(lastIndexOfSeparator + 1);
  51.                         Console.WriteLine("value: '{0}'", value);
  52.                         Console.WriteLine("parentFullValue: '{0}'", parentFullValue);
  53.  
  54.                         // parent should have been already appended to avoid the error:
  55.                         // An unhandled exception of type 'Microsoft.SharePoint.Client.PropertyOrFieldNotInitializedException' occurred in Microsoft.SharePoint.Client.Runtime.dll
  56.                         // Additional information: The property or field 'FullValue' has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.
  57.                         //parentId = ltTargetEntries.First(e => e.FullValue == parentFullValue).Id;
  58.                         parentId = localIdToFullValueMap.First(e => e.Value == parentFullValue).Key;
  59.                         Console.WriteLine("parentId: '{0}'", parentId);
  60.  
  61.                     }
  62.  
  63.                     // instead creating a new ID, you can copy the existing ID
  64.                     // it works only if you copy the entries to another PWA instance,
  65.                     // and only if there wasn't already an entry with the same ID
  66.                     var id = Guid.NewGuid(); // lte.Id;
  67.  
  68.                     var leci = new LookupEntryCreationInformation
  69.                     {
  70.                         Id = id,
  71.                         Value = new LookupEntryValue { TextValue = value },
  72.                         SortIndex = lte.SortIndex
  73.                     };
  74.  
  75.                     Console.WriteLine("leci Id: '{0}', Value: '{1}'", leci.Id, leci.Value.TextValue);
  76.                     var fullValue = value;
  77.  
  78.                     // we should set the ParentId property only if the entry has really a parent
  79.                     // setting the ParentId property to null is not OK
  80.                     if (parentId.HasValue)
  81.                     {
  82.                         leci.ParentId = parentId.Value;
  83.                         fullValue = parentFullValue + separator + value;
  84.                     }
  85.  
  86.  
  87.                     localIdToFullValueMap.Add(leci.Id, fullValue);
  88.  
  89.                     ltTargetEntries.Add(leci);
  90.                     // if there are a lot of entries, it might be advisable to update and execute query after each of the entries
  91.                     // to avoid "The request uses too many resources" error message
  92.                     // https://pholpar.wordpress.com/2015/07/19/how-to-avoid-the-request-uses-too-many-resources-when-using-the-client-object-model-via-automated-batching-of-commands/
  93.                     // pcTarget.LookupTables.Update();
  94.                     // pcTarget.ExecuteQuery();
  95.                 });
  96.  
  97.                 pcTarget.LookupTables.Update();
  98.                 pcTarget.ExecuteQuery();
  99.             }
  100.             else
  101.             {
  102.                 Console.WriteLine("Target table '{0}' not found on PWA '{1}'", targetTable, targetPwa);
  103.             }
  104.         }
  105.     }
  106. }

The following call copies the lookup table RBS from one PWA instance to another one:

CopyHierarchicalLookupTableValues("http://YourProjectServer/PWA&quot;, "RBS", "http://AnotherProjectServer/PWA&quot;, "RBS");

The notes I made for the flat lookup tables apply for the hierarchical case as well:

If your lookup table has not a lot of entries, you can probably copy them in a single batch, using a single call to the ExecuteQuery method. Otherwise, if thee batch size exceeds the 2 MB limit, you might have an exception like “The request uses too many resources”. In this case I suggest you to invoke the ExecuteQuery method for each entry, or create an ExecuteQueryBatch method, as described in this post.

Theoretically, you could copy the entries with their ID, but technically it is not always an option. For example, if you would like to copy the entries within the same PWA instance, you can’t have two entries sharing the same IDs. Based on my experience, if you have already an entry with the same ID, and you would like to copy it into another lookup table, although no exception is thrown, the entry won’t be copied.

And one last additional note yet: Of course, you can copy not only hierarchical lookup tables, but flat lookup tables as well with this script.

Advertisements

June 20, 2017

Copying Flat Lookup Table Entries via the Managed Object Model

Assume you have in Project Server a flat lookup table (I mean a lookup table having a single level, without any hierarchy between the entries), and you would like to copy the entries to another (already existing!) lookup table, that may exist on the same or on another server / PWA instance. You can do the via the managed object model of Project Server, as demonstrated by the code below:

  1. private void CopyLookupTableValues(string sourcePwa, string sourceTable, string targetPwa, string targetTable)
  2. {
  3.     LookupEntryCollection ltSourceEntries = null;
  4.     using (var pcSource = new ProjectContext(sourcePwa))
  5.     {
  6.         pcSource.Load(pcSource.LookupTables, lts => lts.Where(lt => lt.Name == sourceTable).Include(lt => lt.Masks, lt => lt.Entries.Include(e => e.FullValue, e => e.Id, e => e.SortIndex)));
  7.         pcSource.ExecuteQuery();
  8.  
  9.         if (pcSource.LookupTables.Any())
  10.         {
  11.             ltSourceEntries = pcSource.LookupTables.First().Entries;
  12.         }
  13.         else
  14.         {
  15.             Console.WriteLine("Source table '{0}' not found on PWA '{1}'", sourceTable, sourcePwa);
  16.         }
  17.     }
  18.  
  19.     if (ltSourceEntries != null)
  20.     {
  21.         using (var pcTarget = new ProjectContext(targetPwa))
  22.         {
  23.             pcTarget.Load(pcTarget.LookupTables, lts => lts.Where(lt => lt.Name == targetTable).Include(lt => lt.Name));
  24.             pcTarget.ExecuteQuery();
  25.  
  26.             // target table exist
  27.             if (pcTarget.LookupTables.Any())
  28.             {
  29.                 var ltTargetEntries = pcTarget.LookupTables.First().Entries;
  30.  
  31.                 ltSourceEntries.ToList().ForEach(lte => {
  32.                     ltTargetEntries.Add(new LookupEntryCreationInformation
  33.                         {
  34.                             // instead creating a new ID, you can copy the existing ID
  35.                             // it works only if you copy the entries to another PWA instance,
  36.                             // and only if there wasn't already an entry with the same ID
  37.                             Id = Guid.NewGuid(), // lte.Id,
  38.                             Value = new LookupEntryValue { TextValue = lte.FullValue },
  39.                             SortIndex = lte.SortIndex
  40.                         });
  41.                     // if you have a lot of entries, it might be better to execute the query for each entries
  42.                     // to avoid 'The request uses too many resources' error
  43.                     // pcTarget.LookupTables.Update();
  44.                     // pcTarget.ExecuteQuery();
  45.                 });
  46.  
  47.                 pcTarget.LookupTables.Update();
  48.                 pcTarget.ExecuteQuery();
  49.             }
  50.             else
  51.             {
  52.                 Console.WriteLine("Target table '{0}' not found on PWA '{1}'", targetTable, targetPwa);
  53.             }
  54.         }
  55.     }
  56. }

The following call copies the lookup table Divisions from one PWA instance to another one:

CopyLookupTableValues("http://YourProjectServer/PWA&quot;, "Divisions", "http://AnotherProjectServer/PWA&quot;, "Divisions");

If your lookup table has not a lot of entries, you can probably copy them in a single batch, using a single call to the ExecuteQuery method. Otherwise, if thee batch size exceeds the 2 MB limit, you might have an exception like “The request uses too many resources”. In this case I suggest you to invoke the ExecuteQuery method for each entry, or create an ExecuteQueryBatch method, as described in this post.

Theoretically, you could copy the entries with their ID, but technically it is not always an option. For example, if you would like to copy the entries within the same PWA instance, you can’t have two entries sharing the same IDs. Based on my experience, if you have already an entry with the same ID, and you would like to copy it into another lookup table, although no exception is thrown, the entry won’t be copied.

The sample above works only for flat (non-hierarchical) lookup tables. You can copy hierarchical lookup tables (like RBS – resource breakdown structure) as well, but it requires a bit more coding, as I show you in the next post.

You can find further sample codes to manipulate Project Server enterprise custom fields and lookup table via the client object model in this older post.

June 13, 2017

A Quick and Dirty Solution to Create a Blank Site in SharePoint 2013

Filed under: Administration, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 18:36

Recently one of our clients requested a change in a custom-built SharePoint application. The original version of the application was built for SharePoint (MOSS) 2007 using Visual Studio 2008, then upgraded to SharePoint 2010 using Visual Studio 2010. Later the site was upgraded to SharePoint 2013, without any change in the code of the solution.

Now we had to create a replica of the site in our developer environment including the list data. We pulled a backup of the site using the Export-SPWeb cmdlet successfully in the productive system, and created a new team site in the development system as a target of the Import-SPWeb cmdlet. When executing the restore operation we’ve got this exception:

Import-SPWeb : Cannot import site. The exported site is based on the template STS#0 but the destination site is based on the template STS#1. You can import sites only into sites that are based on same template as the exported site.

image

In the error message STS#0 means the Team Site template, and STS#1 stands for the Blank Site template (see SharePoint site template IDs and their description here). Jason Warren suggests in this thread to use the -Force switch of the Import-SPWeb cmdlet to force the overwrite of the existing site, but we had the same issue even using this switch. How could we create a new web site using the Blank Site template? Solutions available using the server side, like using PowerShell or unhiding the Blank Site template are discussed in this thread. But what could we do, if we had no access to the server side, as this site template is not available on the web UI anymore?

We found a simply solution using only a single browser (Internet Explorer in our case) and the F12 Developer Tools.

Load the site creation page in the browser, then start the Developer Tools, and select the list of templates using the DOM Explorer.

 

image

Select an options in the select element, like the Team Site

image

… change its value attribute to STS#1

image

… and finally click the Create button on the web page to create the new blank site.

This solution is quick, but I consider it to be dirty, as users should perform it themselves and each time they need a blank site, so definitely not a user friendly option. But it might be handy if you need a simple way without access to the server side.

May 12, 2017

Getting a List of Checked-Out Projects from PowerShell via REST

Filed under: PowerShell, Project Server, REST — Tags: , , — Peter Holpar @ 05:22

We have an application running as a monthly scheduled batch process that updates enterprise fields of projects on our Project Server implementation based on values taken from various satellite systems. As a prerequisite, all affected projects should be checked-in. Projects checked-out to users won’t be updated. Of course, technically it would be possible to force check-in on those projects, but it was a business decision not to do that as we wanted to avoid data inconsistency by checking in a project that is not yet meant to be ready for that by the project manager.

Our application iterates through the projects, and if they are checked-in, it checks them out, updates the values, checks the project back and publishes it. If the project is checked-out, it sends a warning to the owner that the project was not updated due to its state. Occasionally project owners are doubting this warning, saying they are sure to check-in their projects, so I decided to create a sort-of report running just before starting the updating process to prove the list of projects left checked-out. It is something similar administrators already have on the Force Check-in Enterprise Objects page under PWA Settings.

image

Recently I wrote about how we can use PowerShell to create simple reports based on the data we query via the REST interface. This time I applied the very same technique to get the list of projects that are checked-out, including the name of the projects, the check-out description, checked-out time and the name and e-mail address of the user checked-out the project. The key was to assemble the REST query URL, including the $expand expression for the CheckedOutBy field.

$url = ‘http://YourProjectServerPWA/_api/ProjectServer/Projects?$expand=CheckedOutBy&$select=Name,CheckOutDescription,CheckedOutDate,CheckedOutBy/Title,CheckedOutBy/Email&$filter=IsCheckedOut’

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = ‘application/json;odata=verbose’

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()

$result = ConvertFrom-Json -InputObject $data

$result.d.results | % {
select -Input $_ -Prop `
    @{ Name=’Name’; Expression={$_.Name} },
    @{ Name=’User’; Expression={$_.CheckedOutBy.Title} },
    @{ Name=’EMail’; Expression={$_.CheckedOutBy.Email} },
    @{ Name=’Date’; Expression={[DateTime]::Parse($_.CheckedOutDate).ToString(‘g’)} },
    @{ Name=’Description’; Expression={$_.CheckOutDescription} }
    } | Export-Csv -Path CheckedOutProjects.csv -Delimiter ";" -Encoding UTF8 –NoTypeInformation

The result is a comma separated value (.csv) file, that one can open in Excel easily as well.

"The file name you specified is not valid or too long. Specify a different file name." Error When Using Redirection in IIS

Filed under: Explorer View, SP 2013, WebDAV — Tags: , , — Peter Holpar @ 05:18

Recently a user complained, that although he can create and copy files on a mapped drive on his Windows 7, linked to a SharePoint document library, the following error message was displayed to him in the Windows Explorer view of the library when he tried to rename any file:

The file name you specified is not valid or too long. Specify a different file name.

image

The error message was already known to us, it is typically a result of a special character or a space in the URL that is being encoded, and used in this encoded form to map the drive, or the mapped path might contain a trailing slash ‘/’, see threads here and here.

In this case there wasn’t any issue with the characters, but as we checked the mapping via the NET USE command, we noticed that the connection was listed as

\\YourServer\DocLib

although the SharePoint site was configured to use HTTPS (let’s say with URL https://YourServer), so the connection should have been actually:

\\YourServer@SSL\DocLib

On the SharePoint server (SharePoint 2013 on Window Server 2012 R2) we verified the configuration in Internet Information Services (IIS) Manager, and found the HTTPS binding all right.

There was however an other web site with the very same binding as the SharePoint site, but instead of HTTPS it was bound to HTTP (that means http://YourServer). The sole purpose of this web site was to forward any incoming HTTP request to the SharePoint site using HTTP Redirect with the settings below (see this page for configuration details):

Redirect requests to this destination option checked: https://YourServer$S$Q

Redirect all requests to exact destination (instead of relative to destination) option checked

image

The solution was in this case so simple as to disconnect the mapped folder and to reconnect it using HTTPS:

NET USE Y: "https://YourServer/DocLib&quot;

Conclusion of the story: Redirection apparently works with WebDAV as well, however renaming files fails in this case.

Disabling SharePoint Alerts Temporarily for a Specific SharePoint List

Filed under: Alerts, PowerShell, SP 2013 — Tags: , , — Peter Holpar @ 05:16

Recently we extended a SharePoint list in our test environment with a few new fields. Users have been complained that they received immediate notifications due to their existing subscriptions on the list. To avoid the same situation in the live system, we decided to temporarily deactivate the alerts for the time of the list field extension. I find a solution for that in this thread, implemented in C#. Although I like C#, for administrative tasks like this one I prefer using PowerShell, so I transformed the code into a few-line script:

$url = ‘http://YourSharePoint/WebSite&#8217;
$listTitle = ‘Title of your list’
$targetStatus = [Microsoft.SharePoint.SPAlertStatus]::Off # or [Microsoft.SharePoint.SPAlertStatus]::On

$web = Get-SPWeb $url
$list = $web.Lists[$listTitle]

# to query the current status of the alerts only:
# $web.Alerts | ? { $_.List.ID -eq $list.ID } | % { $_.Status }

$web.Alerts | ? { $_.List.ID -eq $list.ID } | % {
  $_.Status = $targetStatus
  $_.Update()
}

After implementing the changes, you can reactivate the alerts (in this case you should use the value [Microsoft.SharePoint.SPAlertStatus]::On in $targetStatus), however, you should wait a few minutes, as the immediate alerts are sent every 5 minutes by default (see screenshot below). If you turn the alerts on before the next run of the job, your previous change to inactivate the notifications has no effect and the alerts would be sent to the user.

image

By letting the Immediate Alerts job to have a run after you make the changes in the list, the notification events waiting in the event queue will be purged and not included in the upcoming immediate alerts. They will be however included in the daily and weekly summaries, but that was not an issue in our case.

If you don’t want to wait for the next scheduled run, you can start the job from the UI (see Run Now button above), or via script like this:

Get-SPTimerJob | ? { $_.Name -eq "job-immediate-alerts"} | % { Start-SPTimerJob $_ }

March 29, 2017

Working with the REST / OData Interface from PowerShell

Filed under: OData, PowerShell, REST, SP 2013 — Tags: , , , — Peter Holpar @ 20:56

If you follow my blog you might already know that I am not a big fan of the REST / OData interface. I prefer using the client object model. However there are cases, when REST provides a simple (or even the only available) solution.

For example, we are working a lot with PowerShell. If you are working with SharePoint on the client side at a customer, and you are not allowed to install / download / copy the assemblies for the managed client object model (CSOM), you have a problem.

Some possible reasons (you should know, that the SharePoint Server 2013 Client Components SDK is available to download as an .msi, or you can get the assemblies directly from an on-premise SharePoint installation):

  • You might have no internet access, so you cannot download anything from the web.
  • If you happen to have internet access, you are typically not allowed to install such things without administrator permissions on the PC. It’s quite rare case, if you or the business user you are working with has this permission.
  • You have no direct access on the SharePoint server, so you cannot copy the assemblies from it.
  • You are not allowed to use your own memory stick (or other storage device) to copy the assemblies from it.
  • Even if there is no technical barrier, company policies might still prohibit you using external software components like the CSOM assemblies.

In this case, using the REST interface is a reasonable choice. You can have a quick overview of the REST-based list operations here.

The main questions I try to answer in this post:

  • Which object should I use to send the request?
  • How to authenticate my request?
  • How to build up the payload for the request?

First of all, I suggest you to read this post to learn some possible pitfalls when working with REST URLs from PowerShell and how to avoid them with escaping.

Reading data with the SharePoint REST interface

Reading data with a GET request

Sending a GET request for a REST-based service in PowerShell is not really a challenge, might you think, and you are right, it is really straightforward most of the cases. But take the following example, listing the Id and Title fields of items in a list:

$listTitle = "YourList"
$url = "http://YourSharePoint/_api/Web/Lists/GetByTitle(&#8216;$listTitle‘)/Items?`$select=Id,Title"

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = ‘application/json;odata=verbose’

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
# ConvertFrom-Json : Cannot convert the Json string because a dictionary converted from it contains duplicated keys ‘Id’ and ‘ID’.
#$response = $reader.ReadToEnd()
$response = $reader.ReadToEnd() -creplace ‘"ID":’, ‘"DummyId":’

$result = ConvertFrom-Json -InputObject $response
$result.d.results | select Id, Title

If you would use

$response = $reader.ReadToEnd()

instead of

$response = $reader.ReadToEnd() -creplace ‘"ID":’, ‘"DummyId":’

then you became this exception, when trying to convert the JSON response:

ConvertFrom-Json : Cannot convert the Json string because a dictionary converted from it contains duplicated keys ‘Id’ and ‘ID’.

The reason, that the JSON response of the server contains the fields Id and ID. JSON is case-sensitive, but PowerShell is not, so it is an issue if you want to convert the JSON response to a PowerShell object. You can read more about it in this post, although I don’t like the solution proposed there. Although it really helps to avoid the error, but it uses the case insensitive replace operator instead of the case sensitive creplace, so it converts both fields into a dummy field. PowerShell seems to have no problem with the duplicated properties.

Instead of using a System.Net.WebRequest object, we can achieve a shorter version using the Invoke-RestMethod cmdlet. Note, that we don’t select and display the Id property in this case to avoid complications. See my comments about that in the next section discussing the POST request.

$listTitle = "YourList"
$url = "http://YourSharePoint/_api/Web/Lists/GetByTitle(&#8216;$listTitle‘)/Items?`$select=Title"
$headers = @{ ‘Accept’ = ‘application/json; odata=verbose’}
$result = Invoke-RestMethod -Uri $url -Method Get -Headers $headers -UseDefaultCredentials
$result.d.results | select Title

Reading data with a POST request

There are cases when you have to use the POST method instead of GET to read some data from SharePoint. For example, if you need to filter the items via a CAML query. In the following example I show you how to query the file names all documents in a library recursively that are older than a threshold value:

$listTitle = "YourDocuments"
$offsetDays = -30

$urlBase = "http://YourSharePointSite/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)/GetItems?`$select=FileLeafRef"

$viewXml = "<View Scope=’Recursive’><ViewFields><FieldRef Name=’Created’/><FieldRef Name=’FileLeafRef’/></ViewFields><Query><Where><Lt><FieldRef Name=’Created’ /><Value Type=’DateTime’><Today OffsetDays=’$offsetDays’ /></Value></Lt></Where></Query></View>"

$queryPayload = @{ 
                   ‘query’ = @{
                          ‘__metadata’ = @{ ‘type’ = ‘SP.CamlQuery’ };                      
                          ‘ViewXml’ = $viewXml
                   }
                 } | ConvertTo-Json

# authentication
$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

# the actual request
$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘Accept’ = ‘application/json; odata=verbose’ }
$result = Invoke-RestMethod -Uri $url -Method Post -Body $queryPayload -ContentType ‘application/json; odata=verbose’ -Headers $headers –UseDefaultCredentials

# displaying results
$result.d.results | select FileLeafRef

Just for the case of comparison I include the same payload in JavaScript format:

var queryPayload = {
                     ‘query’ : {
                        
‘__metadata’ : { ‘type’ : ‘SP.CamlQuery’ },
                         ‘ViewXml’ : viewXml
                    
}
                   };

As you can see, these are the most relevant differences in the format we need in PowerShell:

  • We use an equal sign ( = ) instead of  ( : ) to separate the name and its value.
  • We use a semicolon ( ; ) instead of the comma ( , ) to separate object fields.
  • We need a leading at sign ( @ ) before the curly braces ( { ).

The Invoke-RestMethod tries to automatically convert the response to the corresponding object based on the content type of the response. If it is an XML response (see the authentication part above) then the result will be a XmlDocument. If it is a JSON response then the result will be a PSCustomObject representing the structure of the response. However, if the response can not be converted, it remains a single String.

For example, if we don’t limit the fields we need in response via the $select query option:

$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)/GetItems"

then the response includes the fields Id and ID again. In this case we should remove one of these fields using the technique illustrated above with the simple GET request, before we try to convert the response via the ConvertFrom-Json cmdlet.

Note: If you still use PowerShell v3.0 you get this error message when you invoke Invoke-RestMethod setting the Accept header:

Invoke-RestMethod : The ‘Accept’ header must be modified using the appropriate property or method.
Parameter name: name

So if it is possible, you should consider upgrading to PowerShell v4.0. Otherwise, you can use the workaround suggested in this forum thread, where you can read more about the issue as well.

If you are not sure, which version you have, you can use $PSVersionTable.PSVersion to query the version number, or another option as suggested here.

Creating objects

In this case we send a request with the POST method to the server. The following code snippet shows, how you can create a new custom list:

$listTitle = "YourList"

$urlBase = "http://YourSharePoint/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists"

$queryPayload = @{ 
                    ‘__metadata’ = @{ ‘type’ = ‘SP.List’ }; ‘AllowContentTypes’ = $true; ‘BaseTemplate’ = 100;
                    ‘ContentTypesEnabled’ = $true; ‘Description’ = ‘Your list description’; ‘Title’ = $listTitle                      
    } | ConvertTo-Json

$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘Accept’ = ‘application/json; odata=verbose’ }

$result = Invoke-RestMethod -Uri $url -Method Post -Body $queryPayload -ContentType ‘application/json; odata=verbose’ -Headers $headers –UseDefaultCredentials

The response we receive in the $result variable contains the properties of the list we just created. For example, the Id (GUID) of the list is available as $result.d.Id.

Updating objects

In this case we send a request with the POST method to the server and set the X-HTTP-Method header to MERGE. The following code snippet shows, how to change the title of the list we created in the previous step:

$listTitle = "YourList"

$urlBase = "http://YourSharePoint/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)"

$queryPayload = @{ 
                    ‘__metadata’ = @{ ‘type’ = ‘SP.List’ }; ‘Title’ = ‘YourListNewTitle’                      
    } | ConvertTo-Json

$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘Accept’ = ‘application/json; odata=verbose’; ‘IF-MATCH’ = ‘*‘; ‘X-HTTP-Method’ = ‘MERGE’ }

$result = Invoke-RestMethod -Uri $url -Method Post -Body $queryPayload -ContentType ‘application/json; odata=verbose’ -Headers $headers –UseDefaultCredentials

Deleting objects

In this case we send a request with the POST method to the server and set the X-HTTP-Method header to DELETE. The following code snippet shows, how you can delete a list item:

$listTitle = "YourList"

$urlBase = "http://YourSharePoint/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)/Items(1)"

# authentication
$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

# the actual request
$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘IF-MATCH’ = ‘*’; ‘X-HTTP-Method’ = ‘DELETE’ }
$result = Invoke-RestMethod -Uri $url -Method Post -Headers $headers -UseDefaultCredentials

Note: Although the documentation states, that “in the case of recyclable objects, such as lists, files, and list items, this results in a Recycle operation”, based on my tests it is false, as the objects got really deleted.

Final Note: This one applies to all of the operations discussed in the post. If the SharePoint site you are working with available via HTTPS and there is an issue with the certificate, you can turn off the certificate validation, although it is not recommended in a production environment. You should include this line in your code before making any web requests:

[System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }

How to Create a Simple “Printer Friendly” Display Form

Filed under: JavaScript, SP 2013, SPD — Tags: , , — Peter Holpar @ 05:44

Our users needed a simply way to print items in SharePoint, that mean only item properties without any ribbon or navigation elements.

Assuming you have a list ‘YourCustomList’ available at the URL http://YourSharePoint/Lists/YourCustomList, the standard display form of a list item (in this case the one with ID 1) would be:

http://YourSharePoint/Lists/YourCustomList/DispForm.aspx?ID=1

This page contains however the site navigation elements and the ribbon as well. Appending the query string parameter IsDlg=1 (like http://YourSharePoint/Lists/YourCustomList/DispForm.aspx?ID=1&IsDlg=1) helps to remove the navigation parts, but the ribbon remains.

Our solution to remove the ribbon was to add this very simple JavaScript block via a Script Editor Web Part to the display form page (DispForm.aspx). I suggest to insert the Script Editor Web Part after the existing List Form Web Part on the page.

// http://stackoverflow.com/questions/901115/how-can-i-get-query-string-values-in-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, ” “));
}

if (getParameterByName(‘IsPrint’) == ‘1’) {
  var globalNavBox = document.getElementById(‘globalNavBox’);
  if (globalNavBox) {
    globalNavBox.style.display = ‘none’;
  }
}

Note: You can switch the display form to page edit mode via the ToolPaneView=2 query string parameter (see more useful hints here), for example:

http://YourSharePoint/Lists/YourCustomList/DispForm.aspx?ToolPaneView=2

The main part of the solution, the getParameterByName method was borrowed from this forum thread. It helps to get a query string parameter value by its name. Using this method we check, if there is a parameter IsPrint, and if it is there having a value of 1, the we make the globalNavBox HTML element, that is actually a placeholder for the ribbon, invisible.

It means, if we call the display form by the URL http://YourSharePoint/Lists/YourCustomList/DispForm.aspx?ID=1&IsDlg=1&IsPrint=1 then there is no ribbon or other navigation element on the page. Using this URL format you can even add a custom action, for example, a new button to the ribbon or an edit control block (ECB) menu-item (see example later in the post), or refer a print form directly from a document or from an e-mail.

In the above case, the users can then print the page via right-clicking with the mouse and selecting Print… from the pop-up menu. Alternatively we could inject a Print button on the form itself. This technique will be demonstrated below.

In this case we use JQuery, and our JavaScript code is a bit more complex, so we store it into a separate file in the Site Assets library of the site, and refer only the files in the Script Editor Web Part:

/font/ema%20href=
http://../../SiteAssets/js/printForm.js

Our JavaScript code (printForm.js) would be in this case:

// http://stackoverflow.com/questions/901115/how-can-i-get-query-string-values-in-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, " "));
}

// https://davidwalsh.name/add-rules-stylesheets
var sheet = (function() {
    // Create the <style> tag
    var style = document.createElement("style");

    // Add a media (and/or media query) here if you’d like!
    style.setAttribute("media", "print")

    // WebKit hack 😦
    style.appendChild(document.createTextNode(""));

    // Add the <style> element to the page
    document.head.appendChild(style);

    return style.sheet;
})();

$(document).ready(function() {
  if (getParameterByName(‘IsPrint’) == ‘1’) {
    sheet.insertRule("#globalNavBox { display:none; }", 0);
    sheet.insertRule("input { display:none; }", 0);

    $(‘input[value="Close"]’).closest(‘tr’).closest(‘tr’).append(‘<td class="ms-toolbar" nowrap="nowrap"><table width="100%" cellspacing="0" cellpadding="0"><tbody><tr><td width="100%" align="right" nowrap="nowrap"><input class="ms-ButtonHeightWidth" accesskey="P" onclick="window.print();return false;" type="button" value="Print"></input></td></tr></tbody></table></td><td class="ms-separator">&nbsp;</td>’);
  }
});

In this case we inject a Print button dynamically and don’t hide the ribbon, but use the technique illustrated here to add CSS styles to hide UI elements (ribbon and the buttons) only in the printed version via the media attribute of the style sheet.

Note: The above code is for a SharePoint site with English UI. Since the value of the Close button is language dependent, you should change the code if you have a SharePoint site with another culture settings. For example, in a German version the JQuery selector would be:

input[value="Schließen"]

In this case you should have to save the script using Unicode encoding instead of ANSI to prohibit the loss of special character ‘ß’.

Finally, I show you how to create a shortcut to the form in the ECB menu using SharePoint Designer (SPD).

Select your list in SPD, and from the Custom Actions menu select the List Item Menu.

image

Set the fields as illustrated below:

image

The full value of the Navigate to URL field:

javascript:OpenPopUpPageWithTitle(ctx.displayFormUrl + ‘&ID={ItemId}&IsDlg=1&IsPrint=1′, RefreshOnDialogClose, 600, 400,’Print Item’)

We use the OpenPopUpPageWithTitle method and a custom made URL to show the printer friendly display form with the necessary query string parameters. See this article on more details of the OpenPopUpPageWithTitle method.

After saving the custom action, you can test it in your list:

image

This is the customized form having the extra Print button on it:

image

And that is the outcome of the print:

image

March 26, 2017

Generating Pseudo GUIDs for Your Project Server Entities

Filed under: PowerShell, Project Server, Tips & Tricks — Tags: , , — Peter Holpar @ 06:24

As you might have known, since the version 2013, Project Server utilizes pseudo-GUIDs to improve Project Server performance. These ones has the format of a “classical” GUID, but actually generated sequentially. As Microsoft states in this TechNet article:

"We handle GUIDs a little better in Project Server 2013 – and in many places they are sequential GUIDs which cause less index fragmentation"

This topic is quite good described in the Project Conference 2014 presentation Project Worst Practice – Learning from other peoples mistakes by Brian Smith. See the video recording between 6:08-13:54, or the slides 10-14.

One of the main components of the pseudo-GUID generation is the NewSequentialUid method of the Microsoft.Office.Project.Server.Library.PSUtility class:

public static Guid NewSequentialUid() 

  Guid guid; 
  if (NativeMethods.UuidCreateSequential(out guid) != 0) 
    return Guid.NewGuid(); 
  byte[] b = guid.ToByteArray(); 
  Array.Reverse((Array) b, 0, 4); 
  Array.Reverse((Array) b, 4, 2); 
  Array.Reverse((Array) b, 6, 2); 
  return new Guid(b); 
}

So if you want to use the same kind of pseudo-GUIDs for your own custom entities you create from code, you can get the IDs by invoking the method (for example, via PowerShell). The code sample below illustrates, how to get a single ID, or a batch of  IDs (in this case, 5 of them):

# load the necessary assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Project.Shared")
# generate a single sequential ID
[Microsoft.Office.Project.Server.Library.PSUtility]::NewSequentialUid()
# or generate a range of sequential IDs, in this case, five of them
(1..5) | % { [Microsoft.Office.Project.Server.Library.PSUtility]::NewSequentialUid().Guid }

March 25, 2017

Microsoft.Workflow.Client.InvalidRequestException: Failed to query the OAuth S2S metadata endpoint – The remote server returned an error: (400) Bad Request

Filed under: Certificates, SP 2013, Workflow — Tags: , , — Peter Holpar @ 21:11

Recently we installed a new Workflow Manager farm (a single-server one) on the front-end server of one of our SharePoint farms.

I wanted to register the Workflow Manager for a web application in the SharePoint farm via the PowerShell cmdlet:

Register-SPWorkflowService -SPSite https://YourSharePointSite -WorkflowHostUri https://YourWorkflowManagerServer:12290 -ScopeName YourScope –Force

But I received an error like this one:

Register-SPWorkflowService : Failed to query the OAuth S2S metadata endpoint
at URI ‘https://YourSharePointSite/_layouts/15/metadata/json/1&#8217;.
Error details: ‘An error occurred while sending the request’. HTTP headers received from the server – ActivityId:
d10c4cbb-bde4-4040-b09f-1ace1491dc87. NodeId: YourWFNode. Scope: /YourScope.
Client ActivityId : b89c2ff9-8560-458e-9ea2-31ec6c8fde36.
At line:1 char:1
+ Register-SPWorkflowService -SPSite https://YourSharePointSite/&#160; -W …

In the Event Viewer (Application and Services Logs / Microsoft-Workflow / Operational) we had this error:

image

Failed to query the remote endpoint for the S2S metadata document. Details: System.Net.Http.HttpRequestException: An error occurred while sending the request. —> System.Net.WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. —> System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.
   at System.Net.TlsStream.EndWrite(IAsyncResult asyncResult)
   at System.Net.ConnectStream.WriteHeadersCallback(IAsyncResult ar)
   — End of inner exception stack trace —
   at System.Net.HttpWebRequest.EndGetResponse(IAsyncResult asyncResult)
   at System.Net.Http.HttpClientHandler.GetResponseCallback(IAsyncResult ar)
   — End of inner exception stack trace —

In the ULS logs we had this error message:

Microsoft.Workflow.Client.InvalidRequestException: Failed to query the OAuth S2S metadata endpoint at URI ‘https://YourSharePointSite/_layouts/15/metadata/json/1&#8217;. Error details: ‘An error occurred while sending the request.’. HTTP headers received from the server – ActivityId: d10c4cbb-bde4-4040-b09f-1ace1491dc87. NodeId: YourWFNode. Scope: /YourScope. Client ActivityId : b89c2ff9-8560-458e-9ea2-31ec6c8fde36. —> System.Net.WebException: The remote server returned an error: (400) Bad Request.     at Microsoft.Workflow.Common.AsyncResult.End[TAsyncResult](IAsyncResult result)     at Microsoft.Workflow.Client.HttpGetResponseAsyncResult`1.End(IAsyncResult result)     at Microsoft.Workflow.Client.ClientHelpers.SendRequest[T](HttpWebRequest request, T content)     — End of inner exceptio…

The SharePoint site https://YourSharePointSite and the Workflow Manager endpoint URL https://YourWorkflowManagerServer:12290 were both available without any issue (e.g. no problem with the certificate too), on both nodes (front-end and application servers) of the SharePoint farm, as well as from client computers.

The articles I found about the issue (like this one or this one) explained the problem with the reason, that the SharePoint endpoint URL (in our case ‘https://YourSharePointSite/_layouts/15/metadata/json/1‘) is not accessible, probably because of a name resolution issue. In our case that was definitely not the issue, because if I switched the SharePoint URL from HTTPS to HTTP (via changing the Alternate Access Settings for the site + bindings in IIS manager), I was able to run the registration script successfully:

Register-SPWorkflowService -SPSite http://YourSharePointSite -WorkflowHostUri https://YourWorkflowManagerServer:12290 -ScopeName YourScope –Force -AllowOAuthHttp

After switching back the URL to HTTPS we had the problem again.

My next assumption was, that the service account for the Workflow Manager does not have the root certificate of the SSL certificate under the Trusted Root Certification Authorities.

So I’ve started the Microsoft Management Console (mmc.exe) and added the Certificates snap-in for the service account of the Workflow Manager Backend service:

image

image

image

I found that the list of Trusted Root Certification Authorities contains the root certificate of the SSL, so it could not be a problem either.

As next step, I’ve logged in on the Workflow Manager server (that is the front-end server of the SharePoint farm) the using the Workflow Manager service account to test the connection to the SharePoint site interactively via Internet Explorer. In this case I was faced with the problem, that the SharePoint site https://YourSharePointSite has a certificate warning. As I opened the certificate for the site in Internet Explorer, I saw only the very last entry in the certificate chain (for example, the entry for YourSharePointSite), but none of the certificates above. I’ve found it either, that the account has configured not to use a proxy server. I enabled the proxy connection, then restarted Internet Explorer, and voila no more issues with the certificate. I was able to register the Workflow Manager as well. I don’t exactly know, what was the problem, but I assume, the certificate revocation list was not available without the proxy, and that prohibited the certificate validation necessary for the registration of the Workflow Manager.

« Newer PostsOlder Posts »

Blog at WordPress.com.