Submit forms to Google Drive and aggregate data in Google Sheets
One option for where to submit data from your form is to an account on Google Drive, the cloud-based Google file storage platform. Formotus integrates with both Drive and Sheets (Google's spreadsheet app) so that you can create both an archive of submitted forms and a Sheets-based dashboard for aggregating form data.
Here is a short how-to video overview:
Step 1: Set up Google Drive and Sheets
If you don't have a Google Drive account you can create one for free. At a minimum you'll need one folder for storing all the files (PDF and XML) submitted from your forms, and one spreadsheet to contain your data and links to your files.
You can organize your folders and sheets in whatever way makes sense for your situation. Each form can submit files to a different folder, or all forms can submit to the same folder. Data from each form can submit to a different spreadsheet, or to different sheets in the same spreadsheet. Multiple forms can even submit to the same sheet. It makes no difference to Formotus how you choose to organize your Google account, as long as you can provide a "can edit" link to each folder or sheet.
Below is one way you might organize your files on Google:
In this example you create one folder named Formotus to contain everything Formotus-related. Inside that folder you create one Google spreadsheet (here "Formotus Data Sheets") to aggregate all your form data, and one folder (here "Formotus Forms") to contain all your submitted files.
Step 2: Configure your data connection in your Formotus form.
Open the form you've created online in Formotus. (Note: This data connection is not available for uploaded InfoPath forms.)
Important: You should not set up this data connection until all the fields in your form are finalized. Adding, deleting or renaming data fields after the data connection is created can cause problems.
In the creator with your form open, do the following:
- In the right pane, click on the Data button.
- On the Submit tab, click +Data Destination.
- In the wizard that pops up select Google, then click Next.
- Configure your data connection in the screen below:
The Name is just what this data connection will be called within the creator, and can usually be left as the default name.
Instructions for the three main sections of the screen above follow:
2.1. Google Information
Sheet Name refers to the sheet within your spreadsheet where the data will land. By default a blank Google spreadsheet creates a sheet named Sheet1, and by default Formotus matches that name. Modify if necessary to match your Sheet name.
Spreadsheet URL and Folder URL are special links generated by Google that let you share access with Formotus. Do NOT copy a URL out of the browser. Instead select the item (spreadsheet or folder) and click the Get Shareable Link button in the menu bar (alternatively, you can right-click the item and select Share):
Make sure link sharing is On, and click Sharing Settings. On the Share with Others screen below, make sure it is set to Anyone with the link can edit, then click Copy link.
2.2. Submit Options
File Name defines the name that will be given to each submitted form file. By default Formotus inserts a concat() formula that will generate a unique name for each submitted file based on the form name and a timestamp. Learn more about using formulas >
Set as default data connection is only useful if your form has more than one data destination. The destination set as the default will be the one used by the menu in the mobile app, if enabled.
What happens to the form after submit can only be set for the default data connection, and it applies to all data connections. Normally the form will be closed after it is submitted, but you can set it to stay open or to open a blank new draft after submit.
Leaving the form open after submit can be useful, for example, if you want the user to submit again to a different destination, or if the user will be submitting a series of drafts that all contain identical information that won't need to be re-entered.
Note: This setting only applies to users of Formotus Pro. The Formotus Now app will always close after submit.
2.3. Select Fields to Promote in the Sheet
Note: This is an advanced feature that is not available in the Starter plan.
When you submit to a Google Sheet, the columns that are created in the sheet always include the file name, links to the PDF and XML files, time submitted and user who submitted the form (if available).
Click Select Fields to Promote if you want to include data fields from your form as additional columns in your sheet. This lets you aggregate submitted data in a sheet where it can be searched, sorted, graphed and analyzed.
Note: Do NOT promote fields that contain images such as photos, signatures or maps. Images will not be visible in sheet cells, but will instead appear as long base64 strings. (To see the images in a form, click the link to the PDF file and view the whole form including images.)