How to check-in multiple files at once


SCENARIO:

When you have a mandatory column in document library and when you upload multiple files and folders in it using “Open with explorer” option, all the files either within or without folders will be checked out by default.

Checked Out Files

When you try to check-in the file, it will prompt you to fill out all the required properties before checking in the file.

SOLUTION:

How to fill out the properties for multiple files(including the files within folders):

  • Click on Library in the ribbon and click “Create View” under Manage Views
  • Choose Datasheet view, give a name(say, Properties view) and check the boxes under Columns for Type, Name and the mandatory columns
  • Expand the folder and choose “Show all items without folders”and click OK button.

Folder Options

  • Now, fill out the values for mandatory column in properties view

Properties View

  • Stop editing this list

How to Check-in the files:

  • Go to Properties view and switch to Standard view
  • Select all the documents using ‘Tick Mark” behind Name column
  • Click on Files tab in the ribbon and select “Check In” under “Open & Check Out”

Check In.png

Alternative ways to check in multiple files in Publishing sites:

If you have at least edit permission in a publishing site, you can use the below method:

  • Go to Site Settings and click on “Content and Structure” under Site Administration
  • Change the view from “Default View” to “Checked Out To Me” as depicted below:

Content and Structure - Checked Out to Me.png

  • Select all the documents, go to Actions and select “Check In” as shown below

Content and Structure - Check In.png

That’s it!

Challenges faced after exporting Excel data to SharePoint


In previous article, we have seen how to connect to SharePoint site from excel for exporting data. Today, let us see the challenges faced in SharePoint after exporting the data.

  1. Number columns with greater than or equal to 4 digits in excel – Lets consider, Application ID column with value 20158 for better understanding. As number column in excel are associated with number column in SharePoint, they are displayed with commas as 20,158
    • To remove the commas, never try converting it into Single line of text in SharePoint because it will be converted into exponential form as 20+158e
    • If you would like to show them without commas, create a calculated column with the below formula:
    • =TEXT([Application ID], “0”)
    • By using this formula, Commas will be removed but calculated columns will be shown only in display form
    • So, before exporting it is recommended to convert number field with more than 4 digits in excel to text column as to associate it with single line of text. Now, to prevent the users from entering number in Single line of text field, follow the below steps:
    • Go to List->List settings and click on Application ID column
    • It takes us to Change Column page for Application ID where we need to enter the below formula in Data validation section and click ok
    • =ISNUMBER([Application ID]+0)
  2. Person or Group column in SharePoint for user names like Manager in excelAs text columns are associated with  Single line of text, they cannot be converted to Person or group column. What if we need to use People Picker(Person or Group) for user names. Below is a small workaround which will be useful if there are thousands of items.
    •  If number of items is less, create a person or group column, open edit form of each item and copy paste the value manually from single line of text to Person or group field. When there are large number of items(say 2000), create below two columns in SharePoint:
    • Flag – Yes/No type
    • Mgr – Person or group type in SharePoint
    • Go to SharePoint designer and create a workflow which should run whenever an item is changed
    • Create the below step:
      • If flag equals Yes
      • set Mgr(Person or group column) to Manager(Single line of text)
    • Now, go to Datasheet view of the list and simply select Yes and drag the Yes value till the last item
    • Advantage: It reduces the time than doing it manually
    • Disadvantages:
      • Workflow fails when exact match is not found meaning either the person has left the organization or their Id in active directory is changed
      • We need to manually fill for those items where workflow has failed
      • Remove the workflow from the list once all the user names are copied to Person or group column
  3. Missing “Add new item” link in SharePoint 2010 – To overcome this, create a Standard view and set it as default view

If the above post is helpful, do not forget to like my article and rate it.

Exporting Excel table to SharePoint


SharePoint is used everywhere especially for document and content management. Nowadays, data that were stored in excel are being moved to SharePoint , thus making the ease of access and management of data. To facilitate this, SharePoint has an app(list) called “Import Spreadsheet” as shown below:

import-spreadsheet creating-sp-list
import-dialog

