Create A Sports Team Roster From Google Docs Template Using Google Sheets Data

August 7, 2024 • Google Apps Script, Google Docs, Google Sheets • Published By Josh • 8 minute read

In this article, you’ll learn how to get player information from a Google Sheet and create a roster with it based on a Google Doc template! You’ll also learn how to create a custom menu in the spreadsheet to make it easy to create the roster. As a bonus, you’ll also learn how to email the roster as an attached pdf!

Roster Template Setup
Spreadsheet Setup
Script Editor
Get Players
Style A Google Docs Table
Create Roster
Custom Menu
Email Roster

Roster Template Setup

We need a template to base our roster on. Let’s start by creating a new Google Doc. You can make it look however you want. The important piece is where we want the table of player information to appear. Decide where that will be and type the following text in that location:

{{Players}}

Later, we will look for this specific text and replace it with the actual table of player information.

Spreadsheet Setup

Create a sheet called Players.
Create the following columns:
- Jersey
- First Name
- Last Name

Script Editor

Let’s start writing some code! Google Sheets has a handy script editor available.

Navigate to Extensions > Apps Script.
The script editor will include a starting function. You can remove all the code.
Navigate to File > Save. Give the script project a name and select Ok.

Get Players

Let’s create a function to return all of our player information. Create a function called getPlayers.

function getPlayers() {

}

Within the getPlayers function, create a variable called rows and get all of the rows of data from the sheet called Players.

const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Players').getDataRange().getValues();

The Jersey column has numbers. Even if you format the column to be just numbers, no decimal places, it will still return a decimal. We don’t want to see jersey numbers with decimals in our final roster! To keep it simple, we’ll just convert every column value in every row to a string. Create a variable called data and map every column value in every row to a string.

const data = rows.map(row => row.map(val => val.toString()));

All that is left to do is return the data.

return data;

Here’s the final code for getPlayers.

function getPlayers() {
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Players').getDataRange().getValues();
  const data = rows.map(row => row.map(val => val.toString()));
  return data;
}

Style A Google Docs Table

We want a nice compact looking table with minimal padding. We’ll have to override the default table style with our own. Create a function called styleTable with an argument being passed in called table.

function styleTable(table) {

}

Within the styleTable function, create a variable called rows and get the number of rows for the passed in table.

const rows = table.getNumRows();

Styling involves defining an object where each property name is an attribue and the value is the new value to be applied. We want new styles for the column headings and each cell. Create two variables called headingStyle and cellStyle and assign empty objects to them.

const headingStyle = {};
const cellStyle = {};

We want the column headings to be bold and the top and bottom padding of each cell to be smaller. Add the following:

headingStyle[DocumentApp.Attribute.BOLD] = true;
cellStyle[DocumentApp.Attribute.PADDING_TOP] = 0.025;
cellStyle[DocumentApp.Attribute.PADDING_BOTTOM] = 0.025;

Apply the heading style to the first row of the table.

table.getRow(0).setAttributes(headingStyle);

Apply the cell style to every cell. We have to loop through each row and cell in the table to apply it.

for (let r = 0; r < rows; r++) {
  const cols = table.getRow(r).getNumChildren();
  for (let c = 0; c < cols; c++) {
    const cell = table.getRow(r).getCell(c);
    cell.setAttributes(cellStyle);
  }
}

Here’s the final code for styleTable.

function styleTable(table) {
  const rows = table.getNumRows();
  const headingStyle = {};
  const cellStyle = {};

  // Define custom styling
  headingStyle[DocumentApp.Attribute.BOLD] = true;
  cellStyle[DocumentApp.Attribute.PADDING_TOP] = 0.025;
  cellStyle[DocumentApp.Attribute.PADDING_BOTTOM] = 0.025;
  
  // Apply column heading style
  table.getRow(0).setAttributes(headingStyle);

  // Apply cell style
  for (let r = 0; r < rows; r++) {
    const cols = table.getRow(r).getNumChildren();
    for (let c = 0; c < cols; c++) {
      const cell = table.getRow(r).getCell(c);
      cell.setAttributes(cellStyle);
    }
  }
}

Create Roster

We are now ready to create the roster based on the template and player information! Create a function called createRoster.

function createRoster() {

}

Within the createRoster function, create a variable called ss, which will represent the spreadsheet. We will use a function provided by this object later.

const ss = SpreadsheetApp.getActiveSpreadsheet();

Remember the function we created earlier to get player information? We’re ready to use it! Create a variable called players and call the getPlayers function.

