Excel Office Scripts: Easily Create Tables from Any Excel File

One of the biggest challenges when working with Power Automate and Excel files is having a data table in the spreadsheet. They are not frequently used and are the only way Power Automate can interact with the data on a sheet from the cloud. You can always use Power Automate Desktop but you should always go with a cloud-first approach when it comes to Power Automate. In my opinion of course.

NOTE: This script was edited as i realized there were some missing cases. This script now will turn the entire used range of a sheet into a table if no startRow or searchValue is specified. To find a blank value please use the literal string EMPTY in the searchValue parameter.

Script Time

The following script works by accepting 4 parameters:

• tableName: This parameter is what name you want to give the table. I always prefix a name like “tb_TableName” to make it clear.
• sheetName: This is the sheet where the data resides that you want to create a table in. (Optional: Default will use the current sheet)
• startRow: This is the starting row on the spreadsheet where the script will look for data. A lot of sheets come with headers and this will allow you to bypass them. (Optional: Will start at the first row in the sheet)
• searchValue: This is what value you want to go down until. Use the word EMPTY to find the first empty value in the first column, or specify a value to search for. (Optional: If no value is specified will go all the way to bottom of used range)

function main(
    workbook: ExcelScript.Workbook,
    tableName: string,
    sheetName?: string, // Optional sheet name defaults to the active sheet
    startRow?: number, // Default to the first row of the sheet
    searchValue?: string
) {
    if (startRow == 0 || startRow == null) {
        startRow = 1;
    }

    let sheet: ExcelScript.Worksheet;
    if (sheetName) {
        sheet = workbook.getWorksheet(sheetName);
        if (!sheet) {
            console.log(`The sheet named "${sheetName}" does not exist.`);
            return;
        }
    } else {
        sheet = workbook.getActiveWorksheet();
    }

    // Convert start row to zero-indexed
    let zeroIndexedStartRow = startRow - 1;
    searchValue = searchValue ?? "";

    // Obtain used range and values
    let usedRange = sheet.getUsedRange();
    let usedValues = usedRange.getValues();

    // Initialize variables for the last row and column
    let lastRow = usedValues.length - 1; // Initialize to the last row
    let lastColumn = usedValues[0].length - 1; // Initialize to the last column
    let foundSearchValue = false;

    if (searchValue !== "") {
        for (let row = zeroIndexedStartRow; row < usedValues.length; row++) {
            const cellValue = usedValues[row][0];

            // Check if search value is empty or marked as "EMPTY"
            if (
                (searchValue === "EMPTY" && (cellValue === "" || cellValue === null)) ||
                cellValue === searchValue
            ) {
                foundSearchValue = true;
                lastRow = row;
                break;
            }
        }

        if (!foundSearchValue) {
            lastRow = usedValues.length - 1;
        }
    }

    if (lastRow < zeroIndexedStartRow) {
        console.log(`There are no data rows after row ${startRow}.`);
        return;
    }

    // Determine end column letter and define range address
    let endColumnLetter = String.fromCharCode(65 + lastColumn);
    let rangeAddress = `A${zeroIndexedStartRow + 1}:${endColumnLetter}${lastRow + 1}`;
    let targetRange = sheet.getRange(rangeAddress);

    // Check if a table already exists on the sheet
    let existingTable: ExcelScript.Table | null = null;
    let tables = sheet.getTables();
    for (let i = 0; i < tables.length; i++) {
        if (tables[i].getRange().getAddress() === targetRange.getAddress()) {
            existingTable = tables[i];
            break;
        }
    }

    if (existingTable) {
        // Rename the existing table
        existingTable.setName(tableName);
        console.log(`Existing table renamed to "${tableName}".`);
    } else {
        // Create a new table for the specified range
        const table = sheet.addTable(targetRange, true);
        table.setName(tableName);
        console.log(`New table created and named "${tableName}".`);
    }
}

The scripts performance is great and will allow you to run from Power Automate on any sheet. If you run into any issues please let me know. Enjoy!