This approach is known to everyone where we select Import Spreadsheet list, provide a name and description, browse the excel file, select the range of cells to be imported. But only few of us know how to connect from excel to SharePoint. This article will cover the later approach. Let us see how to do.

  • Open desired sheet in excel and select the range of cells to be moved to SharePoint(both rows and columns)
  • Click on Insert->Table. In create table dialog box, check the range and hit ok

create-table

  • Now, you will be able to see Design Tab in the ribbon.
  • Under External Table Data section, click on Export->Export Table to SharePoint List

design-and-export

  • In Export table step 1 dialog box, give the address of SharePoint site, check create a read-only connection box, provide a name and description for SharePoint list and click Next

step-1

  • “Create a read only connection to the new SharePoint list” box is used to keep the excel table up-to-date with the changes made in SharePoint list. This is only one way meaning, you cannot update changes from excel to SharePoint once it is exported. When you check the box, it creates two columns in the excel table
    • Item Type  – Item/Folder
    • Path – folder path for a list item
  • In step 2, we can verify the data types of excel columns associated with data types in SharePoint. For example, Student Name of type text in excel is associated with Single line of Text in SharePoint. After verification, click Finish button.

step-2

  • Table has been published successfully and two columns(Path and Item Type) are created in excel as said previously

exported-successfully

excel-table
Excel Table

sharepoint-list
SharePoint List
  • To demonstrate the power of “Create a read-only connection to the new SharePoint list”, let us edit the mark of student, Chitra in SharePoint list

edit-in-splist

  • Then go to excel and click Design->Refresh. Excel table is updated as depicted below

autoupdated-excel

  • To remove this read-only connection, click on Design->Unlink

unlink

Advantages:

  • This approach is useful when we need to check the associated datatypes(shown within braces) before exporting data to SharePoint. If we need to change the data type, cancel step 2, change datatype in excel and follow step 1 & 2 again

advantage

  • Creating a read-only connection allows to keep excel report up-to-date rather than often exporting updated data from SharePoint list.

If you found this post useful, please press like button, rate this article and share your comments.

Query Strings in SharePoint


Let us see some of the query strings used in SharePoint:

  • contents=1 ->used to troubleshoot the sharepoint page.When it is appended at the end of the url as:

https://…/sitepages/Home.aspx?contents=1

it takes us to webpart maintenance page where we can find the webpart causing the error by trying to close each one and deleting the problematic webpart

  • IsDlg=1->used to hide the header and quick launch(i.e. chrome).Generally used in SharePoint forms.
  • FilterField1=Gender&FilterValue1=Male(FilterField1=columnname&FilterValue1=columnvalue)->used to filter sharepoint list webpart with the condition, Gender=”Male”
  • FilterName=Hobbies&FilterMultiValue=Painting;Reading(FilterName=columnname&FilterMultiValue=columnvalue1; columnvalue2)->used to filter sharepoint list with multiplevalues in singlecolumn
  • PageView=Shared&ToolPaneView=2->used to show the edit mode of the page, browse and add the webpart
  • PageView=Shared&ToolPaneView=3->used to show the edit mode of the page, search and add the webpart
  • SortField=Hobbies&SortDir=Desc(SortField=ColumnName&SortDir=Desc/Asc) ->used to sort the sharepoint list by Hobbies in Descending order
  • _layouts/viewlsts.aspx -> To go to “All Site Content” Page

Printing Webpart


Let us consider that you have one of the below scenario:

Scenario 1:

You have multiple web parts in a page, say a list and a chart web part and you need to print only chart web part

Scenario 2:

You have an sharepoint list form with quick launch and you need print out without quick launch.

Solution:

  • Go to the desired page and edit it
  • Insert script editor web part and paste the below code or refer the below code in content editor webpart
  • To find the webpart element ID, press F12, point to the webpart and get the ID

<input onclick="javascript:void(PrintWebPart())" type="button" value="Print Web Part"/>

var WebPartElementID = "WebPartWPQ2";
//change the webpart element ID with yours

