THE SCRIPT:
function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getWorksheets()[0];
// Read headers from row 1 (Explicitly specify columns A:AH - Cell by Cell)
const headers: string[] = [];
for (let colIndex = 0; colIndex < 34; colIndex++) { // Loop through columns A to AH (0 to 33 index)
const cellValue = sheet.getCell(0, colIndex).getValue(); // Get the value of each cell in the first row
headers.push((cellValue ?? "").toString().trim().toLowerCase()); // Add the lowercase, trimmed header to the array
}
console.log("✅ Explicit Headers (A1:AH1 - Cell by Cell):", headers); // Log the extracted headers
// Function to find the index of a column based on its title (case-insensitive)
const getColumnIndex = (title: string): number =>
headers.findIndex((h: string) => h === title.trim().toLowerCase());
// Function to rename a column
const renameColumn = (sourceTitle: string, newTitle: string): void => {
const index: number = getColumnIndex(sourceTitle); // Get the index of the column to rename
if (index !== -1) { // If the column is found
sheet.getRangeByIndexes(0, index, 1, 1).setValues([[newTitle]]); // Set the new header value
headers[index] = newTitle.toLowerCase(); // Update the headers array
} else { // If the column is not found
console.log(`⚠️ Column '${sourceTitle}' not found for renaming.`);
}
};
// Function to delete a column by its title
const deleteColumnByTitle = (title: string): void => {
const index: number = getColumnIndex(title); // Get the index of the column to delete
if (index !== -1) { // If the column is found
sheet.getRangeByIndexes(0, index, sheet.getUsedRange().getRowCount(), 1).delete(ExcelScript.DeleteShiftDirection.left); // Delete the entire column
headers.splice(index, 1); // Remove the header from the array
} else { // If the column is not found
console.log(`⚠️ Column '${title}' not found for deletion.`);
}
};
// Array of column titles to delete
const columnsToDelete: string[] = [
"PMS ID", "New Patient", "Telehealth", "SEM Credit", "Not New Credit",
"Stripe Charge ID", "Payment Date", "Refund Date", "Transaction line type",
"Amount", "Application Fee", "Net", "Service", "Resource", "Booking Form", "Appointment type", "Payment method",
"Consent received", "Voucher code", "Marked Attended", "External Payment Id",
"External Payment Amount", "External Payment Refunded"
];
columnsToDelete.forEach((title: string) => deleteColumnByTitle(title)); // Loop through and delete each specified column
// Function to duplicate a column and rename the duplicate
const duplicateAndRenameColumn = (sourceTitle: string, newTitle: string): void => {
const sourceIndex: number = getColumnIndex(sourceTitle); // Get the index of the column to duplicate
if (sourceIndex !== -1) { // If the column is found
const values: (string | number | boolean)[][] = sheet
.getRangeByIndexes(0, sourceIndex, sheet.getUsedRange().getRowCount(), 1) // Get all values in the source column
.getValues();
const insertIndex: number = sheet.getUsedRange().getColumnCount(); // Get the index of the next empty column
sheet.getRangeByIndexes(0, insertIndex, sheet.getUsedRange().getRowCount(), 1).setValues(values); // Insert the values into the new column
sheet.getRangeByIndexes(0, insertIndex, 1, 1).setValues([[newTitle]]); // Set the header of the new column
headers.push(newTitle.toLowerCase()); // Add the new header to the array
} else { // If the column is not found
console.log(`⚠️ Column '${sourceTitle}' not found for duplication.`);
}
};
// Rename specific columns
renameColumn("Booking ID", "booking_id");
renameColumn("Appointment Date", "appointment_date");
renameColumn("Appointment Time", "appointment_time");
renameColumn("Location", "he_vision_clinic_booked_at____");
renameColumn("Patient Name", "full_name_");
renameColumn("Patient Email", "email");
renameColumn("Patient Phone", "phone");
renameColumn("Cancelled", "cancelled");
renameColumn("Cancelled Time", "cancelled_time");
// Duplicate and rename specific columns
duplicateAndRenameColumn("appointment_date", "he_free_assessment_date");
duplicateAndRenameColumn("appointment_time", "he_free_assessment_time");
duplicateAndRenameColumn("Cancelled", "he_cancelled");
duplicateAndRenameColumn("Booking Date", "he_booking_date");
duplicateAndRenameColumn("Booking Time", "he_booking_time");
duplicateAndRenameColumn("cancelled_time", "he_cancelled_time");
// Create a table from the used range (at the end)
let currentUsedRange = sheet.getUsedRange(); // Get the final used range of the sheet
let table = sheet.addTable(currentUsedRange.getAddress(), true); // Create a table from the used range with headers
table.setName("ContentData"); // Name the table "ContentData"
console.log("✅ Table 'ContentData' created from used range (at the end)."); // Log the table creation
}
