Add a formula column to multiple sheets efficiently

SmartBA

Hi,

I have a workspace with approx 1000 sheets in it - they are all based on the same template. Sometimes a user wants a new data/formula column adding to the template. I would like to know how i could then add this new column (and its formula if applicable) to all 1000 sheets in the workspace (without manually doing it one at a time).

The specific use case is adding a date helper column to every sheet that contains the column formula:

=YEAR([Delivery Completion]@row) + "/" + IF(MONTH([Delivery Completion]@row) < 10, "0" + MONTH([Delivery Completion]@row), MONTH([Delivery Completion]@row))

By having this new column in every sheet I can then use it in the master report that summarises data from the 1000 sheets - enabling me to group the report by this date helper column (ie Delivery Completion Year/Month).

(If there is an easier way to group the report by the year and month of a date column please tell me?! I searched for ways of adding a formula column to the report - but seems you must have the column in every sheet underlying the report)

Paul Newcome

Are you using Control Center?

SmartBA

no - not using Control Center

Paul Newcome

In that case you MIGHT be able to use the API. Otherwise you are looking at a manual process.

Andrée Starå

@SmartBA

I hope you're well and safe!

The premium app Control Center would be the best solution, but all sheets would have needed to have already been provisioned.

What I'd recommend is setting up the formulas in one sheet and then adding a special character before it so it's not active and then creating a report and copying/pasting it and then user Find/Replace on the special character so the formulas activate, but you'd unfortunately would have to right-click and activate the column formulas manually, or maybe use the Smartsheet API instead or a macro engine.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SmartBA

Thanks both for your advice - sounds like its still going to be pretty manual though.

I have not used the API before (and am not experienced with APIs!) - can you/anyone suggest how it might be done?

Andrée Starå

@SmartBA

Happy to help!

More info on the API.

https://developers.smartsheet.com/

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!