How to update reports efficiently when column name changes in source sheet?

Blaine Loos

I have a problem and would love to hear if anyone has a creative solution.

I have a large "Master Sheet" that essentially aggregates data from hundreds of other sheets across multiple workspaces and serves as a central data repository. Then, I (and many others) run a lot of different reports off of that "Master Sheet" for various purposes.

The issue I encounter is that when a column name is changed within that "Master Sheet", all of the reports that run off of it break (i.e. the reports do not automatically update). I then must figure out what reports reference the column that changed and manually change the column reference in those reports.

The really tricky part is figuring out what reports reference the column in question. We have dozens and dozens of reports, managed by different people, for different purposes. I'm sure there are reports that exist that I'm not even aware of and don't have permission to access. So, one minor change in the "Master Sheet" can affect many many reports, and there's no way to know what reports it will affect.

Is anyone aware of a way to either make reports automatically update when a column that they reference is changed, or, alternatively, map out which reports reference the column that has changed?

Andrée Starå

Hi Blaine,

Unfortunately, as far as I know, there isn't a better way than to not renaming the columns, but it would be an excellent addition to Smartsheet to be able to see everything referenced in an overview.

Please submit an Enhancement Request when you have a moment to have your vote added

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Cleversheet

Here is what I submitted via the Enhancement Request form. Others might suggest something similar.

_ _ _ _ _

Please bring up a dialog when I attempt to change a column name alerting me to any Reports and formulas (eg, Indeed/Match) used by that column, and enabling me to save or print that list so I can follow up to fix those Reports and formulas. 

Currently, changing a column name will break all dependent reports and formulas—and completely invisibly. That is, you don't know until you observe the broken Report or referencing formula, and it can be quite difficult to diagnose what happened, especially if much time has passed between the name change and discovery of the problem. There's no way to track the potential or actual effects of a column name change. 

THE BETTER SOLUTION would be to make column names independent of their column IDs. You can change a Report name with no consequence because there's a table associating that name with a report ID. Since clearly all columns already have their own unique IDs, they should be tabulated similarly so that changing column names would no longer break things. 

Paul Newcome

@cleversheet Changing a column name should not break a formula. I do this intentionally very frequently. If I have a longer column name that I need to reference multiple times in a formula, I will actually change it to something much shorter first. Then I will write out the formula and change the column name back to what I originally wanted it and the formulas automatically update.

Bryce Henry

@cleversheet That's a great enhancement request! Thanks for making it.

What is unfortunate is that this has been a feature request, presumably multiple requests, since at least 2017. The standard advice of, "try to solidify column names as early as possible," is neither a solution nor all that practical. Things change. I am hard pressed to think of another database, spreadsheet, or language that similarly fails to account for such changes in some fashion.

Paul Newcome

@Bryce Henry The only thing that changing a column name itself affects is reports. It does not affect formulas.

Carlos Yanes

@Paul Newcome Does renaming a column on a report affect the how the data is read from the master sheet?

Jamie Bedford

I cannot believe this is still an issue, but it is. Changing a column name in a Sheet breaks any Reports that point at it, at least in the Gov version.

This requires many, many clicks to resolve. Find the Reports, open each Report, remove the old column and add back the new column, reorder the columns, resize the new column. Pray that you identified all of the Reports that use the column.

There's a forum post from 2017 identifying this issue:

Reports break when changing a column name on sheet? — Smartsheet Community

Yes, I submitted an enhancement request. What is going on, Smartsheet? This does not feel very smart at all.

Mike E

Reports Using Specified Column.zip

Amazingly this is still a problem in July of 2022 and is a major problem for my team. I also submitted an enhancement request back in February. No response/action yet. I didn't realize this issue has been a problem for so many years!

This isn't just a 'bump' - a tidbit to add to this thread: Changing the type of column on a sheet (IE: from Text/Number to Dropdown List) will also break the Report's column in the same way a column rename does.

Attached to this post is a Python script I wrote that asks for a column name to look for, then uses the Smartsheets API to find Reports that use a column with that name. It outputs a .csv file containing the list of Reports (and their permalinks!) that use a column of the specified name . Makes identifying and navigating to impacted Reports much easier, but it is still required to manually go to each report and make the changes as @Jamie Bedford described. [I'm not a professional developer/programmer. Please forgive any sloppy/inefficient code!]

Unfortunately, modifying Reports via the API is still not possible, so that avenue of automating a solution is a no-go.

bleydon

September 2023 and still an issue.

I have ~20 reorts for team members to use. Columns were changed on the master report due to leadership decision to rebrand phases.

Now I have to manually edit ~20 reports!!!! This can't be the way!!

Marcela

Hi @bleydon,

If by “columns were changed,” you mean that the column names were changed, Smartsheet has been updated since this post was created: a renamed column will now automatically show as a connected column in your report. Here is the release note for more information: Change column names without disrupting reports.

If this isn’t what you meant, I found a similar product request in our Community. You can support this idea by casting your vote here: Auto-Update Report Column Names. Our Product team reviews the top-voted posts monthly, and you’ll receive email notifications if there are any updates related to your voted or created posts.

If you have any more questions or need further assistance, feel free to reach out.

Cheers,

Marcela