function PrintWebPart()
{
var bolWebPartFound = false;
if (document.getElementById != null)
{
var PrintingHTML = '\n\n';
if (document.getElementsByTagName != null)
{
var HeadData= document.getElementsByTagName("HEAD");
if (HeadData.length > 0)
PrintingHTML += HeadData[0].innerHTML;
}
PrintingHTML += '\n\n\n';
var WebPartData = document.getElementById(WebPartElementID);
if (WebPartData != null)
{
PrintingHTML += WebPartData.innerHTML;
bolWebPartFound = true;
}
else
{
bolWebPartFound = false;
alert ('Cannot Find Web Part');
}
}
PrintingHTML += '\n\n';
if (bolWebPartFound)
{
var PrintingWindow = window.open("","PrintWebPart", "toolbar,width=800,height=600,scrollbars,resizable,menubar");
PrintingWindow.document.open();
PrintingWindow.document.write(PrintingHTML);
PrintingWindow.print();
}
}

SPUtility


We have seen how to check if user belongs to a particular sharepoint group or not in Part 1. Let’s see how to show/hide/disable sharepoint fields using SPUtility.

About SPUtility

It is a javascript library used to

  • set or get field values
  • Show or hide fields
  • Make the fields as read-only

It is compatible with SharePoint 2010, SharePoint 2013, O365 and MOSS 2007.

Prerequisite:

Download the below files, copy and save it as .js

To set values when form is loaded:

_spBodyOnLoadFunctionNames.push("Default");
function Default()
{
SPUtility.GetSPField('Title').SetValue('Hello');
}

You can also use in jquery as:

$(document).ready(function()
{
SPUtility.GetSPField('Title').SetValue('Hello');
});

To get the values:

$(document).ready(function()
{
var status = SPUtility.GetSPField('Status').GetValue();
});

To show, hide and disable the fields:

$(document).ready(function()
{
SPUtility.GetSPField('Status').Show();
SPUtility.GetSPField('Status').Hide();
SPUtility.GetSPField('Status').MakeReadOnly();
});

Insert these codes as per your requirement into previous posted code.

Check if user belongs to SharePoint group


In SharePoint, we need not allow all the users to see all the controls in the form. We may need to show, hide or disable the SharePoint controls for users in particular SharePoint group. Lets see this as two parts.

  • Check if logged in user belongs to SharePoint group(in SharePoint 2010, 2013)
  • Show/Hide/Disable fields using sputility.js

PART 1: Check if logged in user belongs to SharePoint group

  • Create a SharePoint group with the desired permissions and add users to it.
  • When we go to the address bar, we can see the url of SharePoint group as ../_layouts/people.aspx?MembershipGroupId=7
  • ID in the above url is 7. Use this ID for groupCollection.getById(ID) in SharePoint 2010 and use the groupname in sharepoint 2013

SharePoint 2010

<script>
ExecuteOrDelayUntilScriptLoaded(IsUserExists, "sp.js");
var group;
var users;
var ctx;
var groupCollection;
var user;
var currentuser;
function IsUserExists()
{
ctx = SP.ClientContext.get_current();
groupCollection = ctx.get_web().get_siteGroups();
currentuser=ctx.get_web().get_currentUser();
group = groupCollection.getById(7);
ctx.load(group);
ctx.load(currentuser);
ctx.executeQueryAsync(Function.createDelegate(this, this.OnGetGroupSuccess), Function.createDelegate(this, OnFailure));
}
function OnGetGroupSuccess()
{
users=group.get_users();
ctx.load(users);
ctx.executeQueryAsync(Function.createDelegate(this, this.OnGetuserSuccess), Function.createDelegate(this, OnFailure));
}
function OnGetuserSuccess()
{
var userEnumerator = users.getEnumerator();
while (userEnumerator.moveNext())
{
var user = userEnumerator.get_current();
if (user.get_id() == currentuser.get_id())
{
alert("You have access to this form");
//sputility code to show the controls
break;
}
}
}
function OnFailure(sender, args) {
alert("You do not have Permission");
//sputility code to hide/disable the controls
}

SharePoint 2013:

