Officescript how to set value in table cell (row/column) by row index-CodePudding

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]

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!

CodePudding user response：

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);

CodePudding user response：

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")
}