SurveyCTO form validation in Google Sheets

Click the "Follow" button on the top of this page to receive email updates about this add-on.

When you upload a form to your SurveyCTO server, your form is validated before it is successfully uploaded. This means it looks for errors to make sure the form will run. For example, the built-in form validator ensures that all field references are to fields that exist, all begin/end statements come in matching pairs, and lots more.

If you are creating your form definition in Google Sheets, you may want to validate the form right then-and-there, instead of uploading it to your SurveyCTO server, or using the SurveyCTO Desktop offline form validator. Frequently validating your forms can help you catch errors early, and help shorten the amount of time spent troubleshooting later. With the SurveyCTO Form Validation add-on, you can now validate your form with the click of a button, right within Google Sheets!

The Google Sheets validator (current in beta) is not as comprehensive as the standard server or SurveyCTO Desktop validator, so it will not detect all of the same errors, such as dependency cycles. However, it will check for some of the most common errors, and even some errors that the standard validator does not, such as indexed-repeat errors. Plus, it will list all detected errors at once instead of only one-to-three-or-so at a time, so you do not have to re-upload the form to the server each time an error is fixed.

If you are working in Microsoft Excel, you can validate your form while offline using SurveyCTO Desktop. To learn more, check out our documentation on validating forms offline.

Setting up the add-on

Save the validator

This add-on is currently in public beta, so it is not yet available in the Google Add-on store. You will need to save the Google App Script File to your Google Drive. Follow these steps to do so

  1. Open the Google App Script file here (last updated June 22, 2021).
  2. In the upper-left, click the i in a circle, Overview.
  3. On the upper-right, click the copy symbol (when you hover over it, it will say Make a copy). The copy will open in a new tab, and it will appear in your Google Drive's My Drive folder.
  4. Optional: The file name of the copy will begin with "Copy of". You can remove that. You can change the file name either by clicking Copy of SurveyCTO Form Validator (beta) on the top, or changing it from the folder the file is currently in.
  5. Optional: Move the file to a good location.
If the validator is updated, you will have to re-follow these steps to save its latest version.

Open form definition from add-on

Whenever you would like to use the add-on, you will need to open it from the Google App Script . Follow these steps to use the add-on:

Part 1: Open the test window

  1. Open the Google App Script file in your drive.
  2. On the upper-right, if it says Use legacy editor, click that link (the new editor does not support testing add-ons).
    1. A new window may appear. You can close out of this, but you can also tell Google from there that you are opting-out because it is Missing editor add-on testing.
    2. If you see a popup that says Try the brand new Apps Script editor, click either Don't ask me again or Dismiss.
  3. On the top, go to Run > Test as add-on.
    1. If it does not say Run at the top, make sure you are using the legacy editor, as described in step 2.
  4. If you have already used this add-on on a form, skip to part 3. Otherwise, continue to part 2.

Part 2: Add the form definition

  1. On the bottom, click the Select Doc button.
  2. Search for and select the spreadsheet form definition you would like to use with this add-on.
  3. Click Save.

Part 3: Run the add-on

  1. In the list under Execute Saved Test, click on the Google Sheet form definition you would like to use with this add-on.
  2. Click Test.

The Google Sheet will then open in a new tab.

Open the validator

Follow these steps to the add-on in a form:

  1. In the Google Sheet form definition, on the top, click Add-ons > SurveyCTO Form Validation > Open validation panel.
  2. The form validator will open as a panel on the right.

Convert a form to a Google Sheet

If you are working with an XLSX (Excel) file, it is easy to convert to Google Sheets format. Conversion will allow you to use the add-on, and also take advantage of the other benefits of Google Sheets (such as international cooperation to avoid form version errors). You can set up Google Drive so it automatically converts all compatible files added to Google formatting, such as converting an XLSX file to a Google Sheet. If you prefer not to use automatic conversion, follow these steps to convert the file:

  1. Drag-and-drop the file from your computer into Google Drive. 
  2. In Google Drive, right-click the file, hover over Open with, and click Google Sheets.
  3. The form will open in Google Sheets, but as an XLSX file. On the top, click File > Save as Google Sheets.
  4. A Google Sheet file version of the original spreadsheet will be created in the same location as the XLSX file, and it will open in Google Sheets.
  5. Optional: Delete the XLSX version of the file from Google Drive, since you now have a Google Sheets version of the file in the same location, and the original XLSX file should still be on your computer.. However, it is a good idea to keep at least one copy of the original XLSX file, just to be safe.

Use the validator

To use the validator, simply click the Validate button in the validation panel, and it will run!

Message types

There are three types of messages that will appear in the list: errors, warnings, and app errors:

Errors: These are issues that need to be fixed in order for the form to work well and collect data.

Warnings: These are issues that the validator recommends resolving, since they could interfere with data collection or analysis, but you will still be able to collect data well even if they are not resolved.

App errors: By far the least common, these are errors with the form validator itself. If you get one of these errors, please send the app error, and your form definition spreadsheet to support@surveycto.com so this can be fixed. Use the subject "Form validator error".

For a complete list of validations performed that could result in error or warning messages, check out the repository.

Additional functions

Some of the warnings will allow you to auto-fix them. Click where indicated in the warning message to start the process. You will be asked to confirm before the fix occurs. Here is a list of auto-fixes that may be allowed:

  • Replace position(..) with index().
  • Remove leading and trailing spaces

Support for this project

This add-on is currently in public beta. If you feel you've found something in this resource that can be fixed or improved, email your form definition to support@surveycto.com, providing the fullest description of the problem you can. Use the subject "Form validator error". To download a form definition from a Google Sheet, on the top, go to File > Download > Microsoft Excel (.xlsx).

If you are curious about the source code, check out the repository. Keep in mind that the main Google App Script file is not directly linked to the repository, so there may be minor differences.

Click the "Follow" button on the top of this page to receive email updates about this add-on.

Do you have thoughts on this support article? We'd love to hear them! Feel free to fill out this feedback form.

0 Comments

Please sign in to leave a comment.