Bulk Operations - Adding multiple rows to sheet

Question

I'm attempting to write data from a data table to a sheet via the smartsheet API (using c# SDK). I have looked at the documentation and I see that it supports bulk operations but I'm struggling with finding an example for that functionality.

I've attempted to do a work around and just loop through each record from my source and post that data.

//Get column properties  (column Id ) for existing smartsheet and add them to List for AddRows parameter
            //Compare to existing Column names in Data table for capture of related column id
            var columnArray = getSheet.Columns;
            foreach (var column in columnArray)
            {
                foreach (DataColumn columnPdiExtract in pdiExtractDataTable.Columns)
                {
                    //Console.WriteLine(columnPdiExtract.ColumnName);
                    if(column.Title == columnPdiExtract.ColumnName)
                    {
                        long columnIdValue = column.Id ?? 0;
                        //addColumnArrayIdList.Add(columnIdValue);
                        addColumnArrayIdList.Add(new KeyValuePair<string, long>(column.Title,columnIdValue));
                    }
                }
            }



            foreach(var columnTitleIdPair in addColumnArrayIdList)
            {
                Console.WriteLine(columnTitleIdPair.Key);

                var results = from row in pdiExtractDataTable.AsEnumerable() select row.Field<Double?>(columnTitleIdPair.Key);

                foreach (var record in results)
                {
                    Cell[] cells = new Cell[]
                    {
                        new Cell
                        {
                            ColumnId = columnTitleIdPair.Value,
                            Value = record
                        }
                    };
                    cellRecords = cells.ToList();
                    cellRecordsInsert.Add(cellRecords);

                }

            Row rows = new Row
            {
                ToTop = true,
                Cells = cellRecords
            };

            IList<Row> newRows = smartsheet.SheetResources.RowResources.AddRows(sheetId, new Row[] { rows });

            }

I expected to generate a value for each cell, append that to the list and then post it through the Row Object. However, my loop is appending the column values as such: A1: 1, B2: 2, C3: 3 instead of A1: 1, B1: 2, C3: 3

The preference would be to use bulk operations, but without an example I'm a bit at a loss. However, the loop isn't working out either so if anyone has any suggestions I would be very grateful!

Thank you, Channing

score 3 · Answer 1 · answered Jan 17 '19 at 18:24

3

Have you seen the Smartsheet C# sample read / write sheet? That may be a useful reference. It contains an example use of bulk operations that updates multiple rows with a single call.

answered Jan 17 '19 at 18:24

Taylor Krusen

963
6
10

Taylor, thank you for this reference. It has moved me further along! I was able to generate a list of columns and their respective values. However, I am getting a "duplicate columnId per Record" error. I believe the solution here would be to loop through that list and build the records but I'm struggling with the logic. Is it appropriate to follow up with a question here or do I need to create another thread specific to my issue? – Channing Jan 18 '19 at 14:30

score 2 · Accepted Answer · edited Jan 22 '19 at 20:33

Taylor,

Thank you for your help. You lead me in the right direction and I figured my way through a solution.

I grouped by my column value list and built records for the final bulk operation. I used a For loop but the elements in each grouping of columns is cleaned and assigned a 0 prior to this method so that they retain the same count of values per grouping.

      // Pair column and cell values for row building - match 
      // Data source column title names with Smartsheet column title names

        List<Cell> pairedColumnCells = new List<Cell>();

        //Accumulate cells 
        List<Cell> cellsToImport = new List<Cell>();

        //Accumulate rows for additions here
        List<Row> rowsToInsert = new List<Row>();

        var groupByCells = PairDataSourceAndSmartsheetColumnToGenerateCells(
                             sheet, 
                             dataSourceDataTable).GroupBy(
                                 c => c.ColumnId,
                                 c => c.Value, 
                                (key, g) => new { 
                                    ColumnId = key, Value = g.ToList<object>() 
                                });

        var countGroupOfCells = groupByCells.FirstOrDefault().Value.Count();

        for (int i = 0; i <= countGroupOfCells - 1; i++)
        {

            foreach (var groupOfCells in groupByCells)
            {
                var cellListEelement = groupOfCells.Value.ElementAt(i);

                var cellToAdd = new Cell
                {
                    ColumnId = groupOfCells.ColumnId,
                    Value = cellListEelement
                };

                cellsToImport.Add(cellToAdd);
            }

            Row rows = new Row
            {
                ToTop = true,
                Cells = cellsToImport
            };

            rowsToInsert.Add(rows);

            cellsToImport = new List<Cell>();


        }

        return rowsToInsert;

Bulk Operations - Adding multiple rows to sheet

2 Answers2