rowsModifiedsince is not returning all rows meeting the criteria

LJR

I have an app that queries a source sheet, looks for rows modified in the last few days, checks if the attachment has already been posted to SharePoint, and, if not, grabs the attachment and posts it in SharePoint.

This is one if my GET calls …

https://api.smartsheet.com/2.0/sheets/[sheetID]?rowsModifiedsince=2025-01-30T01:00:00Z

(sheetID removed for security)

The returned dataset includes fourty-four (44) rows from the source Smartsheet sheet, but there should be fourty-six (46) rows. The two missing rows are within the rowsModifiedsince parameter.

This has happened a handful of times since putting this app into production. The dates are formatted correctly, and there are no other parameters on the GET call. I have not been able to deduce why the returned data set is incomplete. Can anyone help?

Michelle Choate 2

Could it be because your app is set to your timezone and the system columns are set to UTC?

LJR

@Michelle Choate 2 - I've tried adjusting rowsModifiedsince by as much as 48 hours thinking maybe there was some misconfig due to timezones, but the rows are still not included in the returned data set. I've also reached out to Smartsheet Support about this in the past, but they were not helpful. I've also tried different platforms (PowerAutomate and Postman), but the returned dataset did not change between platforms.

Michelle Choate 2

Hmm. That is super strange! @Darren Mullen - have you seen this behavior before?

Darren Mullen

@Michelle Choate 2 @LJR

Thanks for the shout out. I've never queried a sheet over the API using the modifiedsince flag.

It would be interesting to see if you created a filter on the sheet based on the modified date does it give you what you want, and then you could GET the sheet with the filter applied by the filter ID.

LJR

@Darren Mullen - I'd considered trying a filter, but it's not an option for this op scenario. (1) I do not have permission from the org unit to create/apply filters on their sheet. (2) This app runs nightly and it's not feasible to apply a filter every day.

The strangeness of this issue is due to the fact that, most nights, Smartsheet sends the correct dataset based on rowsModifiedsince, in its entirety, whether it 1-2 rows or 15 rows of data. But occasionally (once every 1-2 months), the data set sent by Smartsheet will be incomplete. I've spent hours with Smartsheet Support troubleshooting the missing rows and, by all evidence, there's no reason the rows should not have been sent.

shocker

This might be a complete red herring, or simply a stupid guess, but I've seen SharePoint (On-Premises) display very similar behavior. I was sending a similar request to SharePoint with a date query parameter, like modifiedSince or modifiedBefore, and I found that some results which were "ties" on modification date, as in they all had exactly the same modification date, were being dropped if they happen to have a modification date which fell on the page-break count of 100. SharePoint pages (or did) its results of a REST call in counts of 100 by default. So if I had 23 results with a modification date of exactly 2025-02-01T02:13:06Z, for example, and those 23 results spanned across the page limit of 100, then a portion of those results would be dropped. It was strange, and disappointing to me.

But as I said, this post may be a complete red herring and require immediate deletion. It feels quite similar though.

If what I have described is what's happening, then I'm saying that Smartsheet's API has a bug. And I don't know that I'm qualified to make that statement. But I would be curious enough to run a few more tests.