Create a Custom Automated Report Using Minimal Resources

const SHEET_ID = 'YOUR-SHEET-ID';
const API_KEY = 'GOOGLE-API-KEY';
// Defining ranges from Sheets, using multiple lists in the document we divide the data we wanna use in the final report.
const RANGES = ["'Website Sources'!A2:B11", "'Website Mediums'!A2:B11", "'Website Campaigns'!A2:B11", "'Website Top Pages'!B2:C11", "'Monthly changes'!J1:Q12", "'Monthly changes social media'!H1:M12"]; 
const FETCH_URL = `https://sheets.googleapis.com/v4/spreadsheets/${SHEET_ID}/values:batchGet?ranges=${RANGES.join('&ranges=')}&key=${API_KEY}`;

export default {
    async fetch(request) {
        const url = new URL(request.url);
        const path = url.pathname;  // Extracting the path from URL.
        const id = url.searchParams.get('id'); // Extracting a query parameter from URL.

        // Fetching data from the Sheets API
        const data = await fetch(FETCH_URL);
        const sheetData = await data.json();

        // A helper function to transform data into a simplified object.
        const transformData = (entries, id) => {
            const index = parseInt(id) - 1;
            if (index >= 0 && index < entries.length) {
                return {
                    "name": entries[index][0],
                    "data": parseInt(entries[index][1])
                };
            }
            return null;
        };

        //  Example output of the helper function.
        //  {
        //    "name": "(direct)",
        //    "data": 102
        //  }

        // Splitting the relevant data from the Google Sheets response into separate variables.
        const sourcesData = sheetData.valueRanges[0].values;
        const mediumsData = sheetData.valueRanges[1].values;
        const campaignsData = sheetData.valueRanges[2].values;
        const topPagesData = sheetData.valueRanges[3].values;
        const monthlyChangesData = sheetData.valueRanges[4].values;
        const monthlyChangesDataSM = sheetData.valueRanges[5].values;

        let responseData = null;

        // Defining the responses for each endpoint and path.

        // If path is a 'source', then return only the data from list of top sources.
        if (path === '/source') {
            const sourceData = transformData(sourcesData, id);
            if (sourceData) {
                responseData = {
                    [path]: sourceData
                };
            }

        //  Example output after returning the correct data.
        //  {
        //    "/source": {
        //       "name": "(direct)",
        //       "data": 102
        //    }
        //  }
          
        } else if (path === '/medium') {
            const mediumData = transformData(mediumsData, id);
            if (mediumData) {
                // If path is 'medium', return only the selected data from...
                responseData = {
                    [path]: mediumData
                };
            }
        } else if (path === '/campaign') {
            const campaignData = transformData(campaignsData, id);
            if (campaignData) {
                // If path is 'campaign', return only the selected data from...
                responseData = {
                    [path]: campaignData
                };
            }
        } else if (path === '/toppage') {
            const topPageData = transformData(topPagesData, id);
            if (topPageData) {
                // If path is 'toppages', return only the selected data from...
                responseData = {
                    [path]: topPageData
                };
            }
        } else if (path === '/change') {
            const now = new Date();
            const monthIndex = now.getMonth();
            const monthNames = [
                "January", "February", "March", "April", "May", "June",
                "July", "August", "September", "October", "November", "December"
            ];
        
            const previousMonth = monthNames[(monthIndex - 1 + 12) % 12];
            const index = parseInt(id);
        
            if (isNaN(index) || index < 1 || index > 7) {
                return new Response(JSON.stringify({ error: 'Invalid id. Must be between 1 and 7.' }), { status: 400 });
            }
        
            const headerRow = monthlyChangesData[0];
            const targetRow = monthlyChangesData.find(row => row[0] === previousMonth);
        
            if (targetRow && headerRow && targetRow.length > index) {
                responseData = {
                    [path]: {
                        name: headerRow[index].replace('\n', ' '),
                        data: parseFloat(targetRow[index].replace('%', ''))
                    }
                };
            }

        }   else if (path === '/change-social-media') {
            const now = new Date();
            const monthIndex = now.getMonth();
            const monthNames = [
                "January", "February", "March", "April", "May", "June",
                "July", "August", "September", "October", "November", "December"
            ];
        
            const previousMonth = monthNames[(monthIndex - 1 + 12) % 12];
            const index = parseInt(id);
        
            if (isNaN(index) || index < 1 || index > 7) {
                return new Response(JSON.stringify({ error: 'Invalid id. Must be between 1 and 7.' }), { status: 400 });
            }
        
            const headerRow = monthlyChangesData[0];
            const targetRow = monthlyChangesData.find(row => row[0] === previousMonth);
        
            if (targetRow && headerRow && targetRow.length > index) {
                responseData = {
                    [path]: {
                        name: headerRow[index].replace('\n', ' '),
                        data: parseFloat(targetRow[index].replace('%', ''))
                    }
                };
            } else {
                return new Response(JSON.stringify({ error: 'Data not found for previous month' }), { status: 404 });
            }
        }

        // Setting headers.
        const headers = new Headers();
        headers.set('Access-Control-Allow-Origin', '*');
        headers.set('Access-Control-Allow-Methods', 'GET, PUT, DELETE, OPTIONS');
        headers.set('Access-Control-Allow-Headers', 'Content-Type');
        headers.set('Content-Type', 'application/json');

        // Showing our desired data in the response.
        if (responseData) {
            return new Response(JSON.stringify(responseData), { headers });
        } else {
            return new Response(JSON.stringify({ error: 'Invalid endpoint or id' }), { status: 404, headers });
        }
    },
};

// We are done!

// After calling your-url.workers.dev/source?id=1
{
  "/source": {
    "name": "(direct)",
    "data": 102
  }
}