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

Steve Rieger

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 } );

         }

}

Steve Rieger

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;

    }

Genevieve P.

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