GA4.gs

// Analytics.gs - Fixed GA4 API integration with centralized auth
function listGA4Properties() {
  try {
    if (!isAuthenticated()) {
      return {
        success: false,
        error: 'Not authenticated with Google Analytics',
        requiresAuth: true
      };
    }
    
    var token = getAccessToken(); // Use centralized token access
    
    // Get accounts first
    var accountsUrl = 'https://analyticsadmin.googleapis.com/v1beta/accounts';
    var accountsResponse = UrlFetchApp.fetch(accountsUrl, {
      headers: {
        'Authorization': 'Bearer ' + token,
        'Content-Type': 'application/json'
      },
      muteHttpExceptions: true
    });
    
    if (accountsResponse.getResponseCode() !== 200) {
      Logger.log('Accounts API error: ' + accountsResponse.getResponseCode() + ' - ' + accountsResponse.getContentText());
      return {
        success: false,
        error: 'Failed to fetch GA4 accounts: ' + accountsResponse.getResponseCode()
      };
    }
    
    var accountsData = JSON.parse(accountsResponse.getContentText());
    var accounts = accountsData.accounts || [];
    
    if (accounts.length === 0) {
      return {
        success: false,
        error: 'No Google Analytics accounts found'
      };
    }
    
    // Get properties for all accounts
    var allProperties = [];
    
    for (var i = 0; i < accounts.length; i++) {
      var accountId = accounts[i].name;
      var propertiesUrl = 'https://analyticsadmin.googleapis.com/v1beta/properties?filter=parent:' + accountId;
      
      try {
        var propertiesResponse = UrlFetchApp.fetch(propertiesUrl, {
          headers: {
            'Authorization': 'Bearer ' + token,
            'Content-Type': 'application/json'
          },
          muteHttpExceptions: true
        });
        
        if (propertiesResponse.getResponseCode() === 200) {
          var propertiesData = JSON.parse(propertiesResponse.getContentText());
          if (propertiesData.properties) {
            allProperties = allProperties.concat(propertiesData.properties);
          }
        } else {
          Logger.log('Properties API error for account ' + accountId + ': ' + propertiesResponse.getResponseCode());
        }
      } catch (propError) {
        Logger.log('Error fetching properties for account ' + accountId + ': ' + propError.toString());
      }
    }
    
    return {
      success: true,
      properties: allProperties,
      accounts: accounts
    };
    
  } catch (error) {
    Logger.log('Error listing GA4 properties: ' + error.toString());
    return {
      success: false,
      error: error.toString()
    };
  }
}

function fetchGA4Report(propertyId, startDate, endDate, dimensionsStr, metricsStr, limit) {
  try {
    if (!isAuthenticated()) {
      return {
        success: false,
        error: 'Not authenticated with Google Analytics',
        requiresAuth: true
      };
    }
    
    var token = getAccessToken(); // Use centralized token access
    var url = 'https://analyticsdata.googleapis.com/v1beta/properties/' + propertyId + ':runReport';
    
    // Parse and clean dimensions
    var dimensions = [];
    if (dimensionsStr) {
      var dimArray = dimensionsStr.split(',');
      var uniqueDims = [];
      
      for (var i = 0; i < dimArray.length; i++) {
        var dim = dimArray[i].trim();
        if (dim && uniqueDims.indexOf(dim) === -1) {
          uniqueDims.push(dim);
          dimensions.push({ name: dim });
        }
      }
    }
    
    // Parse and clean metrics
    var metrics = [];
    if (metricsStr) {
      var metArray = metricsStr.split(',');
      var uniqueMets = [];
      
      for (var i = 0; i < metArray.length; i++) {
        var met = metArray[i].trim();
        if (met && uniqueMets.indexOf(met) === -1) {
          uniqueMets.push(met);
          metrics.push({ name: met });
        }
      }
    }
    
    if (metrics.length === 0) {
      return {
        success: false,
        error: 'At least one metric is required'
      };
    }
    
    var requestBody = {
      dateRanges: [{
        startDate: startDate,
        endDate: endDate
      }],
      dimensions: dimensions,
      metrics: metrics,
      limit: Math.min(limit || 1000, 10000),
      orderBys: [{
        metric: {
          metricName: metrics[0].name
        },
        desc: true
      }]
    };
    
    Logger.log('Making GA4 API request to: ' + url);
    Logger.log('Request body: ' + JSON.stringify(requestBody));
    
    var response = UrlFetchApp.fetch(url, {
      method: 'POST',
      headers: {
        'Authorization': 'Bearer ' + token,
        'Content-Type': 'application/json'
      },
      payload: JSON.stringify(requestBody),
      muteHttpExceptions: true
    });
    
    Logger.log('GA4 API response code: ' + response.getResponseCode());
    
    if (response.getResponseCode() !== 200) {
      var errorText = response.getContentText();
      Logger.log('GA4 API error response: ' + errorText);
      
      var errorData;
      try {
        errorData = JSON.parse(errorText);
      } catch (parseError) {
        errorData = { error: { message: 'Unknown API error: ' + errorText } };
      }
      
      return {
        success: false,
        error: errorData.error ? errorData.error.message : 'GA4 API request failed',
        details: errorText
      };
    }
    
    var reportData = JSON.parse(response.getContentText());
    
    return {
      success: true,
      data: reportData,
      rowCount: (reportData.rows || []).length,
      propertyId: propertyId,
      dateRange: { start: startDate, end: endDate }
    };
    
  } catch (error) {
    Logger.log('Error fetching GA4 report: ' + error.toString());
    return {
      success: false,
      error: error.toString()
    };
  }
}

