I have a query within spreadsheet B, that is held within a private channel in a MS Team. The query gets data from spreadsheet A that is held in another channel (thanks Amanda for your Get Data From SharePoint video!). Spreadsheet A has lots of sensitive data that I can only have access to, spreadsheet B is accessible to 6 members in my team and strips out the sensitive data through PQ and thus only presents non sensitive data.
Therefore, I'm the only one who can refresh the query in spreadsheet B, as my team members don't have access to spreadsheet A. I'm looking for spreadsheet B to be updated on a daily basis without me having to go in and manually having to refresh myself.
I was thinking of using 'Automate' within Excel Online and then running the script through PowerAutomate, however this does not record refreshing of a PQ. It appears you cannot use PowerAutomate to run a Macro either.
Are there any other options to automate this task?
Hi Peter,
Excel Online cannot refresh queries yet. Your best option at the moment is to write some VBA that opens the file periodically and triggers the refresh before saving and closing.
Mynda
Mynda
Sorry but if we use Excel on the Desktop but via SharePoint folders does the same apply?
Thanks
Justine
It doesn't matter what the location is of the source data, Excel Online has no refresh functionality, so the only option is to refresh from the desktop version of Excel.
