1

We have created an Excel addin using office js API. In our extension we have a module where we fetch data from excel sheet and then process it. The implementation works fine upto 12000 rows after which the API returns 500 internal server error. I tried finding out the limitation of the excel range API but couldn't find any appropriate links. Please find the reference code below.

await Excel.run(async context => {
          let sheet;
          sheet = context.workbook.worksheets.getItem('Sheet 1');
          let range = sheet.getRange('A1:O12000');
  ​        range.load("values");
          await context.sync();
          console.log("Excel data", range.values);
}

Can anyone let me know in case of any limitation. I do have a work arround idea, which is to split the range into chunks of 12000 rows and load the data. But still just want to make sure if the API has a data size limitation. Thank you in advance.

When I add a try catch to my code I see that I get the following error in my catch block. ( When the range is A1:O13000 )

await Excel.run(async context => {
          try {
          let sheet;
          sheet = context.workbook.worksheets.getItem('Sheet 1');
          let range = sheet.getRange('A1:O13000');
  ​        range.load("values");
          await context.sync();
          console.log("Excel data", range.values);
          } catch (e){
             console.error(e);
          }

}

The error I get enter image description here

naveen ashok
  • 311
  • 1
  • 16
  • I just have a tried on your sample code, I cannot repro this issue. it works fine on my side. here is my gist, i tried it in Script Lab https://gist.github.com/lumine2008/633f3967d0a55a7f2993c81c472e88a8 – Raymond Lu Apr 13 '20 at 08:40
  • It actually works in desktop but it dosen't in web. I did use a different range which I have updated in the question now – naveen ashok Apr 13 '20 at 10:38
  • i tried my gist in excel online, it does work. what's the content in the range? does A1:O12000 contains a lot of text? – Raymond Lu Apr 13 '20 at 14:28
  • All the cells have 1 or 2 words max. Excel online works upto A1:O12000. When we increase a 1000 more then it reaches the API size limit I guess. – naveen ashok Apr 13 '20 at 14:41
  • are you saying, it will throw error when you change your code to let range = sheet.getRange("A1:O13000"); // A1:O12000 -> A1:O13000? – Raymond Lu Apr 13 '20 at 14:58
  • Could you please also share the screenshot of the error message? thanks – Raymond Lu Apr 13 '20 at 14:58
  • I have added the screen shot. Please let me know if it helps? Thanks – naveen ashok Apr 14 '20 at 06:19
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/211624/discussion-between-naveen-ashok-and-raymond-lu). – naveen ashok Apr 14 '20 at 11:42

1 Answers1

2

Thanks naveen for your question. we have 2 limitations: a) Excel Online has payload size limit which is 5MB, b)A range is limited to 5 million cells.

According on your sample, it is less than these limits. I also tried your code with Script Lab, it runs OK. here is my gist: https://gist.github.com/lumine2008/633f3967d0a55a7f2993c81c472e88a8

The document can be found at https://learn.microsoft.com/en-us/office/dev/add-ins/develop/common-coding-issues#excel-range-limits

Raymond Lu
  • 2,178
  • 1
  • 6
  • 19
  • Thanks for letting me know the limitation. Could you please let me know how to check if the range exceeds 5mb. – naveen ashok Apr 13 '20 at 10:53
  • Could you try to use this tool to check payload size and confirm it meets the 5M limitation? https://learn.microsoft.com/zh-cn/office/dev/add-ins/testing/debug-add-ins-using-f12-developer-tools-on-windows-10 – Raymond Lu Apr 14 '20 at 08:09