There have been a number of changes to Google Docs over the years each of which has provided more ways to do things. The latest is the new Sheets API V4,which is by far the best way to integrate Excel and Sheets. To see how to do that see Using the Google Sheets V4 API from VBA to integrate Sheets and Excel

I leave these older pages here in case you still use those methods.

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 authenticationPublic 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

Subpages