function getAvailableDimensions() {
  return [
    { name: 'date', displayName: 'Date', category: 'Time', description: 'The date of the event' },
    { name: 'year', displayName: 'Year', category: 'Time', description: 'The year of the event' },
    { name: 'month', displayName: 'Month', category: 'Time', description: 'The month of the event' },
    { name: 'week', displayName: 'Week', category: 'Time', description: 'The week of the event' },
    { name: 'day', displayName: 'Day', category: 'Time', description: 'The day of the event' },
    { name: 'hour', displayName: 'Hour', category: 'Time', description: 'The hour of the event' },
    
    { name: 'country', displayName: 'Country', category: 'Geography', description: 'The country of origin' },
    { name: 'region', displayName: 'Region', category: 'Geography', description: 'The geographic region' },
    { name: 'city', displayName: 'City', category: 'Geography', description: 'The city of origin' },
    
    { name: 'deviceCategory', displayName: 'Device Category', category: 'Technology', description: 'Device category (desktop, mobile, tablet)' },
    { name: 'browser', displayName: 'Browser', category: 'Technology', description: 'The web browser used' },
    { name: 'operatingSystem', displayName: 'Operating System', category: 'Technology', description: 'The operating system' },
    { name: 'mobileDeviceModel', displayName: 'Mobile Device Model', category: 'Technology', description: 'The mobile device model' },
    
    { name: 'pagePath', displayName: 'Page Path', category: 'Content', description: 'The page path portion of the URL' },
    { name: 'pageTitle', displayName: 'Page Title', category: 'Content', description: 'The page title' },
    { name: 'landingPage', displayName: 'Landing Page', category: 'Content', description: 'The first page in a session' },
    
    { name: 'sessionSource', displayName: 'Session Source', category: 'Acquisition', description: 'The traffic source' },
    { name: 'sessionMedium', displayName: 'Session Medium', category: 'Acquisition', description: 'The traffic medium' },
    { name: 'sessionCampaign', displayName: 'Session Campaign', category: 'Acquisition', description: 'The campaign name' },
    { name: 'googleAdsAdGroupName', displayName: 'Ad Group Name', category: 'Acquisition', description: 'Google Ads ad group name' },
    
    { name: 'eventName', displayName: 'Event Name', category: 'Events', description: 'The name of the event' },
    { name: 'customEvent', displayName: 'Custom Event', category: 'Events', description: 'Custom event parameter' }
  ];
}

