Second Life of a Hungarian SharePoint Geek

July 20, 2017

How to Export a SharePoint List View to Excel Automatically Using PowerShell

Filed under: PowerShell, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 21:17

Note: This post is actually only a minor modification of the post I wrote recently about the URL of the Edit View page.

We can easily export the content of a SharePoint List View via the UI, simply by clicking the Export to Excel button on the ribbon:

image

You can achieve that automatically as well, for example from PowerShell.

Note: In the text below I describe the solution for a situation if you work locally on the server, but it is possible to apply the same technique to a remote solution as well, one should only transfer the code to the Managed Client Object Model.

Assume you have a list called YourList in a SharePoint site with URL http://YourSharePoint/Web/SubWeb.

It is easy to find out (for example, by monitoring the network traffic by Fiddler) that the URL generated when you click the Export to Excel button is like this:

http://YourSharePoint/Web/SubWeb/_vti_bin/owssvr.dll?CS=65001&Using=_layouts/15/query.iqy&List=%7B5315A0C9%2DAA6A%2D4598%2DA1D4%2D99B1BBCBF8C7%7D&View=%7B8E449E17%2D593C%2D4218%2DA4A4%2D43A8B47382BC%7D&RootFolder=%2FWeb%2FSubWeb%2FLists%2FYourList&CacheControl=1

The values of the List and View query string parameters are the encoded IDs of your list and list view respectively.

The following code generates the same URL from PowerShell:

$web = Get-SPWeb http://YourSharePoint/Web/SubWeb
$list = $web.Lists[‘YourList’]

# get the default view of the list
$view = $list.DefaultView
# or get an arbitrary view by its name
# $view = $list.Views[‘All Items’]
$viewId = $view.ID

function EscapeGuid($guid)
{
  return "{$guid}".ToUpper().Replace(‘-‘, ‘%2D’).Replace(‘{‘, ‘%7B’).Replace(‘}’, ‘%7D’)
}

$escapedListId = EscapeGuid $list.ID
$escapedViewId = EscapeGuid $view.ID
$escapedRootFolder = $list.RootFolder.ServerRelativeUrl.Replace(‘/’, ‘%2F’)

$url = $web.Url + "/_vti_bin/owssvr.dll?CS=65001&Using=_layouts/15/query.iqy&List=$escapedListId&View=$escapedViewId&RootFolder=$escapedRootFolder&CacheControl=1"

The URL above is actually no URL for the data or its schema, it’s a URL for a descriptor file (with the extension .iqy, see more about that here), that contains the URL for that list data and its schema.

The content of an .iqy file looks like this (you can capture it by Fiddler as well, or have a look at the content of the file we saved in our script further below) :

WEB
1
http://YourSharePoint/Web/SubWeb/_vti_bin/_vti_bin/owssvr.dll?XMLDATA=1&List={5315A0C9-AA6A-4598-A1D4-99B1BBCBF8C7}&View={8E449E17-593C-4218-A4A4-43A8B47382BC}&RowLimit=0&RootFolder=%2fWeb%2fSubWeb%2fLists%2fYourList

Selection={5315A0C9-AA6A-4598-A1D4-99B1BBCBF8C7}-{8E449E17-593C-4218-A4A4-43A8B47382BC}
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=http://YourSharePoint/Web/SubWeb/_vti_bin
SharePointListView={8E449E17-593C-4218-A4A4-43A8B47382BC}
SharePointListName={5315A0C9-AA6A-4598-A1D4-99B1BBCBF8C7}
RootFolder=/Web/SubWeb/Lists/YourList

The URL we have in line 3 refers to the endpoint that returns the data schema and the data itself.. Based on this information, Excel can import and display the data of the list view.

Let’s save the file from the URL of the .icq file we have already from the first script, and start Excel to open the list view data. The script below assumes the extension .iqy is associated with Excel in your system:

$path = "C:\temp\owssvr.iqy"

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = "text/html, application/xhtml+xml, */*"

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

$writer = [System.IO.StreamWriter] $path
$writer.WriteLine($data)
$writer.Close()

# the .iqy file will be opened by Excel
Invoke-Expression $path
# optionally delete the file
# Remove-Item $path

Bulk Deletion of Events from a SharePoint Calendar

Filed under: Calendar, SP 2013 — Tags: , — Peter Holpar @ 21:15

Assume you have a SharePoint Calendar with thousands of events, including recurring events, and recurring event exceptions. These ones are the result of aggregating events from several years, most of them are no more relevant.

Problems

If the number of events is over the List View Threshold limit, your users will not be able to access the All Events view (see sample screenshot below taken from another calendar including a standard and a recurring event, a deleted recurring event and a recurrence exception),

image

they receive instead that an error message:

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

They won’t be able even to delete the calendar, either from the Site Content page (they get a warning like “We’re sorry, we had some trouble removing this. You can try again from the settings page.”):

image

image

or from the Settings page of the list (same error as above):

image

