Error 429 "Too many requests" when refreshing a dataflow that has some google sheet files

[SPIND]

HI community,

I have been searching for information both on the internet and in the Power BI community without success.

Due to company requirements, I must create a project management report in Power Bi using Google Sheets to collect the data.

To put it in context, i have a pro license, the company has 3 headquarters, each with different departments. Standardized google sheet  templates have been created (Projects, Tasks, Invoices etc). Each department of each office will use a Google Sheet file to put information regarding projects, tasks etc.

My idea is to create a  Project dataflow for office 1, that collects all the equivalent spreadsheets from all the departments in the office and repeat the process with a task or invoice dataflow for office 1. 

Currently I have created the task dataflow for an office to which I have connected with 5 Google Sheet files simulating 5 departments. At the moment those files barely have data.

I update the dataflow through Power Automate. The thing is that the 429 "Too Many requests" error appears frequently and randomly. Sometimes there are no problems and other times there are. There are no other dataflows updating at the same time or anything. I have been carrying this error for months.

I am worried that the problem will get worse as I incorporate more dataflows with files from Google Sheet. I know that when scheduling a refresh of dataflows they should be spaced out as much as possible but I have a problem having only 1 dataflow with 5 google sheet files. I have even seen errors with a dataflow with 1 or 2 google sheet files.

The solution provided in a microsoft ticket was "Reduce the load/data to avoid too many request error" and to contact Google support. To be honest that reply is not helpful because i have seen that error  with a dataflow with 1 or 2 google sheet files in it and nothing else getting refreshed at that time.

Any idea how to avoid error 429 "Too many requests". Not sure if i wont get that error if i apply the rule 1 dataflow = 1 google sheet file, although this wont be very useful in any case either.

Thank you for your time.

[LRouver]

Mesmo problema por aqui!

Uma tabela muito pequena, 160 linhas, 3 colunas. Não há mais ninguém inserindo dados nela, e também não há qualquer app de automação conectado a essa plhanilha do google, mesmo assim fica toda hora dando o erro 429 "Too many requests".

Me parece que não deveria ser aceito como resposta a opção que falaram aqui, pois o problema não foi resolvido.

[LRouver]

Mesmo problema aqui.
Alguém conseguiu descobrir uma solução?

[mazwro]

For me, it was that I had google sheet open, and couldn't refresh it. When I cllosed it, the refresh worked

[lbendlin · Accepted solution]

The solution provided in a microsoft ticket was "Reduce the load/data to avoid too many request error" and to contact Google support. To be honest that reply is not helpful

You may not like it but that reply is accurate. The 429 red card is handed to you by Google, not by Power Automate.

Any idea how to avoid error 429 "Too many requests"

Refactor your workflow to reduce the number of requests against that source system.

[JustSayJoe]

There seems to be something else at play here other than reducing the number of requests.

I have a single tab Google Sheet with roughly 40 rows and 10 columns. VERY small data load. I have it connected to my Power BI desktop via import. When I attempt to right-click on the data source and refresh it manually by itself (no other refreshes or requests but this), it CONSTANTLY returns the "429  too many requests" error. Sometimes it takes me 3 or 4 attempts before it will refresh the source.

[James-PBI]

This happens to me too. By any chance, are there often other collaborators in this spreadsheet making changes or adding data? Or do you have any Google App Scripts running for this sheet? In my case, I think these other factors are contributing to the Google quota limit.

[JustSayJoe]

Hi James.

There are no collaborators. I'm the only person with access to the Sheet. There are no Google App Scripts running either.

[SPIND]

I´m going to try to combine some google sheet files into one. Hopefully it will reduce the requests.

[James-PBI]

I'm having the same issue. Did combining the Google sheets into one file help?

[SPIND]

It did.

[Anonymous]

In one sheet within a google sheet? I experience the same, but this isn't really a solution, but I'll try to see if it can work for me... I have several google sheets for different Power BI reports and the refresh now fails more often than not..

[James-PBI]

Please report back your findings! Would love to know if this is a possible workaround.