ExecuteOrDelayUntilScriptLoaded(IsCurrentUserHasContribPerms, 'SP.js');
function IsCurrentUserHasContribPerms()
{
IsCurrentUserMemberOfGroup("Owners", function (isCurrentUserInGroup) {
if(isCurrentUserInGroup)
{
// sputility code to show the controls
}
else
{
//sputility code to hide/disable the controls
}
});
}
function IsCurrentUserMemberOfGroup(groupName, OnComplete) {
var currentContext = new SP.ClientContext.get_current();
var currentWeb = currentContext.get_web();
var currentUser = currentContext.get_web().get_currentUser();
currentContext.load(currentUser);
var allGroups = currentWeb.get_siteGroups();
currentContext.load(allGroups);
var group = allGroups.getByName(groupName);
currentContext.load(group);
var groupUsers = group.get_users();
currentContext.load(groupUsers);
currentContext.executeQueryAsync(OnSuccess,OnFailure);
function OnSuccess(sender, args) {
var userInGroup = false;
var groupUserEnumerator = groupUsers.getEnumerator();
while (groupUserEnumerator.moveNext()) {
var groupUser = groupUserEnumerator.get_current();
if (groupUser.get_id() == currentUser.get_id()) {
userInGroup = true;
break;
}
}
OnComplete(userInGroup);
}
function OnFailure(sender, args) {
OnComplete(false);
}
}

We will see Part 2 in next article.

If my post is helpful to you, please like my article and post your valuable feedback or query in comments.

Charts for SharePoint


We all know that charts are the best way of representing the data to the user. In some sites, we may not have the default chart web part in SharePoint. In such cases, we can go for Google api charts. Google api charts are the one which can be used by anyone. Please find the below steps to use Google api charts in your site:

  • Download the below files and upload it in Site Assets
  • Create a text file(say, MasterChart.txt) and copy the below code into the text file:
<div class="spjs_chartPlaceholder_master"></div>
<script type="text/javascript">
// Set this to true to load the Google Visualization API release candidate
var loadRC = false;
// Set this to true to allow for the use of variables in the "Filter setup textarea"
var allowEval = false;
</script>
<script type="text/javascript" src="/SiteAssets/jquery.min.js"></script>
<script type="text/javascript" src="/SiteAssets/jsapi.js"></script>
<script type="text/javascript" src="/SiteAssets/spjs-charts.js"></script>
  • Save the code and upload it in Site Assets.
  • Copy the link location of MasterChart.txt
  • Go to the page where you have to show your chart
  • Edit the page, insert the content editor web part, paste the link copied into “Content Link” in Content Editor web part and click OK.
  • You will get the below message.Click OK

SPJS-Configuration

  • Stop editing the page and click on the inverted triangle to fill the chart options and hit Save button.

Inverted Triangle.png

Chart Options.png

options.png

  • Chart will be generated as shown below

Bar graph

For more than one chart in the same page:

  • If you need to show more than one chart, create a text file(say, SlaveChart.txt), type the below code, save it and upload it in Site Assets.
<div class="spjs_chartPlaceholder"></div>
  • Insert any number of content editor web parts depending on the number of charts to be shown and paste the SlaveChart.txt link in “Content Link”. Remember that if you remove the Master Chart, Slave Chart will not work.

Advanced options:

  • Options and advanced options in Chart will make your chart more attractive and specific.
  • Some of the options to make your chart attractive:
Chart Type Option Value
All title Quarter II Results (Your Chart Title)
Pie charts is3D true (converts chart from 2D into 3D and it is applicable only for few chart types)
Donut Charts pieHole 0.3(any value between 0 and 1)
All legend.position bottom
left
in
none
right
top
All legend.alignment center
start
end
Gauge Chart min 0
max 10
yellowColor ‘#FF9900’
yellowFrom 0
yellowTo 5
Gauge Pie Chart

To create filters for the chart, please follow the below steps:

  • Check the box “Use custom CAML”
  • Check the box “Create filters above the chart” and select the field from “prefill Textarea from available fields” dropdown
  • Then, click on the link “Build CAML from selected filters” and hit save button.

CAML.png

  • Chart with drop down at the top will be created. Sample is shown below:

Filter.png

NOTE: Filters might not work when the other chart options like chart type, columns are filled for the first time.

For more details and updates, please refer the original article here

Thanks for reading 🙂

If you liked it, Please rate and comment.