Of course, you as an administrator, can increase the List View Threshold limit, delete the list via PowerShell (see below), or even from the web UI if you log in using the farm account, but it takes more time for you and for your users.

$web = Get-SPWeb http://YourSharePoint/Web/SubWeb
$calendar= $web.lists["YourCalendar"]
$calendar.Delete()

Even if the number of events stays below the List View Threshold limit, the users might have experience performance problems.

Solution

Note, that we did not want to delete the list, the limitation with the deletion was only one of the examples. Instead of deletion, we wanted to drastically reduce the number of events by deleting all events that are no more relevant in the current year. That means, deleting all single events that were finished earlier than this year, and repeating events whose repetitions is finished earlier than the current year.

Based on my experience the deletion of such high number of items is not very performant, so I decided to delete the items in batches. There are several examples for that on the web, including solutions for C# (see here or here) or for PowerShell (see here) or for both of them (see this one). I planned to use PowerShell, but the examples I found were typically simple translations of the C# version, without using the structures and features available in PowerShell. Even worse, both the C# and PowerShell implementations out there have a serious limitation: they either output all of the results returned by the ProcessBatchData method, or they display no information at all. It might be OK as long as you successfully delete all of the items, but if you have any problem there, I wish you a good luck to find any usable information about it, if there are really of thousands of items in your list. So I created my own PowerShell implementation using the samples available. Note, that because I don’t want to wait for a feedback about the success of the deletion until all items are processed (we had over some 10k items there), I’m deleting the items in smaller batches, in the code below it is 1000 items / batch.

To get the items I should delete, I used the following CAML query:

<Where>
    <Lt>
    <FieldRef Name=’EndDate’ />
    <Value Type=’DateTime’>2017-01-01 00:00:00</Value>
    </Lt>
</Where>

Here is the full code of the first version of my script, see how I process the results by splitting the response XML to successfully deleted items an failures, and displaying only the latter ones:

  1. # modify the $url and $listTitle values to match your configuration
  2. $url = "http://YourSharePoint/Web/SubWeb&quot;
  3. $listTitle = "YourCalendar"
  4.  
  5. $web = Get-SPWeb $url
  6. $list = $web.Lists[$listTitle]
  7.  
  8. $query = New-Object Microsoft.SharePoint.SPQuery
  9.   $query.Query =
  10.           "<Where>
  11.              <Lt>
  12.                <FieldRef Name='EndDate' />
  13.                <Value Type='DateTime'>2017-01-01 00:00:00</Value>
  14.              </Lt>
  15.            </Where>"
  16. $query.ViewFields = "<FieldRef Name='ID' />"
  17. $query.ViewFieldsOnly = $true
  18. $query.RowLimit = 1000;
  19.  
  20. $itemCount = 0
  21. $listId = $list.ID
  22.  
  23. do
  24. {
  25.     $listItems = $list.GetItems($query)
  26.     $itemIds = $listItems | % { [String]$_.ID }
  27.     [System.Text.StringBuilder]$batchXml = New-Object "System.Text.StringBuilder"
  28.     [Void]$batchXml.Append("<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>")
  29.     $itemIds | % {
  30.       $itemId = $_
  31.       [Void]$batchXml.Append("<Method ID=`"$itemId`"><SetList>$listId</SetList><SetVar Name=`"ID`">$itemId</SetVar><SetVar Name=`"Cmd`">Delete</SetVar></Method>")
  32.     }
  33.     [Void]$batchXml.Append("</Batch>")
  34.     Write-Host Deleting next $listItems.Count entries…
  35.  
  36.     $result = [Xml]$web.ProcessBatchData($batchXml.ToString())
  37.     $success = @(Select-Xml -Xml $result -XPath '//Results/Result[@Code="0"]')
  38.     $failure = @(Select-Xml -Xml $result -XPath '//Results/Result[@Code!="0"]')
  39.     $itemCount += $success.Count
  40.     # list errors
  41.     $failure | % {
  42.         $errorNode = $_.Node
  43.         Write-Host Error deleting entry with ID $errorNode.ID error code: $errorNode.Code error text: $errorNode.ErrorText
  44.     }
  45. }
  46. while ($listItems.ListItemCollectionPosition -ne $null)
  47.  
  48. Write-Host Summary: $itemCount entries deleted

Although a few events have been really deleted, I started to get the following errors very quickly, and no deletion was performed after that:

image

The error messages above correspond to the following XML response:

<Result ID="" Code="-2147023673">
<ErrorText>The operation failed because an unexpected error occurred. (Result Code: 0x800704c7)</ErrorText></Result>

In the ULS logs I found a lot of such entries:

Batchmgr Method error. Errorcode: 0x1c32cbb0. Error message: The operation failed because an unexpected error occurred. (Result Code: 0x800704c7)

and at the top of the a single entry like this:

Batchmgr Method error. Errorcode: 0x1c32cbb0. Error message: Item does not exist.  The page you selected contains an item that does not exist.  It may have been deleted by another user.

