Adding rows and data types with C#

patrick_bouaziz
edited 12/09/19 in API & Developers

Hi Everyone,

I am currently trying to integrated SharePoint data into smartsheet.

However, I am struggling with data types and most of the cells do not take my values.

I think I have no issue with certain type like TEXT_NUMBER or DURATION but for the other types I am stuck.

Can someone show me an example of how to insert such rows?

Thanks a lot in advance!!

 

Please find some details in below:

Here are my columns: (Type | Column Name)

TEXT_NUMBER | Task Name

DURATION | Duration

ABSTRACT_DATETIME | Start

ABSTRACT_DATETIME | Finish

PREDECESSOR | Predecessors

CONTACT_LIST | Assigned To

TEXT_NUMBER | % Complete

PICKLIST | Status

TEXT_NUMBER | Comments

Here is an example of data I am trying to insert: (all string)

Task Name|Intranet

Duration|1d

Start|1d

Finish|1d

Predecessors|9

Assigned To|patrick

% Complete|0d

Status|In progress

Comments|test

Here is my code:

                                // build row

                                // Specify cell values for row

                                Cell[] cells = new Cell[] {

                                    new Cell

                                    {

                                        // Task name

                                        ColumnId = xxxxx49386104708,

                                        Value = title

                                    },

                                    new Cell

                                    {

                                        // Duration

                                        ColumnId = xxxx49851891588,

                                        Value = duration

                                    },

                                    new Cell

                                    {

                                        // Start

                                        ColumnId = xxxxxxx479262084,

                                        Value = start

                                    },

                                    //new Cell

                                    {

                                        // Finish

                                        ColumnId = xxxxxxx5576836,

                                        Value = finish

                                    },

                                    //new Cell

                                    {

                                        // Predecessors

                                        ColumnId = xxxxxxxx2947332,

                                        Value = predecessors

                                    },

                                    new Cell

                                    {

                                        // Assigned To 

                                        ColumnId = xxxxxx898470276,

                                        Value = assignedTo

                                    },

                                    new Cell

                                    {   // Status

                                        ColumnId = xxxxxxxxx712155524,

                                        Value = status

                                    },

                                    new Cell

                                    { // % Complete

                                        ColumnId = xxxxxxxxx5840772,

                                        Value = complete

                                    },

                                    new Cell

                                    {   // Comments

                                        ColumnId = xxxxxxxxx526020,

                                        Value = comments

                                    }

                                };

                                // Specify contents of row

                                Row row = new Row

                                {

                                    ToTop = true,

                                    Cells = cells

                                };

                                // Add rows to sheet

                                IList<Row> newRows = ss.SheetResources.RowResources.AddRows(

                                  currentSheetId,               // long sheetId

                                  new Row[] { row }        // IEnumerable<Row> rowsToAdd

                                );

Comments

  • dAVE Inden
    dAVE Inden Employee

    You can find an example of how to add a row to a sheet via the C# SDK here: http://smartsheet-platform.github.io/api-docs/?csharp#add-rows

    For any TEXT_NUMBER column you can provide a string as the value for the cell in the Value. For any DATE or ABSTRACT_DATETIME column you will need to provide a valid date. Info on dates and times can be found in the docs here: http://smartsheet-platform.github.io/api-docs/?csharp#dates-and-times

    How you provide the Date may be specific to C#, but the info on what the API is expecting is there. If you are using Dependencies on your sheet you only need to provide a date to the Start Date of your sheet and a Duration. Dependencies will then calculate the Finish Date for you.

    For both Predecessors and Duration you will want to use the ObjectValue Object: http://smartsheet-platform.github.io/api-docs/?csharp#objectvalue-object

    For a PICKLIST you can provide the value as a string, but it does need to match the option of the Dropdown exactly including case.

    If you have further issues on this you are welcome to reach out to us on the Support team by opening a case with us here: https://www.smartsheet.com/gethelp

  • patrick_bouaziz
    edited 04/10/18

    Thanks a lot Dave!

    Your answer helped me a lot !

    I was getting errors because I was trying to set a value on the Finish column but thanks to you I know now that this value is actually calculated automatically.

    For the dates or datetimes, from what I can see smartsheet accepts the C# Datetime type without any particular format.

    Now the type where I still not getting the expected value from smartsheet is CONTACT_LIST|Assigned To.

    Is that like the PICKLIST type ? The value has to match with predefined contact options ?

    With many thanks in advance.

    Best regards,

    Patrick

  • dAVE Inden
    dAVE Inden Employee

    For CONTACT_LIST type columns you will want to make sure you are providing a string value in the "value" that is in the format of a valid email address like "test@email.com"

    If you provide an invalid string you will get an error.

    If you are using the Contact Options in your column properties you can still provide any email address as long as the string is in a valid email format. If you are using Contact Options and enable the option to "restrict to list values only" you have to provide a string with a valid email that matches exactly to one of the emails for the contacts in that list.