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

Question

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

Pratap Rajput 30

Please refer the below script - when I run it on Excel Web - I get the error mentioned below.

Whereas I run the same script in Excel Desktop App - it works fine.

I have started getting this issue 28-Sep-2023. Earlier the script was working fine.

I request your quick support as my lot of work is on hold due to this & if Microsoft does not support us then I believe we should avoid using Microsoft Prodcuts due to such issue and no support.

function main(workbook: ExcelScript.Workbook) {
  let EmpList = workbook.getWorksheet("EmpList");
  let lastrowEmplist = EmpList.getTable("EmpList").getRowCount() + 3;
  EmpList.getRange("E4:H" + lastrowEmplist).copyFrom(EmpList.getRange("E2:H2"), ExcelScript.RangeCopyType.all, false, false);
}

Error:

Line 3: Workbook getWorksheet: An internal error has occurred.

Logan Doyle 5 Reputation points

2023-10-03T09:24:02.0533333+00:00
We have the same issue with all our scripts - working fine on desktop (updated to the Sept 28th release version 2309 to match the web version) but on the web version of excel it fails with internal error - appears to occur on any line referencing a workbook, ie:

let selectedSheet = workbook.getActiveWorksheet();

resulting in the same internal error
This is naturally preventing our flows from running with 400 and 502 series errors (ie bad gateway or session not found)
Pratap Rajput 30 Reputation points

2023-10-03T09:40:06.9266667+00:00

@Kai Yao @Yanli

@Yanli Jiang - MSFT

@Anonymous

@SokiGuo-MSFT

Issue reported by us is after some update from Microsoft Side, can you please have a look at it and push it to concerned to roll back the new update or fix it on top priority.

This is an issue with Office Script and must be affecting billions of users worldwide.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-03T17:03:51.29+00:00

Just 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?
Pirtz, Allison 0 Reputation points

2023-10-03T22:00:51.0966667+00:00

@Yutao Huang - MSFT I am getting this same error; it is happening in both places - within Code Editor in Excel Web and when running it from Power Automate.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-03T23:16:08.9666667+00:00

It seems I wasn't able to repro the issue on our side. I was able to run a test script successfully on both Web and Power Automate. Could any of you provide a minimum piece of script that could repro the issue (ideally not relying on specific content in the workbook)? Also would you mind sharing which region you are in? E.g, Europe, America, Asia, etc. Thanks!
Luke Eastman 0 Reputation points

2023-10-04T00:11:10.14+00:00
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();
Pratap Rajput 30 Reputation points

2023-10-04T04:19:24.0633333+00:00

@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.
Pratap Rajput 30 Reputation points

2023-10-04T04:23:06.9866667+00:00
@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.
Pratap Rajput 30 Reputation points

2023-10-04T04:27:24.1+00:00

@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.
Pratap Rajput 30 Reputation points

2023-10-04T04:28:59.9233333+00:00

@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.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-04T05:27:52.3533333+00:00
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:
Pratap Rajput 30 Reputation points

2023-10-04T07:26:16.2833333+00:00

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.
Inge Thijsen-Kuppen 5 Reputation points

2023-10-04T09:41:57.58+00:00

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.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-04T17:40:43.9866667+00:00

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!
Pratap Rajput 30 Reputation points

2023-10-05T06:14:23.5666667+00:00

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.
Pratap Rajput 30 Reputation points

2023-10-05T06:17:42.1733333+00:00

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.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-06T02:47:57.5333333+00:00

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!
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-06T17:57:29.3466667+00:00

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!
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-06T18:23:18.4+00:00

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!
Pratap Rajput 30 Reputation points

2023-10-07T03:34:33.24+00:00

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.
Inge Thijsen-Kuppen 5 Reputation points

2023-10-07T07:14:39.91+00:00

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.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-07T17:42:01.39+00:00

@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).
Inge Thijsen-Kuppen 5 Reputation points

2023-10-09T08:20:43.2833333+00:00

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.....
Pratap Rajput 30 Reputation points

