Adjust Excel Office Script for Other Sheets / Variable Data

[Scott4850]

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

[#2 · Accepted solution]

@Scott4850 

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

	}
}

[#3]

@SergeiBaklan

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?

[#4]

@Scott4850 

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???

[#5]

@rachel 

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!

[#6]

@Scott4850 

10000 rows are taken with the gap, we may take entire range starting from A3 till end of the column, doesn't matter. Size shall be more than possible range with actual data. Since on next step we use .getUsedRange() and it is cut initial range with 10000 rows to the range with actual data only. Other words from A3 till last not-blank cell.

Next into nRows we return the number of the rows in the range with actual data. Instead of taking A3:D8 we take only the range with headers A3:D3 and resize it down on number of rows in actual data.

Here is the script with comments

function main(workbook: ExcelScript.Workbook) {

	// get array of all worksheets in the workbook
	const sheets = workbook.getWorksheets()

	for( let sheet of sheets) { // iterate sheets

		// insert empty first column moving all data to the right
		sheet
			.getRange("A:A")
			.insert(ExcelScript.InsertShiftDirection.right)
		// set value into A3 of new column
		// we assume headers are always in third row
		sheet
			.getRange("A3")
			.setValue("New Col Header")

		// define first cell for the data in first column
		let target = sheet.getRange("A4")

		// set value into the first data cell in first column of future table
		target
			.copyFrom(sheet.getRange("B1"),
				ExcelScript.RangeCopyType.all,
				false,
				false)

		let range = sheet
			.getRange("B3:B10000")  // Initial data starts from B3 and we take
															// the range with the gap, assuming
															// it's not more than 100000 rows in source data.
															// Could be change on any other value
			.getUsedRange()					// from above get range only with actual data,
															// thus blank cells are cut.
															// Resulting range has the size of actual data
															// in source

		// get number of rows in source data
		let nRows = range.getRowCount()
	
		// fill value in A4 till end of the first column
		target
			.getOffsetRange(1,0)  // shift the range down on one cell
														// actulally that's A5
			.getResizedRange(nRows-3,0) // resize the range,
														// now it starts from A5 till end of data
			.copyFrom(target, ExcelScript.RangeCopyType.values)
														// copy data from A4 to every cell of
														// first colmn range

		workbook
			.addTable(
					sheet
					.getRange("A3:D3")  // set range with headers
															// we assume number of columns is fixed and
															// the same for all sheets
															// if not that shall be adjusted
					.getResizedRange(nRows-1,0) // expand range till end of data
				, true)
			.setPredefinedTableStyle("TableStyleLight8") // set table style

	}
}

Thanks @rachel for the sample and test.

[#7]

@Scott4850 

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.

[#8]

Thanks so much for all your help!

[#9]

@Scott4850 , you are welcome