Add Table Rename Headers Etc

AddTableRenameHeadersEtc-ExcelOnline

/**
 * Replace all speech marks with blanks in the active sheet of Excel Online.
 */
function main(workbook: ExcelScript.Workbook) {
	// Get the currently active worksheet in the workbook
	let activeSheet = workbook.getActiveWorksheet();

	// Get the used range of the active sheet
	let usedRange = activeSheet.getUsedRange();

	// Get all values from the used range
	let allValues = usedRange.getValues();

	// Replace all speech marks in one go
	for (let row = 0; row < allValues.length; row++) {
		for (let col = 0; col < allValues[0].length; col++) {
			allValues[row][col] = allValues[row][col].toString().replace(/["']/g, ''); // Remove speech marks
		}
	}

	// Set the modified values back to the used range
	usedRange.setValues(allValues);

	// Continue with the rest of the original script

	// Check if a table with the name "Table1" already exists in the workbook
	let existingTable = workbook.getTable("Table1");

	// If a table with the name "Table1" doesn't exist, create a new table at the active sheet's used range
	let newTable = existingTable ? existingTable : workbook.addTable(activeSheet.getUsedRange(), true);

	// Set the width of column(s) at range A:XFD on the active sheet to 56.25
	activeSheet.getRange("A:XFD").getFormat().setColumnWidth(56.25);

	// Add a new worksheet
	// let recommendation1 = workbook.addWorksheet();

	// Add a new pivot table named "PivotTable1" on the active sheet's used range, and place it in recommendation1's range A2:B11
	// let newPivotTable = workbook.addPivotTable("PivotTable1", activeSheet.getUsedRange(), recommendation1.getRange("A2:B11"));

	// Insert a pivot chart of type barClustered based on the layout of newPivotTable on the recommendation1 sheet
	// let chart_1 = recommendation1.addChart(ExcelScript.ChartType.barClustered, newPivotTable.getLayout().getRange());

	activeSheet.getRange("B:B").delete(ExcelScript.DeleteShiftDirection.left);
	activeSheet.getRange("D:G").delete(ExcelScript.DeleteShiftDirection.left);
	activeSheet.getRange("E:H").delete(ExcelScript.DeleteShiftDirection.left);
	activeSheet.getRange("F:X").delete(ExcelScript.DeleteShiftDirection.left);
	activeSheet.getRange("J:N").delete(ExcelScript.DeleteShiftDirection.left);
	activeSheet.getRange("J:J").delete(ExcelScript.DeleteShiftDirection.left);

	// let table1 = workbook.getTable("Table1");
	// Sort on table: table1 column index: '5'
	// table1.getSort().apply([{ key: 5, ascending: true }]);
}