C# datatable to upload to a smartsheet API

Options

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!

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers
    Options

    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)

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN