Excel Online Script to add row with formula

[chrisparkinsonarthouse]

Hi All,

I've 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", "formula"]]);
// 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);
}

 

[b_widg]

Before I offer some code, make sure I understood, what you need. If not, feel free to clarify and I'll try again.

1. You have some raw data that starts in A1 that needs a header and a footer.
2. You want to insert a header row at the top that will be "0" in A1 and "83854" in B1
3. You then want to add a footer that will contain the row number of your last row of data in column A and a formula in column B
4. You then want to copy columns A-C and paste them back in place as values only.

If I haven't gone off the rails anywhere, here's one way to do it with a SUM function in column B. I hope it helps. Let me know if you have questions or want me to take another try.

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

  // Get row number of last used row
  let lastRow = selectedSheet.getUsedRange().getLastRow().getRowIndex();
 
  selectedSheet.getRange(`A${lastRow + 1}:B${lastRow + 1}`).setValues([[lastRow, `=SUM(B2:B${lastRow})`]]);

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