MeadInKent
| Links |Part One - an online survey using Google forms| Alphabetical Index |

Connecting Excel to the Google form results

Part Two

Excel create link to external data source

A Google form has been created and configured to Publish the Results to the Web in TXT format.

In Excel we will make a connection to the external data. Select the option Data | Get External Data | From Text.

An Import Text File dialog box appears - paste the URL of the published file into the Filename and click [Open]. The Text Import Wizard will guide you through the steps of analysing the file. Make sure it is defined as a tab delimited file type

Import a Text file containing the published results

Excel will prompt you for a cell location where you want the list to be placed (e.g. =$A$1). Be sure to put the list in a location where there is room for expansion - i.e. the rows beneath the list should be left empty.

The range containing the new list of results is recognised by Excel as having certain connection properties and the data can be refreshed without the need for re-running the Text Import Wizard. If you select a cell within the list the Data | Connections | Properties button will be activated. Make sure that the properties are set to refresh the data each time the workbook is opened.

Data connection properties

The Refresh All button will update the list with any new Google Form survey responses - although there might be a delay of a few minutes before they are published and available to Excel.

It maybe more convenient (for non expert users) to create a simple macro which can perform the data refresh and link it to a button or shape on a worksheet.

Note that the linked data may include the Google Docs Timestamp which may not correspond to your local date format. i.e. Timestamp uses 'mm/dd/yyy' while Excel may interpret dates as 'dd/mm/yyy'.

formula to convert timestanp date from US to UK
  Sub RefreshLink()
' Refresh the external data connection to the Google form survey results
Worksheets("formdata").Select
Range("A1").Select
ActiveWorkbook.Connections("Connection1").Refresh
' put the date and time of the latest refresh in cell Main!C10
Worksheets("main").Range("C10").Value = Now()
End Sub

The lengthy formula takes each element of the original timestamp to create a new date - converting 1st July to the intended 7th January.


Part one - publishing a list of survey results to the web

file: GoogleForm2.htm Page last updated: Jan14 MeadInKent.co.uk 2014