Firebase cloud function that will run every saturday at midnight. The goal is to gather all of the necessary KPIs that we have logged with events in our application

gatherApis.js

const {google} = require('googleapis');
const ExcelJS = require('exceljs');
const {Storage} = require('@google-cloud/storage');

exports.scheduledFunction = functions.pubsub.schedule('0 0 * * 6').onRun(async (context) => {
    // Initialize Google Analytics API
    const analytics = google.analyticsreporting('v4');

    // Fetch data from Google Analytics
    const response = await analytics.reports.batchGet(/* ... */);

    // Process response to get data for KPIs
    const kpiData = processResponse(response);

    // Create an Excel workbook
    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet('KPI Report');

    // Populate the workbook with KPI data
    populateWorkbook(sheet, kpiData);

    // Write workbook to a buffer
    const buffer = await workbook.xlsx.writeBuffer();

    // Initialize Google Cloud Storage
    const storage = new Storage();
    const bucketName = 'your-bucket-name';
    const filename = `KPI_Report_${new Date().toISOString()}.xlsx`;

    // Save to Google Cloud Storage
    const file = storage.bucket(bucketName).file(filename);
    await file.save(buffer);

    console.log(`Report saved to ${filename} in bucket ${bucketName}`);
});

function processResponse(response) {
    // Initialize an array to hold processed KPI data
    let kpiData = [];

    // Assuming response is structured with rows of data
    response.data.rows.forEach(row => {
        // Extract data from the row
        const eventName = row.dimensions[0]; // Example: 'sign_up'
        const eventCount = row.metrics[0].values[0]; // Example: count of 'sign_up'
        const dateRange = `${row.startDate} to ${row.endDate}`;

        // Add to KPI data array
        kpiData.push({
            eventName,
            totalCount: parseInt(eventCount, 10),
            dateRange
        });
    });

    return kpiData;
}


function populateWorkbook(sheet, kpiData) {
    // Populate the Excel sheet with KPI data
    // Add column headers
    sheet.columns = [
        { header: 'Event', key: 'event', width: 10 },
        { header: 'Total Count', key: 'count', width: 10 },
        { header: 'Date Range', key: 'dateRange', width: 20 }
    ];

    // Add rows for each KPI
    kpiData.forEach(kpi => {
        sheet.addRow({
            event: kpi.eventName,
            count: kpi.totalCount,
            dateRange: kpi.dateRange
        });
    });
}