Forum Discussion
Adjust Excel Office Script for Other Sheets / Variable Data
Okay, just getting started here, but I do have some Excel / Programming experience. I recorded the script below on a simple sheet to demonstrate, and everything is great, but I would like to adjust it so I can use it on multiple sheets with the same format, but with a variable amount of data rows at the bottom. I have researched various things like finding the last row, the end of data, etc. but I just can't seem to get the details right -- statements after the //Auto fill... and //Create a table... comments.
This just adds a column to sheet, sets a column header, copies a value from the top of the sheet to the first row, fills that value to the bottom, then formats the data rows as table:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Insert at range A:A on selectedSheet, move existing cells right
selectedSheet.getRange("A:A").insert(ExcelScript.InsertShiftDirection.right);
// Set range A3 on selectedSheet
selectedSheet.getRange("A3").setValue("New Col Header");
// Paste to range A4 on selectedSheet from range B1 on selectedSheet
selectedSheet.getRange("A4").copyFrom(selectedSheet.getRange("B1"), ExcelScript.RangeCopyType.all, false, false);
// Auto fill range
selectedSheet.getRange("A4").autoFill("A4:A8", ExcelScript.AutoFillType.fillCopy);
// Create a table with format on range A3:D8 on selectedSheet
let table1 = workbook.addTable(selectedSheet.getRange("A3:D8"), true);
table1.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") } }
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?
