Forum Discussion
Adjust Excel Office Script for Other Sheets / Variable Data
Not sure I understood correctly the logic of what you do, as variant
function main(workbook: ExcelScript.Workbook) { const sheets = workbook .getWorksheets() for( let sheet of sheets) { sheet .getRange("A:A") .insert(ExcelScript.InsertShiftDirection.right) sheet .getRange("A3") .setValue("New Col Header") sheet .getRange("A4") .copyFrom(sheet.getRange("B1"), ExcelScript.RangeCopyType.all, false, false) let range = sheet .getRange("B3:B10000") .getUsedRange() let nRows = range.getRowCount() let target = sheet.getRange("A4") target .getOffsetRange(1,0) .getResizedRange(nRows-3,0) .copyFrom(target, ExcelScript.RangeCopyType.values) workbook .addTable( sheet .getRange("A3:D3") .getResizedRange(nRows-1,0) , true) .setPredefinedTableStyle("TableStyleLight8") } }
Not sure I understood correctly the logic of what you do, as variant
function main(workbook: ExcelScript.Workbook) {
const sheets = workbook
.getWorksheets()
for( let sheet of sheets) {
sheet
.getRange("A:A")
.insert(ExcelScript.InsertShiftDirection.right)
sheet
.getRange("A3")
.setValue("New Col Header")
sheet
.getRange("A4")
.copyFrom(sheet.getRange("B1"),
ExcelScript.RangeCopyType.all,
false,
false)
let range = sheet
.getRange("B3:B10000")
.getUsedRange()
let nRows = range.getRowCount()
let target = sheet.getRange("A4")
target
.getOffsetRange(1,0)
.getResizedRange(nRows-3,0)
.copyFrom(target, ExcelScript.RangeCopyType.values)
workbook
.addTable(
sheet
.getRange("A3:D3")
.getResizedRange(nRows-1,0)
, true)
.setPredefinedTableStyle("TableStyleLight8")
}
}
Sorry. I just meant a script that could be used on other sheets, and those sheets would have a variable amount of data in what became the table at the end.
Mainly, it was these two lines that I needed help with:
selectedSheet.getRange("A4").autoFill("A4:A8", ExcelScript.AutoFillType.fillCopy);
let table1 = workbook.addTable(selectedSheet.getRange("A3:D8"), true);
Instead of A4:A8 and A3:D8, I need both of those to go to the bottom of the data, which will be anywhere from 1-n rows long.
I didn't really need the loop, but it was good to learn. I also very much appreciate the effort you put into this. I think I understand most of it, and will continue to study it, but I don't think this is what I need because it appears to assume that every future sheet has at least 3 rows.
Also curious if it is really necessary to get 10,000 rows like you did?
Funny I think the script is exactly what you need?
I assume your original data looks like below:
That is, header is in A3:C3; table name is in A1. Data is from Row4 to the bottom right.
After running the script, I got below. that is not what you are looking for???
Actually, you are correct, it does work. My misunderstanding (and yes, I should have run it before responding!!!).
I still don't understand the seemingly arbitrary (and prone to breaking?) selection of 10,000 rows. And, well, I'm surprised that changing something like A3:D8 to A3:D<end of data> requires so much code.
Lots more to learn, I guess. Thanks!
As for the size of the code. Changing of
.getRange("A3:D8")on
.getRange("A3:D3") .getResizedRange(nRows-1,0)adds not a lot of code. However, we need some additional code to calculate number of rows in target range.
