Workbook: Export to CSV with Date Range Parameters (JavaScript)

1
// This script triggers an export job in Sigma and downloads the export once ready.
2
3
// Load environment variables from a specific .env file for configuration
4
require('dotenv').config({ path: 'sigma-api-recipes/.env' });
5
6
// Import the function to obtain a bearer token from the authenticate-bearer module
7
const getBearerToken = require('../get-access-token');
8
9
// Import Axios for making HTTP requests
10
const axios = require('axios');
11
12
// Load necessary modules for file handling
13
const fs = require('fs');
14
const path = require('path');
15
16
// Load use-case specific variables from environment variables
17
const baseURL = process.env.baseURL; // Base URL for the Sigma API
18
const workbookId = process.env.WORKBOOK_ID; // Workbook ID from which to export data
19
const elementId = process.env.ELEMENT_ID; // Element ID within the workbook to target for export
20
21
async function initiateExport(accessToken) {
22
    // Prepare the options for the export request with correct format and filters
23
    const exportOptions = {
24
        elementId: elementId,
25
        format: { type: 'csv' }, // Define the export format
26
        parameters: {
27
            "DateFilter":"min:2024-04-16,max:2024-04-17" // Define the date range for the export
28
        },
29
        runAsynchronously: true // Request the export to run asynchronously
30
    };
31
32
    console.log('Final export options:', JSON.stringify(exportOptions, null, 2));
33
34
    try {
35
        const response = await axios.post(
36
            `${baseURL}/workbooks/${workbookId}/export`,
37
            exportOptions,
38
            { headers: { Authorization: `Bearer ${accessToken}`, 'Content-Type': 'application/json' } }
39
        );
40
        console.log('Export initiated successfully, response:', response.data);
41
        return response.data.queryId; // Extract and return the query ID from the response
42
    } catch (error) {
43
        console.error('Failed to initiate export:', error);
44
        return null;
45
    }
46
}
47
48
async function checkExportReady(queryId, accessToken) {
49
    // Continuously check if the export is ready for download
50
    console.log(`Checking export readiness for queryId: ${queryId}`);
51
    while (true) {
52
        try {
53
            const response = await axios.get(
54
                `${baseURL}/query/${queryId}/download`,
55
                { headers: { Authorization: `Bearer ${accessToken}` }, responseType: 'stream' }
56
            );
57
58
            if (response.status === 200) { // Check if the export is ready
59
                console.log('Export is ready for download.');
60
                return response.data;
61
            } else {
62
                console.log(`Received unexpected status code: ${response.status}`);
63
            }
64
        } catch (error) {
65
            if (error.response && error.response.status === 204) {
66
                // Export not ready yet, wait before retrying
67
                console.log('Export is not ready yet. Waiting to retry...');
68
                await new Promise(resolve => setTimeout(resolve, 10000));
69
            } else {
70
                console.error('Failed to check export status:', error);
71
                return null;
72
            }
73
        }
74
    }
75
}
76
77
async function downloadExport(data, filename) {
78
    // Handle the download of the export file
79
    const filePath = path.join(__dirname, filename);
80
    const writer = fs.createWriteStream(filePath);
81
82
    return new Promise((resolve, reject) => {
83
        data.pipe(writer);
84
        let error = null;
85
86
        writer.on('error', err => {
87
            error = err;
88
            writer.close();
89
            reject(err);
90
        });
91
92
        writer.on('finish', () => {
93
            if (!error) {
94
                console.log(`Export downloaded successfully to: ${filePath}`);
95
                resolve(true);
96
            }
97
        });
98
    });
99
}
100
101
async function exportWorkflow() {
102
    // Main workflow to manage the export process
103
    const accessToken = await getBearerToken();
104
    if (!accessToken) {
105
        console.error('Failed to obtain bearer token.');
106
        return;
107
    }
108
109
    const queryId = await initiateExport(accessToken);
110
    if (!queryId) {
111
        console.error('Failed to initiate export or obtain queryId.');
112
        return;
113
    }
114
115
    const data = await checkExportReady(queryId, accessToken);
116
    if (data) {
117
        await downloadExport(data, 'export.csv');
118
    } else {
119
        console.error('Failed to prepare the export for download.');
120
    }
121
122
    // Forcibly exit the process if the script is still hanging
123
    process.exit(0);
124
}
125
126
exportWorkflow();