API Access to Column Formulas

coby · 03/19/21

I have been trying to understand how to handle column formulas and all I can see is that you have error messages to let us know that we can modify cells in columns with column formulas but there is no column attribute to indicate that a column formula is present via the API.

Also, when a column formula is present, it is only displayed as an attribute of each cell within that column as opposed to belonging to the column object itself.

The only thing I believe I can reasonably do is copy the column data, delete the column, recreate a new column and then change values thereafter. But this is not ideal given corollary effects of this approach.

Is there any plans to add Column Formulas to the API in the near future?

coby · 03/19/21

@David Tutwiler I figured it out!

So after you showed that it worked I decided to see if the issue was my sheet itself

so I went into cmd and ran this Curl command:

curl https://api.smartsheet.com/2.0/sheets/{sheetId}/columns \
-H "Authorization: Bearer {ACCESS TOKEN}"

and I was able to see the formulas, for the same sheet I going for prior. so I realized it wasn't the sheet itself it was code.

So then I decided to pip upgrade smartsheet-python-sdk and it went from 2.101.0 to 2.105.1 and that did the trick.

Thnx for the help!

David Tutwiler · 03/19/21

Coby, have you pulled in the column information for a sheet yet through the API? There is a "formula": field that I think you could pull from and write to that's a property for the column.

coby · 03/19/21

@David Tutwiler pulled column data many times and the column formula is not showing up, the formulas only show up in the formula attribute of the cell. 2 questions for you:

1) have you tried updating a column's formula via a column_update request?

2) Do you use any kwargs or additional args in your API get request that might give you column formulas that I am missing here?

This is what my column data looks like via the API:

David Tutwiler · 03/19/21

Here's what I have that gave me the column properties. I'm going to try and go in and update the formula now and see what it does. I'm writing in Python fwiw.

David Tutwiler · 03/19/21

Yes, I was able to make a modification using this:

I can verify this updated the column formula immediately.

coby · 03/19/21

thanks @David Tutwiler , I am in python as well. It's interesting because I gave this a shot and the request per your code above went through but the response does not show the column formula and the column formula was not modified. Also, when I request the sheet.columns the response provided does not display any formula attributes.

...Basically, I see that is working on your end, however, there is something different that is causing my sheet to not show formulas (I am the owner/admin of the sheet). Anyways, thanks for the help! will keep digging in here and follow-up if I find the issue.

David Tutwiler · 03/19/21

That's a real bummer, sorry. Might be worth reaching out to your account rep to see if they can't get engineering/high-level support to look into it.

coby · 03/19/21

@David Tutwiler I figured it out!

So after you showed that it worked I decided to see if the issue was my sheet itself

so I went into cmd and ran this Curl command:

curl https://api.smartsheet.com/2.0/sheets/{sheetId}/columns \
-H "Authorization: Bearer {ACCESS TOKEN}"

and I was able to see the formulas, for the same sheet I going for prior. so I realized it wasn't the sheet itself it was code.

So then I decided to pip upgrade smartsheet-python-sdk and it went from 2.101.0 to 2.105.1 and that did the trick.

Thnx for the help!

David Tutwiler · 03/19/21

Ah! There you go. Didn't even think about the version of the SDK. Good catch!

API Access to Column Formulas

Best Answer

Answers

Categories