Auto Indent on Excel Import vs. Manual process - pretty please?

James Sutton

First I'll share how we do this manually inside smartsheet in case others are trying to do the same thing but then I'll make a feature request for this to be done automatically.

 

Many times we want to make a sheet available to users with parent/child data.  SmartSheet's hierarchy is great for this, but getting the data from database to smartsheet can be a little tricky.  Here's what I currently do.  I'll use the concept of Transaction Header and Item detail.

 

1. First we query the data so that the result set contains both header and detail rows with the transaction # on all rows.  The Item # column contains '0' in the header row.  The result set is dumped to an excel sheet.

2. We import this Excel file into Smartsheet choosing the transaction # as the Primary

3. Then we filter out all Item# = 0 rows.  This hides the headers and shows only detail lines

4. We select all rows and indent

5. Remove filter and voila, if all the rows were in the correct order the details are now children of the header rows.

 

Some Additional Cleanup

1.  Sometimes we'll collapse all and change the ITEM# column of all the parent rows to a formula like =COUNT(CHILDREN()) which changes the value in ITEM# column of the header row from '0' to the count of items.

 

Feature Request:

 

What would save us an enormous amount of time is if we had an option on import to have rows with duplicate primary column values automatically indented as a child under the first row of the set of duplicates. 

 

Or even how about the ability to indicate secondary and tertiary columns which would be used to keep indenting to allow for children of children, etc.

 

This would allow us to instantly create a hierarchical sheet from flat file.  Awesome right?

 

 

Shaine Greenwood

Hi James,

 

It appears you're looking for a conditional indenting functionality, such that would apply an indent when a certain condition is met on the row. (Multiple rules might be set up, similar to our existing Conditional Formatting functionality.)

 

As you've discovered, we don't have this type of functionality in Smartsheet, but I've added your vote for this to our enhancement request list for further consideration.

 

With that being said, if you have development resources available at your organization, you might consider using our API to create this functionality. Within our update rows method (https://smartsheet-platform.github.io/api-docs/#update-row-s) exists a row location operation (https://smartsheet-platform.github.io/api-docs/#row-location).

 

If you have any questions, you're welcome to email api@smartsheet.com. Keep in mind that our API team is happy to answer questions and work through issues when using the API, but we don't build applications via the API for customers at this time.