Import Data From Google Sheets via Web Query Excel VBA Macro

แชร์
ฝัง
  • เผยแพร่เมื่อ 30 ต.ค. 2024

ความคิดเห็น • 46

  • @rhymeswithyourmom_
    @rhymeswithyourmom_ 2 ปีที่แล้ว +2

    Exactly what I needed. Worked perfectly! Thanks.

  • @PedroSilva-bc9gy
    @PedroSilva-bc9gy 7 หลายเดือนก่อน +1

    Thank you, excellent material and explanation. Subscribed!

  • @SwissMarkus
    @SwissMarkus 11 หลายเดือนก่อน

    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
      @ExcelMacroMania  11 หลายเดือนก่อน +1

      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
      @SwissMarkus 11 หลายเดือนก่อน +1

      excellent thank you very much for your help@@ExcelMacroMania

    • @SwissMarkus
      @SwissMarkus 11 หลายเดือนก่อน

      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✌

  • @martinpintado2101
    @martinpintado2101 11 หลายเดือนก่อน

    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
      @martinpintado2101 11 หลายเดือนก่อน

      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
      @ExcelMacroMania  11 หลายเดือนก่อน

      @@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

  • @JonelAlboroto
    @JonelAlboroto 7 หลายเดือนก่อน

    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
      @ExcelMacroMania  7 หลายเดือนก่อน

      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

  • @georgiivanov1902
    @georgiivanov1902 ปีที่แล้ว +1

    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
      @ExcelMacroMania  ปีที่แล้ว +1

      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
      @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
    @jcmerlob746 ปีที่แล้ว

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

    • @ExcelMacroMania
      @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): 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.

  • @oguzzeybek
    @oguzzeybek 2 ปีที่แล้ว +1

    thx you

  • @ruangbelajarku2023
    @ruangbelajarku2023 10 หลายเดือนก่อน

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

    • @ExcelMacroMania
      @ExcelMacroMania  10 หลายเดือนก่อน +1

      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

    • @ruangbelajarku2023
      @ruangbelajarku2023 10 หลายเดือนก่อน +1

      @@ExcelMacroMania Thank You sir🙏

  • @loveloveonly6964
    @loveloveonly6964 หลายเดือนก่อน

    Can tell me how to edit this data

  • @tulioh.r.r.7768
    @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
      @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.

  • @ruangbelajarku2023
    @ruangbelajarku2023 10 หลายเดือนก่อน

    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
      @ExcelMacroMania  10 หลายเดือนก่อน

      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

  • @rockonlyrock777
    @rockonlyrock777 ปีที่แล้ว

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

    • @ExcelMacroMania
      @ExcelMacroMania  11 หลายเดือนก่อน

      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"

  • @Mr9Inchez3
    @Mr9Inchez3 ปีที่แล้ว

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

    • @ExcelMacroMania
      @ExcelMacroMania  ปีที่แล้ว +1

      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

  • @apidetdamrongsuntisuk8750
    @apidetdamrongsuntisuk8750 2 ปีที่แล้ว +1

    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
      @apidetdamrongsuntisuk8750 2 ปีที่แล้ว

      this is what it look like the header row is blank

    • @ExcelMacroMania
      @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

  • @jpl9938
    @jpl9938 2 ปีที่แล้ว

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

    • @ExcelMacroMania
      @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: 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.

  • @phoneus1phoneus134
    @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
      @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 TH-cam. Another possibility is using the Google Sheets API, but that's more complicated.

  • @asiam4774
    @asiam4774 2 ปีที่แล้ว

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

    • @ExcelMacroMania
      @ExcelMacroMania  2 ปีที่แล้ว +1

      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.

  • @yllazora.7553
    @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
      @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: th-cam.com/video/qnAboKJafg8/w-d-xo.html

    • @yllazora.7553
      @yllazora.7553 ปีที่แล้ว

      @@ExcelMacroMania thank you..

  • @ravikumar2582
    @ravikumar2582 ปีที่แล้ว

    can't import Pivot?

    • @ExcelMacroMania
      @ExcelMacroMania  ปีที่แล้ว

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

  • @theogerber1747
    @theogerber1747 7 หลายเดือนก่อน

    its dosnet work!!!

    • @ExcelMacroMania
      @ExcelMacroMania  7 หลายเดือนก่อน

      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