Unanswered

Office JS error: Line 4: PivotTable refresh

(1) Share

Report

Posted on by skanord

Hello,

I am trying to build a flow to automatically run and Excel online script, intended to refresh an external data connection in my online excel file.

I have had trouble getting this to work, and now i have run into this error:

We were unable to run the script. Please try again.
Office JS error: Line 4: PivotTable refresh: An internal error occurred while processing the request.
clientRequestId: e58d8d7b-f878-4df3-b35d-8f2e40969dff

The excel online script looks like this:

function main(workbook: ExcelScript.Workbook) {
	let varekategori = workbook.getPivotTable("varekategori");
	// Refresh varekategori
	varekategori.refresh();
	let tabel4 = workbook.getTable("Tabel4");
	// Insert row at index 0 into table tabel4
	tabel4.addRow(0);
	let selectedSheet = workbook.getWorksheet("Varekategori Power BI link");
	// Paste to table tabel4 cell in row 0 on column Antal linjer from range D2:E2 on selectedSheet
	tabel4.getColumn("Antal linjer ").getRangeBetweenHeaderAndTotal().getRow(0).copyFrom(selectedSheet.getRange("D2:E2"), ExcelScript.RangeCopyType.values, false, false);
}

The script runs without issue if i run it in Excel.

In general I have had a lot of trouble getting power automate to refresh an external data connection in excel online, so if anyone has other suggestions on how to overcome that issue they are very welcome too.

Thanks in advance!

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (0)

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi! Have you found a solution? I'm about to give up refreshing workbooks in SharePoint using Excel scripts...

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

I have fixed my issue by refreshing all connections at the same time (not applicable to the original post, but it may help someone). Then I've refreshed all pivot tables:
workbook.refreshAllDataConnections();
workbook.refreshAllPivotTables();
Also, I've removed all options for manual refresh in Excel (including Refresh this connection on Refresh All, which is counterintuitive, since I'm triggering the refresh all connections method!)

Was this reply helpful? Yes No
Ari-elli 2 on at

Like (0)

Report
Copy link

Link copied!

Did this only work after removing the option for a manual refresh? I am stuck trying to get Power Automate to run an Office Script that refreshes a series of pivot tables.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Correct. It only worked when I cleared of the manual refresh options. I hope it works for you too. Good luck!

Was this reply helpful? Yes No