Connecting Excel to the Google form results

Part Two

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

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.

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'.

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.