Google Looker Studio Simple Activity Counts

In this code snippet we use the list_activities and list_responses API endpoints. The snippet first pulls a list of activities and fetches their UID, title, start_date, and end_date and populates those in the spreadsheet. Next it inspects the responses of those activities and fetches counts of complete, incomplete, and analysed responses.

function fetchResponseCounts() {
  Logger.log("=== Starting fetchResponseCounts ===");
  
  try {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getActiveSheet();
    Logger.log("Spreadsheet accessed");

    const CITIZEN_SPACE_URL = "your_citizenspace_url.com";
    const API_KEY = "your_key";
    const API_SECRET = "your_secret";
    
    const credentials = API_KEY + ":" + API_SECRET;
    const encodedCredentials = Utilities.base64Encode(credentials);
    
    const options = {
      "method": "GET",
      "headers": {
        "Authorization": "Basic " + encodedCredentials,
        "Accept": "application/json"
      },
      "muteHttpExceptions": true
    };

    Logger.log("Clearing sheet...");
    sheet.clear();
    sheet.appendRow([
      "Activity UID", 
      "Title", 
      "Start Date", 
      "End Date",
      "Complete Responses", 
      "Incomplete Responses", 
      "Analyzed Responses"
    ]);
    
    Logger.log("Fetching activities...");
    const activitiesResponse = UrlFetchApp.fetch(CITIZEN_SPACE_URL + "/api/1/activities", options);
    
    if (activitiesResponse.getResponseCode() !== 200) {
      throw new Error("Failed to fetch activities: " + activitiesResponse.getContentText());
    }
    
    const activities = JSON.parse(activitiesResponse.getContentText());
    Logger.log(`Found ${activities.length} activities`);

    // Process in batches of 10 to avoid timeouts
    const batchSize = 10;
    for (let i = 0; i < activities.length; i += batchSize) {
      const batch = activities.slice(i, i + batchSize);
      Logger.log(`Processing batch ${Math.floor(i/batchSize) + 1} of ${Math.ceil(activities.length/batchSize)}`);
      
      for (const activity of batch) {
        Logger.log(`Processing activity: ${activity.title}`);
        
        const responseUrl = `${CITIZEN_SPACE_URL}/api/1/activities/${activity.uid}/responses`;
        const responsesResponse = UrlFetchApp.fetch(responseUrl, options);
        
        if (responsesResponse.getResponseCode() === 200) {
          const responsesData = JSON.parse(responsesResponse.getContentText());
          
          let completeCount = 0;
          let incompleteCount = 0;
          let analyzedCount = 0;
          
          responsesData.forEach(response => {
            if (response.completed && !response.deleted) completeCount++;
            else if (!response.completed && !response.deleted) incompleteCount++;
            else if (response.deleted) analyzedCount++;
          });
          
          Logger.log(`${activity.title}: Complete=${completeCount}, Incomplete=${incompleteCount}, Analyzed=${analyzedCount}`);
          
          // Format dates for readability - if they exist
          const startDate = activity.start_date ? new Date(activity.start_date).toLocaleDateString() : 'Not set';
          const endDate = activity.end_date ? new Date(activity.end_date).toLocaleDateString() : 'Not set';
          
          sheet.appendRow([
            activity.uid,
            activity.title,
            startDate,
            endDate,
            completeCount,
            incompleteCount,
            analyzedCount
          ]);
        } else {
          Logger.log(`Failed to fetch responses for ${activity.uid}: ${responsesResponse.getContentText()}`);
          sheet.appendRow([
            activity.uid,
            activity.title,
            'Error',
            'Error',
            "Error",
            "Error",
            "Error"
          ]);
        }
        
        // Add a small delay between requests
        Utilities.sleep(100);
      }
      
      // Add a longer delay between batches
      if (i + batchSize < activities.length) {
        Logger.log("Pausing between batches...");
        Utilities.sleep(1000);
      }
    }
    
    Logger.log("=== Script completed successfully ===");
    
  } catch (e) {
    Logger.log("Error: " + e.message);
    Logger.log("Stack: " + e.stack);
    throw e;
  }
}