Back in February, the amazing Open eBooks program began offering thousands of free eBooks to low-income students in America. The New York Public Library developed an e-Reader app that comes loaded with eBooks for students ages 4-18, which can be read without any checkouts or holds. While hundreds of books are already in public domain, the Open eBooks app has popular books such as Dork Diaries and the Haddix series, as well as robust fantasy, action & adventure, and mystery sections.

After logging into the app, a student selects the book they want to read by clicking on it, reads the synopsis, and then selects “Get.” This will download the book to their device.

When they’re finished with the book, they go to “My Books” and then click on “Return now.”

(Almost) all students in my school can download this free app under “Purchased” on their iPad. I say “almost” because there are always issues with a few student iPads that keeps them from downloading apps. For everyone else, they simply need to get the app, and then login with the access code and PIN they received from their English teacher.

For the English teachers…

If you requested it from our librarian, you received a Google Sheets file with unique access codes and PINs that need to be assigned to each student. Now you’re wondering, How can I distribute these in an efficient way?

Luckily I’ve covered this in a past post, so if you read it, you should be good to go. If not, here’s a refresher!

My list of codes looked like this:

While the codes and PINs are blurred out, you may see that the format is:

ACCESS CODE | PIN

as well as a blank row between each code.

From here you can print the codes, cut them out, and then hand them out to students. Alternatively, you can continue to set this worksheet up so that you can just email each student an individual set.

Step 1: Eliminating empty rows

The first thing I wanted to do was eliminate those blank rows. I used the Google Sheet add-on called Power Tools, which, as the name implies, has different tools that you can use to manipulate information in cells.

Step 2: Splitting the access codes and PINS

Now that you’ve eliminated the empty rows, you’ll want to separate the access codes and PINs. The way they’re formatted can be confusing for some students. I plan to email them stating that their access code is ABCDEF123 and their PIN is 2468. This makes it much clearer than

ABCDEF123 | 2468.

(By the way, this is the format that the codes and PINs are delivered to our librarian, so don’t fault her!)

Step 3: Copy and paste the names and emails of your students

You’ll need a list of your students with their emails. Refer back to this post on how to download a list of all student emails from Powerschools.

Next, copy the names and emails from the downloaded Excel file and paste it next to the access codes. You don’t need to make this look pretty, just paste it to the right of the codes.

Step 4: Create a mail merge

In a previous post I discuss how to create a mail merge using the formMule add-on for Google Sheets.

Before you can begin the mail merge, you need to name the columns. Add a row to the beginning of your spreadsheet, and label the Access Code, PIN, student email, and Student Name columns. You’ll need these when using the formMule add-on.

Step 5: Have students log into the Open eBooks app

Now that they have their access codes and PINs, they can log in. They only need to do this once on their iOS or Android device since they stay logged in unless they decide to logout!