4. Writing a more advanced script - jaredhasenklein/FRC-API-for-Google-Sheets GitHub Wiki

This tutorial will walk you through how to write the intermediate.gs script. The script pulls in multiple endpoints and uses Google Sheets formulas to provide per-team data.

Preparing

Determine your variables

Based on the endpoint you select, you will need to define which variables you will include in your code. For this example, we will set variables for :season and :eventCode but set tournamentLevel as a constant at Qualifications

Writing the script

Define your variables, prep the sheet, and prepare to fetch API data

We'll start with code in line with the basic tutorial. To understand more about the following base code, see that tutorial first.

// User-defined variables
const YEAR = '2024'; // Replace with the desired year
const EVENT = 'CAOC'; // Replace with the desired event code

// Your API credentials (replace with your actual credentials)
const API_USERNAME = 'your-api-username';
const API_TOKEN = 'your-api-token';

// Function to fetch API data and begin to parse it
function fetchAndParseData(endpoint, sheetName) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);
  
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  } else {
    sheet.clear();
  }
  
  // Fetch data from API
  var options = {
    'method': 'get',
    'headers': {
      'Authorization': 'Basic ' + Utilities.base64Encode(API_USERNAME + ':' + API_TOKEN)
    }
  };
  
  var response = UrlFetchApp.fetch(endpoint, options);
  var data = JSON.parse(response.getContentText());
  
  // Flatten JSON and write to sheet
  var flattenedData = flattenJSON(data);
  var headers = Object.keys(flattenedData[0]);
  
  // Write headers
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  
  // Write data
  var rowData = flattenedData.map(function(row) {
    return headers.map(function(header) {
      return row[header];
    });
  });
  
  if (rowData.length > 0) {
    sheet.getRange(2, 1, rowData.length, headers.length).setValues(rowData);
  }
  
  // Auto-resize columns
  sheet.autoResizeColumns(1, headers.length);
}

Fetch and Parse Data

In this script, we'll make two calls to the API: one for the score breakdown and one for the match schedule. The Score Breakdown contains all of the great per-robot data (in many games, this includes some action during AUTONOMOUS and ENDGAME). That endpoints does not provide a list of teams, so we'll also need to pull the Match Schedule (so that we can match "Red Robot 1" to whichever team number was in that match/station).

The function we created earlier is function fetchAndParseData(endpoint, sheetName). In this case, we'll put the Score Breakdown and Match Schedule each on their own sheets, named for their endpoint. You can follow this syntax to make a third or subsequent call to the API.

function fetchScoreBreakdown() {
  var endpoint = `https://frc-api.firstinspires.org/v3.0/${YEAR}/scores/${EVENT}/Qualification`;
  fetchAndParseData(endpoint, "Score Breakdown");
}

function fetchMatchSchedule() {
  var endpoint = `https://frc-api.firstinspires.org/v3.0/${YEAR}/schedule/${EVENT}?tournamentLevel=Qualification`;
  fetchAndParseData(endpoint, "Match Schedule");
}

Process the JSON

Next, we'll flatten the JSON to be readable in the sheet. Refer to the basic tutorial for more info.

function flattenJSON(data) {
  var result = [];
  
  var dataArray = data.MatchScores || data.Schedule;
  
  dataArray.forEach(function(item) {
    var flatItem = {};
    Object.keys(item).forEach(function(key) {
      if (typeof item[key] === 'object' && item[key] !== null) {
        Object.keys(item[key]).forEach(function(subKey) {
          if (typeof item[key][subKey] === 'object' && item[key][subKey] !== null) {
            Object.keys(item[key][subKey]).forEach(function(subSubKey) {
              flatItem[key + '_' + subKey + '_' + subSubKey] = item[key][subKey][subSubKey];
            });
          } else {
            flatItem[key + '_' + subKey] = item[key][subKey];
          }
        });
      } else {
        flatItem[key] = item[key];
      }
    });
    result.push(flatItem);
  });
  
  return result;
}

