Trying to Update Sheet Columns with Data

Andrew Roe

I am currently trying to update my smartsheet sheet with data. I have used the smartsheet sheet_id to get all of the columns and there ID's associated with the sheet here:

Now I use that function inside of my other function which iterates and stores the necessary data to get the column ID's I need to iterate through here:

The data that you see being appended to the list "cloudcheckrBillingData" is coming from an api call I made above which I cannot show for security purposes but rest assured that the data is coming in and that's not apart of the issue. For reference the data looks like this [('hi','hello','goodbye'), ('thank you','for','your','help!')] it is a list of tuples. Once I have all that data I attempt to iterate over the sheetColumns data that I get from the above "getColums" call which returns a list of column ids. Then I iterate again over the cloudcheckrBillingData and then again to get each individual piece of data within the tuple. This data is all being passed to the "addToColumns" function which is this:

This function takes in the iterated columnId, the sheetId which is hardcoded at the top and works perfectly fine (can't show again for security reasons) and the iterated data from the last nested for loop above.

The error I am currently getting when running this program is as follows:

{"index": 0, "options": ["007475378486 (IR DOI-OSMRE)"], "title": "007475378486 (IR DOI-OSMRE)", "type": "TEXT_NUMBER"}

{"response": {"statusCode": 404, "reason": "Not Found", "content": {"errorCode": 1006, "message": "Not Found", "refId": "1krpbkz7vgvz9"}}}

{"result": {"code": 1006, "errorCode": 1006, "message": "Not Found", "name": "ApiError", "recommendation": "Do not retry without fixing the problem. ", "refId": "1krpbkz7vgvz9", "shouldRetry": false, "statusCode": 404}}

As you see the data in the first line is properly print but I get a 404 for some reason and I'm not sure how to interpret the rest. Any help would be greatly appreciated!!

Johnathan Vargas Cruz

I believe you are using the wrong SDK function to try and update your sheet. If you are intending to alter the column's attributes, then update columns is the correct thing to use. If you are wanting to insert data into the sheet, then update rows is needed.

Andrew Roe

I found out that you are correct Johnathan I do have another question if you have a moment. If I have multiple pieces of data that I want in separate can I use just one "row" for that?

Johnathan Vargas Cruz

Do you mean you have data that fits multiple cells in a given row or data that fits multiple rows in a given sheet?

For the former, create an array of objects that has [{id: rowId, cells: [{columnId: column, value: yourData}]}]

where rowId is the row that you want to update, column is the specific column that cell belongs to, and yourData is the new value. You will need to add more objects to cells depending on how many cells you want to update in a given row.

For the latter, create an array of objects just as above, but add more row objects to the first array.

Andrew Roe

So for the data I have I am iterating it such that one piece of data should fit in one cell as in the picture below:

The data above comes from this function here:

As you see I am creating a different row for each piece of data and for some reason the data is formatting in a staggered way on smartsheets, is there a reason for this? and is there a better way to add data to rows then what I did above? My main concern is that when I go to update those rows with new data instead of doing the adding that I'm doing here it will be different. Again, thank you so much for your help!

Johnathan Vargas Cruz

Sorry for the late response, but here are some notes:

The data is staggered on different columns because you are creating new row objects for each cell. If you want the data for a given row to remain on the same 'line' (actually be part of the same row in the smartsheet) append more cells to the row in the loop. Whenever you call smart.Sheets.add_rows, you are creating a new row object. Calling row_a.cells.append adds a cell object to given row. Assuming each iteration in the loop is a different row, you only need to add a row once, then append as many cells as you need.