Template with shortcut buttons for common operations

Foghorn Leghorn

Hello,

I want to create a template whereby I define a bunch of rows (for example, "bread," "fruit," "dairy") and some columns (eg "serving size," "calories," "price," "perishable").

I want the user of the template to add one or more rows nested one level under each food category, eg: "pita," "rolls," and "biscuits" as rows under "bread" and fill in the column values for each.

I know one can right-click to insert a row, then click the toolbar to indent it, but I would like an "add new item" button that would do both, and would insert a predefined row with custom validation. Eg, the "price" field has to be a number.

As a second choice, I could have a form whereby the user is prompted to add one or more items, and for each item they must select a category ('bread," "fruit," etc) and the results would populate a sheet with correct nesting.

I can write a web app to do this but wondering if possible solely using SS?

Brett Wyrick

This can be done - but solely using Smartsheet...not entirely possible. So I'll give you the best answer I can utilizing Smartsheet's API.

I'd recommend to create a form using the built-in Forms function in Smartsheet (link), which will enable you (or your users) to add a new row to the sheet. Natively, the Forms function simply adds new rows to either the bottom or the top of the sheet.

Adding new rows in this way does not natively "nest" items under particular header rows - for that, you'd want to utilize the API to move a new row (added from the aforementioned Form) to be nested in a particular location. You can find out more here: https://smartsheet-platform.github.io/api-docs/#specify-row-location

You'd need to figure out the Row ID for each of your header rows. You could either do this dynamically through search, or by simply grabbing the Row ID (right-click the row in your sheet and select Properties, the RowID is there).

I would personally utilize Microsoft's Power Automate for this, but you could utilize any number of integrations like Zapier, Automate.io, Integromat, basically anything that'll allow a "GET" and a "PUT" to the Smartsheet API. The trigger I would use for this is natively built into Power Automate from Smartsheet, named "When a new row is added". After that, I'd utilize a PUT HTTP request within Power Automate to move it to the location desired (utilizes the RowID). Here's an example of what that HTTP PUT would look like:

curl https://api.smartsheet.com/2.0/sheets/{sheetId}/rows \
-H "Authorization: Bearer ll352u9jujauoqz4gstvsae05" \
-H "Content-Type: application/json" \
-X PUT \
-d 
[
  {"id": "ID of Newly Created Row Goes Here"
    {
        "parentID": "Header RowID",
        "toBottom": true
    }
  }
]

Hope this helps. If you're already savvy in writing webapps, you may have a different solution - but you'll need to work through the API one way or another to get the rows to automatically go under particular headers.

Foghorn Leghorn

Thanks @Brett Wyrick . Unfortunately, collecting data and making a row is the trivial part. Not sure I would bother with Smartsheet Forms if I have to write script externally anyway. I would probably sooner write a web app that collects the data and inserts the row accordingly all at once.

Thanks anyway!

Brett Wyrick

@Foghorn Leghorn I figured as much. But hey, this post and my answer may help some random googler 👀 out there lurking for a similar answer as to what you're looking for, and it'll work for them. ¯\(ツ)/¯

I do the same thing; if I'm already using an external system and it's just easier, I take the path of least resistance. I end up using the API for most everything rather than updating things manually if I can help it.

If you need any API help I'd be happy to lend a hand, or you can post a question over on the API community.

Andrée Starå

Hi @Foghorn Leghorn

I hope you're well and safe!

To add to Brett's excellent advice/answer.

Another potential option.

I developed a solution using two forms (or the same form by using conditional logic) that can be used to submit the main information and then the other information on a new row for each submission after the first.

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Foghorn Leghorn

Hi @Andrée Starå

I'm not sure I follow you. Could you elaborate? Thanks!

Andrée Starå

Hi @Foghorn Leghorn

I hope you're well and safe!

Happy to help!

How did it go? Did you manage to get something set up?

I hope that helps!

Have a fantastic weekend & Happy New Year!

Best,

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