Month: September 2018

Popular sheets formulas

Just a collection of formulas I use from time-to-time in Google Sheets.

Query two sheets to find rows with a cell matching another cell’s value.

In this case, I was looking up employee type (Admin, teacher, support) in column I (Col9) by finding the email address (Col2) that corresponds to the email address in the current sheet’s column (K) and row (2).

=QUERY(IMPORTRANGE({"##### URL of first Google spreadsheet with data . ####","AllFaculty!A$2:M");>IMPORTRANGE("##### URL of second Google spreadsheet with data . ####","AllAdmin!A$2:M")},"Select Col9 where Col2 CONTAINS '"&K2&"'")

Get Unique PowerSchool parent email addresses from mother, father and guardian email fields.

We export parent email addresses out of PowerSchool using the cnt1_email, cnt2_email and guardianemail fields from the Students table. This often results in duplicates as cnt1 & cnt2 are often both in the guardian field and sometimes even the cnt1 & 2 fields have 2 addresses separated by a comma. To get the unique values, we use this little gem which assumes the three exported fields land in columns D, E, & F.

=TRANSPOSE(UNIQUE(QUERY(TRANSPOSE({IF(D2<>"",SPLIT(LOWER(SUBSTITUTE(D2," ","")),","),""),IF(E2<>"",SPLIT(LOWER(SUBSTITUTE(E2," ","")),","),""),IF(F2<>"",SPLIT(LOWER(SUBSTITUTE(F2," ","")),","),"")}),"SELECT Col1 WHERE Col1 <> ''")))

If that’s entered in column G, then columns G-J will have only the unique addresses for each student.

If you just want all the unique email addresses for all parents, just use this in column K:

=SORT(UNIQUE({G:G;H:H;I:I;J:J}))

INDEX(MATCH): An Improvement Over VLOOKUP

Google Sheet’s VLOOKUP returns undesired results when lookup values are not present in the source data. Combining INDEX with MATCH resolves the issue.

=INDEX(TabName!C:C,MATCH(B2,TabName!B:B,0))

will find the value in column C of tab TabName of the first row where the value in B2 matches a value in column B of TabName.

MacOS Missing Admin Account

We’ve had a few student and faculty MacBooks somehow  having Standard user accounts and no Administrator accounts. After scouring the Internet, I’ve found the following steps to work well. Removing the .AppleSetupDone file makes the Mac think it hasn’t finished the startup sequence and forces it to startup as new – selecting location, logging into WiFi, agreeing to terms and conditions, etcetera, including creating a new Admin user account.
Don’t stress out when you restart the computer and it looks like a brand new machine. Your current Standard account will still be there when you’ve finished.

  1. Reboot into Single user mode by pressing and holding Cmd-S at startup.
  2. At the root# prompt, type:
    mount -uw /
    rm /var/db/.AppleSetupDone
  3. Reboot
  4. Go through the startup process. Feel free to skip signing into an Apple Account. Create a new account with a name like Administrator. You can delete this account later.
  5. Once the computer completes its startup, in System Preferences, select Users & Groups.
  6. Click the lock and enter the (new) Admin account’s password.
  7. Click on your Standard account and check the “Allow user to administer this computer” checkbox.
  8. Log out and log into your formerly Standard account which is now an Admin account.
  9. If you wish, go back into Users & Groups and delete the Admin account you created in step #4.