OTT-o-matic Google Console – Change Username

A long time ago I wrote a script to help a school district move from to a more privacy focused email address. They really did not want to give companies the students name simply by entering an email address. So I wrote the script and it has sat dormant for years. Well today is the day to share a refined tool that simply allows a list of usernames to be re-named and to remove the alias that keeps the old email address active. The script is quite simple, but effective. Feel free to use and modify as needed. Below is a copy of the script, but I have prepared a file for easy use. Just click here to see the template and simply the process of updating usernames.

Google Script used

//Authored by Jeran Ott. Ed.D.

//Note: Requires Superadmin and enabling “Admin Directory API” in “Resources Menu” under “Advance Google Service”

function changeUsername() {
var ui = SpreadsheetApp.getUi();
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(“ChangeUsername”);

//How many students by counting column A
var column = “A”;
var lastRow = sheet.getMaxRows();
var values = sheet.getRange(column + “1:” + column + lastRow).getValues();
for (; values[lastRow – 1] == “” && lastRow > 0; lastRow–) {}
var StuCount = lastRow – 1;
ui.alert(StuCount + ‘ students listed’);

//Updates each users email address
for(i=0; i<StuCount; i++) {
try{var existingEmail = sheet.getRange(i+2, 3).getValue();
var newEmail = sheet.getRange(i+2, 4).getValue();
var user = AdminDirectory.Users.get(existingEmail);
user.primaryEmail = newEmail;
AdminDirectory.Users.update(user, existingEmail);
sheet.getRange(i+2,5).setValue(“Email Changed”);

//Remove old Alias so that the original email address no longer works
AdminDirectory.Users.Aliases.remove(newEmail, existingEmail);
sheet.getRange(i+2,6).setValue(“Alias Removed”);
SpreadsheetApp.flush();<br> } catch(e) {ui.alert(“Problem renaming “+ existingEmail)}

ui.alert(“All Done!”);

Combining multiple Google Forms assessments using Google Sheets

A fellow Google Certified Trainer asked the following:

“A teacher I am working with has created module tests in Google Forms. The forms are all linked to one Google Sheet which creates a different tab for each module test. She wants to be able to track progress for everyone on one tab. Ideally, she would like to be able to see how many module tests each student has completed and what their scores were to see a summary of each student’s progress without having to look in each tab (i.e. Johnny: module 1 75%, module 2 90%, etc). Is there a way to do this?”

Formulas you will need

For this solution (get your copy of the Spreadsheet here) I utilized the formulas ARRAYFORMULA(), if(), Query(), Sort() and Unique(). The use of {;} was used to combine the data from the multiple module tabs.

Steps to creating this solution

First of all I made two fake forms that feed into two module tabs. Each uses an if() statement wrapped by an arrayformula() [ =ARRAYFORMULA(if(A2:A<>””,1,””) for module 1 and =ARRAYFORMULA(if(A2:A<>””,2,””) for module 2 ] to automatically check if column A has a value, and if it does, place the module number in that column (I figured the assessment may not automatically say what module it is from).

Module 1 Tab
Module 2 Tab

The on a new Tab I use a combining statement { array1;array2} wrapped by a unique() statement and sort() statement to create a sorted list of the auto-collected email addresses which will allow you to sort by student in a later step. The formula used is =sort(unique({Module1!B2:B;Module2!B2:B})).

Combined student email list

In the 4th Tab I combined the two queries together using the statement {query(Module1!A2:G,”Select A,B,F,G”,0);query(Module2!A2:G,”Select A,B, E, F “,0)}. Additional modules would be added by insert insert ” ;array3″. You must be careful to make sure that the queries pull in the same values. I have pulled Timestamp, Email, Score and Module, and these are not necessarily in the same columns on the different assessment tabs. I also did not include the headers in the queries, they were manually inserted.

Combining Modules into one list

Suggestion: At this point I would suggest using my Google Sheet add-on “Student Name Import” which uses your Google Classroom People and inserts a new column with the students name. Great for schools that use the “” style email address. Click here to learn more.

The last step is to create a query page with a drop down menu. First, use Data Validation to make a drop down list of the students emails (or you could use the names if you utilize Student Name Import). Next, create your header row that will be above your query. Finally, use a query to pull the information from the combined report, but restrict the list to the selected student. The formula use in this solution is =query(‘Combined Modules’!A4:D, “Select A, B, C, D where B = ‘”&B1&”‘ “,0) which pulls Columns A, B, C and D only where B is equal to the value in the drop-down list. Typically you might use a query with “where B = ‘’ “, the trick is to replace ‘’ with ‘”&B1&”‘ (B1 represent the location for the drop-down list) allowing you to create a single query that isolates based on the student.

Google Sheets is amazingly powerful. I use these techniques for many of the resources on my website. Takes a little time to learn them, but you will truly benefit by learning these skills.

Side-by-Side Translate – Newsletter

Teachers often need to communicate home with parents, but do you communicate in a way that parents can read? In this post I am sharing how to use Side-by-Side Translate to make your Classroom Newsletter more accessible to parents.

Creating a newsletter in Google Docs is easy. The example below started with on of the newsletter templates that are built in. I did change the margins to 0.3 in order to give lots of space for the side-by-sdie translations

Using Side-by-Side Translate you can easily transform any newsletter. The example below in translated into Arabic. We have many families in our district that do not have mastery of English, so this would be better suited to reach out to them. Once you have completed the translation, feel free to make any edits to the format that would improve the look of your newsletter.

If you are interested in Side-by-Side Translate or want to learn more click here.