Author: Paul Ericksen

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.

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.

 

I use a Codiad instance on my webserver to host my coding projects. Unfortunately, I had neglected to save the password for one of my accounts and Codiad has no password recovery method built in. However, as long as you can access the data/users.php file on the server, you can change a user’s password to
0da4aea08df1536477acc5ae8a1a6ab73a307c44
This is the hashed value for the password “welcome”. That is, once changed for that user, they log in with their username and password “welcome”.

That’s it. Just remember to change the password once logged in.

Unfortunately, the Spreadsheet Tasker Plugin used in this post is no longer maintained and no longer works. I leave this post here in case anyone comes across an alternative that they are able to share.

I really like my Project Fi (from Google) cellular service. It works in nearly every country I travel to and I don’t need to buy a new SIM card every time I go back to the homeland in the summer. But Project Fi is limited to US phone numbers. Sadly, doing anything online in the Republic of Korea requires a local mobile phone number. I tried getting a Korean Skype-In number, but alas, it is unable to receive SMS messages and begins with a 070 prefix, not the 010 (used for mobile phones) that’s required by my bank.

I finally resolved to purchase a local prepaid SIM card to pop into an old cell phone just to receive SMS messages. A nice young gal at the SK Telecom shop in Gangnam graciously accepted my lacking proficiency in Korean and set me up with a SIM card. For just ₩50,000 I will be able to receive SMS messages for the next 12 months. However, I don’t want to tote around another phone, obliged to keep it charged for those infrequent occasions it will be needed. So, I resolved to leave the phone on and permanently plugged in at home, only to forward received sms messages to another device.

After some hours of work, I finally settled on using a Tasker event to post incoming SMS messages to a Google Spreadsheet with the Spreadsheet Tasker Plugin and have the updated spreadsheet trigger an email to myaddress@kis.or.kr – just in case something came through that I wasn’t expecting.

How to do it.

1. First, install Tasker and the Spreadsheet Tasker Plugin and create a blank spreadsheet called “SMS Messages” in your Google Drive.
2. On your Android phone receiving SMS messages, open Tasker and click the + icon in the bottom-right to create a new profile.
3. Select Event > Phone > Received Text
4. Click the Back button (←) and select New Task.
5. Give it a reasonable name like “Update SMS Spreadsheet” and click the check (✓) icon in the bottom-right.
6. Click the plus (+) sign to add an action.
7. Select Plugin > Spreadsheet Tasker Plugin > Spreadsheet Update
8. Click the Configuration (pencil) icon in the upper-right corner.
9. Click Spreadsheet and navigate to the SMS Messages sheet you created in step 1 and click “Select”.
10. Click the “Cells” line and enter the following:
    %SMSRF;%SMSRN;%SMSRB;%MMSRD;%SMSRT
    This adds the sender’s number, name, message text, MMS content (if any) and the time it was received in an hours.minutes format such that 20.35 means 8:35Pm.
11. Here you can scroll down and click “Save” and then “Test”. You can check your spreadsheet and should see a new line appended to it. At the moment, it prolly has the variables that you entered above. But if you send yourself an SMS, you should start seeing the message data appear in the sheet within 10-20 seconds. At this point, if you know you’re going to receive an SMS, you can just open that spreadsheet and see it as it’s received. If it doesn’t, double check the steps above and let me know if you need help.
12. If you want to receive an email notification whenever the sheet receives an SMS message, just set up the notification rules in the spreadsheet (Tools > Notification rules > Add notification rule > Any changes are made and > Email – right away) .
13. BONUS: I also just set it up to notify me if I miss a call to that number. I created another spreadsheet (though I prolly coulda used the same) and selected Missed Call instead of Received Text and modified the variables to:
    %CNAME;%CNUM;%CDATW;%CTIME

1. From the main PowerSchool page, select desired students
2. In the left menu, click “PowerScheduler”
3. Near the bottom of the left menu, select “Functions”
4. Near the bottom of the list, select “Update selection”
5. Verify that the Current Table is the Students table and the “Current Records in Selection” matches the desired number of students
6. Do not change anything else on this page
7. To the right of “Current Records…”, select “Modify Records”
8. Verify that the “Current Table” is Students and the number of students in your selection is correct
9. In the dropdown, select “Sched_NextYearTeam”
10. Verify that you’ve selected “Sched_NextYearTeam” and not something similar. You will be making changes directly to the database
11. Enter the Team Number (like 2933) not the Team Name in the entry box
    • NOTE: you can see the team numbers corresponding to team names by clicking on Teams under Scheduling Setup in the left menu
12. Verify that you’ve entered the correct team number, selected Sched_NextYearTeam and your selected records number looks correct
13. Repeat step #12
14. Click the blue “Modify Selected Records”