After that, I’ve tried to delete the items using the same CAML query, hoping that I get more information about the failure. I used this script:

  1. # modify the $url and $listTitle values to match your configuration
  2. $url = "http://YourSharePoint/Web/SubWeb&quot;
  3. $listTitle = "YourCalendar"
  4.  
  5. $web = Get-SPWeb $url
  6. $list = $web.Lists[$listTitle]
  7.  
  8. $query = New-Object Microsoft.SharePoint.SPQuery
  9.   $query.Query =
  10.           "<Where>
  11.              <Lt>
  12.                <FieldRef Name='EndDate' />
  13.                <Value Type='DateTime'>2017-01-01 00:00:00</Value>
  14.              </Lt>
  15.            </Where>"
  16. $query.ViewFields = "<FieldRef Name='ID' />"
  17. $query.ViewFieldsOnly = $true
  18. $listItems = $list.GetItems($query)
  19. $itemIDsToDelete = $listItems | % { $_["ID"] }
  20. $totalCount = $itemIDsToDelete.Count   
  21. Write-Host $totalCount item will be deleted
  22. $counter = 1
  23. $itemIDsToDelete | % {
  24.   $itemID = $_
  25.   Write-Host Deleting item with ID $itemID `($counter / $totalCount`)
  26.   $item = $list.GetItemById($itemID)
  27.   $item.Delete()
  28.   $counter++
  29. }

I’ve got similar error messages as earlier for specific items, but the deletion went further than:

Exception calling "Delete" with "0" argument(s): "Item does not exist.
The page you selected contains an item that does not exist.  It may have been
deleted by another user."
At line:19 char:3
+   $item.Delete()
+   ~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException 
    + FullyQualifiedErrorId : SPException

image

I stopped the script after a while, and checked the ULS logs:

Item does not exist.  The page you selected contains an item that does not exist.  It may have been deleted by another user.<nativehr>0x81020016</nativehr><nativestack></nativestack>
SPRequest.GetListItemDataWithCallback2: UserPrincipalName=i:0).w|s-1-5-21-3634847118-1559816030-2180994487-3194, AppPrincipalName= ,pSqlClient=<null> ,bstrUrl=http://YourSharePoint/Web/SubWeb ,bstrListName={2A67D5C3-7AC1-4F3E-AB47-2051CDB94237} ,bstrViewName=<null> ,bstrViewXml=<View Scope="RecursiveAll" ModerationType="Moderator"><Query><Where><Eq><FieldRef Name="ID"></FieldRef><Value Type="Integer">2328</Value></Eq></Where></Query><RowLimit Paged="TRUE">1</RowLimit></View> ,fSafeArrayFlags=SAFEARRAYFLAG_NONE
System.Runtime.InteropServices.COMException: Item does not exist.  The page you selected contains an item that does not exist.  It may have been deleted by another user.<nativehr>0x81020016</nativehr><nativestack></nativestack>, StackTrace:    at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()     at Microsoft.SharePoint.SPListItemCollection.get_Count()     at Microsoft.SharePoint.SPList.GetItemById(String strId, Int32 id, String strRootFolder, Boolean cacheRowsetAndId, String strViewFields, Boolean bDatesInUtc, Boolean bExpandQuery)     at Microsoft.SharePoint.SPList.GetItemById(String strId, Int32 id, String strRootFolder, Boolean cacheRowsetAndId, String strViewFields, Boolean bDatesInUtc)     at Microsoft.SharePoint.SPList.GetItemById(String strId, Int32 id, String strR…
…ment.Automation.Runspaces.RunspaceBase.RunActionIfNoRunningPipelinesWithThreadCheck(Action action)     at System.Management.Automation.ScriptBlock.InvokeWithPipe(Boolean useLocalScope, ErrorHandlingBehavior errorHandlingBehavior, Object dollarUnder, Object input, Object scriptThis, Pipe outputPipe, InvocationInfo invocationInfo, Object[] args)     at System.Management.Automation.ScriptBlock.InvokeUsingCmdlet(Cmdlet contextCmdlet, Boolean useLocalScope, ErrorHandlingBehavior errorHandlingBehavior, Object dollarUnder, Object input, Object scriptThis, Object[] args)     at Microsoft.PowerShell.Commands.ForEachObjectCommand.ProcessRecord()     at System.Management.Automation.CommandProcessor.ProcessRecord()     at System.Management.Automation.CommandProcessorBase.DoExecute()     at System.Mana…
Item does not exist.  The page you selected contains an item that does not exist.  It may have been deleted by another user.<nativehr>0x81020016</nativehr><nativestack></nativestack>
SPRequest.DeleteItem: UserPrincipalName=i:0).w|s-1-5-21-3634847118-1559816030-2180994487-3194, AppPrincipalName= ,bstrUrl=http://YourSharePoint/Web/SubWeb ,bstrListName={2A67D5C3-7AC1-4F3E-AB47-2051CDB94237} ,lID=2327 ,dwDeleteOp=3 ,bUnRestrictedUpdateInProgress=False
System.Runtime.InteropServices.COMException: Item does not exist.  The page you selected contains an item that does not exist.  It may have been deleted by another user.<nativehr>0x81020016</nativehr><nativestack></nativestack>, StackTrace:    at Microsoft.SharePoint.SPListItem.DeleteCore(DeleteOp deleteOp)     at Microsoft.SharePoint.SPListItem.Delete()     at CallSite.Target(Closure , CallSite , Object )     at <ScriptBlock>(Closure , FunctionContext )     at System.Management.Automation.Interpreter.LightLambda.RunVoid1[T0](T0 arg0)     at System.Management.Automation.ScriptBlock.InvokeWithPipeImpl(Boolean createLocalScope, ErrorHandlingBehavior errorHandlingBehavior, Object dollarUnder, Object input, Object scriptThis, Pipe outputPipe, InvocationInfo invocationInfo, Object[] args)     a…

