topic RE: Sharepoint List Library VBO - Odata queries in Digital Exchange

Sharepoint List Library VBO - Odata queries

SwatiGupta — Thu, 29 Aug 2019 16:09:00 GMT

Hi
I am trying to utilize Sharepoint List Library VBO from Digital exchange. I tried to update few records/items in sharepoint but I am facing issues with customized Odata query. I tried with different HTTP methods in my query as well PUT, POST and PATCH but it didn't work. Can you please help us to share how we can update sharepoint data and what will be customized Odata query syntax?

Here are few sample queries to pull/read data from sharepoint:
getbytitle('Test List 1')/items?$filter=(FName eq 'FNameABC')
getbytitle('Test List 1')/items?$filter=ID eq 5

But for update, no luck. Attached is the screenshot of sharepoint.
Update scenario: Update any item under FName column.

------------------------------
Swati Gupta
Senior Developer
Tyson Foods
Asia/Bengaluru
------------------------------

RE: Sharepoint List Library VBO - Odata queries

BimalSebastian — Wed, 04 Sep 2019 14:04:00 GMT

Hi Swati,

OData is generally used with Gets since its a query.

Inorder to run an update, you will need to split you API call into two pieces. Post API calls.

Part 1: Getting the "ListItemEntityTypeFullName". This is needed to update items in a list. The reference implementation on how to get this value is available in the sample process in the DX along with this skill. Refer the Create Item Page where there is an action added to get this value. you will not need to create a new APi for this, since this is already present. you only need to use the same action by referring to the create item page in the reference process.

Part 2: Once you have this value, you will need to create a request in the same lines as the Create items APi call. The only changes that will be needed are, the Request endpoint, Headers of the request and the parameter "[Item Fields and Values]". The template and all other values will remain same as the create items request.

The changes needed are below,

A. Request End Point (notice the item id, ensure you have the right id)

http://site url/_api/web/lists/GetByTitle('Test')/items(item id)

B. Headers(these are not present for create items, but you will need it for update)

"IF-MATCH": etag or "*" "X-HTTP-Method":"MERGE"

C. [Item Fields and Values] : Keep everything same. in the value field just make sure the updated values are present.

------------------------------
Bimal Sebastian
Consultant
Blueprism
Asia/Kolkata
------------------------------

RE: Sharepoint List Library VBO - Odata queries

RyanGodsey — Wed, 04 Sep 2019 15:08:00 GMT

Bimal,

Thank you for the info. Independently from Swati, I had gotten as far as creating an Update Item API as you've described. However, we are getting the following error.

Internal : Unexpected error Error during Web API HTTP Request

HTTP Status Code: 400

