Import Data From Google Sheets via Web Query Excel VBA Macro

Подписаться 6 тыс.

Просмотров 10 тыс.

50% 1

In this video we will see how to import data from Google Sheets in Excel using VBA macros, and taking advantage of Excel's web query functionality. It requires the Google spreadsheet to be public and the spreadsheet key added in the query at the end of this url: "spreadsheets.google.com/tq?tq..."
You can find the macro example to import data from Google Sheets as a web query table using VBA macros in this post of the Excel Macro Class blog: excelmacroclass.blogspot.com/...
Importing a Google spreadsheet in Excel as a web query table is quite like importing any other web table. We have already seen how to add a web query table to a worksheet in this other video in the channel: • Add Web Query To Works...
You can find more details for the macro example to add a web query table to a worksheet, which is similar code to the one used to import a Google spreadsheet, in this post of the Excel Macro Class blog: excelmacroclass.blogspot.com/...
And you can find many other macro examples and Excel VBA learning materials (including the Excel VBA Guide for Beginners) in the blog under the link: 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/)

Опубликовано:

28 мар 2022

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 45

@PedroSilva-bc9gy 4 месяца назад

Thank you, excellent material and explanation. Subscribed!

@rhymeswithyourmom_ Год назад

Exactly what I needed. Worked perfectly! Thanks.

@oguzzeybek Год назад

thx you

@JonelAlboroto 4 месяца назад

Wow, it works perfectly and very simple. thanks a lot Question: is it possible to also import all format and formulas? or maybe import an excel file from google drive?

@ExcelMacroMania 4 месяца назад

Unfortunately, not with this method. You could get the formulas using a Google script, for example: var rng = MySheet.getRange("A10").getFormula(); 'or getFormulas() for many cells and then exporting then the formulas as plain text. Once in excel, the formula as "text" would probably be recognized as formula if added correctly: Range("A10").Formula = txt 'where txt was imported as text and is something like "=SUM(A2:A9)" More complicated, but if you are interested you can have a look at these other videos: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-qnAboKJafg8.htmlsi=2x7bsQl1K80K18RO ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-VmlSN-C2GGo.htmlsi=hH9EeNbSl1Dk0AAa

@georgiivanov1902 Год назад

Great video, very informative and easy to follow. Is there a way to pull data from a private google sheet file, not a public one?

@ExcelMacroMania Год назад

YES it is possible. But you need to implement Google Sheets API, get an API key, implement OAuth, etc, and that's kind of tricky in Excel VBA. An alternative, and what I personally do, is creating my own Google script/macro/API with my own authorization to access the restricted spreadsheet, and then connect from Excel VBA. But that requires some knowledge of Google Apps Script. I may upload something about that in the near future. Hope that helps!

@georgiivanov1902 Год назад

@@ExcelMacroMania thank you. I did go down the rout of creating project, key, 0Auth etc but where I'm struggling with is the code to bring it all together. I haven't tried the AppScript though. I look forward to your next video on it 👍

@jcmerlob746 Год назад

hi, thanks for your video. do you think you could overwrite the web database with VBA?

@ExcelMacroMania Год назад

I guess you refer to the "web database" as the Google spreadsheet used as a database? In that case, you can overwrite the values or add new ones in various ways. This other video shows how to add values through a Google Form (this adds new entries, do not overwrite/change): ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bebQ-Rws0rs.html You will need to use the Google Sheets API to overwrite/change values. I generally create my own Google script (within the Google spreadsheet or separately) to do that. That's all a bit more complicated, I will probably try to show it in a later video.

@SwissMarkus 8 месяцев назад

brilliant tutorial ! thank you very much for sharing your know How👍 question: my Google-Sheet(Table/File) contains 5 different sheets what will I have to specify in order to import let's say Sheet named "Monday" ?

@ExcelMacroMania 8 месяцев назад

I explain that with a URL example in the blog post: excelmacrofun.blogspot.com/2023/09/5-ways-to-get-data-from-google-sheets.html You need to use the gid of the sheet you want to import the data from. The gid is shown in the sharing or editing url usually at the end, and is different for each sheet in the Google spreadsheet. You add it to the modified url presented in this video as follows: spreadsheets.google.com/tq?tqx=out:html&key=" & key & "&gid=" & gid

