Office Scripts: Convert Excel Tables to Markdown Text

|Fruit Name|Price|Expiry Date|Stock Status|Link to Product Page|Notes|
|---|---|---|---|---|---|
|**Apple**|¥100|10/31/2024|*In stock*|[About Apple](https://dummy/)|***Sweet and delicious!***|
|**Banana**|¥80|11/5/2024|*Out of stock*|[About Banana](https://dummy/)||
|**Strawberry**|¥200|10/30/2024|*In stock*|[About Strawberry](https://dummy/)|Perfect for desserts.<br />How about in a parfait?|

/**
 * Constants
 */
const BOLD_FORMAT = "**";
const STRIKETHROUGH_FORMAT = "~~";
const ITALIC_FORMAT = "*";
const NEW_LINE = "\n";
const CELL_SEPARATOR = "|";
const TABLE_SEPARATOR = "---";
const TABLE_CREATION_INSTRUCTIONS_URL = "https://support.microsoft.com/office/create-a-table-in-excel-bf0ce08b-d012-42ec-8ecf-a2259c9faf3f";
const ESCAPE_CHARACTERS = {
    '\n': "<br />",
    '|': "&#124;",
    '*': '\\*',
    '_': '\\_',
    '\\': '\\\\',
    '`': '\\`',
    '{': '\\{',
    '}': '\\}',
    '[': '\\[',
    ']': '\\]',
    '(': '\\(',
    ')': '\\)',
    '>': '\\>',
    '#': '\\#',
    '+': '\\+',
    '-': '\\-'
};

/**
 * Main function that converts Excel tables to markdown text and outputs to console
 */
function main(workbook: ExcelScript.Workbook): string[] {
    const tables = workbook.getActiveWorksheet().getTables();

    if (tables.length === 0) {
        console.log(`The markdown table could not be created because there was no table. Please see the following link for instructions on how to create the table: ${TABLE_CREATION_INSTRUCTIONS_URL}`);
        return [];
    }

    const markdownTables: string[] = [];

    for (const table of tables) {
        const columns = table.getColumns();

        let markdownTable = createHeaderRow(columns);
        markdownTable += createSeparatorRow(columns);
        markdownTable += createDataRows(table, columns);

        markdownTables.push(markdownTable);
    }

    console.log(markdownTables.join(NEW_LINE));
    return markdownTables;
}

/**
 * Creates the header row of a markdown table
 */
function createHeaderRow(columns: ExcelScript.TableColumn[]): string {
    return CELL_SEPARATOR + columns.map(column => {
        // Check if the column is hidden
        if (column.getRange().getHidden()) {
            return '';
        }

        return escapeMarkdownSpecialChars(column.getName()) + CELL_SEPARATOR;
    }).join('') + NEW_LINE;
}

/**
 * Creates the separator row of a markdown table
 */
function createSeparatorRow(columns: ExcelScript.TableColumn[]): string {
    return CELL_SEPARATOR + columns.map(column => {
        // Check if the column is hidden
        if (column.getRange().getHidden()) {
            return '';
        }

        return TABLE_SEPARATOR + CELL_SEPARATOR;
    }).join('') + NEW_LINE;
}

/**
 * Creates data rows of a markdown table from an Excel table
 */
function createDataRows(table: ExcelScript.Table, columns: ExcelScript.TableColumn[]): string {
    const dataRows: string[] = [];
    const rowCount = table.getRowCount();

    // The first row of the table contains column names, so start from the second row.
    for (let i = 1; i <= rowCount; i++) {
        // Skip hidden rows
        if (table.getRange().getRow(i).getHidden()) {
            continue; 
        }

        const rowData: string[] = [];
        for (let j = 0; j < columns.length; j++) {
            const cell = table.getRange().getCell(i, j);

            // Check if the cell is hidden
            if (cell.getHidden()) {
                continue;
            }

            rowData.push(createCellText(cell));
        }

        if (rowData.length > 0) {
            dataRows.push(CELL_SEPARATOR + rowData.join(CELL_SEPARATOR) + CELL_SEPARATOR + NEW_LINE);
        }
    }

    return dataRows.join('');
}

/**
 * Creates Markdown-formatted text for a given Excel cell
 */
function createCellText(cell: ExcelScript.Range): string {
    const cellValue = cell.getTexts()[0][0].trim();
    const hyperlink = cell.getHyperlink();
    const format = cell.getFormat().getFont();
    let cellText = escapeMarkdownSpecialChars(cellValue);

    if (cellText) {
        if (format.getBold()) {
            cellText = `${BOLD_FORMAT}${cellText}${BOLD_FORMAT}`;
        }
        if (format.getStrikethrough()) {
            cellText = `${STRIKETHROUGH_FORMAT}${cellText}${STRIKETHROUGH_FORMAT}`;
        }
        if (format.getItalic()) {
            cellText = `${ITALIC_FORMAT}${cellText}${ITALIC_FORMAT}`;
        }
    }

    return hyperlink ? `[${cellText}](${hyperlink.address})` : cellText;
}

/**
 * Escapes special characters within markdown cells
 */
function escapeMarkdownSpecialChars(text: string): string {
    return text.replace(/[\n|*_\\`{}[\]()<>#+\-]/g, match => ESCAPE_CHARACTERS[match]);
}