Read in data from a sheet

Why?

Google Apps scripts can be used to automate all kinds of things, like enrolling students onto courses in Classroom to sending out bulk emails. However, all these things require data to iterate through, e.g. a list of names or email addresses. These are most easily made available by putting them into a spreadsheet in Sheets, where Apps Script can read them when it needs them.

How?

Paste the following code into your Apps Script editor:

function getStudentsFromSheet() {
  const sheet = SpreadsheetApp.getActiveSheet();
  let studentEmails = sheet.getDataRange().getValues().flat();
  return studentEmails.map(a => a.toLowerCase());
}

Notes

The first line assumes that the email addresses are on the currently selected sheet of the spreadsheet. This is safe when there is only one sheet in the spreadsheet, but if there are multiple sheets you can replace it with

SpreadsheetApp.getActive().getSheetByName('Name-of-the-sheet');

The second line assumes the sheet contains nothing except a single column of data (the email addresses) and therefore just selects all cells with data (.getDataRange()) and then reads in the values (.getValues()). If the sheet has a table with multiple columns, you can instead specify the exact cells to read:

sheet.getRange(2, 2, sheet.getLastRow(), 1).getValues().flat();

This takes four parameters: row, column, numRows, numColumns. The first two are the index (starting at 1) of the top-left most cell in the range. The second two are the number of rows and columns to read in. The above will start in cell B2, as if the email addresses are in the second column and there is a column heading in Row 1. It only selects a single column, but it will go down to the last row.

When the data is read from the sheet it is stored as an array of rows and each element of that array is an array of the columns for each row, ie [[A1, B1, C1, …], [A2, B2, C2, …], [A3, B3, C3, …]]. Because we know that we have only read a single column, an array of arrays is unnecessarily complicated, e.g. [[A1],[A2],[A3],…]. The .flat() method at the end of the line converts this to just [A1, A2, A3, …].

Finally, the .map converts all of the values to lower case, which is just a very basic clean of the data.

Of course, if you do actually need all of the data from the sheet, it is quicker and easier to just read everything in once and use javascript to extract the part required:

const sheet = SpreadsheetApp.getActiveSheet();
let studentData = sheet.getDataRange().getValues();
studentData.shift();
let studentEmails = studentData.map(a => a[1]);
studentEmails = studentEmails.map(a => a.toLowerCase());

The .shift() removes the first element of the array of arrays, which is the header row of the table of data.

The (first) .map then extracts the second element of the subarray (array indexing, unlike row and column indexing, starts at 0, so the [1] means the 2nd element) from each element of the full array.

The full studentData array remains available for other routines in the script, if they need other data from the sheet.