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.