none
power query & google drive & google sheets RRS feed

  • Question

  • I have many, many google sheets (exactly the same format) within a single google drive.  I am hoping there is a way to connect to google drive and append all these google sheets for a single dataset.  Does anyone know how to do this?  jzmcnaughton@gmail.com
    Tuesday, December 10, 2019 5:24 AM

Answers

  • Hi

    (I do not use Google drive & sheets. Below process tested + success)


    • Set up a query from that table with code:
    let
        Source = Excel.CurrentWorkbook(){[Name="myTable"]}[Content],
        AddedCustom = Table.AddColumn(Source, "GoogleSheetContent", each
            let
                GoogleSource = Excel.Workbook(Web.Contents([URL]), null, true),
                SheetData = GoogleSource{[Item="mySheet",Kind="Sheet"]}[Data],
                PromotedHeaders = Table.PromoteHeaders(SheetData, [PromoteAllScalars=true])
            in
                PromotedHeaders),
        CombinedGoogleSheets = Table.Combine(AddedCustom[GoogleSheetContent])
    in
        CombinedGoogleSheets

    (replace Bold+Italic with your "values")

    1st time you set this up you will have to deal with (I choose Anonymous):

    If this solves your problem please Mark as answer (can help others…). Thanks

    Tuesday, December 10, 2019 9:15 AM