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.

Advertisements

PART 2 : Working in MOSS using SPServices


In this article, we will see how to set and get People Picker value using SPFindPeoplePicker.

Get value from the people Picker:


<script>

var ppl = $().SPServices.SPFindPeoplePicker({
peoplePickerDisplayName: "User", //Display Name of People Picker Field
checkNames: true
});
alert(ppl.currentValue);

</script>

Set value to the People Picker:

To set static value in  the People Picker:


<script>

$(document).ready(function()
{
$().SPServices.SPFindPeoplePicker({
peoplePickerDisplayName: "User",
valueToSet: "kamal",
checkNames: true
});
});

</script>

To set logged in user name to the People Picker:


<script>

$(document).ready(function()
{
var user= $().SPServices.SPGetCurrentUser();
$().SPServices.SPFindPeoplePicker({
peoplePickerDisplayName: "User",
valueToSet: user,
checkNames: true
});
});

</script>

PART 1 : Working in MOSS using SPServices


After working in SharePoint 2010 and 2013 platform, it was bit difficult for us to work in SharePoint 2007.  SPServices came to our rescue. I have consolidated different scenarios we encountered and placed them in a single post below. So, it might help someone in the future.

Download the below files, save and provide a reference to these scripts before using the below script:

SPServices to populate people picker with logged-in username and to disable it in IE and Chrome:

<script type="text/javascript"> 
    var currentUserName ;   
    $(document).ready(function() {
    currentUserName = $().SPServices.SPGetCurrentUser({
    fieldName: "Title",
    debug: false
});
 alert(currentUserName);
 if((navigator.userAgent.indexOf("MSIE") != -1 ) || (!!document.documentMode == true )) //IF IE > 10
      {
          alert("Browser used for display is IE");          
      $("div[title='People Picker']").text(currentUserName);
      $("div[title='People Picker']").prop('disabled',true);
       $('img[title="Browse"]').css('display','none');
       $('img[title="Check Names"]').css('display','none');
        
      }     
      else if(navigator.userAgent.indexOf("Chrome") != -1 )
      {
          alert("Browser used for display is Chrome");  
   $("textarea[title='People Picker']").val(currentUserName);
      $("textarea[title='People Picker']").prop('disabled',true);
      $('img[title="Browse"]').css('display','none');
      $('img[title="Check Names"]').css('display','none');    
        
      }
 });
</script>

SPServices to check whether the user belongs to the specific SharePoint group(say, Admin):

<script language="javascript" type="text/javascript">
$(document).ready(function() 
{
    $().SPServices({
      operation: "GetGroupCollectionFromUser",
      userLoginName: $().SPServices.SPGetCurrentUser(),
      async: false,
      completefunc: function(xData, Status) {
        if($(xData.responseXML).find("Group[Name='Admin']").length == 1)
        {
      alert("You belong to admin group");
  //your code for admin users
    }
        else
        {
          alert("You do not belong to admin group");
  //your code for non-admin users
        }
      }
   }); 
}); 
</script>

SPServices to perform read operation:

<script>
$(function(){
getListItems();
});
function getListItems(){
var method = "GetListItems";
var list = "Demo";
var fieldsToRead =  '<ViewFields></ViewFields>';
var query = "<Query><Where><Eq><FieldRef Name='ID' /><Value Type='Number'>"+ID+"</Value></Eq></Where></Query>";            $().SPServices({
operation: method,
async: false,
listName: list,
CAMLViewFields: fieldsToRead,
CAMLQuery: query,
completefunc: function (xData, status) {
$(xData.responseXML).SPFilterNode("z:row").each(function () {
var eTitle = ($(this).attr("ows_Title"));
alert(eTitle);
});
}
});
}
</script>

To get the latest version of SPServices and to know more about it, visit this link

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.

JS Link


JS Link is one of the client side rendering(CSR) mechanism.

What is meant by CSR?

If the data is transformed by the client rather than the server by using client side technologies such as HTML,CSS and JavaScript, then it is known as CSR.

In Previous version of SharePoint, server side rendering like XSLT and CAML were used. We can even prefer server side rendering(XSLT) in SharePoint 2013, by referring XSL file in XSL Link and checking the Server render box as highlighted in red below.

WEBPART.png

JS Link can be found in List view web parts under Miscellaneous section(highlighted in black above). It links to a JavaScript file where rendering logic is written. To support JS Link, new content type called JavaScript Display template has been introduced with special site columns such as Target Control type, Target Scope etc. to ensure that CSR is targeted to the correct list web part.

