Google Forms/Sheets

Google Forms is a common platform that is used to edit and record information for surveys. Google Sheets can be used in conjunction with Google Forms, allowing all records to be instantly saved to Google Sheets once it is recorded on the Google Form. This platform allows an easy-to-use “database” system that works exactly like Excel, but in mobile form. Great for clients with in-depth Excel knowledge but not much database familiarity.

Google Forms and Sheets have much more functionality that you would think. Once data is in the Google Sheet, a script can be run to do just about anything with the data, much like a regular database.  For our project we created a script that would archive all data from one sheet to another sheet.

How to use Google Forms: https://gsuite.google.com/learning-center/products/forms/get-started/

How to use calculations in Google Sheets: https://support.google.com/docs/table/25273

How to create scripts in Google Sheets: https://developers.google.com/apps-script/guides/sheets/functions

NOTE: You will need to know Java Script to code in Google Sheets, but your knowledge in C# from 333k will help you understand most scripts that are readily available

Accurately Referencing Cells on Form-Linked Tabs:

1. Mechanics: On form-linked tabs, when a new entry is received on the Google Form it is connected to, it does not simply copy over the received information to the next available row on that sheet. It actually inserts the data below the row it previously inserted upon. (This is how Google has the connection between forms and sheets configured).

• On a user level, when looking at the sheet, it looks all the same whether the inputted data on the form is copied over to an existing row or a new row is inserted on the next available row

1. However, the problem arrives when you are attempting to reference future cells on the form-linked sheet on other tabs on the sheet

• If you use a static absolute reference like $A$3 to capture the next row of data that will be appended to the form-linked tab, it won’t work because, mechanically, a row will be inserted in row 3, and your reference will be shifted down to $A$4.
• To work around that, you have to use something more dynamic:
  • =INDIRECT("'Customer Form'!D" & ROW())
• Essentially, this formula says to give me the data in Cell D?, where the ? is provided by row(). The row() function simply returns the number of the row in which this formula is placed.
  • Example: So if this formula above was written in cell A1 on a tab like “Customers”, it will be asking the “Customer Form” tab for the data in cell D1. Even when new insertions occur, this pointer will not change
  • If you copy this formula across A1 to A999 on the “Customers” tab, any data that is inserted in column D1 to D999 on the form-linked tab will automatically copy over to A1 to A999. This is very useful for transcribing information

1. This is crucial to know when expanding functionality that stems into the use of forms & form-linked tabs. With this method, you can copy over all information inserted into form-linked tabs automatically.

Connecting a New Form to Sheets:

• How to Link a Form to a Sheet
  • When prompted, make sure to select “existing spreadsheet”, and click on MMM Database Google Sheet
  • Please also refer to “Accurately Referencing Cells on Form-Linked Tabs” to properly reference cells on form-linked tabs

The functionality is endless when it comes to Google Forms/Sheets, and is a good platform for companies that don’t have existing processes and a limited budget!