SOLVED

Autoupdate table that get its data from other sources (Sharepoint list, SQL server etc)

Copper Contributor

I just created a table using this options (sorry its in Spanish =()

pain.png

 

So basically a table from a sharepoint List. I need the created table to then auto update with no input from me. 

 

dd.png

The connection property is set like this. So in theory it should be updating once every minute even if I have the file closed. But nothing is happening :( Should I check another option here or in other place?

Ty for the help :D

5 Replies

@Rickytruco 

It is updated every one minute only if file is opened. If file is closed nothing happens.

@SergeiBaklan hey ty for your reply.
Oh :( And is there no way to make it update witouth opening the file? Then what is the 1st option for "updating in the background"?
best response confirmed by Rickytruco (Copper Contributor)
Solution

@Rickytruco 

Enable Background Refresh means that with Refresh All we do not wait with performing something else while connections are refreshed. Negative impact could be when you have few Power Query connections and PivotTable. The latest could be updated not waiting for queries refresh, thus we may have not updated data in PowerPivot. We need to Refresh All once more or disable background refresh.

To my knowledge to make any refresh we have to open Excel. Workaround could be PowerShell script which opens Excel, Refresh All and closes the file. Potentially another workaround could be combination of Power Automate and Office Script, but so far they Excel for web doesn't refresh external connections.

In any case above is not every minute frequency. I'd keep Refresh on file open plus Refresh on every n minutes. However, every user with that shall have at least R/O access to data source.

Oh :( So there really is no way T_T... I'll explore the PowerShell option but it's so weird that they would design it this way. Anyway thanks again!
1 best response

Accepted Solutions
best response confirmed by Rickytruco (Copper Contributor)
Solution

@Rickytruco 

Enable Background Refresh means that with Refresh All we do not wait with performing something else while connections are refreshed. Negative impact could be when you have few Power Query connections and PivotTable. The latest could be updated not waiting for queries refresh, thus we may have not updated data in PowerPivot. We need to Refresh All once more or disable background refresh.

To my knowledge to make any refresh we have to open Excel. Workaround could be PowerShell script which opens Excel, Refresh All and closes the file. Potentially another workaround could be combination of Power Automate and Office Script, but so far they Excel for web doesn't refresh external connections.

In any case above is not every minute frequency. I'd keep Refresh on file open plus Refresh on every n minutes. However, every user with that shall have at least R/O access to data source.

View solution in original post