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

Jaret McMillan

Hello All,

I'm looking for a solution to use Forms to add multiple rows to a sheet.

Thanks!

Kelly Moore

Hey @Jaret McMillan

Currently there is not an option to add multiple rows directly from a form. A simple workaround is to use your browser backspace after submittal. Your completed form will show up - you can change the answer for the one field and quickly submit a new submission. (Do not have the form set to reload).

If your smartsheet license allows you access to the premier app Bridge, you can use Bridge to build workflows that insert rows and dynamically populate designated fields.

Kelly

Scott Orsey

Hi,

I realize your question is a bit old at this point and I hope you found a solution that works for you. The approach I took did not require a premier app or integration. I utilized 2 sheets. The first sheet contains the form responses in a single row in the normal way that forms work. To that sheet, I added a helper column where I calculate the number of rows that I would need if I separated that row into multiple rows. I also created an automation that copies that row to the second sheet as many times as the value in the helper column. Therefore, the second sheet contains multiple rows for each form response. Each of these rows is identical, so I created some helper columns in the second sheet that identify the unique value that applies to that row (In your case "ISNG", "nSA", etc) so that each row is now unique.

CamrynR

@Scott Orsey would you be able to share the formulas in the helper columns that you used, and how did you get the automation to copy as many times as the value? This would help me out immensely... thank you in advance!

Scott Orsey

Hi @CamrynR, Happy to try to help. I'll tackle the helper columns one at a time:

In the Source Sheet:

Count of Rows: The formula depends on how you structured your form. In my case I had a separate "yes/no" question (column) for each situation that needs its own row. For example:

The automation Workflow is just brute force. I created a condition that says "If [Count of Rows] is greater than or equal to 1 then copy the row to the destination", in the same Workflow I added another condition AFTER the first action that says "If [Count of Rows] is greater than or equal to 2 then copy the row to the destination" and so on until I reached a number that was greater than the largest number of rows that I realistically thought could occur. In my case, I used 6. There doesn't appear to be a limit to how many you could have, so it's just a matter of how much time you can put into building the Workflow. Tedious but not hard.

In the Destination Sheet: If the Source sheet is working, then you should get multiple rows in the Destination sheet, like this:

Index: This helper column returns which instance of the duplicate row I am at. The first row of a given Survey ID would be 1, the second would be 2, and so on. It would reset to 1 once you get to the next survey. In my case, I used

=COUNTIF([Survey ID]$1:[Survey ID]@row, [Survey ID]@row)

Note the "$" locking the first row. You could also use a formula comparing the Survey ID to the Survey ID in the row immediately above it and incrementing when the same or resetting to 1 if different.

You will now have a running count for each Survey ID, but you still won't programmatically know which specific response each row represents.

Unique Response: This helper translates the Index (a number) into a more useful piece of data specific to the response you are disaggregating. The way you do this will depend on how you collected the data. In my case, I added a "Header Row" to the Destination Sheet. In the Header Row I placed the value that I wanted to show for each response. Once I did this, I used Index/Collect to return this value for each row:

=INDEX(COLLECT([Response 1]$1:[Response 4]$1, [Response 1]@row:[Response 4]@row, ="Yes"), Index@row)

Note that this will return an error for the header row so you can delete it from that row or use IFERROR to correct it (I don't like error messages, but in this case it really doesn't matter). In my sample, if everything works right, you'll get this:

Some considerations: I didn't use the Multiselect column in my survey like OP. If you do, you will need to adjust the formula for "Count of Rows" to count up the number of values in the list. I don't know a good way to do this. You'd also need to adjust the formula for "Unique Response" to return the correct instance of the multiselecct that corresponds to the index of the row. I don't think this is possible, at least not easily without creating a bunch of helper columns. I recommend that, if you use a Multiselect in your form, disaggreggate this in the source sheet to the multiple columns like I have in this example. You can do this with IF(CONTAINS) on the value of the Multiselect to create a unique column for each row you want to create in the Destination sheet.

Hope this helps!!! Good luck

CamrynR

@Scott Orsey Thank you thank you thank you!!! I was on the right track I think, but this makes it clear to me that I need to have columns for each possible dropdown selection to support the duplication.

Cheers!

Vera

Hi Kelly,

Can you share the details of how to use Bridge to allow multiple entries into a form? I am new to Bridge but do need to set up that functionality & add multiple rows directly from a form.

Thanks