What should it mean, that specific items were deleted? I was sure, I work alone on the calendar. Fortunately, I already worked a lot previously with calendar entries, so it took not very long time to find a reason for the problem.

You should know, that if you delete the “master” item of the recurring events in SharePoint (and we had a lot of them in this case), all of the series exception are deleted the same time. As our original query returned this exception items as well, we had errors when we wanted to delete the already deleted items. The second script (without batch deletion) was able to survive it, but the batch deletion script got mad because of that.

What’s the solution for this issue? Let’s fix our CAML query and select only the items that are not recurring event exceptions:

<Where>
    <And>
    <Lt>
        <FieldRef Name=’EndDate’ />
        <Value Type=’DateTime’>2017-01-01 00:00:00</Value>
    </Lt>
    <Lt>
        <FieldRef Name=’EventType’/>
        <Value Type=’Integer’>2</Value>
    </Lt>
    </And>
</Where>

The query for EventType less than 2 originates from my practice, an unofficial documentation can be found here. The event types are defined (at least more or less) in the internal static class Microsoft.SharePoint.ApplicationPages.Calendar.EventType, for example Deleted has a value 3 and Updated has a value of 4.

The updated script:

  1. # modify the $url and $listTitle values to match your configuration
  2. $url = "http://YourSharePoint/Web/SubWeb&quot;
  3. $listTitle = "YourCalendar"
  4.  
  5. $web = Get-SPWeb $url
  6. $list = $web.Lists[$listTitle]
  7.  
  8. $query = New-Object Microsoft.SharePoint.SPQuery
  9.   $query.Query =
  10.           "<Where>
  11.              <And>
  12.                <Lt>
  13.                  <FieldRef Name='EndDate' />
  14.                  <Value Type='DateTime'>2017-01-01 00:00:00</Value>
  15.                </Lt>
  16.                <Lt>
  17.                  <FieldRef Name='EventType'/>
  18.                  <Value Type='Integer'>2</Value>
  19.                </Lt>
  20.              </And>
  21.            </Where>"
  22. $query.ViewFields = "<FieldRef Name='ID' />"
  23. $query.ViewFieldsOnly = $true
  24. $query.RowLimit = 1000;
  25.  
  26. $itemCount = 0
  27. $listId = $list.ID
  28.  
  29. do
  30. {
  31.     $listItems = $list.GetItems($query)
  32.     $itemIds = $listItems | % { [String]$_.ID }
  33.     [System.Text.StringBuilder]$batchXml = New-Object "System.Text.StringBuilder"
  34.     [Void]$batchXml.Append("<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>")
  35.     $itemIds | % {
  36.       $itemId = $_
  37.       [Void]$batchXml.Append("<Method ID=`"$itemId`"><SetList>$listId</SetList><SetVar Name=`"ID`">$itemId</SetVar><SetVar Name=`"Cmd`">Delete</SetVar></Method>")
  38.     }
  39.     [Void]$batchXml.Append("</Batch>")
  40.     Write-Host Deleting next $listItems.Count entries…
  41.  
  42.     $result = [Xml]$web.ProcessBatchData($batchXml.ToString())
  43.     $success = @(Select-Xml -Xml $result -XPath '//Results/Result[@Code="0"]')
  44.     $failure = @(Select-Xml -Xml $result -XPath '//Results/Result[@Code!="0"]')
  45.     $itemCount += $success.Count
  46.     # list errors
  47.     $failure | % {
  48.         $errorNode = $_.Node
  49.         Write-Host Error deleting entry with ID $errorNode.ID error code: $errorNode.Code error text: $errorNode.ErrorText
  50.     }
  51. }
  52. while ($listItems.ListItemCollectionPosition -ne $null)
  53.  
  54. Write-Host Summary: $itemCount entries deleted

Using the modified CAML query I had no more errors, BUT according to the comment of Andrey Markeev in this thread, batch deleting only moves the items to the Recycle Bin, instead of really deleting them. That is not optimal for me.

