google-apps-scriptgoogle-forms
I am using google forms to punch orders in my Google Sheets.
Although I am facing a challenge as the number of products is variable.
For example
Question 1: Item Name
Question 2: Quantity
I want the responder to add as many items and quantities as they want.
Is it possible to do the same with core features or appscripts? If not possible with google forms, is there any other form app to do the same?
Yes, you can, by using the Go to page based on answer feature. This implies that you have to divide your form into pages, which is easy to do.
Given you have already created your form with three questions:
Create a page for each section of your form:
You should now have three pages; one for each question.
Now make your first question a Multiple choice question, and tick the Go to page based on answer checkbox:
Add options to your question (Yes and No). For each option, select the page to go to if that option is selected:
On page 2 (with the question Which kind of cat do you like most) you would want to skip the next page (Why do you dislike cats):
That's it. I've setup an example form here, feel free to play with it: https://docs.google.com/forms/d/1wGKiAO8s1CjgW5puNrCZYooa7mFJHq0BygBvw3fHFNg/viewform
Cell A2 -> Headers
=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,0)
This could be repeated several times, one for each header column, just change the last parameter accordingly.
Cell B2 -> data
=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*2+1,1,2)
Fill down as necessary
OFFSET() is used to get the values from the source data.'Form responses 1'!$A$2 is the start point to count columns and rows.row() is used to increment the row and column iterators.ceiling((row()-1)/5,1), row iterator, is used to set the row from the source data.mod(row()-2,5)*2+1, column iterator, is used to set the column from the source data.In case that are several "header data" columns, add 1 for each of them. I.E. if there are five "header data" columns, and six "data columns" the column iterator should be
mod(row()-2,5)*6+5
The final data formula to be added to the F2 cell is:
=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*6+5,1,6)
In the row and column iterators, 5 is used because there are 5 columns for "record data".
Best Answer
You could use sections having each section with three questions
The "Would you like to add another item?" question should be one of the types that allow to jump to another section. In case that the respondent choose No, then submit the form.
On the last section you could tell to the respondent that if they want to add more items, they should send a new form submission. You could set your form to show a link to submit another form response in the form submission confirmation page.
References