2023-10-09T08:30:52.0533333+00:00

unable to conclude anything from your reply - request your further inputs/clarity on your response.
Inge Thijsen-Kuppen 5 Reputation points

2023-10-09T12:31:23.2733333+00:00

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*
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-09T16:10:38.9+00:00
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.

Bryan Stewart 0

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());

Inge Thijsen-Kuppen 5 Reputation points

2023-10-09T20:58:02.88+00:00

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.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-10T16:39:50.0666667+00:00

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!
Bryan Stewart 0 Reputation points

2023-10-10T18:09:28.9966667+00:00

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?
Inge Thijsen-Kuppen 5 Reputation points

2023-10-10T18:37:58.9433333+00:00

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
Pratap Rajput 30 Reputation points

2023-10-13T04:21:18.11+00:00

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

2023-10-13T04:36:06.8566667+00:00

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?
Inge Thijsen-Kuppen 5 Reputation points

2023-10-13T07:06:48.59+00:00

@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.
Bryan Stewart 0 Reputation points

2023-10-13T19:47:57.03+00:00
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);
Inge Thijsen-Kuppen 5 Reputation points

2023-10-16T07:25:42.2866667+00:00

@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.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-17T07:30:27.16+00:00

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.
Pratap Rajput 30 Reputation points

2023-10-19T07:01:52.45+00:00

Error Screenshot
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-19T13:45:45.38+00:00

@Pratap Rajput Please try closing the workbook, wait for ~10 minutes, open it again, and see if you are still experiencing the problem. Sometimes there might be some caching issues. If the issue still exists, can you share your session ID and I can take a look at the logs? Thanks!

1 answer

Your answer

Logan Doyle 5 Reputation points

2023-10-03T09:24:02.0533333+00:00

We have the same issue with all our scripts - working fine on desktop (updated to the Sept 28th release version 2309 to match the web version) but on the web version of excel it fails with internal error - appears to occur on any line referencing a workbook, ie:

let selectedSheet = workbook.getActiveWorksheet();

resulting in the same internal error
This is naturally preventing our flows from running with 400 and 502 series errors (ie bad gateway or session not found)
Pratap Rajput 30 Reputation points

2023-10-03T09:40:06.9266667+00:00

@Kai Yao @Yanli

@Yanli Jiang - MSFT

@Anonymous

@SokiGuo-MSFT

Issue reported by us is after some update from Microsoft Side, can you please have a look at it and push it to concerned to roll back the new update or fix it on top priority.

This is an issue with Office Script and must be affecting billions of users worldwide.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-03T17:03:51.29+00:00

Just 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?
Pirtz, Allison 0 Reputation points

2023-10-03T22:00:51.0966667+00:00

@Yutao Huang - MSFT I am getting this same error; it is happening in both places - within Code Editor in Excel Web and when running it from Power Automate.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-03T23:16:08.9666667+00:00

It seems I wasn't able to repro the issue on our side. I was able to run a test script successfully on both Web and Power Automate. Could any of you provide a minimum piece of script that could repro the issue (ideally not relying on specific content in the workbook)? Also would you mind sharing which region you are in? E.g, Europe, America, Asia, etc. Thanks!
Luke Eastman 0 Reputation points

2023-10-04T00:11:10.14+00:00

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();
Pratap Rajput 30 Reputation points

2023-10-04T04:19:24.0633333+00:00

@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.
Pratap Rajput 30 Reputation points

2023-10-04T04:23:06.9866667+00:00

@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.
Pratap Rajput 30 Reputation points

2023-10-04T04:27:24.1+00:00

@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.
Pratap Rajput 30 Reputation points

2023-10-04T04:28:59.9233333+00:00

@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.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-04T05:27:52.3533333+00:00

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:
Pratap Rajput 30 Reputation points

2023-10-04T07:26:16.2833333+00:00

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.
Inge Thijsen-Kuppen 5 Reputation points