To delete the items from the Recycle Bin as well, there are two main alternative solutions. Either you extend the batch deletion script with removing recycled items from the Recycle Bin, or you perform a cleanup in a second step.

The code samples in the forum thread referred to earlier as well are deleting either all items from the Recycle Bin, or trying to delete the items using the original list item ID, although the ID in the Recycle Bin differs from the original one. That is either not ideal or does not function. If you invoke the Recycle method of a SPListItem instance, the new ID, the transaction ID is returned to you (as a Guid), but this information is unfortunately not available by batch deletion. We had no folder structure in our calendar, so the LeafName property of the SPRecycleBinItem correspond to the server relative URL of the root folder of the source list (where the item was deleted) without the leading slash, and the DirName property corresponds to the list item ID (ending with ‘_.000’), so we can simply make a query for the IDs in the Recycle Bin, and remove them permanently:

$recBin = $web.Site.RecycleBin
$recBinIds = @($recBin | ? { $itemIds -contains $_.LeafName.Trim(‘_.000’) -and $_.DirName -eq $list.RootFolder.ServerRelativeUrl.Trim(‘/’) } | % { $_.ID })
$recBin.Delete($recBinIds)

Note, that this script is not universal. It might not function, if you have deleted an item from a list including folder structure or a document from a library. For example, in the case of a document library the LeafName property correspond to the file name without the extension and not to the ID.

The updated script:

  1. # modify the $url and $listTitle values to match your configuration
  2. $url = "http://YourSharePoint/Web/SubWeb&quot;
  3. $listTitle = "YourCalendar"
  4.  
  5. $web = Get-SPWeb $url
  6. $list = $web.Lists[$listTitle]
  7.  
  8. $query = New-Object Microsoft.SharePoint.SPQuery
  9.   $query.Query =
  10.           "<Where>
  11.              <And>
  12.                <Lt>
  13.                  <FieldRef Name='EndDate' />
  14.                  <Value Type='DateTime'>2017-01-01 00:00:00</Value>
  15.                </Lt>
  16.                <Lt>
  17.                  <FieldRef Name='EventType'/>
  18.                  <Value Type='Integer'>2</Value>
  19.                </Lt>
  20.              </And>
  21.            </Where>"
  22. $query.ViewFields = "<FieldRef Name='ID' />"
  23. $query.ViewFieldsOnly = $true
  24. $query.RowLimit = 1000;
  25.  
  26. $itemCount = 0
  27. $listId = $list.ID
  28.  
  29. do
  30. {
  31.     $listItems = $list.GetItems($query)
  32.     $itemIds = $listItems | % { [String]$_.ID }
  33.     [System.Text.StringBuilder]$batchXml = New-Object "System.Text.StringBuilder"
  34.     [Void]$batchXml.Append("<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>")
  35.     $itemIds | % {
  36.       $itemId = $_
  37.       [Void]$batchXml.Append("<Method ID=`"$itemId`"><SetList>$listId</SetList><SetVar Name=`"ID`">$itemId</SetVar><SetVar Name=`"Cmd`">Delete</SetVar></Method>")
  38.     }
  39.     [Void]$batchXml.Append("</Batch>")
  40.     Write-Host Deleting next $listItems.Count entries…
  41.  
  42.     $result = [Xml]$web.ProcessBatchData($batchXml.ToString())
  43.     $success = @(Select-Xml -Xml $result -XPath '//Results/Result[@Code="0"]')
  44.     $failure = @(Select-Xml -Xml $result -XPath '//Results/Result[@Code!="0"]')
  45.     $itemCount += $success.Count
  46.     # list errors
  47.     $failure | % {
  48.         $errorNode = $_.Node
  49.         Write-Host Error deleting entry with ID $errorNode.ID error code: $errorNode.Code error text: $errorNode.ErrorText
  50.     }
  51.  
  52.     # delete items from Recycle Bin as well
  53.     $recBin = $web.Site.RecycleBin
  54.     $recBinIds = @($recBin | ? { $itemIds -contains $_.LeafName.Trim('_.000') -and $_.DirName -eq $list.RootFolder.ServerRelativeUrl.Trim('/') } | % { $_.ID })
  55.     Write-Host Deleting $recBinIds.Count entries from recycle bin…
  56.     $recBin.Delete($recBinIds)
  57. }
  58. while ($listItems.ListItemCollectionPosition -ne $null)
  59.  
  60. Write-Host Summary: $itemCount entries deleted

As the second option, you can delete all items you recycled from the calendar after you have finished the batch deletion. The script below removes items from the Recycle Bin that were deleted from the calendar in the past hour by the current user:

$recBin = $web.Site.RecycleBin
$ids = $recBin | ? { $_.DeletedDate -gt (Get-Date).ToUniversalTime().AddHours(-1) -and $_.DirName -eq ‘Web/SubWeb/Lists/YourCalendar’ -and $_.DeletedById -eq $web.CurrentUser.ID } | % { $_.ID }
Write-Host Deleting $ids.Count item from recycle bin…
$recBin.Delete($ids)

