Google sheet

[Kojo Robertson]

Please I have this code to pull data from Google Sheet in Excel, however it only import (pulls) 100 rows from the google sheet while the data is about 3000 rows.
So is there anyway to pull 3000 rows of data from google sheet?

>> Use Code -tags <<
​

Sub Basic_Web_Query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://docs.google.com/spreadsheets/d/1eYp2ZvlOAWCTK3inTnz84wxAUZX502FdPRpfgeQXehc/edit?fbclid=IwAR3ox5aHjGmEiQMYjXJusRNVNgN5BhzGA4bf3jEdItTbR7NmZFY2_D1gQ6Y#gid=0", Destination:=Range("$A$1"))
.Name = "q?s=goog_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1,2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Chihiro]

You can't use legacy get from web to load entire date. It limits data query to 100 rows. If you have access to PowerQuery, you can get around this limitation (by querying URL used for downloading file as csv/xlsx file).

If you don't... then best bet is to use Google Sheet API. You can find some sample codes in link.
http://ramblings.mcpher.com/Home/excelquirks/exceldocsintegration/excelsheetsv4

 

[Kojo Robertson]

Thanks

 

[merakli]

Sub GetData()

    qry = Application.EncodeURL("SELECT A, C, G, B")
    myURL = "https://docs.google.com/spreadsheets/d/1ZCrQlBjfMmO9636npMth9ErDr4kSnK8LhKb7JXS2KxU/gviz/tq?tqx=out:csv&sheet=1&tq=" & qry

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myURL, Destination:=Range("$A$1"))
        .Name = "myTable"
        .TextFilePlatform = 65001
        .SaveData = False
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .Refresh BackgroundQuery:=False
    End With

End Sub

Sub DeleteData()
    ActiveSheet.UsedRange.Cells.Clear
End Sub