Unanswered

Office script help Fill data automatically in worksheet cells

(0) Share

Report

Posted on by 123213123

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

}

@GeoffRen

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

Community Power Pla... on at

Like (0)

Report

Following

Was this reply helpful? Yes No
MichelleRanMSFT Microsoft Employee on at

Like (0)

Report
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

Was this reply helpful? Yes No
123213123 on at

Like (0)

Report

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

Was this reply helpful? Yes No
MichelleRanMSFT Microsoft Employee on at

Like (0)

Report

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!

Was this reply helpful? Yes No
123213123 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Verified answer

MichelleRanMSFT Microsoft Employee on at

Like (1)

Report
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()); } } }) }

Was this reply helpful? Yes No
123213123 on at

Like (0)

Report

hi Michelle,

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

Was this reply helpful? Yes No
MichelleRanMSFT Microsoft Employee on at

Like (1)

Report
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.

Was this reply helpful? Yes No