Refresh Google Sheets in Power BI

[Anonymous]

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:

https://developers.google.com/analytics/devguides/reporting/core/v4/limits-quotas

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

[v-yuezhe-msft · Accepted solution]

@Anonymous,

You can use Function.InvokeAfter()  function to set wait time between calls in Power Query.

Alternatively, you can use R script to refresh Google sheets and set Sys.sleep(30) as described in the following similar thread.
https://community.powerbi.com/t5/Desktop/HTTP-429-Error-while-refreshing-data-from-Google-Sheets-using-R/td-p/264416

Regards,
Lydia

[Anonymous]

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

Thankyou v-yuezhe-msft.

[Anonymous]

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]

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.

[Anonymous]

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)