Create additional sheets and set formulas

The data we pull above is a great starting point, but still requires significant manipulation in Google Sheets to make it actionable. In this next section, we will begin to process the data using Google Sheets formulas. You can omit these steps and manipulate the data by hand or with your own formulas, if you wish. These formulas are game-agnostic and should continue to work with different seasons' match breakdowns. However, if certain elements of the API specification change, this functionality may break.

We will start by creating two additional sheets: Simplified and Items. If they already exist, the script will clear their contents. If they do not exist, the script will create the sheets.

  // Create "Simplified" sheet
  var simplifiedSheet = spreadsheet.getSheetByName("Simplified");
  if (!simplifiedSheet) {
    simplifiedSheet = spreadsheet.insertSheet("Simplified");
  } else {
    simplifiedSheet.clear(); // Clear existing content if the sheet already exists
  }

  // Create "Items" sheet
  var itemsSheet = spreadsheet.getSheetByName("Items by Team");
  if (!itemsSheet) {
    itemsSheet = spreadsheet.insertSheet("Items by Team");
  } else {
    itemsSheet.clear(); // Clear existing content if the sheet already exists
  }

Next, we'll set the formulas.

On the Simplified sheet, our goal will be to create a combined sheet which lists the matches in columns A through B, the teams in columns C through H, and the per-robot API data in columns I through the end. The final column will depend on how many per robot fields exist for the game. For example, in 2024, there are two fields: autoLine and endGame, so there are 12 columns (2 field * 6 robots).

We'll pull in the match list simply by arraying the first two columns from the Score Breakdown sheet on the Simplified tab: simplifiedSheet.getRange('A1').setFormula("=ArrayFormula('Score Breakdown'!A:B)");

Then, we'll fill the rest of the column headers (from C1 through the rest of row 1) from the Score Breakdown. This formula looks for any column headers which contain teamNumber (like teams_2_teamNumber) and any fields which end Robot1, Robot2, or Robot3 (like alliances_0_autoLineRobot2 in the 2024 game):

  simplifiedSheet.getRange('C1').setFormula(
    "=ArrayFormula({FILTER('Match Schedule'!1:1, REGEXMATCH('Match Schedule'!1:1, \"teamNumber\")),FILTER('Score Breakdown'!1:1, REGEXMATCH('Score Breakdown'!1:1, \"Robot1|Robot2|Robot3\"))})"
  );

Now, we'll grab the team numbers from the Match Schedule. This will fill in next six columns (column C through H). These are in consistent columns each time, so we can pull them in the order we want using the CHOOSECOLS function:

  simplifiedSheet.getRange('C2').setFormula(
    "=CHOOSECOLS('Match Schedule'!F2:U,1,4,7,10,13,16)"
  );

Next, we'll fill in columns I through the end with any of the per-robot data. We're using the CHOOSECOLS formula again, but because the robot-level data changes with each game, we'll again look for headers which include RobotN and use those columns to dynamically pull the results:

  simplifiedSheet.getRange('I2').setFormula(
    "=CHOOSECOLS('Score Breakdown'!$A$2:$999, FILTER(MATCH(TRANSPOSE(FILTER('Score Breakdown'!1:1, REGEXMATCH('Score Breakdown'!1:1, \"Robot1|Robot2|Robot3\"))), 'Score Breakdown'!$A$1:$1, 0), ISNUMBER(MATCH(TRANSPOSE(FILTER('Score Breakdown'!1:1, REGEXMATCH('Score Breakdown'!1:1, \"Robot1|Robot2|Robot3\"))), 'Score Breakdown'!$A$1:$1, 0))))"
  );

That one was complicated! The next one will be easy.

Now, we'll move on to the Items sheet. On the items sheet, our goal is to have a list of teams and their per-robot actions. Teams will appear multiple times in the list. For example, if team 9999 played in five qualification matches, they will appear in five separate rows with API data for each match they played in a new line.

