[Excel add-in] Worksheet getRange: An internal error has occurred.

[lcarlier-arm]

Hello, I'm writing an Excel add in in typescript that I'm running from my browser. I'm getting the following error message in the console while running my script.

The code which is being executed is the following

type RowType = (string | string | string | string | string | string)[][];

function clear_sheet(workbook: ExcelScript.Workbook, sheetName: string)
{
  const ticketListSheet = workbook.getWorksheet(sheetName)
  //Clear the whole sheet
  ticketListSheet.getRange().clear()
  ticketListSheet.getRange().getFormat().getFill().clear()
}

function fill_worksheet(workbook: ExcelScript.Workbook, sheetName: string, rows: RowType, fillColor: string)
{
  console.log(`Filling ${rows.length} issues into "${sheetName}" tab`)
  clear_sheet(workbook, sheetName)
  const sheet = workbook.getWorksheet(sheetName)
  if (rows.length > 0) {
    const range = sheet.getRange("A1").getResizedRange(rows.length - 1, rows[0].length - 1); //!!!!!!!!! THIS IS LINE 132 !!!!!!!! 
    range.setValues(rows);
    range.getFormat().getFill().setColor(fillColor);
  }
}

The data are coming from a webserver which I'm querying via a REST api using fetch. In the particular example of crash, the rows variable contains 43 entries.

After that error message in the console, the worksheet seems to work completely. I cannot re-run the script anymore as it timeouts. Also, the spreadsheet becomes completely unresponsive and I'm getting this error message.

From the network traces I have spotted a couple queries that seems to fail And those error message in the console

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: Office on the web
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: __
• Operating System: Linux
• Browser (if using Office on the web): Firefox

Expected behavior

No crash

Current behavior

Crashing. It is unusable.

Steps to reproduce

My example contains sensitive data so I can't share it. If you can help me try some things to reproduce I would be happy to help.

Link to live example(s)

None

Provide additional details

None

Context

Just running my script.

Useful logs

The logs are part of the description

Thank you for taking the time to report an issue. Our triage team will respond to you in less than 72 hours. Normally, response time is <10 hours Monday through Friday. We do not triage on weekends.

[alison-mk]

Hi @lcarlier-arm, thank you for submitting this issue! I've assigned it to the engineering team who will be able to provide you with more information or help resolve the issue.

Cheers, Alison

[jipinshi]

@lcarlier-arm, thanks for reaching us. can you help verify which step introduce the crash? Like if you use a new worksheet to do the insert, will it cause the crash? And which kind of data will trigger the crash? For example, you can use data which is not real to help reproduce the issue with scriptlab. Need to scope down the crash line first, and better to have simple data and simple step to produce the issue. Let me know if you still need more info. When I figured out the issue line, may can provide workaround for you first.

[lcarlier-arm]

Hello @jipinshi, I have had a little luck and found a "solution". I actually found that the spreadsheet was crashing when I was copy pasting data in the place where my script was suppose to write so I concluded that my script isn't to blame here.

I have actually 3 cells that looks like this in my spreadsheet

=IF(INDIRECT("'"&A$29&"'"&"!$B$2")<>"", SORT(UNIQUE(FILTER(INDIRECT("'"&A$29&"'"&"!$B$2:$B$999"),INDIRECT("'"&A$29&"'"&"!$B$2:$B$999")<>""))), TRIM(" "))

When I copy pasted more than 40 rows in the "indirected sheet", everything would hang. Things would also hangs when I was manually adding the 41st row by typing the data.

Because of that I concluded that probably the cell referencing the cells where I was updating my data where causing problems. That's how I spotted my formula above.

I then updated my formula by changing $B$2:$B$999 to $B$2:$B$100 and things are hanging anymore. I would guess the range is actually too high. So with the following formula

=IF(INDIRECT("'"&A$29&"'"&"!$B$2")<>"", SORT(UNIQUE(FILTER(INDIRECT("'"&A$29&"'"&"!$B$2:$B$100"),INDIRECT("'"&A$29&"'"&"!$B$2:$B$100")<>""))), TRIM(" "))

everything is working good.

Personally I do not need any help anymore as my crash has disappeared but it might be worth to investigate on your side?

Cheers,

Laurent

[jipinshi]

@lcarlier-arm, great to hear you have found the workaround. If need further investigation from engineer team, may still need your help on the exact repro step. Like the original data in the workbook, and the example repro step. I tried to give some random data and try the above formula and cannot repro the crash, seems need some specific condition like the data in these referenced cells.

[lcarlier-arm]

@jipinshi Maybe it is coming from the fact that the cells filled in by the above formula are yet again referenced by another cell in another page? Here is the formula I'm using

=IF(SORT('Data View'!A3:K26,$L$11)<>"",SORT('Data View'!A3:K26,$L$11),"")

So in my specific case, I had the above formula in cell A3 of the Data View sheet.

Also I got that repeated 3 times in by spreadsheet.

I.e. 3 times the formula above in different cells of 1 sheet

and 3 times the formula in this post in different cell.

[jipinshi]

@lcarlier-arm Still with no luck to repro the issue. If there is some simple repro steps with no DataView, and share the whole gist with scriptlab may can help to investigate more.

[lcarlier-arm]

Hi @jipinshi ,

I was trying to strip down the issue myself but I couldn't reproduce the issue in a stripped version.

I have actually try to revert my formula to its old value i.e. using range from B2 to B999 and now it is surprisingly working on the original sheet that was crashing before..

I'm not sure exactly how I could be of any help.

Kind regards,

Laurent

[jipinshi]

@lcarlier-arm , thanks for your help on the simple the repro steps. So let's close this first. If it ever can repro again, reactive this one or create a new.