Couldn't load cell value for more than 12000 rows in Excel using Office js Excel API

1
votes

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

1
javascriptoffice-jsjexcelapi
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 gist.github.com/lumine2008/633f3967d0a55a7f2993c81c472e88a8 – Raymond Lu
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
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
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
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

1 Answers

2
votes

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://docs.microsoft.com/en-us/office/dev/add-ins/develop/common-coding-issues#excel-range-limits