If the deletion was quicker than 60 minutes, you can reduce the time span used in the script, to reduce the possibility you delete an item inadvertently from the Recycle Bin.

July 16, 2017

Find Your Scripts in SharePoint within Seconds – the Effective, but Unsupported Way

Filed under: JavaScript, SP 2013, SQL, Tips & Tricks — Tags: , , , — Peter Holpar @ 18:41

The SharePoint environment I’m working on contains hundreds of webs. I create test sites for various tasks (like prototyping JSLink-based solutions) including the necessary lists, and store the .js and .css files typically in the Site Assets library of that web site to keep the solution artifacts (lists / files) together. It is rather common, that after I’ve finished the proof of concept, I don’t need it for months, then suddenly I should return to it, but I don’t find it anymore, as I don’t remember, which site I used for that solution.

For that kind of search I’ve created a simply SQL query to find the script directly in the content database of the site collection. Yes, I know it is officially unsupported to access the SharePoint databases directly, but I’m OK with that in my test system. Use it on your own risk.

SELECT [Id]
      ,[SiteId]
      ,[DirName]
      ,[LeafName]
      ,[TimeLastModified]
      ,[DeleteTransactionId]
  FROM [dbo].[AllDocs]
  WHERE LeafName LIKE ‘%.js’
  AND DirName LIKE ‘%SiteAssets%’
  ORDER BY TimeLastWritten DESC

This script lists the file name (LeadName) and path (DirName) of the scripts stored in various sites in their Site Assets library. The name of script and the date of last modification (TimeLastModified) is usually enough to identify the script I need. Note, that the records, that have a value other that 0x in the DeleteTransactionId column are recycled and located in the Recycle Bin. Of course, this method works only in the case of on-premise installations, and only as long as you have access to the SharePoint databases.

July 9, 2017

Import-SPWeb: Failed to read package file

Filed under: PowerShell, SP 2013 — Tags: , — Peter Holpar @ 21:41

A few days ago I wanted to restore a SharePoint list (including data) on a development server via the Import-SPWeb cmdlet based on a backup created by the Export-SPWeb cmdlet in the productive farm:

$url = "http://YourSharePoint/Web/SubWeb&quot;
$filePath = "E:\Data\ListExport.cmp"

Import-SPWeb $url -Path $filePath

Surprisingly, I’ve received a “Failed to read package file” exception, although the same .cmp file could have been imported in the test farm, having the same SharePoint version as the development and productive systems using the same user and same permissions.

PS C:\Users\pholpar> Import-SPWeb $url -Path $filePath

Log file generated:
        E:\Data\ListExport.cmp.import.log

Import-SPWeb : Failed to read package file.
At line:1 char:1
+ Import-SPWeb $url -Path $filePath
+
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (Microsoft.Share…CmdletImportWeb:
   SPCmdletImportWeb) [Import-SPWeb], SPException
    + FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletImportWeb

image

Having a look into the generated log file, I found the next surprise in the inner exception: “Failure writing to target file”. Actually, I wanted to read the .cmp file, not to write, what’s the problem then with writing?

[03.07.2017 21:03:28] Start Time: 03.07.2017 21:03:28.
[03.07.2017 21:03:28] Progress: Initializing Import.
[03.07.2017 21:03:28] Error: Failure writing to target file
[03.07.2017 21:03:28] Debug:    at Microsoft.SharePoint.SPGlobal.HandleComException(COMException comEx)
   at Microsoft.SharePoint.Library.SPRequest.ExtractFilesFromCabinet(String bstrTempDirectory, String bstrCabFileLocation)
   at Microsoft.SharePoint.SPSecurity.<>c__DisplayClass5.<RunWithElevatedPrivileges>b__3()
   at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode)
   at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(WaitCallback secureCode, Object param)
   at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(CodeToRunElevated secureCode)
   at Microsoft.SharePoint.Deployment.ImportDataFileManager.Uncompress(SPRequest request)
[03.07.2017 21:03:28] FatalError: Failed to read package file.
*** Inner exception:
Failure writing to target file
[03.07.2017 21:03:28] Debug:    at Microsoft.SharePoint.SPGlobal.HandleComException(COMException comEx)
   at Microsoft.SharePoint.Library.SPRequest.ExtractFilesFromCabinet(String bstrTempDirectory, String bstrCabFileLocation)
   at Microsoft.SharePoint.SPSecurity.<>c__DisplayClass5.<RunWithElevatedPrivileges>b__3()
   at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode)
   at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(WaitCallback secureCode, Object param)
   at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(CodeToRunElevated secureCode)
   at Microsoft.SharePoint.Deployment.ImportDataFileManager.Uncompress(SPRequest request)
