5 Methods To Get Data From Google Sheets with Excel VBA
HTML-код
- Опубликовано: 21 сен 2023
- Read the post here: excelmacrofun.blogspot.com/20...
In this video we review five different methods to import data from Google Sheets using Excel VBA. One way or another, there is always a HTTP request to the Google web resource that connects with the Google spreadsheet. The main difference between the various methods presented here lies on the Google web resource/API URL used to access the data. Another important difference is the format of the output data. Some methods allow to get only public data, while other may also let import private or restricted data.
Find many other macro examples and Excel VBA learning materials (including the Excel VBA Guide for Beginners and Excel VBA Objects Guide for Intermediate) in the blog spot: excelmacroclass.blogspot.com
And yet, if you want more, you can find various Excel applications of different nature in the other blogs of the Excel Macro Mania saga:
Excel Macro Fun (excelmacrofun.blogspot.com/)
Excel Macro Business (excelmacrobusiness.blogspot.com/)
Excel Macro Sports (excelmacrosports.blogspot.com/)
Awesome, very useful. Thanks for sharing Sir.
Sir, unable to use the Google Script Api ( 5th Method). Please make a dedicate video on the 5th method.
I will make another video to cover that soon.
\o/ I love this video. I will create a licence validation with one of these metods. Thanks a lot!
Hello, i also want to make license validation using the 5th method. But unable to use the 5th method. Can you please help ?
Getting Error "Script function not found: doGet" in 5th Method while sending request from excel vba.
You need to create your own Google API, the one I show was just temporary and for demonstration purposes (plus, you cannot see the full URL in the video).
how to upload data from vba to google drive sir.
When you say google drive I imagine you are thinking of Google Sheets, as that's the best recipient to store Excel data. I have covered how to upload data from Excel to Google Sheets in this other video: ruclips.net/video/bebQ-Rws0rs/видео.htmlsi=bSkUAroOsPBzyJtY
@@ExcelMacroMania What I mean by data here is files (pdf, excel, and the like), to input data into Google Sheets, I have finished studying it, I want to learn how to upload files (pdf, Excel, images) to Google Drive using vba excel, ty sir
@@Bnet-gv1dm There is no google drive library for VBA. You can create your own but need to implement Google Auth2 authentication, which was possible with IE because Excel has native libraries to automate IE. But now with Edge you need external library (for example Selenium) and is more complicated. Some alternatives are 1. using google forms to upload file in a similar way as I used it to upload data to google sheets in this video: ruclips.net/video/bebQ-Rws0rs/видео.htmlsi=UZIEbDeT-0A9QS8l
2. Another possibility is creating your own google script API as I did to get restricted data from google sheets in this other video: ruclips.net/video/VmlSN-C2GGo/видео.htmlsi=cNdAmB6a9J2sy7bG
3. Another option, depending on your knowledge of .NET, is using Visual Studio to create an VSTO add-in to do the job with VB.NET or C#, because .NET can work with Google APIs
4. Another option far more simple is uploading to Microsoft OneDrive instead of Google Drive.
How to get the key
The key is in the Google Sheets URL after docs.google.com/spreadsheets/d/..... and before the next forward slash. For example, my Google spreadsheet below has the following key or ID: 1WopmB1ExWSNul9sw9n7I9FEyquS4AyEugt_fKQrHJSI
docs.google.com/spreadsheets/d/1WopmB1ExWSNul9sw9n7I9FEyquS4AyEugt_fKQrHJSI/edit?usp=sharing
More details in the post: excelmacrofun.blogspot.com/2023/09/5-ways-to-get-data-from-google-sheets.html