C# datatable to upload to a smartsheet API

OptimusPrime

Is there a quick way to upload a C# datatable object into a smartsheet sheet through API without doing it cell by cell? Thank you!

Brian_Richardson

Typically through API it's row by row, not cell by cell Although you do still have to define each and every column / field that you want to send on and it's value for each row. I don't use C# but through Javascript it's relatively easy to map the destination column Ids to the source objects and build arrays of rows using the values.

Don't forget you'll also need to chunk the arrays into 500 row objects or less for the API to accept the request.

Here's the API documentation which outlines the Add Row method and the array and object requirements.

https://smartsheet.redoc.ly/tag/rows#operation/rows-addToSheet

Here's the Javascript code I use to read data from a Smartsheet report and map the report columns to destination columns, then build an array of row objects which is then chunked and sent on to the API Endpoint for Add Rows. I know you said C# but hopefully there's a reasonable translation of this approach.

// CODE STARTS HERE //

//Build Map of Target Sheet Columns. This JS module parameter is an array of columns from the Get Sheet module, fetching the target sheet.

const targetColumnMap = new Map();

targetColumns.map(tcolumn => targetColumnMap.set(tcolumn.title, tcolumn.id))
const sourceColumnMap = new Map()

sourceColumns.map(scolumn => sourceColumnMap.set(scolumn.title, scolumn.virtualId))
const rows = []; //Empty array to store cells objects later

for (row of sourceData) {

//Duplicate cells objects as many times as you need to based on # of target columns you want to update
const resultObject = {

cells: [{

"columnId": targetColumnMap.get('Scenario'),

"value": row.cells[sourceColumnMap.get('Scenario')].value || null

},

{

"columnId": targetColumnMap.get('Key'),

"value": row.cells[sourceColumnMap.get('Key')].value || null

},

{

"columnId": targetColumnMap.get('Parent'),

"value": row.cells[sourceColumnMap.get('Parent')].value || null

},


],

"toBottom": true

}

rows.push(resultObject)

}
// The chunkArray function splits any array into small arrays of 500 chunks.

// This is to allow all Smartsheet API calls to succeed as there is a limit of 500 rows per request.

const chunkArray = (array) => {

const arrays = [];
let i,

j,

temparray,

chunk = 500; // Sets the value to 500 items per array.
for (i = 0, j = array.length; i < j; i += chunk) {

temparray = array.slice(i, i + chunk);

arrays.push(temparray);

}
return arrays;

}


return chunkArray(rows)