Using JS Link, we can customize or control the rendering of

  • List Views
  • Forms(Display, Edit and New Forms)
  • Fields

Two events used for customization are:

  • OnPreRender
  • OnPostRender

To render a custom template, we need to register the template using the below javascript method:

SPClientTemplates.TemplateManager.RegisterTemplateOverrides(options);

where options takes the below structure:

var options = {
Templates:{
View: /* function or string */,
Body: /* function or string */,
Header: /* function or string */,
Footer: /* function or string */,
Group: /* function or string */,
Item: /* function or string */,
Fields: {
'Field Internal Name': {
View: /* function or string */,
EditForm: /* function or string */,
DisplayForm: /* function or string */,
NewForm: /* function or string */
}
}
},
OnPreRender: /* function or array of functions */,
OnPostRender: /* function or array of functions */
};

To find the internal name of the field, navigate to the list settings and click on the field under columns. This will take you to the “Edit Column” page. In “Edit column” page, click on the url to see the internal name of the field located at the end of URL as shown below:

https://yoursite.com/sites/SharePointConnect/_layouts/15/FldEdit.aspx?List=%9B7D21587E%2D7759%2D463A%2D89D2%2D014A41493451%7D&Field=Mark

For MDS(Minimal Download Strategy) disabled site:

We wrap the rendering logic in anonymous function so that it executes itself when the page is loaded and the global namespace will not be polluted.

Example 1:

(function()
{
var markContext  = {};
markContext.Templates = {};
markContext.Templates.Fields = {
'Mark': { 'View' : customizemark }
};
SPClientTemplates.TemplateManager.RegisterTemplateOverrides(markContext);
})();
function customizemark()
{
var tem= ctx.CurrentItem[ctx.CurrentFieldSchema.Name];
if(tem<50)
return "<span style='color:red;font-weight:bolder'>"+tem+"</span>";
else if(tem==50)
return "<span style='color:orange;font-weight:bolder'>"+tem+"</span>";
else if(tem>50)
return "<span style='color:green;font-weight:bolder'>"+tem+"</span>";
}

RESULT:

CSR-Sample1

Let us consider one more column called Status(Pass/Fail) and have requirement to show only status and not the mark. We can hide the column, ‘Mark’ from view using “Modify View” page but what about the forms. So, in order to hide the Mark field from forms, follow the below example:

Example 2:

(function () {
(window.jQuery || document.write('<script src="//ajax.aspnetcdn.com/ajax/jquery/jquery-1.10.0.min.js"></script>'));
var hiddenFiledContext = {};
hiddenFiledContext.Templates = {};
hiddenFiledContext.Templates.OnPostRender = hideField;
hiddenFiledContext.Templates.Fields = {
"Mark": {
"NewForm": hiddenFiledTemplate,
"DisplayForm": hiddenFiledTemplate,
"EditForm": hiddenFiledTemplate
}
};
SPClientTemplates.TemplateManager.RegisterTemplateOverrides(hiddenFiledContext);
})();
function hiddenFiledTemplate() {
return "<span class='csrHiddenField'></span>";
}
function hideField(ctx) {
jQuery(".csrHiddenField").closest("tr").hide();
}

 

RESULT:

HiddenField

For MDS Enabled site:

To apply Example-1 for MDS Enabled site, we need to register the template using:
RegisterModuleInit(relative path to file, function name);

RegisterModuleInit('/SiteAssets/CSR.js', Mark);
Mark();
function Mark()
{
var markContext  = {};
markContext.Templates = {};
markContext.Templates.Fields = {
'Mark': { 'View' : customizemark }
};
SPClientTemplates.TemplateManager.RegisterTemplateOverrides(markContext);
}
function customizemark()
{
var tem= ctx.CurrentItem[ctx.CurrentFieldSchema.Name];
if(tem<50)
return "<span style='color:red;font-weight:bolder'>"+tem+"</span>";
else if(tem==50)
return "<span style='color:orange;font-weight:bolder'>"+tem+"</span>";
else if(tem>50)
return "<span style='color:green;font-weight:bolder'>"+tem+"</span>";
}

When we use JS Link in a page where there are more than one list view, the rendering or customization may not appear correctly. To ensure that it is applied to the respective views, three additional options are used:

  • List Template Type – ID of list template
