SEND HTTP REQUEST TO SHAREPOINT: "Missing URI Parameter"?

[ericonline]

No Google results for this error!~

Scenario:

• Send an HTTP request to Sharepoint action
  • Works great in the following circumstance:
    • Select 70 columns by name
    • Filter where ID is greater than or equal to 1
    • Bring back the top 5000 records

  • https://mycompany.sharepoint.com/sites/test/_api/web/lists/GetByTitle('my list')/items?$select=Title,Column1,Column2,Column70&$filter=ID ge 1&$top=5000

  • 
  • Does NOT work great (Fails with error) when the query is changed like so:

  • https://mycompany.sharepoint.com/sites/test/_api/web/lists/GetByTitle('my list')/items?$select=Title,Column1,Column2,Column70&$filter=Modified ge variables('varDateTime')&$top=5000

  • 
  • 

Error:

{

"message": "Uri parameter missing\r\nclientRequestId: 89c0710f-3007-4154-b28c-80a179168212",

"status": 400,

"source": "sharepointonline-eus.azconn-eus.p.azurewebsites.net"

}

Tried so far: All failed

• Hardcoded: 2019-09-18T22:42:04.6462931Z and '2019-09-18T22:42:04.6462931Z'
• Expression: formatDateTime(body('Get_past_time')) and formatDateTime(body('Get_past_time'), 'yyyy-mm-ddThh:mm:ss')

The WEIRD part of this is: When I remove a bunch of the 70+ columns, the query executes EXCEPT... it returns XML instead of JSON!!

Gosh... whats going on?!

[ericonline]

I even diffed the "Peek code" on the HTTP calls and all the variables...its basically just the changes in filter query (which I hold in two variables "varQueryFull" and "varQueryDelta").

Could not find anything that stands out...It must be something to do with filtering with a datetimestamp...?

[RezaDorrani]

Hi @ericonline 

Just tested this with 

/_api/web/lists/GetByTitle('Orders')/items?$select=Title,abba,Status&$filter=ID ge 1&$top=5000

and

/_api/web/lists/GetByTitle('Orders')/items?$select=Title,abba,Status&$filter=ID ge 1 and Created ge '@{addDays(utcNow(),-20)}' &$top=5000

both worked

bold part is expressions

question for you is - what is your use case/condition for the Created date field?

[ericonline]

Hello @RezaDorrani , thank you for peeking at this. 

I wonder what is different between our two tests.

This is successful:

This still fails when trying utcnow(now(),-20)...

[ericonline]

PS: The use case for using the Modified column is to grab deltas from a list. This Flow has two paths, one to grab a full download of records from the lists, the other path only grabs deltas (records which have changed in the last x days).

[RezaDorrani]

Hi @ericonline 

for getting items modified in last x days

Can you just try and directly use the below expression (highlighted in bold) as part of the query itself rather than reading it from a variable

/_api/web/lists/GetByTitle('Orders')/items?$select=Title,abba,Status&$filter=ID ge 1 and Modified ge '@{addDays(utcNow(),-20)}' &$top=5000

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ericonline]

Boom! Found the issue. Please test this if you can to confirm. And if anyone knows a workaround, I'd much appreciate.

Steps to reproduce:

• Send an HTTP request to Sharepoint
  • _api/web/lists/GetByTitle('yourTableName')/items?$select=you, need, 32, columns, to, test&$filter=Modified ge '2019-09-09T23:10:19.6357317Z'
  • This will fail
  • Change the $select to 31 columns and it will succeed.

Questions:

• Why does $select work with 64 columns:
  • _api/web/lists/GetByTitle('yourTableName')/items?$select=feel, free, to, use, all, 60-plus, columns, to, test&$filter=ID ge 1&$top=5000
• But filtering by Modified $select only works with a max of 31 columns?

[ericonline]

Hi @RezaDorrani , I was using that expression in the screenshot. (Arrow pointing to "Your expression")

[RezaDorrani]

@ericonline 

Test it with 33 columns and works for both so not sure why yours is acting up

This worked for me

/_api/web/lists/GetByTitle('Orders')/items?$select=Title,col1,col2,Status,a,b,d,e,f,g,h,i,j,k,l,m,n,o,p,q,s,t,u,v,w,x,y,z,cricket,football,tennis,play,pause&$filter=ID ge 1 and Modified ge '2019-09-09T23:10:19.6357317Z'

[ericonline]

Eh... ok, Thank you for testing that.

If its not NUMBER of columns, what about TYPE of columns?

Here are the data types of all 58 columns in this list:

• 54 Date and time
• 1 Lookup
• 2 Person or Group
• 1 Single line of text

[ericonline]

Also, I don't have an ID statement in the filter (&$filter=Modified ge '2019-09-09T23:10:19.6357317Z')

[RezaDorrani]

@ericonline 

It could be the col types 

Dont think ID makes a difference - I will test

[RezaDorrani]

@ericonline

No problem with dates either

Here is what my action looks like - No issues 

[ericonline]

Thanks for testing @RezaDorrani. 

Here is the full recap of the issue. One issue still occurring on my end.

This works on lists with >5k records (pulled in 5k batches):

• _api/web/lists/GetByTitle('tableName')/items?$select=ID&$top=1&$orderby=ID desc
• _api/web/lists/GetByTitle('tableName')/items?$select=ID&$top=1&$orderby=ID asc
  • Calculate diff, then
• _api/web/lists/GetByTitle('tableName')/items?$select=all, IDs, you, want&$filter=ID ge <batch1Start>&$top=5000
  • Then continue with batching

This works on lists with <5k records :

• _api/web/lists/GetByTitle('tableName')/items?$select=up, to, 31, columns, tested&$filter=Modified ge '2019-08-01'
• Note: I had to Index the Modified column to get around the 502 threshold error
• STILL GETTING A 401 unauthorized error on two lists 

[ericonline · Accepted solution]

More findings: 

- "400 Missing URI" error required me to formatDateTime to the SAME "friendly" format as the Sharepoint Modified column (MMMM dd, yyyy) for 2 of the lists

- "502 threshold" error required me to index the Modified column

- "401 unauthorized" error required me to formatDateTime to 'yyyy-MM-dd' for 2 of the lists AND to serialize the $select statement. 

  - Previously it was a variable set to:

Title,
Column2, 
Column3, 
ColumnN

  - This was adding extra linebreak characters to the URL when URL-encoded

- Changed it to a variable set to: 

Title,Column2,Column3,ColumnN

  - With no spaces or line breaks to URL-encode.

  - IF there is a max char count for URL's, I'm under it now even with 89 columns!

Wow! That was an effort...