Form Views: Review and Suggestions

Forms and Form Views is an amazing new feature and I’m looking forward to using it a lot. The video does a great job of highlighting a strong use case. I started using it without watching the video first, and found the following to be very useful right away:

  1. Hidden field with a default value (useful for a submission date/time as well as the example shown in the video).

  2. Fields that use values from a related row automatically show up in a drop-down box in the form. This is something I’ve tried doing with Google Forms but it requires scripting if you want the options to update automatically when changes are made to a spreadsheet. Your implementation makes this just automatic based on the use of the related row data type - very cool. [Hopefully future updates will allow the drop-down list to be filtered or customized based on who is using the form]

  3. I experimented with the User as one of the fields, and it also automatically included the spreadsheet users in a drop-down. It would be cool to have an option for assigning the User automatically based on who is editing the spreadsheet (so that an employee can’t submit a form for a different employee)

  4. When you use a Date data type, your default value can be chosen as “Today”. When using a Date Time data type, your default value can be chosen as “Now”. This will be very useful for logs and trackers that automatically store the submission date and or time. [Edit - see my next comment for a correction to this]

  5. The options to customize the response after the submission will be very useful for surveys, the ability to submit multiple responses in a row, etc. - all things I would have expected based on use of google forms.

  6. In general, the link between the form and the spreadsheet column’s data type and format is pretty strong. I really like that. It’s slightly annoying that when creating a form from scratch using a default sheet you see all the columns A-Z in the fields list (and it’s still time consuming to delete one column at a time), but the point is that you can either create your table first and then build the form from it, or you can work on the form first and choose data types for new columns via the form (such as when creating a survey).

  7. Attachment - This makes the form very powerful. My use case would involve submission of receipts for an expense tracking system, but the resume submission is also a great example. It’s also awesome that you can submit multiple attachments.

I am also very excited about the planned advanced features:

Populating field values using URL parameters: This will likely provide the answer to one of the suggestions I was going to make regarding giving specific users (employees) a particular link to a form that is unique to them. For example, having a User field automatically populated (and preferably disabled so that it can’t be changed, but is still viewable).

It would also be possible to set up a separate spreadsheet like a weekly timesheet that provided a way for an employee to edit their times throughout the week, then use the HYPERLINK function to automatically generate the links that would take them to a form for submitting each row of their timesheet.

Another example would be a spreadsheet that acted as a type of mortgage or loan calculator, with a link within the spreadsheet that used values within the calculator to automatically start a loan application (populating the application form with those values).

Another example would be an invoice form that contained a link for submitting that invoice to an invoice tracker worksheet. The link could fill the url with parameters like the customer id, invoice amount, date, etc.

Conditional Form Logic: This might be able to accomplish what I’ve recommend above regarding choosing values based on the User who is submitting the form. I haven’t personally found the need for branching-logic questionnaires, but I know that is a popular use case. Conditional lists is one of the things I’d like to see eventually - such as one field filtering the values that can be chosen in a different field. An example would be a timesheet where only certain tasks were available to be chosen by a particular employee.

SUGGESTION #1:
A “disable” option along with “hidden” and “required” so that you could show a value that will be submitted with the form but not allow the user to change that value. I mentioned one example above (User), but it could also be used to show that a date is being recorded, etc. This would be especially useful for when you are using a survey to collect information and you want to be completely transparent about what information is being saved.

SUGGESTION #2:
The “Text Block” component is great, but to be even more useful, we’ll need an “Image Block” component so that we can do things like surveys that show a choice between two visual options, quizzes and tests that involve images, etc.

SUGGESTION #3:
Column Formula data type: I like that the column updates after the form submission - this is a useful and necessary part of many spreadsheets. However, until the formulas used in a Column Formula data type can handle complex functions and formulas, I’m still left with creating my own formulas … and these are not automatically copied down when rows are inserted. I’d like the Column Formula data type to be able to handle any complex function or formula that you can enter in a cell. If that was possible, then it would eliminate the need to figure out a fix for automatically copying a formula down to fill in the newly inserted rows. One idea would be to have a user define a Column Formula based on the formula that would be used in the very first row of the table, with subsequent formulas copied down just like they would be with the traditional formula syntax (following the rules related to absolute and relative references).

[Sorry for packing so much into a single topic this time]

2 Likes

CORRECTION: I’ve discovered that the date/time option is not behaving the way I thought it did. I was hoping that choosing “Today” or “Now” as the default value would mean that when accessing the form, the date or time would automatically be the current date or time … but that’s not the case. And I really hope that at some point you make an option for the Date and Time fields to automatically use the date and time of the form submission.

2 Likes

Thanks @Vertex42 . Would like to mention a couple of alternatives based on the use cases mentioned.

#3 and #4 can partially be achieved by using createdBy/createdAt column data types as of today. These are read-only columns and are updated based on when and who has submitted the form.

#3 - When a user submits the form while being logged into their own spreadsheet.com account, their user name is recorded in the row’s createdBy field. (Login can be made mandatory by enabling Only allow logged in Spreadsheet.com users to complete the form from setting)

#4 - createdAt column records the date and time of the form submission. (Time when record was created)

2 Likes

Ah, yes! Forgot about the createdBy/createdAt data types. Thanks for the reminder.

1 Like