Creating Dynamic Columns - Office Scripts

CreateDynamicColumns.txt

// Creating Dynamic Columns challenge

function main(workbook: ExcelScript.Workbook) {

// Extract

	const selectedSheet = workbook.getWorksheet("Challenge")
	const source = selectedSheet.getTable("Source")

	let names = source.getColumnByName("Name")
		.getRangeBetweenHeaderAndTotal()
		.getValues();
	let hobbies = source.getColumnByName("Music")
		.getRangeBetweenHeaderAndTotal()
		.getValues();

// Transform

	const hobbiesTexts = hobbies.toString()
		.split(",")
		.map(x => x.trim())
	const hobbyHeader = Array.from(new Set(hobbiesTexts) )
		.sort()
		.filter( x =>  x.length > 0  )
	const mapHobbies = hobbies.map(hb =>
		hobbyHeader.map(x => (
			hb.toString()
				.split(",")
				.map(x => x.trim())
				.indexOf(x) > -1 )
		  ? "Yes":"No" )  )
		
// Load

	const columns = hobbyHeader.length
	const rows = names.length

	const startCell = selectedSheet.getRange("OSstart");
	const headerRange = startCell
		.getOffsetRange(-1, 1)
		.getResizedRange(0, columns - 1);
	const namesRange = startCell
		.getResizedRange(rows - 1, 0)
	const dataRange = startCell
		.getOffsetRange(0, 1)
		.getResizedRange(rows - 1, columns - 1)

	const entireRange = startCell.getResizedRange(rows - 1, columns);
	
	entireRange.clear();
	namesRange.setValues(names);
	headerRange.setValues(Array.of(hobbyHeader));
	dataRange.setValues(mapHobbies);
	entireRange.getSort().apply([{ key: 0, ascending: true }]);



// formatting block

	entireRange.clearAllConditionalFormats();

	let entireRangeFormats = entireRange.getFormat()
		entireRangeFormats.getFont().setSize(12);
		entireRangeFormats.setColumnWidth(60);

	let headerFormats = headerRange.getFormat()
		headerFormats.getFont().setBold(true)
		headerFormats.setHorizontalAlignment(ExcelScript.HorizontalAlignment.center)

	let formatNamesRange = namesRange.getFormat();
		formatNamesRange.getFont().setBold(true)
		formatNamesRange.getFont().setColor("0070c0")

	dataRange
		.getFormat()
		.setHorizontalAlignment(ExcelScript.HorizontalAlignment.center)


// set borders

	function getBorderIndex(n: number): ExcelScript.BorderIndex {
		const index = [
			ExcelScript.BorderIndex.edgeLeft,
			ExcelScript.BorderIndex.edgeRight,
			ExcelScript.BorderIndex.edgeTop,
			ExcelScript.BorderIndex.edgeBottom,
			ExcelScript.BorderIndex.insideHorizontal,
			ExcelScript.BorderIndex.insideVertical,
		]
		return index[n]
	};

	function setGridColor(rng: ExcelScript.Range, color: string) {
		let format = rng.getFormat();
		const a = [0, 1, 2, 3, 4, 5];
		a.map(n => format.getRangeBorder(n).setColor(color))
	};

	setGridColor( entireRange, "#A6A6A6");

	
// conditional formatting

	const conditionalFormatYes = dataRange
		.addConditionalFormat(ExcelScript.ConditionalFormatType.containsText);
	const formatYes = conditionalFormatYes.getTextComparison();
		formatYes.setRule({
			text: "Yes" ,
			operator: ExcelScript.ConditionalTextOperator.contains
	});
	formatYes.getFormat().getFill().setColor("00b050");
	formatYes.getFormat().getFont().setColor("White");


	const conditionalFormatNo = dataRange
		.addConditionalFormat(ExcelScript.ConditionalFormatType.containsText);
	const formatNo = conditionalFormatNo.getTextComparison();
	formatNo.setRule({
		text: "No",
		operator: ExcelScript.ConditionalTextOperator.contains
	});
	formatNo.getFormat().getFill().setColor("f2f2f2");
	formatNo.getFormat().getFont().setColor("a6a6a6");


}