Embedded Files
Consume data from Google Sheets in Excel
Consume data from Google Sheets in Excel
My "MySQL-Key-Dates" Google Sheets spreadsheet contains generic information about MySQL versions and vulnerabilities...
But what if I wanted to use something like the example "Tracker" tab to manage my actual MySQL estate? ....
I don't want to make that information publicly available (i.e. I don't want to populate the Tracker tab with real information).
I don't want to duplicate effort by maintaining the same data in two places (like a public and private version of the spreadsheet)
At work I can only use Excel.
The "Compliance" tab needs the "Versions" tab which, in turn, needs the "CVE" tab, but the "Tracker" only needs the information from the "Compliance" tab.
If I have a copy of the "Compliance" tab in an Excel spreadsheet then I can create a "Tracker" tab in Excel that can use that data.
I can cut&paste it each time I updated the Google Sheet, but that's quite labour intensive.
However, what I can also do is publish the Google Sheet "Compliance" tab and consume it in Excel which can refresh the data automatically each time the Google Sheet is updated.
Here's how...
Publish Google Sheet
Publish Google Sheet
File - Share - Publish to web
In this case I have chosen to publish the "Compliance" tab as a CSV file as that is easy for Excel to ingest later.
Make sure the "Automatically republish when changes are made" checkbox is ticked (scroll down to see it).
Create Connection in Excel
Create Connection in Excel
Data - From Web
Paste the URL from Google into the the URL field.
Leave the toggle at BasicClick "Load"
This will create a tab containing a table of the data from Google.
I will rename that table (Table Design - Table Name) to "ComplianceG".
I will rename some of the columns to make it easier to add them to formulas later (Data - Queries & Connections, double-click the Query in the right hand sidebar and overtype the column headers).
If you just overtype the headers in the Table tab, instead of using the method above, you will lose your customisation each time you refresh the data.
Page updated
Google Sites
Report abuse