Forum Discussion

chrisparkinsonarthouse's avatar
chrisparkinsonarthouse
Copper Contributor
Jul 27, 2022
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...
excel
Excel for web
office 365
Office Scripts
  • JKPieterse's avatar
    JKPieterse
    Jul 28, 2022
    You simply add this line of code immediately below that last one:
    selectedSheet.getCell(theRow, 1).setFormula("=ROW()");
JKPieterse's avatar
JKPieterse
Silver Contributor
Jul 28, 2022
Not sure I understand. How would you manually determine where to put that number 99?
  • chrisparkinsonarthouse's avatar
    chrisparkinsonarthouse
    Copper Contributor
    Jul 28, 2022

    JKPieterse Hi,

    Its keyed in column A, in the last row.

     

    But the last row changes every day.

     

    Chris

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Jul 28, 2022

      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);
}
      • chrisparkinsonarthouse's avatar
        chrisparkinsonarthouse
        Copper Contributor
        Jul 28, 2022

        JKPieterse Hi,

         

        Amazing, so the script adds 99 to column A at the bottom of the row.  Which is great.

         

        Now i need in Column B same row to be the formula =ROW()

        .

        Is this possible?

         

         

Resources