[03.07.2017 21:03:28] Progress: Import did not complete.
[03.07.2017 21:03:28] Finish Time: 03.07.2017 21:03:28.
[03.07.2017 21:03:28] Duration: 00:00:00
[03.07.2017 21:03:28] Finished with 0 warnings.
[03.07.2017 21:03:28] Finished with 2 errors.

Checking the ULS logs provided me further details:

Entering BeginProcessing Method of Import-SPWeb.
Leaving BeginProcessing Method of Import-SPWeb.
Entering ProcessRecord Method of Import-SPWeb.
SecurityTokenServiceSendRequest: RemoteAddress: ‘
http://localhost:32843/SecurityTokenServiceApplication/securitytoken.svc’ Channel: ‘Microsoft.IdentityModel.Protocols.WSTrust.IWSTrustChannelContract’ Action: ‘http://docs.oasis-open.org/ws-sx/ws-trust/200512/RST/Issue’ MessageId: ‘urn:uuid:41e822d8-9b0f-4a48-93a2-003eaf92c8dc’
Leaving Monitored Scope (Build the X509Chain.). Execution Time=168,6921
Leaving Monitored Scope (SPCertificateValidator.Validate). Execution Time=168,7904
SecurityTokenServiceSendRequest: RemoteAddress: ‘
http://localhost:32843/SecurityTokenServiceApplication/securitytoken.svc’ Channel: ‘Microsoft.IdentityModel.Protocols.WSTrust.IWSTrustChannelContract’ Action: ‘http://docs.oasis-open.org/ws-sx/ws-trust/200512/RST/Issue’ MessageId: ‘urn:uuid:e3155df3-9409-4d5a-9391-bf938f9b7007’
Initializing Import.
SecurityTokenServiceSendRequest: RemoteAddress: ‘
http://localhost:32843/SecurityTokenServiceApplication/securitytoken.svc’ Channel: ‘Microsoft.IdentityModel.Protocols.WSTrust.IWSTrustChannelContract’ Action: ‘http://docs.oasis-open.org/ws-sx/ws-trust/200512/RST/Issue’ MessageId: ‘urn:uuid:953853ec-af7a-4100-91f8-b49533a7a986’
<nativehr>0x81070266</nativehr><nativestack></nativestack>Failure writing to target file
SPRequest.ExtractFilesFromCabinet: UserPrincipalName=i:0).w|s-1-5-21-3634847118-1559816030-2180994487-3194, AppPrincipalName= ,bstrTempDirectory=C:\Users\pholpar\AppData\Local\Temp\2\48463715-4bf7-4ca6-8aa1-3b92128d6789 ,bstrCabFileLocation=E:\Data\ListExport.cmp
System.Runtime.InteropServices.COMException: <nativehr>0x81070266</nativehr><nativestack></nativestack>Failure writing to target file, StackTrace:    at Microsoft.SharePoint.SPSecurity.<>c__DisplayClass5.<RunWithElevatedPrivileges>b__3()     at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode)     at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(WaitCallback secureCode, Object param)     at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(CodeToRunElevated secureCode)     at Microsoft.SharePoint.Deployment.ImportDataFileManager.Uncompress(SPRequest request)     at Microsoft.SharePoint.Deployment.SPImport.Run()     at Microsoft.SharePoint.PowerShell.SPCmdletImportWeb.InternalProcessRecord()     at Microsoft.SharePoint.PowerShell.S…
…PCmdlet.ProcessRecord()     at System.Management.Automation.CommandProcessor.ProcessRecord()     at System.Management.Automation.CommandProcessorBase.DoExecute()     at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Object input, Hashtable errorResults, Boolean enumerate)     at System.Management.Automation.PipelineOps.InvokePipeline(Object input, Boolean ignoreInput, CommandParameterInternal[][] pipeElements, CommandBaseAst[] pipeElementAsts, CommandRedirection[][] commandRedirections, FunctionContext funcContext)     at System.Management.Automation.Interpreter.ActionCallInstruction`6.Run(InterpretedFrame frame)     at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)     at System.Management.Automatio…
…n.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)     at System.Management.Automation.Interpreter.Interpreter.Run(InterpretedFrame frame)     at System.Management.Automation.Interpreter.LightLambda.RunVoid1[T0](T0 arg0)     at System.Management.Automation.DlrScriptCommandProcessor.RunClause(Action`1 clause, Object dollarUnderbar, Object inputToProcess)     at System.Management.Automation.CommandProcessorBase.DoComplete()     at System.Management.Automation.Internal.PipelineProcessor.DoCompleteCore(CommandProcessorBase commandRequestingUpstreamCommandsToStop)     at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Object input, Hashtable errorResults, Boolean enumerate)     at System.Management.Automation.Runspaces.LocalPipeline.Inv…
…okeHelper()     at System.Management.Automation.Runspaces.LocalPipeline.InvokeThreadProc()     at System.Management.Automation.Runspaces.PipelineThread.WorkerProc()     at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)     at System.Threading.ThreadHelper.ThreadStart() 
Failure writing to target file
Failed to read package file.  *** Inner exception:  Failure writing to target file
Import did not complete.
Microsoft.SharePoint.SPException: Failed to read package file. —> Microsoft.SharePoint.SPException: Failure writing to target file —> System.Runtime.InteropServices.COMException: <nativehr>0x81070266</nativehr><nativestack></nativestack>Failure writing to target file     at Microsoft.SharePoint.Library.SPRequestInternalClass.ExtractFilesFromCabinet(String bstrTempDirectory, String bstrCabFileLocation)     at Microsoft.SharePoint.Library.SPRequest.ExtractFilesFromCabinet(String bstrTempDirectory, String bstrCabFileLocation)     — End of inner exception stack trace —     at Microsoft.SharePoint.SPGlobal.HandleComException(COMException comEx)     at Microsoft.SharePoint.Library.SPRequest.ExtractFilesFromCabinet(String bstrTempDirectory, String bstrCabFileLocation)     at Microsoft.Sha…
…rePoint.SPSecurity.<>c__DisplayClass5.<RunWithElevatedPrivileges>b__3()     at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode)     at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(WaitCallback secureCode, Object param)     at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(CodeToRunElevated secureCode)     at Microsoft.SharePoint.Deployment.ImportDataFileManager.Uncompress(SPRequest request)     — End of inner exception stack trace —     at Microsoft.SharePoint.Deployment.ImportDataFileManager.Uncompress(SPRequest request)     at Microsoft.SharePoint.Deployment.SPImport.Run()     at Microsoft.SharePoint.PowerShell.SPCmdletImportWeb.InternalProcessRecord()     at Microsoft.SharePoint.PowerShell.SPCmdlet.ProcessRecord()
Error Category: InvalidData    Target Object  Microsoft.SharePoint.PowerShell.SPCmdletImportWeb  Details  NULL  RecommendedAction NULL
Leaving ProcessRecord Method of Import-SPWeb.
Entering EndProcessing Method of Import-SPWeb.
Leaving EndProcessing Method of Import-SPWeb.

