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" ?
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
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✌
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.
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
@@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: th-cam.com/video/c841qBQAhUM/w-d-xo.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: th-cam.com/video/lqFoVeNpA-4/w-d-xo.htmlsi=ctiJB2-QQMDJeEXG
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?
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: th-cam.com/video/qnAboKJafg8/w-d-xo.htmlsi=2x7bsQl1K80K18RO th-cam.com/video/VmlSN-C2GGo/w-d-xo.htmlsi=hH9EeNbSl1Dk0AAa
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!
@@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 👍
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): th-cam.com/video/bebQ-Rws0rs/w-d-xo.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.
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: th-cam.com/video/0bZKqCY5rTU/w-d-xo.htmlsi=I99pQKLUhZWwqfsh
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.
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
Check this other video where I show how to import specific cells: th-cam.com/video/c841qBQAhUM/w-d-xo.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
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"
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
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
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
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: th-cam.com/video/uyIk_2RtgZw/w-d-xo.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.
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?
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 TH-cam. Another possibility is using the Google Sheets API, but that's more complicated.
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.
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: th-cam.com/video/qnAboKJafg8/w-d-xo.html
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: th-cam.com/video/lqFoVeNpA-4/w-d-xo.htmlsi=QlhQwjl4ZFGE4BoU
Exactly what I needed. Worked perfectly! Thanks.
Thank you, excellent material and explanation. Subscribed!
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" ?
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
excellent thank you very much for your help@@ExcelMacroMania
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✌
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.
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
@@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: th-cam.com/video/c841qBQAhUM/w-d-xo.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: th-cam.com/video/lqFoVeNpA-4/w-d-xo.htmlsi=ctiJB2-QQMDJeEXG
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?
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:
th-cam.com/video/qnAboKJafg8/w-d-xo.htmlsi=2x7bsQl1K80K18RO
th-cam.com/video/VmlSN-C2GGo/w-d-xo.htmlsi=hH9EeNbSl1Dk0AAa
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?
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!
@@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 👍
hi, thanks for your video. do you think you could overwrite the web database with VBA?
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): th-cam.com/video/bebQ-Rws0rs/w-d-xo.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.
thx you
Great Video, but when i use button execution more than once.. debug.. ActiveSheet.QueryTables(1).Delete, please help solution
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: th-cam.com/video/0bZKqCY5rTU/w-d-xo.htmlsi=I99pQKLUhZWwqfsh
@@ExcelMacroMania Thank You sir🙏
Can tell me how to edit this data
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?
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.
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
Check this other video where I show how to import specific cells: th-cam.com/video/c841qBQAhUM/w-d-xo.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
How do I implement readystate_complete I would like to prompt MSGBOX completed. Much Appreciated
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"
Can you specify a sheet on the google sheet?
How would the url change?
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
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
this is what it look like the header row is blank
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
Is there a possible way to transfer selected items from google sheet to excel using VBA?
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: th-cam.com/video/uyIk_2RtgZw/w-d-xo.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.
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?
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 TH-cam. Another possibility is using the Google Sheets API, but that's more complicated.
Hi Sir, i need help you, how get data from web by date to date excel vba
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.
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?
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: th-cam.com/video/qnAboKJafg8/w-d-xo.html
@@ExcelMacroMania thank you..
can't import Pivot?
Can NOT, you can only import the data, then you could replicate the pivots in Excel.
its dosnet work!!!
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:
th-cam.com/video/lqFoVeNpA-4/w-d-xo.htmlsi=QlhQwjl4ZFGE4BoU