HTTP Response Content: {"error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"en-US","value":"The type SP.ListItemEntityCollection does not support HTTP PATCH method."}}}

Here is the set up:

I have tried both "etag or *" and simply "*"

Ryan Godsey, Sr. Developer Applications
Applied Technology & Innovation, Automation

Tyson Foods
319 E Emma Ave, Springdale, AR 72764
479-290-1097
ryan.godsey@tyson.com

This email and any files transmitted with it are confidential and intended solely for the use of the addressee. If you are not the intended addressee, then you have received this email in error and any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. Please notify us immediately of your unintended receipt by reply and then delete this email and your reply. Tyson Foods, Inc. and its subsidiaries and affiliates will not be held liable to any person resulting from the unintended or unauthorized use of any information contained in this email or as a result of any additions or deletions of information originally contained in this email.

------Original Message------

Hi Swati,

OData is generally used with Gets since its a query.

Inorder to run an update, you will need to split you API call into two pieces. Post API calls.

Part 1: Getting the "ListItemEntityTypeFullName". This is needed to update items in a list. The reference implementation on how to get this value is available in the sample process in the DX along with this skill. Refer the Create Item Page where there is an action added to get this value. you will not need to create a new APi for this, since this is already present. you only need to use the same action by referring to the create item page in the reference process.

Part 2: Once you have this value, you will need to create a request in the same lines as the Create items APi call. The only changes that will be needed are, the Request endpoint, Headers of the request and the parameter "[Item Fields and Values]". The template and all other values will remain same as the create items request.

The changes needed are below,

A. Request End Point (notice the item id, ensure you have the right id)

http://site url/_api/web/lists/GetByTitle('Test')/items(item id)

B. Headers(these are not present for create items, but you will need it for update)

"IF-MATCH": etag or "*" "X-HTTP-Method":"MERGE"

C. [Item Fields and Values] : Keep everything same. in the value field just make sure the updated values are present.

------------------------------
Bimal Sebastian
Consultant
Blueprism
Asia/Kolkata
------------------------------

RE: Sharepoint List Library VBO - Odata queries

BimalSebastian — Wed, 04 Sep 2019 15:53:00 GMT

Hi Ryan,

It appears you have gotten almost all things right.
I believe the error message is sort of generic for multiple types of errors in sharepoint. (I hate that about sharepoint.)
Anyway, over the top of my head i can think of the following potential reasons,
1. Missing form digest value in header ( i know it may not be needed for create item, but sharepoint is sort wierd that way. try this)
2. May be there are quotes missing in the http request formed. Try quotes between the itemId, or in the header values like say "MERGE" rather than just MERGE.

Have you tried capturing the request data through fiddler or other tools. If, so can you paste the raw http msg here.

I will try this independently at my side when i get some time. Will keep this thread posted if i find something.

------------------------------
Bimal Sebastian
Consultant
Blueprism
Asia/Kolkata
------------------------------

RE: Sharepoint List Library VBO - Odata queries

RyanGodsey — Wed, 04 Sep 2019 20:53:00 GMT

Adding the Form Digest Value header and an adjustment to how we pass the field/value pairs in did the trick!

Where create wanted this format with the double quotes surrounding the whole thing ("'Title' : 'API Test 1', 'FName' : 'FName 1', 'LName' : 'FName 2', 'Gender' : 'Male' "), update wanted us to pass it with double quotes around the field/value parts and no quotes around the entire string ("Title": "API Test1", "FName": 'FName 1").

------------------------------
Ryan Godsey
Sr. Developer Applications
Tyson Foods
America/Chicago
------------------------------

RE: Sharepoint List Library VBO - Odata queries

SwatiGupta — Thu, 05 Sep 2019 15:26:00 GMT

Thank you Ryan and Bimal. It's working now

------------------------------
Swati Gupta
Senior Developer
Tyson Foods
Asia/Kolkata
------------------------------

RE: Sharepoint List Library VBO - Odata queries

HuyTrinh — Fri, 20 Sep 2019 16:35:00 GMT

Ryan, Bimal and Swati - Would you mind sharing how you determine your REST authentication method for this skill in your solution? Looking at the documentation, there are 2 methods available: ACS based authentication and Azure AD Certificate. They seemed to require you to have admin access to the root site of your company.

The list I'm looking to update belongs to a subsite in SharePoint. I doubt our enterprise SharePoint admin would be willing to give me access to the root site of the company which host all other sites within the company. Does bot need to have same privileges as developer in order to execute the REST calls? What's the sufficient access to develop and execute in this context? Are there any dependencies you need to maintain separately for production, code & environment?

Maybe it's just I'm ignorant on this topic. Hope to get your thoughts soon.

------------------------------
Huy Trinh
Software Engineer II
Metlife
America/New_York
------------------------------

RE: Sharepoint List Library VBO - Odata queries

BimalSebastian — Thu, 26 Sep 2019 11:13:00 GMT

Hi Huy Trinh,

You really dont need access at the root site level. You can have the privileges maintained at the subsite level for both authentication modes.
They key difference between both the auth mode is that Sharepoint ACS is a legacy mode of authentication, which will soon be depricated by Microsoft. The Azure AD based auth is more modern and can be managed through Azure and I recommend this model.

Now, the sufficient access really comes down to the action you intend to perform. If its only reading lists for example, then a read only access would suffice. If you intend to upload files, then you will need a Write access.

However, key consideration here is, API access to sharepoint will give permissions to the ClientId holder at site or sub site level only unlike UI based automation, where you could limit access at folder level as well.

Thanks
Bimal

------------------------------
Bimal Sebastian
Consultant
Blueprism
Asia/Kolkata
------------------------------

RE: Sharepoint List Library VBO - Odata queries

SankarSarma — Mon, 21 Oct 2019 09:14:00 GMT

Hi Ryan - I tried with your additional steps, but its same error.

My request looks like below.

{ '__metadata':
{ 'type': '[List Item Type Name]' },
"'LineItemNumber':'Closed'"
}

I am passing header as below.

IF-MATCH	etag or *
X-HTTP-Method	MERGE
X-RequestDigest	[Form Digest Values]

------------------------------
Sankar Sarma
RPA Consultant
Lion Co
------------------------------

RE: Sharepoint List Library VBO - Odata queries

SwatiGupta — Mon, 21 Oct 2019 10:22:00 GMT

Hi Sankar,

The header looks like the attached file.Share the error message. Ryan has shared all the screenshots and your request should looks like

{ '__metadata':
{ 'type': '[List Item Type Name]' },
[Item Fields and Values]
}

Thanks,
Swati
Tyson Foods

------------------------------
Swati Gupta
Senior Developer
Tyson Foods
Asia/Kolkata
------------------------------

RE: Sharepoint List Library VBO - Odata queries

SankarSarma — Mon, 21 Oct 2019 10:35:00 GMT

Hi Swathi - Here is my setup.

Hard coded the item/value pair to show the format.

Error Message

Sankar Sarma

RPA Analytics Leader

Please consider the environment before printing this e-mail.

------Original Message------

Hi Sankar,

The header looks like the attached file.Share the error message. Ryan has shared all the screenshots and your request should looks like

{ '__metadata':
{ 'type': '[List Item Type Name]' },
[Item Fields and Values]
}

Thanks,
Swati
Tyson Foods

------------------------------
Swati Gupta
Senior Developer
Tyson Foods
Asia/Kolkata
------------------------------

RE: Sharepoint List Library VBO - Odata queries

SankarSarma — Mon, 21 Oct 2019 23:11:00 GMT

This issue is fixed now, problem was unwanted space in the body. also I am passing the item key/value with out double quotes.

------------------------------
Sankar Sarma
RPA Consultant
Lion Co
------------------------------

RE: Sharepoint List Library VBO - Odata queries

MelanieGiulian1 — Tue, 22 Oct 2019 14:50:00 GMT

Thanks so much for sharing the solution!

------------------------------
Melanie Giuliani
Analyst
CA Technologies
America/New_York
------------------------------

RE: Sharepoint List Library VBO - Odata queries

SwatiGupta — Fri, 25 Oct 2019 10:34:00 GMT

HI Bimal,

I am using ODATA query to retrieve the records from sharept but query is retrieving maximum 100 records. Is there any issue with API limited to 100 records? If it is API limitation, can you please suggest solution.

I have tried top filter and it is working but I can't use top filter.

https://sharepoint.stackexchange.com/questions/74777/list-api-get-all-items-limited-to-100-rows

I have checked the item limit in sharept it is 30 so I think it is not related to this.

Thanks,
Swati

------------------------------
Swati Gupta
Senior Developer
Tyson Foods
Asia/Kolkata
------------------------------

RE: Sharepoint List Library VBO - Odata queries

NicholasRobbins — Thu, 31 Oct 2019 15:51:00 GMT

Hi, I am working on SharePoint List Library Skill API and my IT Team is concerned with setting up the client secret in Azure because they do not want our BP to have read/write to all of our company's SharePoint pages/sites. Does anyone know how we can limit the API's access, or have a workaround?

------------------------------
Nicholas Robbins Leon
RPA Developer
Darden
Orlando
407-245-6520
------------------------------

RE: Sharepoint List Library VBO - Odata queries

Kamal2017 — Mon, 09 Nov 2020 17:14:00 GMT

I did tried the same web API config however getting 407 Proxy authentication required issue. Do you know how to fixed this issue ? or Can someone please help me to get the actions to get the Form digest value ?

------------------------------
Kamal Ahmed RPA Developer
Sr. Programming Analyst
BNY Mellon
Asia/Kolkata
------------------------------

RE: Sharepoint List Library VBO - Odata queries

Kamal2017 — Mon, 09 Nov 2020 17:22:00 GMT

Hi Ryan,

I did tried the same web API config however getting 407 Proxy authentication required issue. Do you know how to fixed this issue ? or Can you please help me to get the actions to get the Form digest value ?

------------------------------
Kamal Ahmed RPA Developer
Sr. Programming Analyst
BNY Mellon
Asia/Kolkata
------------------------------

RE: Sharepoint List Library VBO - Odata queries

Kamal2017 — Thu, 12 Nov 2020 13:59:00 GMT

Hi Ryan,

Could you please help.

@Bimal Sebastian - Hi Bimal, Will you be able to help me with this part to perform operations like Get Data, Edit Field/Column on SharePoint Online list.

------------------------------
Kamal Ahmed RPA Developer
Sr. Programming Analyst
BNY Mellon
Asia/Kolkata
------------------------------

RE: Sharepoint List Library VBO - Odata queries

VinayaShastry — Fri, 02 Jul 2021 19:48:00 GMT

Can you send me a link to download SharePoint List Library VBO from DX?

------------------------------
Vinaya Shastry
------------------------------

RE: Sharepoint List Library VBO - Odata queries

VinayaShastry — Sun, 04 Jul 2021 06:23:00 GMT

How do you get Form Digest Value in SharePoint 2016?

------------------------------
Vinaya Shastry
------------------------------