Searching for the error code and text on the web, I found this (not SharePoint-related) forum entry, with a bit more descriptive error text “Failure writing to the target file. Please check that you have enough disk space.”, that led me to the rather trivial solution: the C: drive was full, so the process was not able to create a temporary folder by extracting the .cmp file (that is actually a .cab file).

Note 1: As you see at the top of the post, the Import-SPWeb cmdlet was executed from the path C:\Users\pholpar. It does not help however,if you simply change the path to an other drive that has more place, as a temporary folder in the user profile (located in our case on the C: drive) is used to extract the files, see the value of the bstrTempDirectory parameter in the ULS logs above.

Note 2: It does help however, if you create and restore the backup file using the NoFileCompression switch of the Import-SPWeb / Export-SPWeb cmdlets, as in this case there is no need for the extraction process and the temporary folder mentioned above.

The ideal solution is of course, to keep your servers clean and healthy, providing always enough resources (including but not limited to disk space and memory) to fulfill their tasks.

June 25, 2017

How to get the Url of the “Edit View” Page of a Specific SharePoint List View from PowerShell

Filed under: PowerShell, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 07:33

There might be cases when you can’t access the Edit View page of a specific list view from the SharePoint UI. For example, there is no such direct link in the case of Survey lists. There is no ribbon including the Manage Views group, and the Views area is missing from the List settings page as well.

You can, however access the Edit View page from your browser if you know its URL. The standard URL of this page has this pattern:

http://YourSharePoint/Web/SubWeb/_layouts/15/ViewEdit.aspx?List=%7BDC913804%2DB28E%2D4F52%2DAF53%2DDEC490A1C83D%7D&View=%7B2E7DF707%2D42BA%2D44EE%2D87C6%2D0919CA38BDF1%7D

As you see, the ViewEdit.aspx page is responsible for this functionality. The encoded Ids (Guid) of the List and the View are passed as query string parameters (List and View respectively).

You can get the URL of the page using this PowerShell script easily:

$web = Get-SPWeb ‘http://YourSharePoint/Web/SubWeb&#8217;
$list = $web.Lists[‘YourList’]
# get the default view of the list
$view = $list.DefaultView
# or get an arbitrary view by its name
# $view = $list.Views[‘All Items’]
$viewId = $view.ID

function EscapeGuid($guid)
{
  return "{$guid}".ToUpper().Replace(‘-‘, ‘%2D’).Replace(‘{‘, ‘%7B’).Replace(‘}’, ‘%7D’)
}

$url = $web.Url + ‘/_layouts/15/ViewEdit.aspx?List=’ + (EscapeGuid $list.ID) + ‘&View=’ + (EscapeGuid $view.ID)

You can even start the page in Internet Explorer from PowerShell if you wish:

$ie = New-Object -ComObject InternetExplorer.Application
$ie.Navigate2($url)
$ie.Visible = $true

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

"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

Older Posts »

Blog at WordPress.com.