Tuesday 8 April 2014

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!





5 comments:

  1. Very useful James - thanks! I'm working on a way of going in the opposite direction, so I'm thinking of VBA export directly into a Drive sub-folder (as CSV) where an App script can pick it up and work with it to produce reports/charts. I think we really need a database in Google Apps!

    Cheers, David.

    ReplyDelete
    Replies
    1. Thanks David - was wondering how to do it the other way too! We really do need a database - maybe Fusion tables will plug some gaps in the future? If you do manage to get the VBA to Drive working, please could you keep me updated - as I would be very interested in this! Cheers, James

      Delete
  2. I've managed to do this by using the Google drive app to save a .csv locally and have it sync automatically. Then use the 'on open' import from csv script here: https://developers.google.com/apps-script/articles/docslist_tutorial to a sheet in a spreadsheet. Your vlookups etc can then auto-refresh from another sheet and be ready to go.

    ReplyDelete
  3. Hello Sir,

    I have created a google form where my team enters data on a daily basis. All these data gets stored in google spreadsheet in backend.

    I wanted to know if I can somehow push these data on a realtime basis to MS Access .. or whenever I open MS Access I can fetch all data from googlespreadsheet into MS Access and run queries?

    I tried to google for it and read something about setting up ODBC etc .. but couldnt understand. Please help

    ReplyDelete
    Replies
    1. Hi - the only way I have been able to do this is as is described in the article above. It does require Excel to be opened as well as Access, but should refresh

      Delete