The other day I mentioned about trying to find a solution to connect Google Analytics with Microsoft Excel. As a solution, Supermetrics seems to be more up to date with the current versions of Windows and Office, as the BlockSpring installation couldn’t detect my Excel installation even after instructing me to close my open instance of Excel. This left me somewhat disheartened as I have also purchased the course from Coding is for Losers on how to utilise Supermetrics and Blockspring, which focuses on using them in Google Sheets but most of the same steps applied from what I could see in the Excel version of Supermetrics.
So with that roadblock, I resumed my search online, stumbling across and also having trouble installing Analytics Edge, until I finally found myself on this page: https://www.excelinppc.com/using-google-sheets-as-data-source-in-power-query/ Don’t worry about the Powerquery bit, this is actually relevant! I followed the instructions there, downloading my Google Sheet as an XLSX and then finding the download in my browser and copying the download link itself. Then I ignored the part about Powerquery and just went to the Data tab of Excel and selected the From Web option, pasting the link into that box. A moment later, after selecting which sheet I wanted from Sheets, a table of my Sheets data was sitting in front of me in Excel. Overjoyed may be an understatment, this thing has been bugging me for WEEKS! Tentatively, I made some changes in Google Sheets and watched the little ‘Saving…’ message change to ‘All changes saved in Drive’. Switching to Excel, I stared at my sheet for a moment, and then hit Refresh… Success! Deleting data in Google Sheets was also reflected in the Excel table, so I am all set! With this shortcut, my reporting is about to get much easier and that course I bought focusing on usage in Google Sheets is still a good investment. I’m going to learn how to pull all the data into Google Sheets and then link my output sheet to Excel where I do my more advanced work. This also has the added benefit that I can offload the data cleansing and transformation outside of the Excel workbook without actually hiding what’s happening, since Google Sheets is very easy to share and collaborate on. Just before the sale ended, I picked up two courses on Udemy:
Completing these should make me more comfortable and capable with Google Sheets, and add a lot more value to my abilities with the unique options available to Google Sheets over Excel. Look out for my future posts on how they work out!
Here are the links again for reference:
- The Lazy Toolbelt (basics of Blockspring and Supermetrics)
- Use Google Sheets as a data source in Excel
- Advanced Google Sheets For Data Analysis
- Master Google Sheets (and see why it’s better than Excel)