Office Script - getRange / getWorksheet etc. - Error An internal error has occurred

I'm having the same issue with my scripts, getting the same internal error from 28 September. This is on Excel Web and in Power Automate.

let selectedSheet = workbook.getActiveWorksheet();

@Yutao Huang - MSFT

ust to confirm - are you experiencing the issues when running the script from inside the Office Scripts "Code Editor" task pane on Excel Web? Or when running it in Power Automate?

My Response:

I am facing the issue in Excel Web. But when I am trying to run the same script in Desktop Excel - It works fine. And the same script was working perfectly until 28 Sep 2023 on Excel Web.

• @Yutao Huang - MSFT From Asia.

Issue observed in following scenario

• When the range is increased beyond 2000 rows.

And/Or

• And maybe when the getRange / getWorksheet is after approx 20th line of code.

And

I am facing the issue in Excel Web. But when I am trying to run the same script in Desktop Excel - It works fine. And the same script was working perfectly until 28 Sep 2023 on Excel Web.

@Luke Eastman

let selectedSheet = workbook.getActiveWorksheet();

above will not work in powerautoamte you will have to use workbook.getWorksheet("SheetName");

However, the issue is even workbook.getWorksheet("SheetName") is not working now and throwing error on web version when large range is used.

@Yutao Huang - MSFT == And yes for reverting back. Was feeling so helpless as was not getting any response from Microsoft. Even after being a corporate user.

Thanks for providing the details!

@Pratap Rajput Will the issue still repro if your script only has one line of code in the main function:

function main(workbook: ExcelScript.Workbook) {
  workbook.getWorksheet("Sheet1");  
}

Also when it fails running in Power Automate, can you grab a clientRequestId from the failed Run script action and share here? I can look further and see there are any issues in our service. Thanks!

Here is how you can find the clientRequestId:

Pls find the ID (when running through PowerAutomate) "clientRequestId": "fda100b2-dec8-440e-9d23-9f7936770570"

And following is the error when trying to run script manually (tried today)

On First Run get following error

If after above error if try to re-run the same script getting following error - you will observe that the error line is now changed to 2 - whereas I am running the same script after above error.

if after error first error I am trying to navigate to another sheet - it gives following error.

Is there any progress on this issue???

I am experiencing same issues with a o365 power automate flow firing a bunch of excelscripts, since 29th (prior triggered flow ran on 25th; and that one and prior runs have been working fine).

At a given point the code just stops, where it used to run fine; Basis data is unchanged.

Traced it back when running in o365 excel interactively where statement on line 951 almost always fails now:

prior to this failing statement other Range related statements still work and are referencing the same sheet.

Thanks everyone for sharing the details! We have escalated this issue and are actively looking into it. I'll keep you all posted for any updates. Thanks for your patience!

One suggestion here

I believe this is had popped up after the new release of 28 Sep 2023.

Ideally, we should have an option to switch back to previous version of the script / we should be able specify which version to be used in the script itself.

OR

there should be something like pre-release where in if user is facing the issue in the new version he can always enable flag in the script to use the last stable version.

Bcos in current scenario there is something not working and as we don't have option to select which version to use, we are all stuck till the time issue is fixed.

One suggestion here

Ideally we should an option to select which version of the script we want to use either in the script itself (i.e. script level) or in code-editor window.

So that in such scenarios we don't get stuck and things keep going on smoothly.

Bcos currently there is not such feature we all are stuck.

FYI, we have a local repro now, and the team is actively working on it as the highest priority to locate the root cause and look for a fix/mitigation. Thanks for your patience!

Hi all, just wanted to confirm if everyone experiencing the issue are using the range.copyFrom() API in their code. We are now suspecting that could be the culprit based on our troubleshooting so far. Thanks!

Hi all, it seems the range.copyFrom() API might be the culprit! While we are working on a fix to address that. If you happen to be using this API in your code, please see if you can work around by using things like range.getValues() then range.setValues(). Thanks!

Had tried that. There is performance issue and when copying large data - the request times out.

Further, in case of copyfrom we can copy as formula as well, which to the best of my understanding is not possible using getvalue or getvalues.

Uh, sorry? That is not a work-around.

I have 18 active copyfrom lines in my code.

They vary between:
RangeCopyType.values, RangeCopyType.formats, RangeCopyType.formulas, RangeCopyType.All

from my previous post:
i can confirm following: the line after the line that appears to throw the error is actually a copyfrom statement

getValues() > setValues()
getFormulas() > setFormulas() (<< @Pratap Rajput : this could be your temporary work-around)

I haven't found a get/set variant for the formats, which makes me stuck at the copytype All as well.
so @Yutao Huang - MSFT what is microsoft suggesting to use here.

@Inge Thijsen-Kuppen , @Pratap Rajput

Based on our investigation so far, the issue seems to be related to copy/paste of formulas as values. So the other copying/pasting scenarios (RangeCopyType.formats, RangeCopyType.formulas, and RangeCopyType.all) might still work.

I tried these scenarios on my end, and they all seem to work. Would you want to give it a try?

Regarding time out in setValues, a temporary workaround could be to split it into smaller batches (e.g., 5,000 rows per batch). Also please note you may need to do console.log("...") after each batch to force flushing the queued data. (Reference).

Will give it a go, as skipBlanks and Transpose are both false on all my copyfrom Values statements

Please note that the optional skipBlanks and transpose have to be handled by the users work-around-code in case they have provided true at either of them.....