@SwissMarkus 8 месяцев назад

excellent thank you very much for your help@@ExcelMacroMania

@SwissMarkus 8 месяцев назад

your know how helped me a lot and I managed to code what I was looking for. but google sheets drive me insane: when debugging a function everything works fine but the moment I call the same function out of another I get errors: 1. method accessing a Sheet: var myDriveFolder = DriveApp.getRootFolder(); response: "You are not authorized to call DriveApp.getRootFolder." 2. method accessing a Sheet: var spreadsheet = SpreadsheetApp.openById(prodArbeitsmappe); response: "You are not authorized to call SpreadsheetApp.openById." and I'm the Admin/Creator of the sheets and still things work out only when debugging ? maybe you have an Idea that helps me before I go nuts :-) ? thank you already now@@ExcelMacroMania I found the solution: the problem was the try and catch exception handling ... out of unknown reason an error thus catch got activated. I changed the opening to a simple var spreadsheetProd = SpreadsheetApp.openById(prodArbeitsmappe); without the try and catch ... and this works fine ... followed by if (!spreadsheetProd) { ....... perfect ... thank you anyway✌

@tulioh.r.r.7768 Год назад

Hi, It works perfectly, but I need to be able to do it on a restricted google sheets, is there any way to approve credencials inside VBA?

@ExcelMacroMania Год назад

You will need to look at OAuth2.0, and eventually work with Sheets API. However, that's all complicated with Excel VBA. I usually have a workaround and create a script with Google Apps Script (either within the spreadsheet or separately) that can read the restricted spreadsheet, and then connect Excel to that API. I may upload some about it in the future.

@phoneus1phoneus134 2 года назад

Your video is really useful for me but I need to make your macro can upload specific cells and import a specific cell too. I have a custom function on Google Sheets that can calculate distances through google maps. Unfortunately, this function cannot be programmed within Excel VBA However, The custom function contains 6 arguments: =GOOGLEMAPS_DISTANCE(origin,destination,mode,waypoints1,waypoints2,waypoints3) I need to create a function within my excel file that can: - Connect to a Google Sheet file. - Copy the values of its arguments to google sheets to cells B3, C3, D3, E3, F3, G3. -Imports the results of my custom function in google sheets from cell C7. So is this possible within Excel VBA? And if possible how can I do that?

@ExcelMacroMania 2 года назад

This macro only imports/downloads the data. To upload and also download data in specific cells, you probably want to look into HTTP GET requests (to get specific data), and POST requests through a Google form, there are some videos about that on RU-vid. Another possibility is using the Google Sheets API, but that's more complicated.

@rockonlyrock777 Год назад

How do I implement readystate_complete I would like to prompt MSGBOX completed. Much Appreciated

@ExcelMacroMania 8 месяцев назад

Just add the following loop after sending the HTTP request: Do Until HTTPreq.readyState = 4: Loop or Do While HTTPreq.Status 200: Loop And then your MsgBox "Complete"

@martinpintado2101 8 месяцев назад

Great job. I am using it, but, for my purpose I need access the data before macro (Sub) execution end. ¿Is it possible? As far as I saw, the data is available once all macros executions ends.

@martinpintado2101 8 месяцев назад

I found the solution. I leave the piece of code changed With qt .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .BackgroundQuery = False ' Establecer en False para hacer la actualización sincrónica .Refresh End With

@ExcelMacroMania 8 месяцев назад

@@martinpintado2101 Thanks for your question and answer, it's great when people contribute with their knowledge here. I was thinking you could want to use some other method to get the data. I describe another method that sends a HTTP request to the google spreadsheet URL in this other video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-c841qBQAhUM.htmlsi=IUau0F7rO6-cxSm0 And I recently published this other video where I cover 5 different ways to import data from google sheets, I think you could find it interesting: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-lqFoVeNpA-4.htmlsi=ctiJB2-QQMDJeEXG

@Mr9Inchez3 Год назад

Can you specify a sheet on the google sheet? How would the url change?

@ExcelMacroMania Год назад

You need to use the gid of the sheet you want to import the data from. The gid is shown in the sharing or editing url usually at the end, and is different for each sheet in the Google spreadsheet. You add it to the modified url presented in this video as follows: spreadsheets.google.com/tq?tqx=out:html&key=" & key & "&gid=" & gid

