Reusing range object in Excel.run for apps for office

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?

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