unable to conclude anything from your reply - request your further inputs/clarity on your response.

Will give it a go, as skipBlanks and Transpose are both false on all my copyfrom Values statements

Please note that the optional skipBlanks and transpose have to be handled by the users work-around-code in case they have provided true at either of them.....
*
Unfortunately i have provided this as answer, which should have been a comment instead.
Unable to revoke my answer*

Range.copyFrom(
            sourceRange: Range | RangeAreas | string,
            copyType?: RangeCopyType,
            skipBlanks?: boolean,
            transpose?: boolean
        ): void;

To clarify, based on our current investigation, at this moment Range.copyFrom() only has issues when copyType is ExcelScript.RangeCopyType.values. The temporary workaround is to use range.getValues() and range.setValues() plus some batching (e.g., 5,000 rows per batch, with console.log("...") after each batch to flush queued data operations, refer to this for more details and an example).

The other copyTypes (e.g., ExcelScript.RangeCopyType.all, ExcelScript.RangeCopyType.formats, ExcelScript.RangeCopyType.formulas, etc.) should still work as expected.

We have identified the root cause and are now working on a potential fix. Will keep you all posted. Thanks again for your patience.

I'm getting the following errors for a script I modified on Saturday.

Line 43: RangeView getNumberFormat: An internal error has occurred.

Line 45: Worksheet getRangeByIndexes: The request payload size has exceeded the limit. Please refer to the documentation: "https://docs.microsoft.com/office/dev/add-ins/concepts/resource-limits-and-performance-optimization#excel-add-ins".


The section of the script throwing the errors is below. The strange thing is that it works without any issues on a previous Script I have saved. The number of rows is less than 40,000 so I don't understand why I'm getting a payload size error and why it only happens for new scripts using the code below yet works for scripts I have saved before.

I wouldn't be using the script below if skip blanks actually worked when using copyfrom.


let visibleTableRange = sheetx.getRange("M7:T7").getExtendedRange(ExcelScript.KeyboardDirection.down).getVisibleView();
	let visibleTableRangeValues = visibleTableRange.getValues();
	let visibleTableRangeFormats = visibleTableRange.getNumberFormat();
  let sheetToPaste = workbook.getWorksheet("Statedata")
  let pastedValues = sheetToPaste.getRangeByIndexes(0, 0,
    visibleTableRange.getRowCount(), visibleTableRange.getColumnCount());

Hi Bryan,

from my experience script error line is off.

it’ll state the error to be thrown on line x, but actually it’s the code-line after that.

i am 99% sure you have a copyfrom with copytype values on that line.

UPDATE: We made a fix and it has been rolling out. ETA 2~3 days reaching all production environments if everything works smoothly. Thanks again for your patience!

Hi Inge,

Thanks for the tip. I did have one line with a copytype values at the end of the script (line 84). However, removing it didn't help unfortunately. I'm not sure if the fix Yutao mentioned will be rolling out will solve the error I'm getting since it's not a copytype values problem.

@Yutao Huang Did the team find any issues with getNumberFormat?

Is there any way I can be informed roll out has reached my end??? I saved one of the flow-Id’s if that is helpfull, but don’t have it at hand at the moment

@Yutao Huang - MSFT -- still getting the same error - pls confirm when we should try to run the script.

UPDATE: While rolling out our initial fix, we identified a potential issue and had to back it out and made another fix. The second fix is rolling out now and ETA 2~3 days reaching all production environments. We sincerely apologize for the delay and appreciate your patience.

@Bryan Stewart at this moment, we are not aware of any issues with getNumberFormat. I guess sometimes it could be the same copyFrom issue somehow impacting an existing workbook session even after you have removed it from code. Have you tried closing the web page, waiting for a while (5~10 minutes) and opening the workbook again?

@Yutao Huang - MSFT :

That is really unfortunate.

I did attempt to handle with suggested work-around, but haven't been able to see it actually working.
the 227k lines that i need to process in max batch-sizes of 5000 (or less) seem to slow down and more per iteration. The down-side to that is that my powerautomate flow will detect API time-out, therefore would need to rework the flow as well.

Note that i have set calc mode to manual prior to starting the loop as well...

The significant delay that will be caused due to this is not feasible.

So I have nothing else left other than to wait for the fix to be deployed.

I found a workaround to only copy the visible range that hasn't thrown any errors and it does use RangeCopyType.values successfully. When I add a line to copy the formats though it includes everything and not just the visible range, so I'll just format after I copy the visible values.

	const source = workbook.getTable("Statetable").getRange().getVisibleView().getRange();
	// Copy the data into the other sheet.
	const output = sheet1.getRange("A1");
	output.copyFrom(source, ExcelScript.RangeCopyType.values);

@Yutao Huang - MSFT : are there any updates on this? is there any way we can see it's deployed (apologies up front: am a n00b at identifying).

I tried to run script just now and it is still failing.... the estimated 3 days have passed as well.

UPDATE: Unfortunately, there have been several unforeseen issues that have slowed down the rollout process. We may still need another 2~3 days on the safe side. We want to assure you that we are doing everything in our power to resolve the issue and push the fix as soon as possible. We apologize for the delay and thank you for your patience and understanding.

@Inge Thijsen-Kuppen Sorry I'm not sure if there is a goody that you can check the deployment status. I'll make sure to update back once the fix is fully rolled out.

Error Screenshot