none
Access a sheet / row through javascript in excel office web app RRS feed

  • Question

  • Hi,

    Is it possible to access a second sheet through javascript which is embedded in the content pane of first sheet?

    How to access work sheet / another work sheet' s cell from javascript in excel office web app

    • Edited by myowntest Tuesday, October 16, 2012 12:14 PM
    Tuesday, October 16, 2012 8:44 AM

Answers

  • The answer above from Michael is a bit inaccurate, you can definetly access data in any sheet of your workbook, without the user making a selection on it.

    In order to achieve this, you need to create a binding using the bindings.addFromNamedItem method. This method enables app developers to create bindings by using a name of either a range or a table in any sheet in the workbook (note that you can also use this method  in Word to refer to a named Content Control). As you can see from the help topic, the first argument that the method takes is actualy a name of an object to bind to. The beauty of this is that the name can be actually a named range or table, and you can also use the Excel Range nomencalture. so for instance, a named like "Sheet1!A1:Z100" is a valid reference, and you can also have something like "Sheet2!A1:A3". Once a binding is created you can do the regular get/set operations on it to read or write data.

    The other clarification that I want to make, is that you dont need to bother to store the binding id as setting, bindings are automatically persisted the document.

    Here is a code snipet that shows how to do this. (note that I am using a Range, you can also try it with a named Table just send a TableDataObject to set, and expect one when reading :) )

    function setData(elementId) {
        Office.context.document.bindings.addFromNamedItemAsync("Sheet2!A1:A3", Office.BindingType.Matrix, {id:"MyBinding"}, function(asyncResult){
            if (asyncResult.status == Office.AsyncResultStatus.Failed) {
            
            }
            else {
                //showMessage('Added new binding with type: ' + asyncResult.value.type + ' and id: ' + asyncResult.value.id);
                Office.select("bindings#MyBinding").setDataAsync([['Berlin'],['Munich'],['Duisburg']],{ coercionType: "matrix" }, function (asyncResult) {
                    if (asyncResult.status == "failed") {
                        //showMessage('Error: ' + asyncResult.error.message);
                    }
                });
            }

        });
       
    }






    Thursday, October 18, 2012 11:49 AM
    Moderator

All replies

  • With Apps for Office, accessing any part of the document always requires user action (you can't just call something like sheet1.Range["A1:Z100"] as you may have with VBA/VSTO).  The user must either have already selected some cells (in which case you can call getSelectedDataAsync or store the binding with document.bindings.addFromSelectionAsync), or you can prompt the user for a particular selection (document.bindings.addFromPromptAsync).  Once you have the binding, you can store it and persists its ID in the settings for the document.  But the user will still have had to make the selection the first time.


    Wednesday, October 17, 2012 5:32 PM
  • The answer above from Michael is a bit inaccurate, you can definetly access data in any sheet of your workbook, without the user making a selection on it.

    In order to achieve this, you need to create a binding using the bindings.addFromNamedItem method. This method enables app developers to create bindings by using a name of either a range or a table in any sheet in the workbook (note that you can also use this method  in Word to refer to a named Content Control). As you can see from the help topic, the first argument that the method takes is actualy a name of an object to bind to. The beauty of this is that the name can be actually a named range or table, and you can also use the Excel Range nomencalture. so for instance, a named like "Sheet1!A1:Z100" is a valid reference, and you can also have something like "Sheet2!A1:A3". Once a binding is created you can do the regular get/set operations on it to read or write data.

    The other clarification that I want to make, is that you dont need to bother to store the binding id as setting, bindings are automatically persisted the document.

    Here is a code snipet that shows how to do this. (note that I am using a Range, you can also try it with a named Table just send a TableDataObject to set, and expect one when reading :) )

    function setData(elementId) {
        Office.context.document.bindings.addFromNamedItemAsync("Sheet2!A1:A3", Office.BindingType.Matrix, {id:"MyBinding"}, function(asyncResult){
            if (asyncResult.status == Office.AsyncResultStatus.Failed) {
            
            }
            else {
                //showMessage('Added new binding with type: ' + asyncResult.value.type + ' and id: ' + asyncResult.value.id);
                Office.select("bindings#MyBinding").setDataAsync([['Berlin'],['Munich'],['Duisburg']],{ coercionType: "matrix" }, function (asyncResult) {
                    if (asyncResult.status == "failed") {
                        //showMessage('Error: ' + asyncResult.error.message);
                    }
                });
            }

        });
       
    }






    Thursday, October 18, 2012 11:49 AM
    Moderator
  • Thanks, Juan, I didn't know you could grab blocks of cells like that, that's awesome!

    One question:  in this case you're assuming "Sheet2" exists, which we don't necessarily know.  Is there any way to grab cells just from the "active" sheet using the same mechanism, and/or somehow get a list of available sheet names?

    As for what I meant by persisting the name of the binding in the settings, I meant a case where you're dynamically generating an arbitrary number of bindings, and are hence not furnishing them with names.  In this case, you could store the auto-generated names of the bindings in the settings.  Of course, if you specify the name yourself, as in "MyBinding" above, then the storing of the name is not necessary.

    - Michael


    Thursday, October 18, 2012 6:49 PM
  • Yes it’s possible, just omit the "SheetX:" part of the range name. For instance:

    Office.context.document.bindings.addFromNamedItemAsync("A1:A3", ******

    In this
    example if you are in sheet1 you will create the binding in Sheet1!A1:A3, if
    you are in sheet2 then the binding will be created in Sheet2!A1:A3 and so on
    and so forth....

    Btw, right now is not possible to get a list of available sheets, but you will get a 'object not found' exception if you try to establish a binding to a sheet that does not exist.

    Regarding your  other topic, even in that case you don’t need to save them as settings, if you don’t
    supply a binding iD we create one automatically for you and then on subsequent
    document-opens you can call bindings.getAllAsync to get a collection of the bindings
    available in the document and traverse them, you get an array of binging objects as a result of
    that call and can access the ID property on each entry.

    Obviously you can do settings as well if you want, but you really don't need to you are
    just going to be increasing the size of the file in a preventable way.






    Thursday, October 18, 2012 7:25 PM
    Moderator
  • I'm attempting to do something similar, and this is helpful but I'm still unclear.

    Basically my goal is to update the pane of my app with contextual information based on the currently selected sheet.

    Is there any mechanism at all to identify either when a sheet is selected, or changed (or both!) so that I can update my pane?

    If not, are there any other strategies I could use to simulate this behavior? Currently I do have a single named table in each sheet, which would serve just as well to identify a change in selection.

    However, I am also unable to determine which table has been selected. using getSelectedDataAsync(Office.CoercionType.Table) results in an object that has the header of the column and the rows, but does not have any indication of the table to which it belongs.

    Additionally this only returns the VALUE of the item, not the position. if I could at least get a coordinate like "Sheet1:A1" as the selected CELL, I could then use a custom function to determine the current sheet.

    But as of yet I've been unsucessful in finding such an API. is there any way to currently achieve anything similar to this?

    many thanks!

    joshm

    Tuesday, August 26, 2014 4:14 AM
  • How can I get cell number(A1,B5 etc) when a cell is selected? Basically what I am trying to achieve is, writing to cells just above and below the selected cell. Please suggest. Thanks!
    Monday, May 25, 2015 5:58 AM