none
getDataAsync fails when selection is an entire row in Excel RRS feed

  • Question

  • Hi folks,

    I'm getting an error in the Excel app I'm developing that I'm finding hard to work around.  I want to enable users to be able to select an entire row of their spreadsheet.  I then call getDataAsync on that selection so so that I can get the data that was in the selection, but if fails with "The requested data set is too large".  This used to work earlier in the year during the Office apps beta, but then one day I started getting this error message.  Is there any way I can get around this issue?  Is there a way to limit what I get back from the binding so that it is contained and isn't "too large"?  

    Thanks, Isaac


    Tuesday, October 8, 2013 6:44 PM

Answers

  • Hi Pradip,

    After a bunch more research I was able to work around "The requested data set is too large" issue.  Everywhere in my code where I use getDataAsync to retrieve the data from the binding that was created from the AddFromPromptAsync call, I have to first get the number of columns for the binding using the getByIdAsync.  When someone selects a full row in the excel spreadsheet, I found that it is the equivalent of selecting 16k columns.  So, if I see the column count as really big, I set the columnCount parameter of the getDataAsync to a more reasonable number (like 1000), which limits the data returned by getDataAsync.  If the column count is less than 1000, then I have to set it to the actual column count value.  I found that getDataAsync fails when you use a columnCount value greater than the number of columns actually selected, which was quite annoying. It would have been nice if I could have used it as an upper bound.

    Separately, I think there is a documentation bug on the getDataAsync documentation (http://msdn.microsoft.com/en-us/library/fp161073.aspx).  The parameter "columnCount" is changed to just "colCount" in the remarks and in the sample code.  I'm using "columnCount" in my code and it works.  I'm didn't test the use of "colCount", but the inconsistency in the documentation is confusing and should be fixed.

    I also really wish there was actual documentation on the errors that are returned for the different API calls.  Just encountering a "The requested data set is too large" doesn't tell me how large is "too large".  It would have been nice to know what the limit is without trial and error. 

    Hope this info helps others out there who encounter this issue.

    Cheers, Isaac

    • Marked as answer by isaacn7 Wednesday, November 6, 2013 9:23 PM
    Wednesday, November 6, 2013 9:16 PM

All replies

  • To add a little more context to my issue, here's a snippet of code that is failing when you select a full excel row by clicking on the excel row number, but succeeds when the selection is just part of a row.  

    This used to work in an older version of office.js.  I'm wondering how I can work around this error to enable users to select a row (or multiple rows) and have access to the data.  

    	Office.context.document.bindings.addFromPromptAsync(Office.BindingType.Matrix, { 
    		id: 'GF-HeaderBinding', promptText: 'Select your Spreadsheet Heading Row.' 
    	}, function (asyncResult) {
    		Office.select("bindings#GF-HeaderBinding", function onError(){}).getDataAsync(function (asyncResult) {
    			if (asyncResult.status == Office.AsyncResultStatus.Failed) {
    				write('Action failed. Error: ' + asyncResult.error.message);
    			} else {
    
                                    //Do a bunch of stuff
    			}
    		});
    	});


    Tuesday, October 8, 2013 8:16 PM
  • Which version of Office are you using?

    JavaScript API for Office only support Office 2013:

    Applies to:  apps for Office | Office 2013 | Office 365 | Excel Web App | Exchange 2013 | Outlook 2013 | Outlook Web App | Project Professional 2013 | Word 2013 | Excel 2013 | PowerPoint 2013 | OWA for Devices

    There is a sample from MSDN:

    Office.context.document.getSelectedDataAsync("text", {valueFormat:"unformatted", filterType:"all"}, 
       function (result) {
          if (result.status === "success")      
             var dataValue = result.value; // Get selected data.
             write('Selected data is ' + dataValue);
          else {            
             var err = result.error; 
             write(err.name + ": " + err.message);
          }
       });
    // Function that writes to a div with id='message' on the page.
    function write(message){
        document.getElementById('message').innerText += message; 
    }
    Refer to http://msdn.microsoft.com/en-us/library/office/apps/fp161076.aspx


    Thursday, October 10, 2013 9:44 AM
  • I'm running office 2013 on windows 7, of course. That said, I am running it inside a virtual machine, but that shouldn't make a difference. All my other office.js calls work fine. Maybe I didn't make it clear, if I don't select the entire row in excel, it works fine. From a usability perspective, users should be able to select an entire row since it is a more intuitive experience. That behavior used to function just fine, but it now fails when I make the call immediately afterward to get the data. I'm asking the community for a way get the contents of the fully selected row. Thanks, Isaac
    Thursday, October 10, 2013 11:34 AM
  • Mmm, it's clear for me.

    But I don't think they are all the same between VM and Windows operating system.

    It seems there might be something different caused this issue.

    I can't find any useful resource, sorry, have no idea to help you.

    Thursday, October 10, 2013 3:20 PM
  • Hi,

    Thank you for posting in the MSDN Forum.

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Best regards,

    George


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 11, 2013 8:09 AM
    Moderator
  • Is anyone able to reproduce the issue I'm seeing with the code sample I posted?  Or is it just me that is seeing this row selection issue?  Selecting a row shouldn't result in a "The requested data set is too large." Error from the Office Javascript API calls.

    Thanks,

    Isaac

    Tuesday, October 29, 2013 6:41 PM
  • Hi Isaac,

    This is an expected behavior.

    For more in-depth support, please visit the below link to see the various paid support options that are available to better meet your needs.  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone.

    Regards

    Pradip

    Monday, November 4, 2013 9:26 AM
  • Hi Pradip,

    After a bunch more research I was able to work around "The requested data set is too large" issue.  Everywhere in my code where I use getDataAsync to retrieve the data from the binding that was created from the AddFromPromptAsync call, I have to first get the number of columns for the binding using the getByIdAsync.  When someone selects a full row in the excel spreadsheet, I found that it is the equivalent of selecting 16k columns.  So, if I see the column count as really big, I set the columnCount parameter of the getDataAsync to a more reasonable number (like 1000), which limits the data returned by getDataAsync.  If the column count is less than 1000, then I have to set it to the actual column count value.  I found that getDataAsync fails when you use a columnCount value greater than the number of columns actually selected, which was quite annoying. It would have been nice if I could have used it as an upper bound.

    Separately, I think there is a documentation bug on the getDataAsync documentation (http://msdn.microsoft.com/en-us/library/fp161073.aspx).  The parameter "columnCount" is changed to just "colCount" in the remarks and in the sample code.  I'm using "columnCount" in my code and it works.  I'm didn't test the use of "colCount", but the inconsistency in the documentation is confusing and should be fixed.

    I also really wish there was actual documentation on the errors that are returned for the different API calls.  Just encountering a "The requested data set is too large" doesn't tell me how large is "too large".  It would have been nice to know what the limit is without trial and error. 

    Hope this info helps others out there who encounter this issue.

    Cheers, Isaac

    • Marked as answer by isaacn7 Wednesday, November 6, 2013 9:23 PM
    Wednesday, November 6, 2013 9:16 PM