Previous Next

Google Sheets (a.k.a. "Google Spreadsheets") is a powerful, flexible, free online spreadsheet program, available as part of Google Drive and the Google Docs suite of cloud-based office software. There are innumerable ways that you might manage, monitor, share, process, or analyze your data in Google Sheets, so we try to make it easy to publish your data directly from SurveyCTO into Sheets.

You can get started by going to your server console's Export tab, scrolling down to the Advanced: publishing form and dataset data to the cloud section, and clicking the ON/OFF toggle to ON if you haven't already enabled cloud publishing.

To configure any one of your forms to publish to Google Sheets, click on the Configure option for that form, and then click Add connection in the Google Spreadsheets row of the panel that appears.

You will then be asked to give the SurveyCTO server permission to access your Google Sheets and Google Drive accounts. This permission is necessary so that SurveyCTO can list your existing Sheets and publish to the one you select, and it only takes one or two clicks (plus logging in to your Google account, if you haven't already). SurveyCTO will only read from or write to Google Sheets as you instruct in configuring publishing options.

Next, you will need to decide exactly where you want to publish your form data: to an existing Sheet (in which case you just select it from the list) or to a new Sheet (in which case you use the + button, enter the name of the new Sheet to create, and then click the checkmark button to create that Sheet).

After that, you will need to decide exactly which form fields to publish, to exactly which columns on the Google side (we call this process "mapping"). There is an Add all button to simply add all form fields at once (or, in the case of encrypted forms, all form fields that have been marked as publishable), plus an Add button to add fields one-by-one. For each added field, SurveyCTO will default to publishing to a column name on the Google side that matches the field name on the SurveyCTO side – but you can override this default in cases where you want the column name on the Google side to be different.

Note that fields that are inside repeat groups will be published in "wide" format. They will be listed with an * at the end of their names, and they must be mapped to destination columns that also end in *. When repeated data is published, the * will be automatically replaced by _1 for the first instance, _2 for the second, and so on.

Other options

Finally, you have a few other options available:

1. You can indicate one of your form fields to use in uniquely identifying records on the Google side. If you do, then new form submissions will either update an existing row of the Google Sheet – if a row with a matching value in the corresponding column already exists – or insert a new row. If you don't specify a unique identifier, then new submissions will simply publish as new rows. You would likely want to use a unique identifier if you were using a Google Sheet to merge data from multiple sources. (This unique identifier has to be one of the form fields that you listed in the field mapping. I.e., you also have to publish whatever field is being used to uniquely identify rows.)
2. You can indicate one of your form fields to use as a kind of filter. If you do, only submissions for which the specified field contains the value 1 will be published. For example, if you wanted to only publish data for children, you might select an "ischild" field as the filter; in your form, that could be a calculate field with a calculation expression like "if(${age} < 18, 1, 0)".
3. You can check Publish existing data if you want to publish existing form submissions. If you don't check this option, then only new submissions that come in (after you configure publishing) will publish to Google.

Publishing

As submissions come in to the server, they will be automatically published to your Google Sheet according to the field mappings and other options that you have configured – but there will be a brief delay of up to ten minutes.

By default, SurveyCTO publishes into the first (left-most) worksheet in your target document. However, you might have some kind of dashboard set up as your first worksheet, or you might otherwise prefer the raw data to publish to another worksheet rather than the first one. In that case, just name the worksheet into which raw data should publish "data": if SurveyCTO sees a worksheet named data, it will publish to that worksheet even if it's not the first one.

See the Google Sheets website for details on configuring and using Google Sheets itself.

Previous Next