List template type Template ID
Custom List 100
Document Library 101
Survey 102
Links 103
Announcements 104
Contacts 105
Calendar 106
Tasks 107
Discussion Board 108
Picture Library 109
DataSources 110
Form Library 115
No Code Workflows 117
Custom Workflow Process 118
Wiki Page Library 119
CustomGrid 120
No Code Public Workflows<14> 122
Workflow History 140
Project Tasks 150
Public Workflows External List<15> 600
Issues Tracking 1100
  • View Style – ID of different styles available in “modify view” page. To find the ID of each view style:
    • Go to Modify view page
    • Right click on the page and select view source
    • you can find the ID of the style in <option>
    • Below is the list of styles with ID

ID

Style
0 Basic Table
12 Boxed, No Label
13 Boxed
15 Newsletter
16 Newsletter, no lines
17 Shaded
20 Preview Pane
Default Default
  • Base View ID: Attribute of view element that can be found in SharePoint Designer
    • In General, BaseViewID of “AllItemsView” is equal to 1 and BaseViewID of “Summary view” is equal to 0

Uploading JS File:

It is recommended to upload js file to master page gallery. To upload in master page gallery, follow the below steps:

  • Go to Site settings ->Master pages under Web Designer Galleries (In case of Publishing site, it will be Master pages and page layouts under Web Designer Galleries)
  • In Master page Gallery, click on FILE tab and choose Upload Document
  • Browse the js file from your computer and select the content type as JavaScript Display Template.

Javascript Display TemplateJS Display Template.png

  • Target Control Type: Type of control for which customization is applied to. It can be
    • Form
    • View
    • Field
  • Standalone: Specifies to include js file to override during view selection. Options are
    • Override
    • Standalone
  • Target Scope: URL of the website to which override applies to.
  • Target List Template ID: ID of the list template to which override applies to. Refer the above table under List Template ID
  • Fill these columns and hit Save button
  • Don’t forget to publish it if you are uploading it in publishing site.

Publish

  • If you do not have permission to master page gallery, upload it to SiteAssets
  • Now, go to the list web part and refer the uploaded js file as:
    • ~site/_catalogs/masterpage/csr.js if you have uploaded js file in master page gallery
    • ~site/SiteAssets/csr.js if you have uploaded it in Site Assets
  • Use the token ~sitecollection instead of ~ site if you are referring the js file in list web part of subsite
    • Ex:~sitecollection/_catalogs/masterpage/csr.js
  • To refer more than one js file in JS Link, use pipe symbol, |
    • Ex:~site/SiteAssets/csr.js|~site/SiteAssets/Demo.js
  • NOTE: Use CTRL+F5 (Hard Refresh) if changes to the code are not reflected in the browser

Advantages:

  • Performance is better as it rendered by client rather than the server
  • Ease of development

Disadvantages:

  • JS Link is not supported in Survey and events lists like calendar
  • Browser Compatibility – works differently in different browsers
  • JS Link does not work for custom list forms created in SharePoint designer

Refer more sample codes here

Site Template


How to create a site from existing template?

  • Go to the site (whose template is to be used for new site)
  • Click on Gear wheel->Site Settings
  • Click on Save site as template under Site Actions as highlighted below

Site Settings

  • On clicking the link, it takes to Save as  Template page where file name and Template name should be given
  • Check “Include Content” box and hit OK button.

Save As Template

  • The below message will be shown. Click on the “Solution gallery” hyperlink in it

Operation Completed

  • In Solution Gallery, you can see the template of the site that is created before few minutes.
  • Click on it to download the .wsp file. Now, we have a local copy of the template in our system.

Solution Gallery

  • Go to the Central admin site to create a new site collection
  • Click on New->Private Site Collection

New Site.png

  • Do not select in-built templates. Just click on custom tab and hit OK button.

New Site Collection

  • In Template selection, you cannot find the custom template as you have not yet uploaded.
  • To upload the solution, click on “Solution Gallery”

Upload Custom Template

  • Click on Upload Solution in the ribbon.
  • Choose the file from Computer and press OK button.

upload

Add Solution

  • In Add a Solution dialog, click on “Activate” to activate the solution.

Activate

  • If you go to the new site, you can see the custom tab with our template.
  • Select the template and Set up groups for the site.

Create from Custom

Groups

  • Now, we can see the contents of previous site in our new site as highlighted below.

New site from Template.png

CRUD Operations using JSOM


Let us consider the below list “Resource” on which CRUD Operations are to be performed using JavaScript.

 

