Reusing range object in Excel.run for apps for office

Question

I am new to APPS for OFFICE

I am trying a simple code in which I Validate Excel Data. So Rather than nesting things in ctx.sync() again and again, I am writing code like this:-

        // **json** object used beneath is somewhat like:
        {"Field":[
            {"FieldName":"Field1", "FieldDesc":"Field 1 desc", "MappedTo":"B2", "IsMandatory":"true", "LOV":"1,2,3"}]}

       // **LOV** in above json data means:- the field data can only be among the values given.
       
        //********** MY PIECE OF CODE**************
       
        var fieldData = "";
        $.each(json, function (index, field) {
            range = ctx.workbook.worksheets.getActiveWorksheet().getRange(field.MappedTo + ":" + field.MappedTo);
            range.load('text');
            ctx.sync();
            fieldData = range.text;

            if(field.IsMandatory == true && (fieldData == "" || fieldData == null))
            {
                headerValidation = headerValidation + "Data is required for Field : " + field.FieldDesc + "\n";
            }
            else if(field.LOV != "" )
            {
                if($.inArray(fieldData, field.LOV.split(',')) == -1)
                {
                    headerValidation = headerValidation + "Data not among LOV for Field : " + field.FieldDesc + "\n";
                }
            }

            range = null;
        });

As can be seen, I need to read range object again and again. So I am using range object everytime with different address and calling first "load()" and then "ctx.sync()".

If i debug slowly , things do work fine but on running application i get frequent error now and then:-

The property 'text' is not available. Before reading the property's value, call the load method on the containing object and call "context.sync()" on the associated request context.

Please guide me how I can handle this? Also , is my approach correct?

score 3 · Accepted Answer · answered Apr 13 '16 at 16:25

In terms of what's wrong, the error is next to your ctx.sync() statement. You need it to be

ctx.sync()
    .then(function() {
        fieldData = range.text;
        ...
    });

And I wouldn't forget the .catch(function(error) { ... }) at the end, either.

As far as reusing the variable or not, it really doesn't matter. By doing "range = ctx.workbook...." you're actually creating a global range variable, which is considered bad practice. Better to do "var range = ctx.workbook....". And you don't need to worry about setting it to null at the end.

One thing to note is that since you're doing this in a for-each loop, note that the number of concurrent .sync()s that can be happening is limited (somewhere around 50-60, I believe). So you may need to adjust your algorithm if you're going to have a large number of fields.

Finally, you can make this code much more efficient by simulataniously ceating all of your range objects, loading them all at once, and then doing a single ".sync".

var ranges = [];
$.each(json, function (index, field) {
    var range = ctx.workbook.worksheets.getActiveWorksheet().getRange(field.MappedTo + ":" + field.MappedTo);
    range.load('text');
    ranges.push(range);
});
ctx.sync()
    .then(function() {
        // iterate through the read ranges and do something
    })

Hope this helps,

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT

Hey that was really really helpful. Thanks .. What if i just need to write something to a cell only. Say at cell B2, i want to write "XYZ". In that case i will make a range of B2 cell and set its Values property and call ctx.sync(). Thats it . Here i dont have anything to do in "then()" part. Right? — Gurinder Raj, Apr 14 '16 at 07:41
Correct, the "then" is only if you need to chain something in after the fact. But you should still have a .catch, either there or somewhere further upstream if you're returning the promise. — Michael Zlatkovsky - Microsoft, Apr 14 '16 at 15:50

Reusing range object in Excel.run for apps for office

1 Answers1