Looking for solution for Forms to add multiple rows to a sheet

Chad D Kent

I have a problem that I'm trying to find a solution for. We are looking to collect Patient Day information from up to 180 Hospital facilities for them to be able to report on # of days per unit in their facility. There could be up 20+ units that users would have to enter data for and completing individual forms (20 times) is not going to work for the users, to the point that they will not use this to report from.

So we are going to give them the option to select multiple units and report the days for those units, but the issue is on the back end when it comes time to reconcile the data so that it can then be uploaded to our Analytics solution.

I've searched and found these possible solutions, but they are old and hard to understand:

Community Article 1

Community Article 2

Community Article 3

Here is a sample of what we're trying to accomplish.

User will have the option to select multiple Units and enter a number of Days for that unit.

When submitted, it currently only creates 1 row in the spreadsheet with the multiple values in the Unit Cell

Preferably we would like it to show:

If I can get it to this, then I can create a column called Unit Days and do a join across the Test Unit 1 Days through the Test Unit 3 Days to populate this new column and hide the Test Unit 1, Test Unit 2, Test Unit 3. As such:

 

 

I hope that this kind of gives you an idea of what I’m trying to do.

I did submit an enhancement request, but who knows when that will ever be implemented.

Any help that someone may be able to provide would be greatly appreciated.

Thank you for looking

Hollie Green

The only work around I know is to create a sheet with formulas to pull out the data you want to separate rows.

Suggestion to make the form more appealable to being used multiple times is to changes the settings in the form to reload the same form after each submission. This prevents the user from having to reopen the form every time they submit.

Chad D Kent

@Hollie Green

Thank you for your response.

I've thought about that, but then the users would have to select their options again. I know it doesn't sound like a big deal from our perspective, but for the user's (who would hospital employees, not IT) that will be using the form, any extra steps that they have to take, it will get to the point where they won't use it.

My sample only includes the basics of what I'm looking for, but there are a couple other fields that will be collected such as Month/Year and Email Address. If I can figure out the piece to move the Unit to its own row, I can copy the other information over to complete that row.

Entering their data into a spreadsheet is an issue for them, so they wanted a form. Gave them a form to handle their front-end needs, but now the back-end needs to be worked out for our reconciliation prior to uploading the information in the format that is needed for our Analytics software.

Chad D Kent

I found a workaround today, Download the data in Excel and do a Power Query to do a Text split to Columns (except instead of creating a new column, I have it create a new row with the individual units in their own row. I have to clean up the counts, but that's not too big of a deal.

I was going to have to export the file to Excel anyway to SFTP to our Analytics server anyway, just adds a little more work, but not as bad as I thought it was going to be. It would have been nice to have to not worry about reconciling the output and let SmartSheet do all the work. So I guess I gained some "Job Security" in that sense :-) , but it also means that I have to train someone else on how to do it if I should be out of office, and it's not as easy as download to excel and upload to SFTP. Now I have to train someone on Excel PowerQuery.

If someone has a solution for SmartSheet, I would still be interested. Anything to make to long term process easier and hopefully just a Export to Excel and Upload to SFTP as I was initially trying to accomplish.

pgram117

You can use the api with a python script. This has been my best solution so far for no extra work steps. Currently still testing some features.