Resource.PNG

Read Operation:

<script src="/SiteAssets/jquery.min.js"></script>
<script>
_spBodyOnLoadFunctionNames.push("Read");
function Read()
{
var clientContext = new SP.ClientContext.get_current();
var web = clientContext.get_web();
var oList = web.get_lists().getByTitle('Resource');
var camlQuery = new SP.CamlQuery();
var Query='<View><Query><FieldRef Name="Title"/></Query></View>';
camlQuery.set_viewXml(Query);
this.collListItem = oList.getItems(camlQuery);
clientContext.load(collListItem);
clientContext.executeQueryAsync(Function.createDelegate(this, QuerySucceeded), Function.createDelegate(this, this.QueryFailed));
}
function QuerySucceeded()
{
var shtml="";
var listItemEnumerator = collListItem.getEnumerator();
while(listItemEnumerator.moveNext())
{
var oListItem = listItemEnumerator.get_current();
var s=oListItem.get_item('Title');
shtml+="
" +s+ "
";
}
document.getElementById("Result").innerHTML=shtml;
}
function QueryFailed(sender, args)
{
alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}
</script>
<label for="Result">Resources:</label>
<div id="Result"></div>

RESULT:

READ-JSOM.PNG

Create Operation:

<script src="/SiteAssets/jquery.min.js"></script>
<script>
function create()
{
var name=document.getElementById("resName").value;
var role=document.getElementById("resRole").value;
var clientContext = SP.ClientContext.get_current();
var oList = clientContext.get_web().get_lists().getByTitle('Resource');
var itemCreateInfo = new SP.ListItemCreationInformation();
var oListItem = oList.addItem(itemCreateInfo);
oListItem.set_item('Title',name);
oListItem.set_item('Resource_x0020_Role',role);
oListItem.update();
clientContext.executeQueryAsync(QuerySucceeded, QueryFailed);
}
function QuerySucceeded()
{
alert("Record Created");
}
function QueryFailed(sender, args)
{
alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}
</script>
<table>
<tr>
<td><label for="resName">Enter the Resource Name:</label></td>
<td><input type="text" id="resName"/></td>
</tr>
<tr>
<td><label for="resRole">Enter the Resource Role:</label></td>
<td><input type="text" id="resRole"/></td>
</tr>
<tr>
<td colspan="2"><input type="button" value="Create" onclick="create()"></td>
</tr>
</table>

RESULT:

 

Update Operation:

<script src="/SiteAssets/jquery.min.js"></script>
<script>
function Update()
{
var role=document.getElementById("resRole").value;
var resourceId=document.getElementById("resId").value;
var clientContext = new SP.ClientContext.get_current();
var oList = clientContext.get_web().get_lists().getByTitle('Resource');
this.oListItem = oList.getItemById(resourceId);
oListItem.set_item('Resource_x0020_Role', role);
oListItem.update();
clientContext.executeQueryAsync(Function.createDelegate(this, this.QuerySucceeded), Function.createDelegate(this, this.QueryFailed));
}
function QuerySucceeded()
{
alert("Record Updated");
}
function QueryFailed(sender, args)
{
alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}
</script>
<table>
<tr>
<td><label for="resId">Enter the ID:</label></td>
<td><input type="text" id="resId"/></td>
</tr>
<tr>
<td><label for="resRole">Enter the Resource Role:</label></td>
<td><input type="text" id="resRole"/></td>
</tr>
<tr>
<td colspan="2"><input type="button" value="Update" onclick="Update()"></td>
</tr>
</table>

RESULT:

Delete Operation:

<script src="/SiteAssets/jquery.min.js"></script>
<script>
function Delete()
{
var resourceId=document.getElementById("resId").value;
var clientContext = new SP.ClientContext.get_current();
var oList = clientContext.get_web().get_lists().getByTitle('Resource');
this.oListItem = oList.getItemById(resourceId);
oListItem.deleteObject();
clientContext.executeQueryAsync(Function.createDelegate(this, this.QuerySucceeded), Function.createDelegate(this, this.QueryFailed));
}
 
function QuerySucceeded()
{
alert("Record Deleted");
}
function QueryFailed(sender, args)
{
alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}
</script>
<table>
<tr>
<td><label for="resId">Enter the ID:</label></td>
<td><input type="text" id="resId"/></td>
</tr>
<tr>
<td colspan="2"><input type="button" value="Delete" onclick="Delete()"></td>
</tr>
</table>

RESULT: