How to add multiple rows to Smartsheet

[kanemku]

Hello, 

I'm having issues trying to figure out how to add multiple rows to Smartsheet. I'm using workflows from @ivoller from this post about connecting to and pushing data into Smartsheet.  Using the "Write new Smartsheet Record" workflow, I'm successfully able to push one record into Smartsheet, but when I try to alter the inputs to have more than one row to add, it concatenates all of them into one row.

I then get error 1037 back from Smartsheet saying that I have the same column ID in the row more than once.

This won't work for me because I'm trying to push records from one database into Smartsheet using Alteryx, and there will be thousands of records passed through. 

How can I form the JSON to match Smartsheet's requirements so that I can add multiple rows programmatically? 

THANK YOU!

[BonusCup]

@kanemku 
I was just able to figure this out. The first thing you will need to do is figure out your Smartsheet Column IDs, also take note of the other highlighted fields.  I used Postman to figure these out.

• I created a Text Input with the column information. 

 

• I created some sample data with the field names matching the field names in my Smartsheet
• Added a RecordID and transposed the data with the RecordID as the Key Column.

 

• Rename the 'Name' column coming out of the Transpose to "Column" with a Select tool
  

   

• I have a Dropdown list as a field so I added a 'Type' field with a Formula tool
  • if [Column] = 'Status' then 'PICKLIST' ELSE Null() endif
    
• Join the column IDs back in
  

From there, I used the rest of the workflow you referenced from @ivoller's post but in the Summarize tool I grouped by RecordID to create individual records.

 

Hope this helps.

[BonusCup]

[UPDATE]

Looks like I've run into an issue with writing a large count of rows to Smartsheet. 

I am able to create a single row but multiple row testing it shows that it's getting every other row or even more occasionally.  Anyone run into this issue?

[BonusCup]

SUCCESS!!!

Found the solution using this Smartsheet Community post:  https://community.smartsheet.com/discussion/100330/bulk-post-operation-syntax

• After getting your data in order, sort by RecordID and then ColumnID
  
• Create a field named "cell"
  
  • Use formula:
    '{
    "columnId": '+id+',
    "value": "' +[Value]+ '"
    }
    
    
• Summarize Tool
  
  • Group By:  RecordID
  • Concatenate:  cell
    • I renamed this to "cells
      
      
• Sort again by RecordID
  
  
• Use a Formula tool for 'cells'
  • Formula
    '[
    {
    "toTop": true,
    "cells": [
    '+[cells]+ '
    ]
    }
    ]'
    
    
• Summarize Tool
  • Concatenate:  cells
    • I renamed this to "JSON_Output"
      
      
• Formula Tool
  • Create URL field
    • 'https://api.smartsheet.com/2.0/sheets/{sheetId}/rows'
  • Cleaned up 'JSON_Output' field
    Replace([JSON_Output],'],
    [
    ', ',')
    
    
• Download Tool
  • Basic tab
    • URL Field = URL
  • Headers tab
    • Authorization = Bearer {token}
    • Content-Type = application/json
  • Payload tab
    • HTTP Action = POST
    • Take Query String/Body from Field = JSON_Output

Check your Smartsheet for new rows.

Good luck, hope this helps. 

[rdlalli]

There are a couple of community posts on this topic.  I just wanted to add a couple of notes:

1. You can't post or "write" to cells in a Smartsheet column if that column is using a "column formula".  You'll need to do a work-around or convert it to "cell formula".  Every column in the row will fail.  At first, I thought it would just fail on that one column, but it actually gives a Bad Response for the whole row.
2. For those not having access to Postman to return Column ID values, you can convert that column to a 'dropdown select' column type, switch to card view, change the view to the column you just edited, and grab the Column ID from the end of the URL.

[BonusCup]

@rdlalli 
For your #2, I created a macro that provides:

1. Workbook Name
2. Raw data
3. Row and Column IDs

The only requirements needed are the sheetId and the Bearer access token.

[BonusCup]

@rdlalli 

Here's a cleaned up version of the macro.  Let me know if you have any questions with it.  You just need to add a text input like the below.