Google sheet as data source

[Les Isaacs]

Hi all - does anyone know how to use a Google sheet as a data source for a local accdb?

 

[arnelgp]

you can download the sheet and create a linked table.
or use Excel to transform the data from google sheet and create a
linked table from the Excel workbook.

 

[ComputerVersteher]

Seems like there are several commercial ODBC-Drivers available for Google Sheets. Simply search for: google sheets odbc connection.

 

[Les Isaacs]

you can download the sheet and create a linked table.
or use Excel to transform the data from google sheet and create a
linked table from the Excel workbook.

Hi - I need the link to be dynamic, so that the data in the accdb updates as the data in the Google sheet is amended.

 

[Gasman]

They have an API ?

 

[arnelgp]

if you Transform the data to macro-enabled excel, you can have a
vba to Refresh the data:

Sub RefreshQueryTable()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name

    Dim qt As QueryTable
    For Each qt In ws.QueryTables
        qt.Refresh BackgroundQuery:=False
    Next qt
End Sub

 

[Uncle Gizmo]

you can download the sheet and create a linked table.
or use Excel to transform the data from google sheet and create a
linked table from the Excel workbook.

In this video I demonstrate a simple method. Somewhat similar to ArnelGP's solution already posted.

However, if you look in the video comments I've posted chattys overview on how to do it.

With Chatty as a guide, I have been successful in linking to a Google sheet from MS Access.

I also added code to update the Google sheet, to identify when I had already extracted records so that they would be ignored on further downloads. This was done in a Google Apps script... I'm not convinced this is a "Safe" method...

It's a work in progress, not yet ready for publication.

I will eventually do a video explaining Chatty's solution.

I would add that although ChatGPT provided and excellent guide for this process, if I hadn't had experience with Microsoft Access and VBA I don't think I would have been able to achieve a good outcome ..

Copied from the Video Comments:-

@NiftyAccessMicrosoft
2 months ago
Hi @mazwenb5783,

Great question! Syncing data between Google Sheets and Microsoft Access automatically can save you a ton of time and hassle. Here’s how you can get it done:

### Syncing Google Sheets with Access:

*1. Using Third-Party Tools:*
- *Zapier or Integromat (now Make.com):*
These automation tools can bridge the gap between Google Sheets and Access. They create workflows (called Zaps in Zapier) that trigger actions (like data updates) based on changes in your Google Sheets.

- *Zapier Example:*
1. Set up a Google Sheets trigger for new or updated rows.
2. Add an action to send this data to an intermediate service (like a webhook).
3. Have a script or another service that takes this webhook data and updates your Access database.

*2. Using Google Sheets API:*
- If you’re comfortable with coding, you can write a script to fetch data from Google Sheets using its API and then push this data to Access via ODBC (Open Database Connectivity) or a similar method.

Here’s a basic outline of how this could work:
- **Fetch Data**: Use the Google Sheets API to get new data.
- **Update Access**: Use a script (in VBA, Python, etc.) to insert this data into your Access database.

I went with this solution from Chatty:-
*3. Custom VBA Solution:*
- If you prefer sticking with VBA in Access, you can use it to periodically fetch and update data from Google Sheets. This involves:
1. Setting up an HTTP request in VBA to access the Google Sheets API.
2. Parsing the JSON response to extract your data.
3. Inserting this data into Access.

### Why Sync?

- **Efficiency**: Automatic syncing means you’re not wasting time manually transferring data.
- **Accuracy**: Reduces human error in data entry.
- **Real-time Data**: Always have the latest data at your fingertips.

Here’s a sample VBA code snippet to get you started with fetching data from Google Sheets:

Note: Further to this simplified outline of the process provided by chatGPT, I asked chatty a load more questions before I eventually got it working. Chatty lead me up at the garden path several times!

Sub FetchGoogleSheetData()
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    Dim url As String
    url = "https://sheets.googleapis.com/v4/spreadsheets/YOUR_SHEET_ID/values/Sheet1?key=YOUR_API_KEY"
    
    http.Open "GET", url, False
    http.Send
    
    Dim response As String
    response = http.responseText
    
    ' Now parse the JSON response and insert into Access tables
    ' You'll need to use a JSON parser library for VBA
    
    ' Example of parsing response (pseudocode)
    ' Dim json As Object
    ' Set json = JsonConverter.ParseJson(response)
    ' For Each row In json("values")
    '     ' Insert row data into Access table
    ' Next row
End Sub

Syncing can be as simple or as complex as you need it to be, depending on your exact requirements and comfort with the tools and coding.