Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Refresh Google Sheets in Power BI

Hello All,

 

Google sheets donot allow access more than 100 requests per 100 sec. I get an error when I try to access a Google Sheet more than 5 times at a time or Refresh more than 5 Sheets at a time in Power BI. Ref. Link Below:

 
As in the Above Link, There is a limit on Google Spreadsheet API access rate: 10 request per user per sec. (max)
I always get the above error every time I refresh Google sheets in Power BI. (429) UNKNOWN
Please suggest How to Refresh a report inPower BI Services which in turn is fetching data from multiple Google Sheets
 
Thanks
 
 
 
Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I tried Sys.Sleep(5) in R script, But each sheet executes reading of Google Sheet and Access to Google API when I Refresh All.

       

      When I try to get ALL sheets at once, gs_download() is executed as many number of times as the number of sheets and the Sys.Sleep(5) is executed as many times as the number of sheets, but the READ begins for ALL sheets at the same time, thus the error...

      Sys.Sleep(30)
library(googlesheets)
suppressMessages(gs_auth(token = "~/person.rds", verbose = FALSE))
temp <- tempfile(fileext = ".xlsx")
gs_download(gs_key("1k20...1xds"), to=temp, overwrite=TRUE)
require(xlsx)
x1 <- read.xlsx(temp, sheet = 1)




      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hello All,

         

        I am authenticating and dowloading Google sheets in my Power BI Desktop. I am using Function.InvokeLater(..20 sec..) between two sheetIDs download in the same Google Sheet. But right now Each time I download new Google (Individual) sheetID I generate a new Access Token. Should I save Access Tokens in a DB and use the same Token for sheets downloaded within 1 hour of the Access Token generation?? And after the Token expires and I am trying to access Google Sheet, I would regenerate new Token.

         

        I this case will I get (429) error while accessing consecutive Google Sheets, I will still have to use:  Function.InvokeLater(..20 sec..)

         

        Is there an alternative way. 

         

        Thanks for your help.