const players = getPlayers();

We need a folder to store the newly created roster. Pick a folder of your choice and get the folder’s id, which can be found in the url:

https://drive.google.com/drive/u/0/folders/FOLDER_ID_HERE

Create a variable called folder and get the folder by its id.

const folder = DriveApp.getFolderById('FOLDER_ID_HERE');

Remember the roster template we created earlier? We need that file’s id, which can be found in the url:

https://docs.google.com/document/d/FILE_ID_HERE/edit

Create a variable called template and get the file by its id.

const template = DriveApp.getFileById('FILE_ID_HERE');

Create a variable called templateCopy and make a copy of the template, give it a name, and store it in the folder we chose.

const templateCopy = template.makeCopy('Team Roster', folder);

Create a variable called doc to get the newly created doc by its id.

const doc = DocumentApp.openById(templateCopy.getId());

Create a variable called body to get the actual body of the document.

const body = doc.getBody();

Remember the text we typed in the template for the location of where we wanted the player information to appear? Let’s look for that in the body and call it playersPlaceholder.

const playersPlaceholder = body.findText('{{Players}}');

If we find the playersPlaceholder, we need to get its element, find where it’s located in the body and remove it, then insert the table of player information in its place. We also want to style the table using the styleTable function we created earlier.

if (playersPlaceholder) {
  const playersElement = playersPlaceholder.getElement();
  const playersIndex = body.getChildIndex(playersElement.getParent());

  body.getChild(playersIndex).removeFromParent();

  const playersTable = body.insertTable(playersIndex, players);
  styleTable(playersTable);
}

Let’s save and close the document.

doc.saveAndClose();

To show something happened in the spreadsheet, we can show a message to the user. We’ll show a different message depending on if there’s actually any players defined on the Players sheet. We check for a length greater than 1 because the first entry is the column headings.

if (players.length > 1) {
  ss.toast('Roster has been created!', 'Create Roster');
} else {
  ss.toast('No players defined.', 'Create Roster');
}

Here’s the final code for createRoster.

function createRoster() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const players = getPlayers();

  const folder = DriveApp.getFolderById('FOLDER_ID_HERE');
  const template = DriveApp.getFileById('FILE_ID_HERE');

  const templateCopy = template.makeCopy('Team Roster', folder);
  const doc = DocumentApp.openById(templateCopy.getId());
  const body = doc.getBody();

  const playersPlaceholder = body.findText('{{Players}}');

  if (playersPlaceholder) {
    const playersElement = playersPlaceholder.getElement();
    const playersIndex = body.getChildIndex(playersElement.getParent());

    body.getChild(playersIndex).removeFromParent();

    const playersTable = body.insertTable(playersIndex, players);
    styleTable(playersTable);
  }

  doc.saveAndClose();

  if (players.length > 1) {
    ss.toast('Roster has been created!', 'Create Roster');
  } else {
    ss.toast('No players defined.', 'Create Roster');
  }
}

Custom Menu

Let’s create a custom menu so we can create the roster from the spreadsheet’s menu toolbar instead!

The best time to create a custom menu is when the spreadsheet first opens. Use the onOpen trigger, which is executed when the spreadsheet is first opened.

Add a custom menu to the spreadsheet called Roster. Selecting the Roster menu will display a menu option called Create. Selecting Create will run the function called createRoster!

function onOpen() {
  const ui = SpreadsheetApp.getUi();

  ui.createMenu('Roster')
    .addItem('Create', 'createRoster')
    .addToUi();
}

Email Roster

If you’d like to email the roster as a pdf attachment, here’s a function for that. Replace EMAIL_ADDRESS_HERE with the email address you want it to be sent to. If you want to delete the copy of the roster that was created in the folder, that line of code is also included in the function. You can remove it if you don’t want that to happen.

function emailRoster(doc) {
  const htmlBody = '<p>Attached is your team roster!</p>';

  if (MailApp.getRemainingDailyQuota() > 0) {
    MailApp.sendEmail('EMAIL_ADDRESS_HERE', 'Team Roster', '', { attachments: [doc.getAs('application/pdf')], htmlBody: htmlBody });
  }

  // Delete the document
  DriveApp.getFileById(doc.getId()).setTrashed(true);
}

All that is left to do is add this function within the createRoster function right after saving and closing the doc.

doc.saveAndClose();

emailRoster(doc);

Have fun creating your sports team roster!

About the Author

Josh

Front End Developer

https://nightwolf.dev