function getAvailableMetrics() {
  return [
    { name: 'activeUsers', displayName: 'Active Users', category: 'Users', description: 'Users who had an engaged session' },
    { name: 'newUsers', displayName: 'New Users', category: 'Users', description: 'Users who interacted for the first time' },
    { name: 'totalUsers', displayName: 'Total Users', category: 'Users', description: 'Total number of users' },
    { name: 'userEngagementDuration', displayName: 'User Engagement Duration', category: 'Users', description: 'Total engagement time' },
    
    { name: 'sessions', displayName: 'Sessions', category: 'Sessions', description: 'Number of sessions' },
    { name: 'bounceRate', displayName: 'Bounce Rate', category: 'Sessions', description: 'Percentage of single-page sessions' },
    { name: 'averageSessionDuration', displayName: 'Average Session Duration', category: 'Sessions', description: 'Average session length' },
    { name: 'sessionsPerUser', displayName: 'Sessions per User', category: 'Sessions', description: 'Average sessions per user' },
    
    { name: 'screenPageViews', displayName: 'Page Views', category: 'Page/Screen', description: 'Number of page views' },
    { name: 'screenPageViewsPerSession', displayName: 'Page Views per Session', category: 'Page/Screen', description: 'Average page views per session' },
    { name: 'averageTimeOnPage', displayName: 'Average Time on Page', category: 'Page/Screen', description: 'Average time on page' },
    
    { name: 'eventCount', displayName: 'Event Count', category: 'Events', description: 'Total number of events' },
    { name: 'eventCountPerUser', displayName: 'Event Count per User', category: 'Events', description: 'Average events per user' },
    { name: 'eventsPerSession', displayName: 'Events per Session', category: 'Events', description: 'Average events per session' },
    
    { name: 'conversions', displayName: 'Conversions', category: 'Conversions', description: 'Number of conversion events' },
    { name: 'totalRevenue', displayName: 'Total Revenue', category: 'E-commerce', description: 'Total revenue' },
    { name: 'purchaseRevenue', displayName: 'Purchase Revenue', category: 'E-commerce', description: 'Revenue from purchases' },
    { name: 'itemRevenue', displayName: 'Item Revenue', category: 'E-commerce', description: 'Revenue from items' },
    { name: 'itemsPurchased', displayName: 'Items Purchased', category: 'E-commerce', description: 'Number of items purchased' }
  ];
}

function validateReportConfiguration(config) {
  var errors = [];
  
  if (!config.propertyId) {
    errors.push('GA4 Property is required');
  }
  
  if (!config.metrics || config.metrics.length === 0) {
    errors.push('At least one metric is required');
  }
  
  if (config.metrics && config.metrics.length > 10) {
    errors.push('Maximum 10 metrics allowed');
  }
  
  // Check for duplicate metrics
  if (config.metrics && config.metrics.length > 0) {
    var uniqueMetrics = [];
    var duplicates = [];
    
    for (var i = 0; i < config.metrics.length; i++) {
      var metric = config.metrics[i];
      if (uniqueMetrics.indexOf(metric) !== -1) {
        if (duplicates.indexOf(metric) === -1) {
          duplicates.push(metric);
        }
      } else {
        uniqueMetrics.push(metric);
      }
    }
    
    if (duplicates.length > 0) {
      errors.push('Duplicate metrics found: ' + duplicates.join(', '));
    }
  }
  
  if (config.dimensions && config.dimensions.length > 9) {
    errors.push('Maximum 9 dimensions allowed');
  }
  
  // Check for duplicate dimensions
  if (config.dimensions && config.dimensions.length > 0) {
    var uniqueDimensions = [];
    var duplicateDims = [];
    
    for (var i = 0; i < config.dimensions.length; i++) {
      var dimension = config.dimensions[i];
      if (uniqueDimensions.indexOf(dimension) !== -1) {
        if (duplicateDims.indexOf(dimension) === -1) {
          duplicateDims.push(dimension);
        }
      } else {
        uniqueDimensions.push(dimension);
      }
    }
    
    if (duplicateDims.length > 0) {
      errors.push('Duplicate dimensions found: ' + duplicateDims.join(', '));
    }
  }
  
  if (!config.dateRange || !config.dateRange.type) {
    errors.push('Date range is required');
  }
  
  if (config.dateRange && config.dateRange.type === 'custom') {
    if (!config.dateRange.startDate || !config.dateRange.endDate) {
      errors.push('Start date and end date are required for custom date range');
    }
    
    if (config.dateRange.startDate && config.dateRange.endDate) {
      var startDate = new Date(config.dateRange.startDate);
      var endDate = new Date(config.dateRange.endDate);
      
      if (startDate > endDate) {
        errors.push('Start date must be before end date');
      }
    }
  }
  
  var limit = parseInt(config.limit);
  if (isNaN(limit) || limit < 1 || limit > 10000) {
    errors.push('Row limit must be between 1 and 10,000');
  }
  
  return {
    isValid: errors.length === 0,
    errors: errors
  };
}

function testGA4Connection() {
  try {
    if (!isAuthenticated()) {
      return {
        success: false,
        error: 'Not authenticated',
        requiresAuth: true
      };
    }
    
    var propertiesResult = listGA4Properties();
    if (!propertiesResult.success) {
      return propertiesResult;
    }
    
    if (propertiesResult.properties.length === 0) {
      return {
        success: false,
        error: 'No GA4 properties found. Make sure you have access to at least one GA4 property.'
      };
    }
    
    return {
      success: true,
      message: 'Successfully connected to Google Analytics',
      propertyCount: propertiesResult.properties.length
    };
    
  } catch (error) {
    Logger.log('Error testing GA4 connection: ' + error.toString());
    return {
      success: false,
      error: error.toString()
    };
  }
}

