I want to get all the records on a sheet by page with such query https://api.smartsheet.com/2.0/sheets/{sheetId}?rowsModifiedSince={date}&pageSize=20&page=10
But response hasn't totalPages or similar parameter. How to find a total number of pages in a sheet?
Of course, I can check the number of rows on the current page. If it is equal to the 'pageSize' parameter then the next sheet exists. This does not seem to be correct as on the sheet can be a multiple of the number of rows.
The Get Sheet response contains a totalRowCount parameter that specifies the total number of rows in the sheet. You can calculate the total number of pages (i.e., the total number of Get Sheet requests that you'll need to submit to retrieve all rows in the sheet) by dividing the totalRowCount value by the pageSize value you're specifying in the request query string, and rounding the result up to the next whole number.
For example, if:
Your request specifies pageSize=20 (e.g., https://api.smartsheet.com/2.0/sheets/{sheetId}?pageSize=20&page=1).
And the value of the totalRowCount parameter in the Get Sheet response is 105.
This means you'll need to submit 6 Get Sheet requests (each one specifying pageSize=20) to have retrieved all rows in the sheet (because totalRowCount/pageSize = 105/20 = 5.25, rounded up to the next whole number is 6). Each of the first 5 responses (pages) would contain 20 rows, and the 6th response would contain the remaining 5 rows.
** UPDATE #1 **
If you're specifying a query string parameter (e.g., rowsModifiedSince) then the strategy above won't work -- because the value of totalRowCount always indicates the total number of rows in the sheet, which is not necessarily equal to the number of rows that meet the criteria you've specified via the query string parameter(s). In that case, you can just continue submitting Get Sheet requests, incrementing the value of the page parameter each time, until you receive a response where the rows property is an empty array (as shown in the response example below) -- this indicates that the previous page you requested was the final page of results.
{
"id": 3932034054809476,
...
"rows": []
}
** UPDATE #2 **
Unfortunately I'm seeing inconsistent behavior from Smartsheet with the approach described above.
If the last page with results is a full page of results, then the response to the request for the next page of results will return an empty rows[] array. e.g., if 4 rows meet the criteria I specify, and I specify pageSize=2 in each request -- then the first two pages will each contain 2 rows and the third page (i.e., request issued with pageSize=2&page=3 in the query string) will return an empty rows[] array.
However, if the last page with results is not a full page of results, then the response to the request for the next page of results returns the last page of results again. e.g., if 3 rows meet the criteria I specify, and I specify pageSize=2 in each request -- then the first page contains 2 rows, the second page contains 1 row, and the third page (i.e., request issued with pageSize=2&page=3 in the query string) will contain the same rows that were in the previous/last page of results -- i.e., the same 1 row that was returned for page=2.
Unfortunately, this means you may have to resort to doing something like keeping track of the row id values that you receive in each response (and if you get a response that contains a row id you've received previously, this would indicate that the page you requested previously was actually the final page of results).