SharePoint 2016 / ODATA to retrieve list items / List 'updated' property

Question

Hi all,

I have an interesting issue here in a VBA-based business solution that I cannot quite figure out. Yes, I know, but VBA is a business requirement, so we'll have to live with that for now.

To improve the performance of some scenarios where projects in MS Project need to be updated with the help of some SharePoint-based lists, I am planning to create a cache for SharePoint lists that should ensure lists are only downloaded via ODATA once per session, or when the list has changed since it was last downloaded.

For this reason, the cache should remember the 'updated' property of the list, and whenever the same URL (including parameters) is addressed, the cache should first download the 'updated' property of the list only, compare it to the date it remembered from the last full download, and only download fully again if there is a difference between both (or if the cache does not know this URL at all yet). In case the date has not changed, the cache should just return the XMLDocument that it received during the last full download. So a download is made each time, but in the best case it's a minimal one just to verify the timestamp without any more data. In case the cache is stale, or the URL still unknown, a second download will be performed to get all list items, or at least the ones corresponding to the filter criteria.

Following is an example where host name, list name, and parameters were replaced by placeholders.

ODATA URL for retrieving the items from a given list, filtered by some criteria:

https://server/app/00041/1123/_api/web/lists/getbytitle('my list')/items?$filter=((criteria1 eq 'foo') or (criteria1 eq 'bar'))  

To find the timestamp of the most recent update of this list, the code will shorten the URL by finding the /items? substring and replace it (along with the rest of the original URL) with ?$select=updated, resulting in this:

https://server/app/00041/1123/_api/web/lists/getbytitle('my list')?$select=updated  

Doing this in the browser (I'm restricted to Edge unfortunately so I cannot verify if it is the same in other browsers) will consistently return something like this:

<entry xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:georss="http://www.georss.org/georss" xmlns:gml="http://www.opengis.net/gml" xml:base="https://server/app/00041/1123/_api/" m:etag=""15"">  
  
  <id>https://www.server/app/00041/1123/_api/Web/Lists(guid'8d70de50-97ac-45ff-8868-c16deefc478d')</id>  
  
  <category term="SP.List" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>  
  
  <link rel="edit" href="Web/Lists(guid'8d70de50-97ac-45ff-8868-c16deefc478d')"/>  
  
  <title/>  
  
  **<updated>2022-08-17T17:02:34Z</updated>**  
  
  <author>  
  
    <name/>  
  
  </author>  
  
  <content type="application/xml"/>  
  
</entry>  

So the document's content element is expectedly empty to keep it minimal, what counts is that the entry/updated element exists and has a value. I'm assuming the root entry element represents the list itself, and the updated element gives me the last point in time where any change took place in the list or its items.

When I refresh in the browser, the updated element does not change at all, as expected.

However, when I use the very same URL over WinHTTP in VBA, the updated element will always return the current clock time! Well, a different time zone actually but the code is capable of converting that to local time. What's important is that I can see a different clock time in each request's response regarding the entry/updated element's content, always some seconds later than in the previous request's response. Checking in the browser again proves that the date actually did not change.

So it seems as if WinHTTP retrieving the XML Document for some reason causes SharePoint to return 'now' in the updated element, not the actual 'last modified' date of the list which I need.

Needless to say that credentials / access privileges are the same in the browser as in the VBA client application so that should not make a difference.

I have tried to take a deeper insight into the HTTP traffic with Fiddler4 portable but could not find the XML content at all when Edge (the only browser available on the platform I'm using) is navigating to the URL. It just shows me SSL details whereas it sees an XML content when WinHTTP retrieves the document from the same URL, and in that case Fiddler also sees the slipping date. So it's not a WinHTTP artifact, the date is actually treated very differently depeneding on what software uses the ODATA endpoint.

It's probably something in the HTTP headers, however, I wouldn't know what to look for. There must be a key difference between header data in the HTTP request the browser runs and the one that originates in WinHTTP, but both look totally different in Fiddler4 so there is nothing to compare.

Maybe I'm no a completely wrong track here. What I need is the overall 'last changed' timestamp for the list including all of its items, so whatever changes should update this timestamp, but it should not change by itself of course. And it's very unsettling to see that WinHTTP requests are not treated the same way as Edge browser requests despite using the same URL on both.

I'd be grateful for any ideas on this matter.

Thank you very much!

Cheers,

Joe

Answer 1

Hi @Johannes Franke ,

Based on your description, my understanding is: You need the last modified time of the full list. If my understanding is incorrect, please feel free to contact me.

According to my research and testing , you can use the following Rest API to get the last Modified time of the full list:

https://xxx/xxxx/xxxx/_api/web/lists/getbytitle('[ListName]')?$select=LastItemUserModifiedDate  

Hope it can help you. Thanks for your understanding.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Dear TongZhangMSFT-7548,

thanks a ton, actually I have figured it out in the meantime but was not able to post my solution here. It's the same field you mentioned, and I'm also including the LastItemDeletedDate because I consider that a list change, too. Will just pick the later one of both dates.

Cheers,
Joe

Answer 3

All right, stupid me. It was the wrong property I kept looking at.
Actually the request should be:

https://server/app/00041/1123/_api/web/lists/getbytitle('my list')?$select=**LastItemDeletedDate,LastItemModifiedDate**  

Resulting in:

<entry xml:base="https://server/app/00041/1123/_api/" m:etag=""37"">  
  <id>  
    https://server/app/00041/1123/_api/Web/Lists(guid'ddf07101-93ea-4f0d-b578-90da3d9d294f')  
  </id>  
  <category term="SP.List" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>  
  <link rel="edit" href="Web/Lists(guid'ddf07101-93ea-4f0d-b578-90da3d9d294f')"/>  
  <title/>  
  <updated>2022-08-19T11:10:22Z</updated>  
  <author>  
  <name/>  
  </author>  
  <content type="application/xml">  
    <m:properties>  
      <d:LastItemDeletedDate m:type="Edm.DateTime">2022-07-22T08:25:55Z</d:LastItemDeletedDate>  
      <d:LastItemModifiedDate m:type="Edm.DateTime">2022-08-19T11:03:52Z</d:LastItemModifiedDate>  
    </m:properties>  
  </content>  
</entry>  

These two fields deliver the correct timestamps, I'll just use whatever value is the later one.
Thanks to this page where I found the key info: https://www.sharepointdiary.com/2019/03/sharepoint-online-get-list-last-modified-date-using-powershell.html

So this can now be considered resolved. Maybe it helps somebody else.
Cheers,
Joe