@ruangbelajarku2023 7 месяцев назад

Sir I have userform to used some user, I iwant user only import data Last Row from last entry and loop to next row all data. any video tutorial this condition? thanks before

@ExcelMacroMania 7 месяцев назад

Check this other video where I show how to import specific cells: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-c841qBQAhUM.htmlsi=ORw4m9NB1tyO4G3b Then you target the last row and set a condition to add the data (innerText) only from that row. See the code also here: excelmacroclass.blogspot.com/2022/12/import-specific-cells-from-google.html

@asiam4774 2 года назад

Hi Sir, i need help you, how get data from web by date to date excel vba

@ExcelMacroMania 2 года назад

Yes, I remember you asked about it before, and I am planning to upload something soon. If can't do it with a web query, you may wanna look into an HTTP requests, or an API request (if available), or to automate IE with VBA only (rather outdated), or Edge, Chrome, Firefox with Selenium, there are many videos about that out there.

@jpl9938 Год назад

Is there a possible way to transfer selected items from google sheet to excel using VBA?

@ExcelMacroMania Год назад

1. With a web query (as explained in this video), is probably the easiest way, then you select what you need in Excel and move it to other sheet or delete what you don't need. But not good if it's a very big table... maybe that's your scenario. 2. With HTTP request to that same URL, and then get the HTML table, tr, and td elements that you need. The URL would be the same we use in this video and the HTTP request could follow what's presented in this other video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-uyIk_2RtgZw.html 3. A variation of the method in point 2 using a URL that generates a JSON output from google sheets. That URL link allows to query the google spreadsheet and select only certain values (it works similar to SQL). But then you need to convert the JSON back to Excel format. It's more complicated, I may upload some about it in future. 4. User Google Sheets API. This is more complicated, and needs to setup a project, API key, and several other things to connect to the API. It's the way to go with JavaScript or other programing languages, but rather complex with VBA.

@ruangbelajarku2023 8 месяцев назад

Great Video, but when i use button execution more than once.. debug.. ActiveSheet.QueryTables(1).Delete, please help solution

@ExcelMacroMania 8 месяцев назад

You should have this condition before deleting: If ActiveSheet.QueryTables.Count > 0 Then ActiveSheet.QueryTables(1).Delete That ensures you always have only 1 query table. If you want to have more you need to change the code. If you want to update the existing query table check this other video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-0bZKqCY5rTU.htmlsi=I99pQKLUhZWwqfsh

@ruangbelajarku2023 8 месяцев назад

@@ExcelMacroMania Thank You sir🙏

@apidetdamrongsuntisuk8750 2 года назад

can you help me my column header in th browser is blank then when i run macro there is no header in my excel file. thank you . sory for my english is very bad

@apidetdamrongsuntisuk8750 2 года назад

this is what it look like the header row is blank

@ExcelMacroMania 2 года назад

There is a line of code to clear the cells in the active sheet (supposedly, the sheet with the web query table). Remove or comment that line to avoid clearing the cells including your headers. ActiveSheet.Cells.Clear

@yllazora.7553 Год назад

is it not really possible to import data to excel from google sheet if the google sheet is not public? Is there no other way for that case?

@ExcelMacroMania Год назад

YES, it is possible. One way to do that is using the Google Sheets API along with google authentication OAuth 2.0. That's now a bit tricky to implement with Excel VBA and Edge (I did it long ago with IE). Another way is creating your own macro/script/API in the Google spreadsheet, but that requires some Google Apps Script knowledge. I may upload something about it in the near future. Learn more about macros in Google Sheets here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-qnAboKJafg8.html

@yllazora.7553 Год назад

@@ExcelMacroMania thank you..

@ravikumar2582 Год назад

can't import Pivot?

@ExcelMacroMania Год назад

Can NOT, you can only import the data, then you could replicate the pivots in Excel.

@theogerber1747 4 месяца назад

its dosnet work!!!

@ExcelMacroMania 4 месяца назад

Check the code here, it works! excelmacroclass.blogspot.com/2022/03/import-google-sheets-web-query.html Find other options to import data from Google Sheets here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-lqFoVeNpA-4.htmlsi=QlhQwjl4ZFGE4BoU