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 t...
Need Help
  • 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
    8 years ago

    Function.InvokeAfter()  worked for me, with delay=20sec.

     

    Thankyou v-yuezhe-msft.

    • 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hello All,

         

        I am trying to figure out, how many read calls it takes to read/refresh Google Spreadsheet (70 tabs) in Power BI (1 tab at a time). My quota limit in Google Sheet API is 100 requests in 100 sec per user. Should I try to upgrade my quota limit in Google Sheet API??? or read multiple Tabs in Multiple Read Call??

         

         

        Thanks for your answer,

        Vithi Mittal.