CODEWITHSUNDEEP
exceloffice-js
Kero
Kero

Reputation: 1954

get row from range in current worksheet?

I can not get tables name in active worksheet. I have a drop-down that will be populated with worksheets in workbook. I have another drop-down that should get all columns names(header) in selected worksheets. Somehow range.address has "sheet1!2:2" .

Here is code that I used:

function getRow(worksheetName) {
    Excel.run(function (ctx) {
            // Queue a command to write the sample data to the worksheet
            // at moment i have only one worksheet named "Sheet1"

            var range = ctx.workbook.worksheets.getItem(worksheetName).getRange().getRow(1);
            range.load('address');
            // Run the queued-up commands, and return a promise to indicate task 
            //completion
            return ctx.sync().then(function () {
                console.log(range.address); // prints Sheet1!2:2
            })
        })
        .catch(errorHandler);
}

Here is a link to spreadsheet that I used for testing.

Any clue what i am doing wrong here?

Upvotes: 1

Views: 718

Answers (2)

Kim Brandl
Kim Brandl

Reputation: 13500

I'd suggest that you replace getRange() with getUsedRange() instead, as shown here:

function getRow(worksheetName) {
    Excel.run(function (ctx) {
            var range = ctx.workbook.worksheets.getItem(worksheetName).getUsedRange().getRow(1);
            range.load('address');

            return ctx.sync().then(function () {
                console.log(range.address); 
            })
        })
        .catch(errorHandler);
}

Upvotes: 1

Kim Brandl
Kim Brandl

Reputation: 13500

Rick Kirkham's comment above is correct. In the file that you've linked to, there is no table object -- although the worksheet does contain several rows/columns of data, that data is not explicitly contained inside a table.

Are you able to manually manipulate this file? If yes, then you can create a table object for the data by doing the following:

  1. Select the data you want to be in the table.
  2. With that data selected, choose the Table button (on the Insert tab).
  3. Verify inputs in the Create Table prompt and choose OK.

If you are not able to manually manipulate this file, then you can create the table (from the range of data in your worksheet) by using the Office JavaScript API, as described here: https://dev.office.com/docs/add-ins/excel/excel-add-ins-tables#convert-a-range-to-a-table.

Upvotes: 0

Related Questions