Save your Submissions to a Google Sheet

Owned by Megan Ellis
Aug 23, 2022
3 min read

frevvo provides wizards and a Google Connector that support easy direct connectivity with Google Sheets. You'll need a Google account and a frevvo account. Each time someone uses and submits your form, a new row is added to your Google sheet.

Let’s look at the most basic and common use case. You have a form and you want to collect your submissions in a Google Sheet.

Step 1 - Obtain the Google Account Access token

If you do not have an access token for your Google Account, perform this one-time step.

Follow these steps to obtain your Google Account token.

  1. Login to your Google Account

  2. Go to:

    1. Cloud customers: https://app.frevvo.com/google/consent

    2. In-house customers: http://<your frevvo server domain name>/google/consent

  3. Click Allow if you see a screen like the one below (Google often updates their UI so this may differ from what you see).

  4. Copy the access token. Save it to a safe location, you will use it when configuring your forms/workflows.


  • You must obtain a Google access token for every Google Account you wish to use with your forms/workflows. Repeat steps #1-4 above for each Google Account.
  • In-house customers with multiple servers must obtain Google Access Tokens for each server / Google Account. 
  • Google limits the number of access tokens per Google Account. If the token limit is exceeded, older refresh tokens stop working. 
  • Google may revoke Access Tokens unused for ~6 months. If this happens you must obtain a new token and update your forms/workflows.

Step 2: Create your form.

Create a simple example contact form with a few fields. If you need help creating a form, check out the Quick Demo and Form Designer tutorial videos on our website.



Step 3: Create your Google Spreadsheet

Make sure the column header names match the Name property of each control. You can view the spreadsheet we created for the form above at this link (it’s read-only). You cannot have a blank column header, as this will cause the connector to send data to the right of that blank column. If you need to skip a column, give it some column header such as "Leave Blank." Ensure that there are no protected cells or ranges in the rows the connector will write to.


Notice that the column names are Name, Email, Street, City, etc. The Name property of the corresponding control in the form is set to match exactly.

The column name on a Google sheet must match the control name. The matching is case-insensitive and any spaces in the column name are ignored. A control named "FirstName" matches a column header "first name."

Step 4: Connect your form to your spreadsheet using the Save to Google Sheets wizard.

  1. Edit your form/workflow
  2. Go to the Settings mode in the Guided Designer navigation bar.
  3. Click on the Document Actions tab, then click the Send Data tab.
  4. Check Post Submissions to a Google Spreadsheet.

  5. This launches the Google Sheet Wizard.
  6. Enter your Oauth token (from Step 1). Note that the email field is optional and intended only to describe which account is associated with the access token. This email does not validate against the token provided.
  7. A Spreadsheet dropdown appears listing all the spreadsheets in your Google account
  8. Select the Google Sheet from the list. 
  9. Select the worksheet of your Google Sheet from the dropdown.

  10. Click Submit.

  11. That’s it. When the form is submitted, the data will be saved to the selected Google Sheet.  
You can perform all the usual operations on the data e.g. see the Graphs Tab in our example sheet and you can see how we have created some sample graphs to analyze our submission data.

 

Save to Google Sheet via Web Hook on a Workflow Step

You can also save form data to Google sheet using the Activity Doc Action Web Hook on any workflow step.

  1. In your Workflow Outline, click the + Add Step button and select Add Web-hook (HTTP post).

  2. Construct the URL. Replace the items in <> with your Sheet information and login credentials. Note the /w/ for worksheet name, which is different from /wsname/ used when updating sheets via business rules.

    http://app.frevvo.com/google/spreadsheets/key/<Spreadsheet Key>/w/<Sheet Name>?user=<email>&password=<OAuth Token>

  3. Paste the URL into the Web Hook URL field. Check the Data property.

Test your Workflow. The data will post to the Google spreadsheet when the user of the step clicks Continue. 

Troubleshooting

"An unknown error has occurred" message

You may see the error message "An unknown error has occurred" when selecting your Google sheet from the dropdown after you log into the wizard with your Google account and access code. To prevent this, set the Share permission for your Google Sheet to private then run the Save to Google Spreadsheet wizard. Change the share permission to public once the wizard is completed. Click here for other reasons this error may occur.