Adjust Excel Office Script for Other Sheets / Variable Data

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.

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.