To start, we'll simply set the value of A1 to the word Team:

    itemsSheet.getRange('A1').setValue(
    "Team"
  );

Now, we'll set additional column headers starting in B1 and continuing for however many columns are needed in row 1. We'll take any of the fields which start alliances_N_ and end RobotN, remove that alliance-specific and robot-specific data, and filter out only the unique values:

  itemsSheet.getRange('B1').setFormula(
    '={TRANSPOSE(UNIQUE(TRANSPOSE(ARRAYFORMULA(REGEXREPLACE(Simplified!I1:1, "alliances_(0|1)_(autoLine|endGame)Robot(1|2|3)", "$2")))))}'
  );

Finally, we'll get the list of teams and their data. This formula is a bit more complicated, combining the logic referenced above with the CHOOSECOLS and REGEXMATCH functions.

Teams are listed differently between the two API models, so we also have to add some logic to account for this.

Friendly Name Score Breakdown Match Schedule
Red 1 alliances_0_Robot1 teams_0
Red 2 alliances_0_Robot2 teams_1
Red 3 alliances_0_Robot3 teams_2
Blue 1 alliances_1_Robot1 teams_3
Blue 2 alliances_1_Robot2 teams_4
Blue 3 alliances_1_Robot3 teams_5

Finally, we sort by team number. Here's the big formula:

 itemsSheet.getRange('A2').setFormula(
    '=SORT(({CHOOSECOLS(Simplified!2:99999,(FILTER(COLUMN(Simplified!1:1), REGEXMATCH(Simplified!1:1, "^alliances_0.*Robot1$") + (Simplified!1:1 = "teams_0_teamNumber")))); CHOOSECOLS(Simplified!2:99999,(FILTER(COLUMN(Simplified!1:1), REGEXMATCH(Simplified!1:1, "^alliances_0.*Robot2$") + (Simplified!1:1 = "teams_1_teamNumber")))); CHOOSECOLS(Simplified!2:99999,(FILTER(COLUMN(Simplified!1:1), REGEXMATCH(Simplified!1:1, "^alliances_0.*Robot3$") + (Simplified!1:1 = "teams_2_teamNumber")))); CHOOSECOLS(Simplified!2:99999,(FILTER(COLUMN(Simplified!1:1), REGEXMATCH(Simplified!1:1, "^alliances_1.*Robot1$") + (Simplified!1:1 = "teams_3_teamNumber")))); CHOOSECOLS(Simplified!2:99999,(FILTER(COLUMN(Simplified!1:1), REGEXMATCH(Simplified!1:1, "^alliances_1.*Robot2$") + (Simplified!1:1 = "teams_4_teamNumber")))); CHOOSECOLS(Simplified!2:99999,(FILTER(COLUMN(Simplified!1:1), REGEXMATCH(Simplified!1:1, "^alliances_1.*Robot3$") + (Simplified!1:1 = "teams_5_teamNumber"))))}), 1, TRUE)'
  );

}

Combining it all together

Since we have multiple functions, we'll create one main function with all the others inside of it, so we can execute the whole script in one trigger:

function fetchDataAndCreateSheets() {
  fetchScoreBreakdown();  // This calls fetchAndParseData for score breakdown
  fetchMatchSchedule();   // This calls fetchAndParseData for match schedule
  createProcessingsheets();
}

Finally, we'll add a menu item to easily execute the script. When the sheet opens, a new menu labeled FRC API will appear:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('FRC API')
      .addItem('Fetch Data and Create Sheets', 'fetchDataAndCreateSheets')
      .addToUi();
}

Executing the Script

Finally, it is time to save your work and execute the project!

  1. Click the Save icon.
  2. Click the Play icon to execute the script.
  3. If prompted, authorize the script using your Google Account.

Attentively, use the menu item you created to execute the script from the user interface!

Note: the first time you authorize the script, you may need to execute it a second time for it to fully run.