web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : liyaHqLp6mqahvqqcKvGGd
Power Automate - Building Flows
Answered

Office script help Fill data automatically in worksheet cells

Like (0) ShareShare
ReportReport
Posted on 28 Sep 2022 13:28:37 by

hi everyone,

 

In the desktop version you can use the autofill down option for the blanks rows but in the online Excel version this is not available.

Can anyone help me with an office script that auto fills column A and B, based on the values from the row above?

 

thanks in advance.

 

I found this code via internet, however i am get "0'' as result in the empty columns, can any one advise how the code should be populated?

 

function main(workbook: ExcelScript.Workbook) {
  let ws: ExcelScript.Worksheet = workbook.getActiveWorksheet();
  let tableName: string = "Table2";
  let tableColumn: string = "Spec.";
  let columnFormula: string = "=B";
  let inputRange: ExcelScript.Range = workbook.getTable(tableName).getColumn(tableColumn).getRange();
  let inputFormulas: string[][] = inputRange.getFormulas();
  let newVals: string[][] = inputFormulas.map((cell, row) => {
    if (cell.toString() === "") {
      return [columnFormula + (row + 1)];
    } else {
      return [cell.toString()];
    }
  })
  inputRange.setFormulas(newVals);
}

 

123213123_1-1664371607591.png

 

@GeoffRen 

  • MichelleRanMSFT Profile Picture
    Microsoft Employee on 10 Oct 2022 at 19:47:32
    Re: Office script help Fill data automatically in worksheet cells

    Hmm, I'm not able to reproduce this error, but what seems to be happening is that either getAddress() is returning null, or the regex isn't able to make a match. If you're interested in debugging this, you can add some logs like so (replace line 12):

    console.log(column.getName());
    let address = inputRange.getAddress();
    console.log(address);
     
    let match = columnRegex.exec(address);
    console.log(match);
    
    let columnFormula = "=" + match[1];

    and see which of these is failing.

  • 123213123 Profile Picture
    on 10 Oct 2022 at 06:42:39
    Re: Office script help Fill data automatically in worksheet cells

    hi Michelle,

     

    this works, thank you so much!! it does give an error when it is finished:

     

    123213123_0-1665384146754.png

     

  • Verified answer
    MichelleRanMSFT Profile Picture
    Microsoft Employee on 07 Oct 2022 at 17:51:52
    Re: Office script help Fill data automatically in worksheet cells

    Try this script - it should autofill all columns in the given table.

     

    function main(workbook: ExcelScript.Workbook) {
     let ws: ExcelScript.Worksheet = workbook.getActiveWorksheet();
     let table = workbook.getTable("Table2");
    
     const columnRegex = /!([A-Z]+)\d/;
    
     table.getColumns().forEach(column => {
     let inputRange: ExcelScript.Range = column.getRange();
     let inputFormulas: string[][] = inputRange.getFormulas();
     let startRow = inputRange.getRowIndex();
    
     let columnFormula = "=" + columnRegex.exec(inputRange.getAddress())[1];
     
     let newVals: string[][] = inputFormulas.map((cell, row) => {
     if (row > 1 && cell.toString() === "") {
     return [columnFormula + (startRow + row)];
     } else {
     return [cell.toString()];
     }
     })
     inputRange.setFormulas(newVals);
     })
    }

     

    Do note that because you're filling the cells with formulas that reference the above cells, every time you change - say - A2, all the values in A3:A18 will change as well. If you just wanted to copy the values in the cells once, you could use this code:

     

    function main(workbook: ExcelScript.Workbook) {
     let ws: ExcelScript.Worksheet = workbook.getActiveWorksheet();
     let table = workbook.getTable("Table2");
     table.getColumns().forEach(column => {
     let inputRange: ExcelScript.Range = column.getRange();
     let startRow = inputRange.getRowIndex();
     let rowCount = inputRange.getRowCount();
     for (let row = 2; row < rowCount; row++) {
     let cell = inputRange.getCell(row, 0);
     if (cell.getValue().toString() === "") {
     cell.setValue(inputRange.getCell(row - 1, 0).getValue());
     }
     }
     })
    }

     

  • 123213123 Profile Picture
    on 07 Oct 2022 at 06:33:32
    Re: Office script help Fill data automatically in worksheet cells

    it worked now by changing the B to A, thanks!!  However, I want to apply the same to column B, when I change back to B it doesn't work.  Can I combine column A and B in one script?

  • MichelleRanMSFT Profile Picture
    Microsoft Employee on 06 Oct 2022 at 14:44:55
    Re: Office script help Fill data automatically in worksheet cells

    I believe that's because the script starts the cell formulas with "=B", but the data that you want copied is in column A. Try changing the columnFormula variable to "=A". Let me know if that helps!

  • 123213123 Profile Picture
    on 06 Oct 2022 at 08:00:56
    Re: Office script help Fill data automatically in worksheet cells

    hi Michelle, thank you so much for looking into this, however when i try to run your modified script i am getting this result:

    123213123_0-1665043243824.png

     

  • MichelleRanMSFT Profile Picture
    Microsoft Employee on 05 Oct 2022 at 19:51:40
    Re: Office script help Fill data automatically in worksheet cells

    Hi,

     

    Thanks for your question! I suspect the issue you're running into is that the code you're using assumes the table begins on row 1. This change to the script should allow it to work regardless of which row the table starts at:

    function main(workbook: ExcelScript.Workbook) {
     let ws: ExcelScript.Worksheet = workbook.getActiveWorksheet();
     let tableName: string = "Table2";
     let tableColumn: string = "Spec.";
     let columnFormula: string = "=B";
     let inputRange: ExcelScript.Range = workbook.getTable(tableName).getColumn(tableColumn).getRange();
     let inputFormulas: string[][] = inputRange.getFormulas();
     let startRow = inputRange.getRowIndex(); // NEW
     let newVals: string[][] = inputFormulas.map((cell, row) => {
     if (cell.toString() === "") {
     return [columnFormula + (startRow + row)]; // MODIFIED
     } else {
     return [cell.toString()];
     }
     })
     inputRange.setFormulas(newVals);
    }

     Hopefully that helps! Let me know if you have any questions.

     

    Best,

    Michelle

  • Community Power Platform Member Profile Picture
    on 28 Sep 2022 at 15:19:00
    Re: Office script help Fill data automatically in worksheet cells

    Following

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 2

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 2