Forum Discussion
Solved
Excel Online Script to add row with formula
Hi All,
Ive got a workbook saved to a SharePoint folder.
In the workbook is a script. See below. Its the part in bold i need help with.
Basically the script needs to add a header to the sheet and a footer at the bottom of the set of data (row number can change daily).
The script adding the header works fine. I can't get the script to add the footer to the bottom of the list.
Footer needs to be
99, rownumber (which changes)
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Insert at range 1:1 on selectedSheet, move existing cells down
selectedSheet.getRange("1:1").insert(ExcelScript.InsertShiftDirection.down);
// Set range A1:B1 on selectedSheet
selectedSheet.getRange("A1:B1").setValues([["0","83854"]]);
// Insert last row number
let lastrow = selectedSheet.getUsedRange();
selectedSheet.getRange(lastrow).setValues([["99", "83854"]]);
// Paste to range A:C on selectedSheet from range A:C on selectedSheet
selectedSheet.getRange("A:C").copyFrom(selectedSheet.getRange("A:C"), ExcelScript.RangeCopyType.values, false, false);
}
- You simply add this line of code immediately below that last one:
selectedSheet.getCell(theRow, 1).setFormula("=ROW()");
6 Replies
- Not sure I understand. How would you manually determine where to put that number 99?
chrisparkinsonarthouse Is this what you intended?
function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); let theRow = selectedSheet.getUsedRange().getRowCount(); selectedSheet.getCell(theRow,0).setValue(99); }
