Linking Google Sheet data to MS Excel and MS Access

With SharePoint I used to link lists to both Excel and Access quite frequently.  Although Google Sheets does not have the two way interactivity that has been so useful, it is still possible to have data that is entered on a Google Sheet, to be automatically imported and refreshed to a local Excel workbook.  In addition, its possible to use Excel as a bridge to then get Access to use that data.

Link a Google Sheet to Excel

To bring in Google Sheet data to Excel, you need to do a web query in Excel

Enter the URL of your Google Sheet and tick the green box to the left to select the sheet data

This return you something like this in Excel

	A	B	C	D	E	F
1	Trust	Start_Date	End_Date	Recruits	п»ї	п»ї
2	Trust A	08/04/2014	29/03/2014	50	п»ї	п»ї
3	Trust B	17/02/2014	07/02/2014	20	п»ї	п»ї
4	Trust C	19/12/2013	09/12/2013	30	п»ї	п»ї
5	Trust B	20/10/2013	10/10/2013	40	п»ї	п»ї
6	Trust D	08/04/2014	08/04/2014	10	п»ї	п»ї
7	Trust E	07/02/2014	08/04/2014	10	п»ї	п»ї
8	Trust F	09/12/2013	07/02/2014	1	п»ї	п»ї
9	п»ї	п»ї	п»ї	п»ї	п»ї	п»ї
10	п»ї	п»ї	п»ї	п»ї	п»ї	п»ї
11	п»ї	п»ї	п»ї	п»ї	п»ї	п»ї
12	п»ї	п»ї	п»ї	п»ї	п»ї	п»ї
13	п»ї	п»ї	п»ї	п»ї	п»ї	п»ї
14	п»ї	п»ї	п»ї	п»ї	п»ї	п»ї
15	п»ї	п»ї	п»ї	п»ї	п»ї	п»ї
16	п»ї	п»ї	п»ї	п»ї	п»ї	п»ї

As you can see, for any blank cells, these produce "п»ї"  symbols.   You can either hide these with conditional formatting in Excel, or to be much cleaner, just delete empty rows and columns in Google Sheets. If you need to add new data in the Google sheet at any time, its easy just to create new rows as required.

After deleting the unused rows and columns in Google sheets, it now looks like this

In Google Sheets

In Excel

The data in Excel can be refreshed manually by right clicking on the data and clicking refresh, or if you want this to be automatic whenever the sheet is opened, you can right click in the data, and click "Data Range Properties"  clicking the box "refresh data when opening the file"

This should now give you a live link to the Google Sheet data.

Synchronising with MS Access

In Access, you can link an Excel workbook as an Access Table, allowing the data to be then used in queries.

To link in your Excel workbook which contains the linked data from Google Sheets, open Access, and Import and Link as per the below screenshot

Choose the following radio button option

When you have completed the import, you should now see your Google Sheet data, as a table.

As you can see from the above example, Access has recognised numerical and date fields, and my headers for these rows come across as errors.   If you have the same issue, this can easily be sorted in a simple query based from this data as in the below:

Design view:

Datasheet view:

To refresh data from Google sheets therefore, you would just need to open your Excel workbook, allow the refresh from Google to complete, and then open your Access Database, and pull in the new data automatically.  If you wanted, with a few lines of VB you could also get Access to open the Excel workbook when it opens and before it runs queries on the new data.

Please let me know your feedback on this, especially if you encounter any bugs!