API Query for new Row

Kuna Sheelan

Hi. I would like to add a new row to an existing sheet. I need to create a query to pass into the API so the items I want to add, goes into each of the row columns in the sheet. Can I have a sample or idea of how I can do that? I would like to pass a JSON query. I couldn't find the right inputs. Do I have to be an admin to add rows as as well?

Lucas Rayala

Hi @Kuna Sheelan, I don't have my code in front of me, but there's example code on the Smartsheet's Redocly page:

Smartsheet - Official API and SDK Documentation (redoc.ly)

Navigate to the "row" object on the left scroll bar and click on "Methods" -- that will give you example code.

Kuna Sheelan

Hi @Lucas Rayala ,

I greatly appreciate the response. Thank you. I will try this. =)

Kuna Sheelan

Hi,

I believe I am very close.

I have a current sheet with a bunch of columns. How can I know/find the IDs for the column? =)

Lucas Rayala

@Kuna Sheelan I put a snip of my append code below (Python) -- you need to pass your data to this function as a panda dataframe ("df"). FYI, the API takes your commands as a single URL which means there are physical character restrictions, so I have this chunk the API calls to 300 rows of data -- that's a default, you can change it if you want to play around. Also, this code wraps your data in a string function ie str(df.iat(i,j)) you can remove that function if you need to, but I would keep it while you troubleshoot because Smartsheet is picky with data types.

ss = smartsheet.Smartsheet(API_TOKEN)

def append_df(df, sheet_id, parent_row, chunk_interval=300):        # appends df to sheet underneath a single header row
    list_of_rows = []   
    list_of_columns = []

    for c, column in enumerate(df):                                 # column id list, done here to limit smartsheet server hits
        list_of_columns.append(ss.Sheets.get_columns(sheet_id, include_all=True).data[c].id)

    for i, rows in df.iterrows():                                  # loop to create temp rows for the remainder of the df
        temp_row = ss.models.Row()     
        temp_row.to_bottom = True     
        for j, columns in enumerate(df):      
            temp_row.cells.append({
                'column_id': list_of_columns[j],   
                'value': str(df.iat[i,j])                           # string function used to eliminate load errors
            })
        list_of_rows.append(temp_row)                                       


    for x in range(0, len(list_of_rows), chunk_interval):           # chunks rows (300 default). more rows may cause failure (URL length limit)
        ss.Sheets.add_rows(sheet_id, list_of_rows[x:(x + chunk_interval)]) 
  

Kuna Sheelan

Hi,

I was successfully able to write a new row and execute what I needed. Thank you so much. This was much easier than I anticipated. =) Thank you for your help @Lucas Rayala !

bsikes

@Kuna Sheelan If it's at all helpful, I would like to point out that ChatGPT has gotten ridiculously close to providing working code that interacts with the Smartsheet API. We have several projects that could have been greatly expedited if we had this tool when we started.

Lucas Rayala

Hi @Kuna Sheelan glad to help and I'm glad you got it working quickly! @bsikes I've played around with ChatGPT for this and SQL code -- it's a great assist. I have a feeling that once it gets more training on the specific API it will really be able to nail down requests. I know some coders who have been at it for twenty years and they say that they pretty much let ChatGPT do 80% of the work. I think it's better the more coding experience you have, because the little hiccups are always hard to catch.

bsikes

@Lucas Rayala That's been my experience as well. As long as you're able to understand what it's trying to do and have some skill in troubleshooting why it's not working, it can definitely provide a quick framework for the task at hand. Even then, you can usually ask it to explain what the code it generated is supposed to do, and it can usually do a good job explaining that.

I've even given it some of my code that I did a poor job documenting, and asked it to provide comments throughout explaining what it does...

Joaquinwood

Maybe someone in this thread can help me answer the question I posted here: Why does the API require the row id to add a row? — Smartsheet Community