Export Qualtrics Data & Analysis Table to Excel Including In-Progress Responses

// ============================================================================
// QUALTRICS DATA TABLE — EXCEL EXPORT TOOLKIT
// Exports the Data & Analysis table (incl. In-Progress responses) to .xlsx
// Supports single-page and full multi-page export with auto-pagination
// Developed by Pirai AI — piraiai.com
// ============================================================================

(function () {

    // ── 1. LOAD SheetJS from CDN ─────────────────────────────────────────────
    function loadSheetJS(callback) {
        if (window.XLSX) { callback(); return; }
        const script = document.createElement('script');
        script.src = 'https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js';
        script.onload = () => { console.log('✅ SheetJS loaded'); callback(); };
        script.onerror = () => console.error('❌ Failed to load SheetJS.');
        document.head.appendChild(script);
    }

    // ── 2. PAGINATION HELPERS ────────────────────────────────────────────────

    function getTotalPages() {
        const pagination = document.querySelector('[data-testid="DataTablePagination"]');
        if (!pagination) return 1;
        const text = pagination.textContent || '';
        const match = text.match(/of\s+(\d+)/);
        return match ? parseInt(match[1], 10) : 1;
    }

    function getCurrentPage() {
        const input = document.querySelector('[data-testid="DataTablePagination-PageInput"]');
        return input ? parseInt(input.value, 10) : 1;
    }

    function isTableLoading() {
        const spinner = document.querySelector('[role="progressbar"][aria-label="Loading"]');
        return spinner && spinner.offsetParent !== null;
    }

    function waitForTableReady(timeout = 8000) {
        return new Promise((resolve, reject) => {
            const start = Date.now();
            const check = () => {
                if (!isTableLoading()) {
                    // Small buffer after spinner disappears for DOM to settle
                    setTimeout(resolve, 400);
                } else if (Date.now() - start > timeout) {
                    reject(new Error('Timed out waiting for table to load'));
                } else {
                    setTimeout(check, 200);
                }
            };
            // Give Qualtrics a moment to show the spinner before polling
            setTimeout(check, 300);
        });
    }

    function clickNextPage() {
        const btn = document.querySelector('[data-testid="DataTablePagination-NextButton"]');
        if (!btn || btn.getAttribute('aria-disabled') === 'true') return false;
        btn.click();
        return true;
    }

    // ── 3. EXTRACT VISIBLE TABLE DATA ────────────────────────────────────────

    function extractVisibleRows(existingHeaders = null) {
        const result = { headers: [], rows: [] };

        if (!existingHeaders) {
            document.querySelectorAll('table thead tr th').forEach(th => {
                const label = th.getAttribute('aria-label') || th.textContent.trim();
                if (label && label !== 'Record Selection') result.headers.push(label);
            });
        } else {
            result.headers = existingHeaders;
        }

        document.querySelectorAll('table tbody tr').forEach(tr => {
            const cells = tr.querySelectorAll('td');
            const rowData = [];
            let skippedFirst = false;

            cells.forEach(td => {
                if (!skippedFirst) { skippedFirst = true; return; }

                const spans = td.querySelectorAll('span');
                if (spans.length > 0) {
                    const parts = Array.from(spans).map(s => s.textContent.trim()).filter(Boolean);
                    rowData.push(parts.join(' '));
                } else {
                    const btn = td.querySelector('button');
                    rowData.push(btn ? btn.textContent.trim() : td.textContent.trim());
                }
            });

            if (rowData.some(cell => cell !== '')) result.rows.push(rowData);
        });

        return result;
    }

    // ── 4. BUILD & DOWNLOAD EXCEL ────────────────────────────────────────────

    function buildAndDownload(headers, allRows) {
        if (headers.length === 0 || allRows.length === 0) {
            console.error('❌ No data to export.');
            return;
        }

        const wsData = [headers, ...allRows];
        const ws = XLSX.utils.aoa_to_sheet(wsData);

        ws['!cols'] = headers.map((h, colIdx) => {
            const maxLen = wsData.reduce((max, row) => {
                const val = row[colIdx] ? String(row[colIdx]) : '';
                return Math.max(max, val.length);
            }, h.length);
            return { wch: Math.min(maxLen + 2, 50) };
        });

        const wb = XLSX.utils.book_new();
        XLSX.utils.book_append_sheet(wb, ws, 'Survey Data');

        const wsMeta = XLSX.utils.aoa_to_sheet([
            ['Export Info', ''],
            ['Exported by', 'Pirai AI Data Table Toolkit'],
            ['Exported at', new Date().toLocaleString()],
            ['Total rows', allRows.length],
            ['Columns', headers.join(', ')]
        ]);
        wsMeta['!cols'] = [{ wch: 20 }, { wch: 60 }];
        XLSX.utils.book_append_sheet(wb, wsMeta, 'Export Info');

        const timestamp = new Date().toISOString().slice(0, 19).replace(/[T:]/g, '-');
        const filename = `qualtrics_data_table_${timestamp}.xlsx`;
        XLSX.writeFile(wb, filename);

        console.log(`\n✅ Exported ${allRows.length} rows × ${headers.length} columns`);
        console.log(`📥 File: ${filename}`);
    }

    // ── 5. SINGLE-PAGE EXPORT ────────────────────────────────────────────────

    window.downloadDataTableExcel = function () {
        console.log('\n🔄 Exporting current page...');
        loadSheetJS(() => {
            const { headers, rows } = extractVisibleRows();
            console.log(`📊 Found ${rows.length} rows, ${headers.length} columns`);
            buildAndDownload(headers, rows);
        });
    };

    // ── 6. MULTI-PAGE EXPORT (AUTO-PAGINATE) ─────────────────────────────────

    window.downloadAllPagesExcel = async function () {
        const totalPages = getTotalPages();
        const startPage = getCurrentPage();

        console.log(`\n╔══════════════════════════════════════════════════════════╗`);
        console.log(`║  Starting full export — ${totalPages} page(s) detected`);
        console.log(`╚══════════════════════════════════════════════════════════╝\n`);

        if (startPage !== 1) {
            console.warn(`⚠️  Not on page 1. Navigate to page 1 first, then re-run.`);
            return;
        }

        loadSheetJS(async () => {
            let allRows = [];
            let headers = null;

            for (let page = 1; page <= totalPages; page++) {
                console.log(`📄 Extracting page ${page} of ${totalPages}...`);

                try {
                    await waitForTableReady();
                } catch (e) {
                    console.warn(`⚠️ Page ${page} load timeout — extracting whatever is visible`);
                }

                const { headers: h, rows } = extractVisibleRows(headers);
                if (!headers) headers = h;

                console.log(`   ↳ ${rows.length} rows collected (running total: ${allRows.length + rows.length})`);
                allRows = allRows.concat(rows);

                if (page < totalPages) {
                    const moved = clickNextPage();
                    if (!moved) {
                        console.warn(`⚠️ Next page button unavailable at page ${page}. Stopping early.`);
                        break;
                    }
                    // Brief pause before polling for spinner
                    await new Promise(r => setTimeout(r, 600));
                }
            }

            console.log(`\n📊 Total rows collected across all pages: ${allRows.length}`);
            buildAndDownload(headers, allRows);
        });
    };

    // ── 7. PREVIEW ───────────────────────────────────────────────────────────

    window.previewDataTable = function () {
        const data = extractVisibleRows();
        console.log(`\n📋 Headers (${data.headers.length}):`, data.headers);
        console.table(
            data.rows.map(row =>
                Object.fromEntries(data.headers.map((h, i) => [h, row[i] || '']))
            )
        );
        return data;
    };

    // ── 8. USAGE GUIDE ───────────────────────────────────────────────────────
    console.log(`
╔══════════════════════════════════════════════════════════════╗
║   QUALTRICS DATA TABLE — EXCEL EXPORT TOOLKIT               ║
║   Pirai AI — piraiai.com                                    ║
╠══════════════════════════════════════════════════════════════╣
║                                                              ║
║  BEFORE RUNNING:                                             ║
║  • Go to Data & Analysis → Data tab                         ║
║  • Apply your filter (e.g. "Responses in progress")         ║
║  • Make sure you are on page 1                              ║
║                                                              ║
║  COMMANDS:                                                   ║
║                                                              ║
║  previewDataTable()         — Preview current page          ║
║  downloadDataTableExcel()   — Export current page as .xlsx  ║
║  downloadAllPagesExcel()    — Export ALL pages as .xlsx ⭐  ║
║                                                              ║
║  downloadAllPagesExcel() reads the "of N" page count,       ║
║  auto-clicks Next Page, waits for each page to fully load,  ║
║  collects all rows, then downloads a single combined .xlsx   ║
║                                                              ║
╚══════════════════════════════════════════════════════════════╝

Ready! Run: downloadAllPagesExcel()
`);

})();