0

i am writing code where i am updating rows from excel for which i have used rest api but few of my column in sharepoint are choice , persongroup,date type, when i am trying to create item for that its no working below is smaple code

        function createEmployeeDetailsListItem(excelRow) {
            $.ajax
                ({
                    url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Test')/items",
                    type: "POST",
                    data: JSON.stringify
                    ({
                        __metadata:
                        {
                            type: "SP.Data.E2EVMVulnerabilitiesMappingMasterListItem"
                        },
                           Title: excelRow["Title"],
                           ID:excelRow["ID"],
                           DateCol:excelRow[""], //Datecolumn
                           PersonGroupCol:excelRow[""], //Persongroupcolumn
                           ChoiceCol:excelRow[""] //Choicecolumn

                    }),
                    headers:
                    {
                        "Accept": "application/json;odata=verbose",
                        "Content-Type": "application/json;odata=verbose",
                        "X-RequestDigest": $("#__REQUESTDIGEST").val(),
                        "X-HTTP-Method": "POST"
                    },
                    success: function (data, status, xhr) {
                        console.log("success");
                    },
                    error: function (xhr, status, error) {
                        console.log("error");
                    }
                });
        }
  • 1
    For date column you will need data in mm/dd/yyyy format, for Person or Group field you will need ID you person/group and for choice column (single selection) you can pass string like "MyChoice". – Ganesh Sanap Sep 20 '19 at 12:02
  • 1
    Check my answer given [here]. Person or Group fields works same as lookup columns while updating values using REST. For Multiple selection choice fields check my answer given here. – Ganesh Sanap Sep 20 '19 at 12:09
  • thank you let me check this – kumar Sep 20 '19 at 12:17
  • it worked for date and choice column , but in my excel i have user email id how to insert them in people picker column ,user email may differ for each line item – kumar Sep 21 '19 at 5:05
  • 1
    For each email ID you need to fetch its sharepoint user ID using REST API or something. And then use that ID to add in person or group field. – Ganesh Sanap Sep 21 '19 at 5:16
1

See the updated sample code as below:

If you have single selection for User and Group field and for Choice field then create data as given below

function createEmployeeDetailsListItem(excelRow) {
var data = {
    __metadata: {
        type: "SP.Data.E2EVMVulnerabilitiesMappingMasterListItem"
    },
    Title: excelRow["Title"],
    ID: excelRow["ID"],
    DateCol: new Date(excelRow["Datecolumn"]).toISOString(), //Datecolumn
    PersonGroupColId: excelRow["Persongroupcolumn"], //here you need to set the Id of the Group or a User (For single user or group selection)
    ChoiceCol: excelRow["Choicecolumn"] //Choicecolumn, here you need to set you Choice as a Stirng "Choice 1" (Only work for single Choice selection)
}

$.ajax({
    url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Test')/items",
    type: "POST",
    data: JSON.stringify(data),
    headers: {
        "Accept": "application/json;odata=verbose",
        "Content-Type": "application/json;odata=verbose",
        "X-RequestDigest": $("#__REQUESTDIGEST").val(),
        "X-HTTP-Method": "POST"
    },
    success: function(data, status, xhr) {
        console.log("success");
    },
    error: function(xhr, status, error) {
        console.log("error");
    }
});}

If you have Multi selection for User and Group field and for Choice field then create data as given below

function createEmployeeDetailsListItem(excelRow) {
var data = {
    __metadata: {
        type: "SP.Data.E2EVMVulnerabilitiesMappingMasterListItem"
    },
    Title: excelRow["Title"],
    ID: excelRow["ID"],
    DateCol: new Date(excelRow["Datecolumn"]).toISOString(), //Datecolumn
    PersonGroupColId: { 'results': excelRow["Persongroupcolumn"] }, //Here excelRow["Persongroupcolumn"] is array of multiple choice - When you field set as Allow Multiple
    ChoiceCol: {
        "__metadata": {
            "type": "Collection(Edm.String)"
        },
        "results": excelRow["Choicecolumn"] //Here excelRow["Choicecolumn"] is array of multiple choice - When you field set as Allow Multiple
    }
}

$.ajax({
    url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Test')/items",
    type: "POST",
    data: JSON.stringify(data),
    headers: {
        "Accept": "application/json;odata=verbose",
        "Content-Type": "application/json;odata=verbose",
        "X-RequestDigest": $("#__REQUESTDIGEST").val(),
        "X-HTTP-Method": "POST"
    },
    success: function(data, status, xhr) {
        console.log("success");
    },
    error: function(xhr, status, error) {
        console.log("error");
    }
});}
|improve this answer|||||
  • tried using the same it working but not able to get user id for email address store in my excel from which i am reading and adding in sharepoitn list – kumar Sep 21 '19 at 7:28
  • You need to get another call to SharePoint to get the user id from the email address – Krunal Patel Sep 21 '19 at 14:20
  • var email = "testuser@domain.onmicrosoft.com"; Your ajax call url should be like this url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/siteusers/getbyemail(@v)?@v='" + encodeURIComponent(email) + "'", – Krunal Patel Sep 21 '19 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.