Linking Google Forms results to Excel

NOTE - Google has changed (removed) some of the functionality of their documents and reproducing this process may be difficult. (Sep 2014)

Part One - Publishing survey data

The Office Web App now includes a new Excel Survey tool for collecting data which is then stored in a worksheet. It is an interesting development but at this time I prefer the older Google Docs form. This may be because I haven't discovered how to do certain things in the Excel web app and also it wont work with older browsers.

The Google Forms have recently become more sophisticated and there are more options for controlling the responses.  Having set up a form in Google Docs and collected data, some users may then face the inconvenience of copying the survey responses and pasting them into Excel in order to analyse and share them in a more familiar program. Excel 2010 can automate this process.

Creating an on-line survey

Within your (free) Google Docs account, create a new Form document. In the new worksheet, select the Form menu and then create a series of questions for your survey.

The App will display the URL web address of the new form and this link can be shared with your intended recipients. Whenever a form is completed the results are automatically appended as a new row on the worksheet.

The App also allows you to publish the results as a read only list in a variety of formats (e.g. HTML). The URL and hyperlink of that published file can also be shared with colleagues who may use the data.

In order to use the following method of connecting Excel to the survey results, make sure that the format of the published data has been changed to TXT (plain text). If you open the published file you will discover that it is actually a TSV tab separated text file and not plain text - this makes it easier to import and use the data.

This option has been changed by Google and you no longer have the options of choosing a format for the published data. There may be unofficial ways of getting around this (e.g. by appending 'export?format=csv&id' to the link) but they may not work as smoothly as the original application.

This page is not included within the Excel functions guide

Click here for details about obtaining this file