SurveyCTO form validation in Google Sheets – SurveyCTO Support Center

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 Apps Script File to your Google Drive. Follow these steps to do so

Open the Google Apps Script file here (last updated March 6, 2023).
In the upper-left, click the i in a circle, Overview.
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.
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.
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 Apps Script file. Follow these steps to use the add-on:

Part 1: Preparation

Do this every time.

Open the Google Apps Script file in your drive.
In the upper-right, click Deploy, then Test deployments.

Part 2: Enable add-on

Do this the first time you open the validator file.

In the popup, to the right of Select type, click the gear icon Enable deployment types, and click Editor Add-on.

While not a required step, it is okay to de-select Google Workspace Add-on, which is not needed.

Part 3: Prepare form

Do this whenever there is a new form you would like to test.

Go to Saved tests, and click Add test. If this is the first time using the add-on, click Create new test.
Click the Test document text box.
Select the spreadsheet form definition you would like to test.
Click Save test.

Part 4: Test form

Do this every time.

Still in the Test deployments popup, under Saved tests, click the circle to the left of the form you would like to test.
Scroll up, and click Execute. The spreadsheet will open in a new tab.
In the Google Sheet form definition, on the top, click Extensions > SurveyCTO Form Validation > Open validation panel (it may take a few seconds to load the add-ons list). The form validator will open as a panel on the right.

If the SurveyCTO Form Validation option does not appear, follow these steps:

Go back to the Google Apps Script file.
In the editor (under the bar that says "Run" and "Debug", and to the right of the "Files" panel), make a change, such as adding a single letter (it doesn't matter where, since you will undo this later).
Save with Ctrl + S (Windows) or Cmd + S (MacOS).
Undo the change you made with Ctrl + Z (Windows) or Cmd + Z (MacOS).
Save again with Ctrl + S (Windows) or Cmd + S (MacOS).
Follow the steps for Part 4: Test form again.

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:

Drag-and-drop the file from your computer into Google Drive.
In Google Drive, right-click the file, hover over Open with, and click Google Sheets.
The form will open in Google Sheets, but as an XLSX file. On the top, click File > Save as Google Sheets.
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.
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 Apps Script file is not directly linked to the repository, so there may be minor differences.