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 listOfAllInfoCenterQuestionsFromSQL = new List();

// fetch the sql records

listOfAllInfoCenterQuestionsFromSQL = mySQLService.GetInfoCenterRecord( ALL_PROPERTIES, ALL_QIDS );

/ /遍历所有信息记录文件记录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 cellsToUpdate = new List();

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

foreach( KeyValuePair 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. Thecolumn objectwill 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