Iterate through range selection

Question

This is very simple for someone that knows what they are doing.

I have a little script that pulls the current selection and formats the cell background to match the value (provided that the cell is a hex value). I'd like to make this work on any range selected. I'll have to do some checking that each string is a hex value etc but I'm blocked on iterating thru a range.

Any hints on how I would loop through a range selection with office.js?

Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
    function (asyncResult) {
        if (asyncResult.status === Office.AsyncResultStatus.Failed) {
            console.log(asyncResult.error.message);
        } else {
            // write the result to range selection variable
            var rsel = asyncResult.value;
        }            


        Excel.run(function(context) {
            // convert to string
            context.workbook.getSelectedRange().format.fill.color = rsel.toString();
            return context.sync();
        }).catch(function(error) {
            console.log(error);
            if (error instanceof OfficeExtension.Error) {
                console.log("Debug info: " + JSON.stringify(error.debugInfo));
            }
        });
    }
);

Answer 1

Based on your description, it sounds like you want somethign like this?

Here are two functions, one that just writes random hex values to the cells, and another that actually colors them. Note that it's generally best to not mix ExcelApi 1.1 API syntax and Office 2013 getSelectedDataAsync in the same code block, or else the callback vs. promise pattern can get a little confusing -- though you did have it right above. For my examples, though, I'll use just the ExcelApi 1.1 syntax:

function setRandomValues() {
    Excel.run(function (context) {
        var selection = context.workbook.getSelectedRange();
        selection.format.fill.clear();
        selection.load("rowCount, columnCount");

        return context.sync()
            .then(function() {
                var valuesArray = [];
                for (var r = 0; r < selection.rowCount; r++) {
                    var rowValues = [];
                    for (var c = 0; c < selection.columnCount; c++) {
                        rowValues.push(getRandomColor());
                    }
                    valuesArray.push(rowValues);
                }
                selection.values = valuesArray;
            })
            .then(context.sync);

        function getRandomColor() {
            // from http://stackoverflow.com/a/5092872/678505
            return "#000000".replace(/0/g, function(){ 
                return (~~(Math.random()*16)).toString(16);
            });
        }
    })
    .catch(console.log)
}

And

function highlightSelection() {
    Excel.run(function (context) {
        var selection = context.workbook.getSelectedRange();
        selection.load("rowCount, columnCount, values");

        return context.sync()
            .then(function() {
                for (var r = 0; r < selection.rowCount; r++) {
                    for (var c = 0; c < selection.columnCount; c++) {
                        selection.getCell(r, c).format.fill.color = selection.values[r][c];
                    }
                }
            })
            .then(context.sync);
    })
    .catch(console.log);
}