Google Sheets Integration

Prerequisites

• An active premium subscription to Auto Rosters
• An API key (you can create one in your API Keys section)
• A Google account with access to Google Sheets
• Your roster page ID (found in the URL of your roster page)

Step 1: Create a New Google Sheet

1. Go to Google Sheets and sign in with your Google account
2. Create a new blank spreadsheet
3. Rename it to something meaningful like "Roster Data" or "Auto Rosters Sync"
4. Rename the first sheet to "RosterData" (this is the default sheet name used in the script)

Step 2: Open the Apps Script Editor

1. In your Google Sheet, click on "Extensions" in the top menu
2. Select "Apps Script" from the dropdown menu
3. This will open the Google Apps Script editor in a new tab
4. You'll see a default script file named "Code.gs"
5. Delete any code that might be there by default

Step 3: Add the Sync Script

Copy and paste the following code into the Apps Script editor:

/**
* Example Data (replace with your actual values).
*/
function runUpdateRoster() {
    const apiKey = 'YOUR_API_KEY_HERE'; //Define API Key here
    const sheetName = 'RosterData'; // Replace with your sheet name
    const pageId = 'YOUR_PAGE_ID_HERE'; // Replace with the desired page ID
  
    updateRoster(sheetName, pageId, apiKey);
}
/**
 * Fetches data from the AutoRosters API and updates a Google Sheet.
 *
 * @param {string} sheetName The name of the sheet to update.
 * @param {string} pageId The page ID to fetch data from.
 * @param {string} apiKey The API key for authentication.
 */
function updateRoster(sheetName, pageId, apiKey) {
    const apiUrl = `https://autorosters.com/api/roster/${pageId}/users`;
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
      sheetName
    );
  
    if (!sheet) {
      Logger.log(`Sheet "${sheetName}" not found.`);
      return;
    }
  
    const options = {
      method: 'get',
      headers: {
        'X-API-Key': apiKey,
      },
    };
  
    try {
      const response = UrlFetchApp.fetch(apiUrl, options);
      const data = JSON.parse(response.getContentText());
  
      // Clear existing data (optional, if you want to overwrite)
      sheet.clearContents();
  
      // Define Headers
      const headers = [
        'Name',
        'Discord ID',
        'Discord Username',
        'Rank',
        'Command',
        'Certifications',
        'Subdivisions',
        'Promotion Date',
        'Promotion Officer',
        'Patrol Hours',
      ];
      sheet.appendRow(headers);
  
      // Process and write data to the sheet
      data.forEach((item) => {
        const rowData = [
          item.name,
          item.discord_id,
          item.discord_username,
          item.rank,
          item.is_command ? 'Yes' : 'No',
          item.certifications ? item.certifications.join(', ') : '',
          item.subdivisions ? item.subdivisions.join(', ') : '',
          item.promotion_date,
          item.promotion_officer,
          convertPatrolHours(item.patrol_hours), // Ensure patrol_hours is handled
        ];
        sheet.appendRow(rowData);
      });
  
      Logger.log('Sheet updated successfully.');
    } catch (error) {
      Logger.log(`Error: ${error}`);
    }
  }
  
  /**
   * Converts patrol hours (number) to HH:MM format. Handles cases where
   * patrol_hours is missing from the API response.
   *
   * @param {number} hours The patrol hours as a decimal number.
   * @return {string} The formatted time string (HH:MM).  Returns "N/A" if hours
   *     is not present in the API response.
   */
  function convertPatrolHours(hours) {
    if (hours === null || hours === undefined) {
      return 'N/A'; // Patrol hours are disabled or not available
    }
  
    if (typeof hours !== 'number') {
      Logger.log(`Unexpected patrol_hours type: ${typeof hours}. Value: ${hours}`);
      return 'N/A'; // Handle unexpected data types
    }
  
    const wholeHours = Math.floor(hours);
    const minutes = Math.round((hours - wholeHours) * 60);
    const formattedMinutes = minutes < 10 ? '0' + minutes : minutes;
    return `${wholeHours}:${formattedMinutes}`;
  }

Step 4: Configure Your Script

You need to modify three variables near the top of the script with your own information:

1. apiKey: Your Auto Rosters API key
2. sheetName: The name of your sheet (default is "RosterData")
3. pageId: Your roster page ID (found in the URL of your roster page)

function runUpdateRoster() {
    const apiKey = 'YOUR_API_KEY_HERE'; // Replace with your actual API key
    const sheetName = 'RosterData'; // Make sure this matches your sheet name
    const pageId = 'YOUR_PAGE_ID_HERE'; // Replace with your actual page ID
    
    updateRoster(sheetName, pageId, apiKey);
}

Step 5: Save and Test Your Script

1. Click the disk icon or press Ctrl+S (Cmd+S on Mac) to save your script
2. Name your project something meaningful like "Auto Rosters Sync"
3. To test your script, click the "Run" button (play icon) or select the runUpdateRoster function and click Run
4. The first time you run the script, you will need to authorize it to access your Google Sheets and make external requests
5. Follow the prompts to grant the necessary permissions
6. Check your Google Sheet - it should now be populated with your roster data

Step 6: Set Up Automatic Updates

You can configure the script to run automatically at regular intervals:

1. In the Script editor, click on the "Triggers" icon (it looks like a clock) in the left sidebar
2. Click the "+ Add Trigger" button
3. Configure the trigger as follows:
   • Choose which function to run: runUpdateRoster
   • Choose which deployment should run: Head
   • Select event source: Time-driven
   • Select type of time based trigger: Minute timer (recommended)
   • Choose interval: Every minute (or select another interval)
4. Click "Save"
5. You will be prompted to authorize the script to access your Google account - grant the necessary permissions

Troubleshooting

Common Issues and Solutions

Advanced Customization

The script provided is a starting point. Here are some ideas for customization:

• Add formatting to your Google Sheet (colors, fonts, etc.)
• Create pivot tables or charts based on the imported data
• Modify the script to import only specific data fields
• Create multiple sheets for different roster pages
• Set up email notifications when updates occur

For more advanced customization, refer to the Google Apps Script documentation.

Need Help?

If you encounter any issues setting up your Google Sheets integration, please join our Discord server and open a support ticket.