3. Writing your first script - jaredhasenklein/FRC-API-for-Google-Sheets GitHub Wiki
This tutorial will walk you through how to write the basics.gs script. The script allows you to choose an endpoint of your choice and pull the results into a spreadsheet.
Preparing
Determine your endpoint
View the API Docs to determine which endpoint you want to pull. For this example, we will be pulling the SCORE DETAILS
endpoint located at https://frc-api.firstinspires.org/v3.0/:season/scores/:eventCode/:tournamentLevel?matchNumber=&start=&end=
.
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
We'll create variables called YEAR
and EVENT
for the specific query we're running. We will also define our API_USERNAME
and API_TOKEN
as well as declare our API_ENDPOINT.
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';
// API endpoint (replace with your desired endpoint)
const API_ENDPOINT = `https://frc-api.firstinspires.org/v3.0/${YEAR}/scores/${EVENT}/Qualification`;
Prep the sheet
First, we'll need to clear the current spreadsheet (so make sure you don't have any important data there!) to make room for the API response:
function fetchAndWriteData() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
sheet.clear();
Fetch API data
Next, we will use the GET
method to fetch data from the API. We'll establish our Authorization header by combining the API_USERNAME
and API_TOKEN
. Then, we'll assemble the request by fetching the API_ENDPOINT
with the header (and any other future options you may add).
var options = {
'method': 'get',
'headers': {
'Authorization': 'Basic ' + Utilities.base64Encode(API_USERNAME + ':' + API_TOKEN)
}
};
var response = UrlFetchApp.fetch(API_ENDPOINT, options);
Parse the response
Next, we will take the response from the API fetch (which are returned as a JSON and parse them. We will also take headers from the JSON and define them so that we can use them as headers on a spreadsheet next.
var data = JSON.parse(response.getContentText());
// Flatten JSON and write to sheet
var flattenedData = flattenJSON(data);
var headers = Object.keys(flattenedData[0]);
Now, we can take the parsed JSON and write to the spreadsheet -- first the headers, and then the content. This script uses our current spreadsheet and starts at row 1, column 1.
// 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);
}
For ease of readability, we'll then format the columns to be the right size for the data:
// Auto-resize columns
sheet.autoResizeColumns(1, headers.length);
}
Executing the Script
Finally, it is time to save your work and execute the project!
- Click the Save icon.
- Click the Play icon to execute the script.
- If prompted, authorize the script using your Google Account.
Note: the first time you authorize the script, you may need to execute it a second time for it to fully run.