Updating a cell in a column formula is not supported. columnId

11/20/20
Answered - Pending Review

Hi All,

I am trying to update a smartsheet from a sql table. Several of the columns in the smartsheet have column formula's thus, when I try to update them, the API throws the error: "Updating a cell in a column formula is not supported. columnId"

In my code, I open the smartsheet and read all the columns to build a model. Unfortunately, I do not see an attribute that says this column is a column formula. So when I try to write rows to the smartsheet it fails.

See attached code. Any help would be appreciated

// list of SQL records

List<InfoCenter> listOfAllInfoCenterQuestionsFromSQL = new List<InfoCenter>();

// fetch the sql records

listOfAllInfoCenterQuestionsFromSQL = mySQLService.GetInfoCenterRecord( ALL_PROPERTIES, ALL_QIDS );

// Loop through all InfoCenter Records found in SQL

foreach( InfoCenter aSQLInfoCenterRecord in listOfAllInfoCenterQuestionsFromSQL )

{

       //Serialize the SQL object to Jason and add it to the Smartsheet

    Row rowToAdd = mySmartsheetService.AddRowFromJson( JsonConvert.SerializeObject( aSQLInfoCenterRecord ) );

     if( rowToAdd != null )

       {

       mySmartsheetService.SmartsheetClient.SheetResources.RowResources.AddRows( (long)infoCenterSheet.Id, new Row[] { rowToAdd } );

         }

}

Answers

  • Here is the AddRowFromJson code

     // Take a string of Json code and create a Smartsheet Row out of the data

        public Row AddRowFromJson( string sourceJson )

        {

          Row aNewRow = null;

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


          JObject jsonObjects = (JObject)JsonConvert.DeserializeObject( sourceJson );

          foreach( KeyValuePair<string, long> aColumn in columnMap )

          {

            if( jsonObjects.TryGetValue( aColumn.Key, out JToken jToken ) )

            {

              var cellToUpdate = new Cell

              {

                ColumnId = aColumn.Value,

                Value = jToken.ToString(),

                Strict = false

              };

              cellsToUpdate.Add( cellToUpdate );

            }

          }

          if( cellsToUpdate.Count > 0 )

          {

            aNewRow = new Row();

            aNewRow.Cells = cellsToUpdate;

          }

          return aNewRow;

        }

  • Hi @Steve Rieger

    I believe there should be two potential ways to find if a column has a column formula:

    1 . If you retrieve the sheet object, then the sheet object that is returned contains a list of column objects. The column object will then contain a field called "formula", which you can check to see if it is null or not. (See here for the documentation on Sheet Object.)

    2 . Or, you can get a list of columns only with: GET /sheets/{sheetId}/columns  . This may be a bit faster for a larger sheet, since the sheet's data won't be included. (See here.)

    Let me know if either of these work for you!

    Cheers,

    Genevieve

Sign In or Register to comment.