// Enhanced sheet writing with better formatting
function writeReportToSheet(reportData, sheetName) {
  try {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getSheetByName(sheetName);
    
    if (!sheet) {
      sheet = spreadsheet.insertSheet(sheetName);
    } else {
      sheet.clear();
    }
    
    // Build headers
    var headers = [];
    
    if (reportData.dimensionHeaders) {
      for (var i = 0; i < reportData.dimensionHeaders.length; i++) {
        headers.push(reportData.dimensionHeaders[i].name);
      }
    }
    
    if (reportData.metricHeaders) {
      for (var j = 0; j < reportData.metricHeaders.length; j++) {
        headers.push(reportData.metricHeaders[j].name);
      }
    }
    
    // Build data rows
    var rows = [];
    
    if (reportData.rows) {
      for (var k = 0; k < reportData.rows.length; k++) {
        var row = reportData.rows[k];
        var rowData = [];
        
        // Add dimension values
        if (row.dimensionValues) {
          for (var l = 0; l < row.dimensionValues.length; l++) {
            var dimValue = row.dimensionValues[l].value;
            // Try to parse as number if it looks like one
            if (/^\d+$/.test(dimValue)) {
              rowData.push(parseInt(dimValue));
            } else if (/^\d*\.\d+$/.test(dimValue)) {
              rowData.push(parseFloat(dimValue));
            } else {
              rowData.push(dimValue);
            }
          }
        }
        
        // Add metric values
        if (row.metricValues) {
          for (var m = 0; m < row.metricValues.length; m++) {
            var metricValue = row.metricValues[m].value;
            // Parse as number
            if (/^\d*\.?\d+$/.test(metricValue)) {
              rowData.push(parseFloat(metricValue));
            } else {
              rowData.push(metricValue);
            }
          }
        }
        
        rows.push(rowData);
      }
    }
    
    // Write headers
    if (headers.length > 0) {
      var headerRange = sheet.getRange(1, 1, 1, headers.length);
      headerRange.setValues([headers]);
      
      // Format headers
      headerRange.setFontWeight('bold');
      headerRange.setBackground('#f0f0f0');
      headerRange.setBorder(true, true, true, true, true, true);
    }
    
    // Write data
    if (rows.length > 0 && headers.length > 0) {
      var dataRange = sheet.getRange(2, 1, rows.length, headers.length);
      dataRange.setValues(rows);
      
      // Auto-resize columns
      for (var col = 1; col <= headers.length; col++) {
        sheet.autoResizeColumn(col);
      }
      
      // Add borders to data
      dataRange.setBorder(true, true, true, true, true, true);
    }
    
    // Add metadata
    var metadataRow = sheet.getMaxRows() + 2;
    sheet.getRange(metadataRow, 1).setValue('Report generated:');
    sheet.getRange(metadataRow, 2).setValue(new Date());
    sheet.getRange(metadataRow + 1, 1).setValue('Total rows:');
    sheet.getRange(metadataRow + 1, 2).setValue(rows.length);
    
    // Make the sheet active
    sheet.activate();
    
    return {
      success: true,
      sheetName: sheetName,
      rowCount: rows.length,
      columnCount: headers.length
    };
    
  } catch (error) {
    Logger.log('Error writing report to sheet: ' + error.toString());
    return {
      success: false,
      error: error.toString()
    };
  }
}
function startAuthentication() {
    google.script.run
        .withSuccessHandler(function(authUrl) {
            if (authUrl) {
                var authWindow = window.open(authUrl, '_blank', 'width=500,height=600');
                var checkAuth = setInterval(function() {
                    try {
                        if (authWindow.closed) {
                            clearInterval(checkAuth);
                            setTimeout(function() {
                                google.script.run
                                    .withSuccessHandler(function(isAuth) {
                                        if (isAuth) {
                                            checkAuthentication();
                                            loadAllReports();
                                            showToast('Successfully connected to Google Analytics!', 'success');
                                        } else {
                                            showToast('Authentication failed or was cancelled.', 'error');
                                        }
                                    })
                                    .withFailureHandler(handleError)
                                    .isAuthenticated();
                            }, 1000);
                        }
                    } catch (e) {
                        clearInterval(checkAuth);
                    }
                }, 1000);
            }
        })
        .withFailureHandler(handleError)
        .getAuthUrl();
}