Get cell history of all the rows in a column

ahumne25
ahumne25 ✭✭
in API & Developers

Hello,

This is a very urgent requirement I received from my team. They want the history of all cells in a column. I've spent quite a few hours exploring Smartsheet forums. However, the only solution I found was by adding the ID of each row and column individually via API to get the cell history of that particular cell. I want to implement this for 4000 rows, is there a better way of doing it?

Could you please help? Thank you!

· Share on FacebookShare on Twitter

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @ahumne25, you use code to get all the row IDs from your sheet and then use that output to grab all the cell histories. You iterate through all the rows to get the IDs, batch the IDs into a list and then use the list to grab the cell histories. You need to have your code batch the requests by about 300 rows or the request won't go through. It will take the code about 2 minutes to run, I do something similar. Do you have anyone who can run the code?

    · Share on FacebookShare on Twitter
  • ahumne25
    ahumne25 ✭✭

    Thank you for the prompt response Lucas. Unfortunately I do not have anyone who can write the code.

    However, I can try this out myself. Would you know if there are any articles written on the solution you proposed?

    The example on Smartsheet API doc is for just 1 cell at a time.

    · Share on FacebookShare on Twitter
  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 03/07/23

    @ahumne25 -- this isn't trivial if you're just getting started. It will take you some time (potentially days) to get everything set up.

    First, make sure you can access an API token -- go to your account icon in the lower left corner, select Personal Settings, then API Access. If you have the ability to click and generate a new API access token, then you have the correct plan type to move forward. Otherwise you do not.

    If you have the correct plan type, then you need to get Python up and running on your system. If you are in a company, you may need to check with your IT department about using and accessing Python, and you may need to work through your companies proxy server. Then, you can Google "getting started with Python and Visual Studio" on YouTube -- expect a bit of work. Get back to me with an @mention if you get this all going. I can provide some code to get you moving.

    · Share on FacebookShare on Twitter
  • Tidhar
    Tidhar ✭

    Hi @Lucas Rayala ,

    can you share how you perform a batch request to retrieve cell histories of multiple rows?

    · Share on FacebookShare on Twitter
  • Alex Argumedo
    Alex Argumedo ✭✭✭✭✭

    As far as I know, you can only retrieve one row at the time, each row has multiple history lines, and you can get many lines of that history at once, you can control and get more history lines but increasing the page size, you can also do pagination so you can get all history lines, but the call is per row.

    See the documentation on how to call it:

    https://smartsheet.redoc.ly/tag/cells#operation/cellHistory-get

    depending on the number of sheets, and rows in your sheet, and the amount of history you need it can take sometime. Always consider that there is SS limits of the amount of calls you can make per minute. Each history call cost 10 times what it cost a simple call, you need pacing if you have a large set of data to retrieve from the rows.

    · Share on FacebookShare on Twitter
  • Tidhar
    Tidhar ✭
    https://community.smartsheet.com/discussion/comment/457852#Comment_457852

    Thanks for the comment!

    As per documentation, and my tests, List Cell History operation requires sheet_id, row_id and column_id, one object of each.

    I can use it to retrieve history of one cell at a time (sheetXrowXcolumn), how can I use it, or other operation, to retrieve history of the whole row in one api call?

    Also, do you know what is the limit number of calls per minute?

    · Share on FacebookShare on Twitter
  • Alex Argumedo
    Alex Argumedo ✭✭✭✭✭

    @Tidhar As you can see the title of this thread, they are asking how to get the history of all rows of an specific column, my response is considering that question. But to answer your question, only once cell can be retrieved at the time.

    The SS token limit is 300 tokens per minute, a simple call like get sheet, or get row uses one token, but a history call cost 10 tokens each.

    · Share on FacebookShare on Twitter

Categories