Quick Links

Other stuff

Site owners

  • Bruce Mcpherson

Services‎ > ‎Desktop Liberation‎ > ‎

Get Data From Google Docs


With the new Google Sheets, came a some changes to the way that the spreadsheet schemas are organized. This applies to the new sheets. You can still get data from old sheets using the old method. If you want to update sheets, consider Apps built with data abstraction, which can both read and write from Excel to Sheets. If you are just retrieving data, this page is best.

The key

Each Google Spreadsheet has a unique key, that looks something like this 12pTwh5Wzg0W4ZnGBiUI3yZY8QFoNI8NNx_oCPynjGYY.  You can see it in the url for your spreadsheet as you access it. 

Public or private

In the old sheets it was possible to import a public sheet without it being published. WIth new sheets, you will need to publish the sheet from the file menu if you want to get it into Excel using these libraries. The normal usage though will be to get your own private data. For this you need to use oAuth2. I have automated this to make it as unobstrusive as possible but you will need to set up your PC with your Google credentials which you can obtain from the Google Cloud console. See Google Oauth2 VBA authentication for how.

Once you have your key and either published or set up your PC for Oauth2 (you will need the viz scope setup), you are good to go.

Overview of how this works

  • If you need authentication, the library will initiate a conversation with Google to get permission to access the file indicated by your key.
  • It will access the schema of the spreadsheet to find out which worksheets are in the workbook, and also how to access them
  • By default, all the worksheets in the workbook will be extracted and copied to your excel workbook You can supply a list of just the ones you want if you don't need them all. The entire worksheet will be fetched. You cant just take a selection from it. If you need that then sort it out when the data arrives, or consider using DriverSheet
  • Another issue with the new sheets is that if you have a column with mixed data in it (numbers and text for example), it will be omitted from the data transfer. You should use format on your original sheet to change the format to text.

Public workbook import.


This code will import all the data for all the sheets in a published workbook.

Public Sub testWorkBookImportPublicSheets()
    Dim key As String
    
    ' this example imports all the worksheets in a workbook with this Key from a public published sheet
    ' you need to have enabled oauth2
    key = "12pTwh5Wzg0W4ZnGBiUI3yZY8QFoNI8NNx_oCPynjGYY"
    
    If Not importGoogleWorkbookNewSheets(key) Then
        MsgBox ("failed to import workbook at " & key)
    
    End If
End Sub

Private workbook import

This code will import a private workbook. Note that the only difference is the true argument that indicates you want to use oAuth2. You need to have set up your PC for viz scope first (you only need to do it one time), as described in Google Oauth2 VBA authentication

Public Sub testWorkBookImportNewSheets()
    Dim key As String
    
    ' this example imports all the worksheets in a workbook with this Key from a  private sheet
    ' you need to have enabled oauth2
    key = "12pTwh5Wzg0W4ZnGBiUI3yZY8QFoNI8NNx_oCPynjGYY"
    
    If Not importGoogleWorkbookNewSheets(key, , , True) Then
        MsgBox ("failed to import workbook at " & key)
    
    End If
End Sub

Import a subset of sheets.

You can supply a  list of sheets you want like this, for either public or private sheets like this.


Public Sub testSelectedImportNewSheets()
    Dim key As String
    
    ' this example imports the given list of private sheets
    Dim listOfSheets As String
    listOfSheets = "carriers,exceldemo"
    key = "12pTwh5Wzg0W4ZnGBiUI3yZY8QFoNI8NNx_oCPynjGYY"
    
    If Not importGoogleWorkbookNewSheets(key, , , True, , listOfSheets) Then
        MsgBox ("failed to import workbook at " & key)
    
    End If
End Sub

Here's the code for the whole thing. You can download this from Downloads. You'll find in the googleSheets module of cDataSet.xlsm, emptycDataSet.xlsm or dbAbstraction.xlsm

For help and more information join our forum,follow the blog or follow me on twitter .



Comments