pretty simple question but I can't seem to find what I am looking for and wondering if it is possible this way, just starting using Officescript/typescript. In a part of my code, I get the index of the row with a value that matches (cRow is the index of row I am interested in).

  rowValue = collectionTable.getColumnByName("SomeCol").getRangeBetweenHeaderAndTotal().getValues()[cRow]

And then I run some checks on that row and want to update some other things based on the inputs.

So what I am expecting to do is something like the following, changing getValues for setValues:

collectionTable.getColumnByName("UpdateMe").getRangeBetweenHeaderAndTotal().setValues()[cRow]

OR

let col = collectionTable.getColumnByName("SomeCol").getIndex();
let cell = collectionTable.getCell(requestRow,col);

cell.setValue(value);

But doesn't seem to work that way .. From what I can tell, setValues works on ranges but can't quite find how to get the range/row by index number and set a value in one cell. I see all examples doing it with the letter and number but don't want to do it that way if possible.

Thanks for the help!

pretty simple question but I can't seem to find what I am looking for and wondering if it is possible this way, just starting using Officescript/typescript. In a part of my code, I get the index of the row with a value that matches (cRow is the index of row I am interested in).

  rowValue = collectionTable.getColumnByName("SomeCol").getRangeBetweenHeaderAndTotal().getValues()[cRow]

And then I run some checks on that row and want to update some other things based on the inputs.

So what I am expecting to do is something like the following, changing getValues for setValues:

collectionTable.getColumnByName("UpdateMe").getRangeBetweenHeaderAndTotal().setValues()[cRow]

OR

let col = collectionTable.getColumnByName("SomeCol").getIndex();
let cell = collectionTable.getCell(requestRow,col);

cell.setValue(value);

But doesn't seem to work that way .. From what I can tell, setValues works on ranges but can't quite find how to get the range/row by index number and set a value in one cell. I see all examples doing it with the letter and number but don't want to do it that way if possible.

Thanks for the help!

• javascript
• excel
• typescript
• office365
• office-scripts

2 Answers 2

You can use getCell() with the cRow variable after getRangeBetweenHeaderAndTotal() to get a specific cell range. Once you have that range, you can read its value using getValue(). And you can write a value to that range using setValue(). You can see an example of how to do that below:

    function main(workbook: ExcelScript.Workbook)
    {
      let cRow: number = 1;
      let tbl: ExcelScript.Table = workbook.getTable("table1");
      let rowCell: ExcelScript.Range = tbl.getColumnByName("SomeCol").getRangeBetweenHeaderAndTotal().getCell(cRow,0);
      let rowValue: string = rowCell.getValue() as string;
      rowCell.setValue("some updated value");
    }

Can get the value by using the worksheet cell, but I would really like to reference the table itself to not have any issues if things are moved around and such..

let col = collectionTable.getColumnByName("SomeCol").getIndex(); 

// if table starts at beginning of sheet, or else have to offset col and row..
let cell = dataSheet.getCell(requestRow + 1, col);

cell.setValue(value);