2023-10-04T09:41:57.58+00:00

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.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-04T17:40:43.9866667+00:00

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!
Pratap Rajput 30 Reputation points

2023-10-05T06:14:23.5666667+00:00

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.
Pratap Rajput 30 Reputation points

2023-10-05T06:17:42.1733333+00:00

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.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-06T02:47:57.5333333+00:00

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!
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-06T17:57:29.3466667+00:00

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!
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-06T18:23:18.4+00:00

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!
Pratap Rajput 30 Reputation points

2023-10-07T03:34:33.24+00:00

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.
Inge Thijsen-Kuppen 5 Reputation points

2023-10-07T07:14:39.91+00:00

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.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-07T17:42:01.39+00:00

@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).
Inge Thijsen-Kuppen 5 Reputation points

2023-10-09T08:20:43.2833333+00:00

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.....
Pratap Rajput 30 Reputation points

2023-10-09T08:30:52.0533333+00:00

unable to conclude anything from your reply - request your further inputs/clarity on your response.
Inge Thijsen-Kuppen 5 Reputation points

2023-10-09T12:31:23.2733333+00:00

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*
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-09T16:10:38.9+00:00

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.
Bryan Stewart 0 Reputation points

2023-10-09T19:01:40.0933333+00:00

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());
Inge Thijsen-Kuppen 5 Reputation points

2023-10-09T20:58:02.88+00:00

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.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-10T16:39:50.0666667+00:00

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!
Bryan Stewart 0 Reputation points

2023-10-10T18:09:28.9966667+00:00

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?
Inge Thijsen-Kuppen 5 Reputation points

2023-10-10T18:37:58.9433333+00:00

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
Pratap Rajput 30 Reputation points

2023-10-13T04:21:18.11+00:00

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

2023-10-13T04:36:06.8566667+00:00

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?
Inge Thijsen-Kuppen 5 Reputation points

2023-10-13T07:06:48.59+00:00

@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.
Bryan Stewart 0 Reputation points

2023-10-13T19:47:57.03+00:00

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);
Inge Thijsen-Kuppen 5 Reputation points

2023-10-16T07:25:42.2866667+00:00

@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.
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-17T07:30:27.16+00:00

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.
Pratap Rajput 30 Reputation points

2023-10-19T07:01:52.45+00:00

Error Screenshot
Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-19T13:45:45.38+00:00

@Pratap Rajput Please try closing the workbook, wait for ~10 minutes, open it again, and see if you are still experiencing the problem. Sometimes there might be some caching issues. If the issue still exists, can you share your session ID and I can take a look at the logs? Thanks!

Answer 1

Yutao Huang - MSFT 701 Microsoft Employee

UPDATE: The fix is now 100% in production. Please help to validate your code that has the range.copyFrom() API with ExcelScript.RangeCopyType.values as copyType. Thanks again for your patience!

Inge Thijsen-Kuppen 5 Reputation points

2023-10-19T08:10:10.38+00:00

Hi @Yutao Huang - MSFT ,

Thank you the update in this topic.
I've execucted the script piece that used to throw the error; it doesn't throw an error anymore.

I ran the flow which is excuting more copyFrom with RangeCopyType.Values statements and that looks promissing as well.

Have asked my Product Owner to verify the generated output... (Better safe than sorry)

My excel build version states: 16.0.17016.42303 (O365 excel > File > About)
Paul Relf-Davies 0 Reputation points

2023-11-28T17:20:46.8333333+00:00

I've come across this issue around 6 weeks after it was fixed. I think I've found a potentially related problem.

My script uses the Range.copyFrom() method, but using the 'RangeCopyType.all' flag rather than 'RangeCopyType.values'.

The next Worksheet.getRange() or Worksheet.getRangeByIndexes() call triggers a 'Worksheet getRange: An internal error has occurred.' error.

It seems the actual parameters passed to either of those methods don't matter.

Finally, if I call console.log("") after the Range.copyFrom() call, the error isn't triggered, but obviously, there is a significant performance hit.

Share via

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

1 answer

Your answer