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

MayankDuttKaushik

Hi @Kuna Sheelan,

I came across the issue you're facing and noticed that @Lucas Rayala approach is a good start but has a limitation: it converts all values to strings, including numbers. This can cause issues when numbers are treated as text in Smartsheet, especially with an unwanted apostrophe (') prefixing the number, like '1922 instead of1922.

def append_df(pandas_df, sheet_id, chunk_interval=300):

  list_of_rows = []list_of_columns = []
  sheet_columns = smartsheet.Sheets.get_columns(sheet_id, include_all=True).data  # Get the columns from Smartsheet
  for pandas__df_column in pandas_df.columns:  
      try:
          column_id = next(sheet_column.id for sheet_column in sheet_columns if sheet_column.title == pandas__df_column)  # Find the matching column ID
          list_of_columns.append((pandas__df_column, column_id))  # Append the column ID to the list
      except StopIteration:
          raise ValueError(f"Column '{pandas__df_column}' not found in Smartsheet")  # Handle missing columns
  
  for i, row in pandas_df.iterrows():  
      temp_row = smartsheet.models.Row()  # Create a new row
      temp_row.to_bottom = True  # Place the row at the bottom
      for column, column_id in list_of_columns:
          cell_value = row[column] if pandas.notnull(row[column]) else None  # Get the cell value, handling nulls
          if cell_value is not None:
              cell = smartsheet.models.Cell()  # Create a new cell
              cell.column_id = column_id  # Set the column ID
              
              if pandas.api.types.is_numeric_dtype(type(cell_value)):  # Check if the value is numeric
                  if '.' in str(cell_value):  # If the value is a decimal
                      cell.value = float(cell_value)  # Use float for decimal numbers
                  else:
                      cell.value = int(cell_value)  # Use int for whole numbers
              else:
                  cell.value = str(cell_value).strip("'")  # Strip extra apostrophes for text fields
              
              temp_row.cells.append(cell)  # Append the cell to the row
              
      if not temp_row.cells:
          print(f"Row {i} skipped because it has no valid cells.")  # Handle rows with no valid cells
      else:
          list_of_rows.append(temp_row)  # Add the row to the list
  
  for x in range(0, len(list_of_rows), chunk_interval):  # Add rows in chunks
      response = smartsheet.Sheets.add_rows(sheet_id, list_of_rows[x:(x + chunk_interval)])  
      if response.message:
          print(f"{response.message}")  # Print the response message

1. Numeric Handling: The key change is the use of pandas.api.types.is_numeric_dtype() to check if the value is numeric. If it's numeric, we explicitly set it as int or float to ensure Smartsheet treats it correctly as a number. This avoids the apostrophe (') issue seen when numbers are treated as strings.
2. Removing Unwanted Apostrophes: For non-numeric values (text fields), we ensure any leading apostrophes are stripped using str(cell_value).strip("'").
3. Preserving Number Types: Instead of forcing everything into a string like the original approach, we handle numeric data types properly so that Smartsheet displays them correctly as numbers without unnecessary formatting.

This should resolve the issue you're facing when adding rows to Smartsheet with mixed data types, especially numeric ones.

Hope this helps everyone with the same issue.Thank You!Mayank Dutt Kaushik.