Featured

Welcome

Hello and welcome to Mr Ericksen’s home on the web. This isn’t so much a portfolio of my work as it is a place for me to archive my projects and processes while providing a convenient way for me to share them with others who may be interested in using them.

If you’re just browsing around, Technotes are tech-related projects I’ve worked on as an IT Director and EdSys Admin, Travel Hacks is a collection of things I’ve found helpful during since moving abroad in 2006, and Pro Tips are just some short tips for making daily life a little less stressful.

Featured

Air Quality Monitoring on a Corporate Scale

South Korea doesn’t have the worst air in the world, but it’s also no Chippewa Falls, Wisconsin. Recognizing that very bad air days are a threat to the health of our students and staff, our school developed a systematic plan to monitor the air we breath and to filter bad air when necessary. As part of this effort, several staff, faculty and students worked together to build a network of air quality monitoring stations around campus to collect timely data, share current data with stakeholders, and record data long-term for research and future planning. These devices were surprisingly accurate except during times of high humidity when the SDS011’s readings become unreliable. Ultimately, the school’s brass decided to go with a commercially available system for it’s needs. But I have preserved the following explanation of how we built the devices and installed the software to disseminate current data and stored data for future use.

We built a variation of the Sensor.Community’s project. This uses the Plantar SDS011 PM2.5/PM10 particulate matter detector, BME280 temperature/humidity/pressure monitor, the WEMOS D1 Mini ESP8266 development board and a super small breadboard to bring it all together. Note that these items can also be commonly found virtually anywhere that sells microelectronics components and they’re inexpensive. The SDS011 sensor is around $17, the others are a few dollars each.

Setup is fairly straightforward. We attach headers to the Wemos D1 Mini and the BME280. Then we connect the 5V pin of the D1 to the 5V pin of the SDS011,

Wiring Connections:

Wemos D1 MiniSDS011BME280
5V5V
3.3VVIN
D1TXD
D2RXD
D3SDA
D4SCL
GNDGND
Sensor wiring connections

Once everything is connected, we’ll want to flash the Wemos D1 Mini’s code. Visit the Sensor.Community’s download page to retrieve the appropriate version. Connect the Wemos D1 to your computer, and run the flashing program to set it up. If your computer does not recognize the Wemos D1, you may need to install the driver to see it. The driver page is mostly in Chinese, so you will need to look closely to discern which version is needed for your OS.

Once flashed, you’ll need to reboot the device by disconnecting from your computer and then reconnecting – either to your computer or another micro-USB power source.

The first time it restarts, it won’t know how to connect to your local WiFi, so it creates its own WiFi network. On your computer, look for a new SSID that begins with Feinstaubsensor-ID or airRohr-ID followed by a chip ID such as 13597771. Connect to that SSID and note the chip ID as it will be needed later. Once connected, point your browser to 192.168.4.1 and you should see the device setup page. From here, you can select your preferred language, enter your local WiFi network name and password, and identify the sensors connected to the device. Be sure to click the “Zurük zur Starseite” button to save the settings and restart the device.

There are are a couple ways to check your sensor readings. You can access your device directly on your local network if you can identify and visit its local ip address. Alternatively, you can create an account at https://sensor.community to conveniently access your device data online. You’ll need the Chip ID you wrote down earlier to find your sensors. But once your account is set up, you’ll be easily able to bookmark your device to see your local air quality, temperature and humidity conditions.

Featured

Bulk Editing Google Docs with a Script

We used Google Docs for elementary progress reports. A spreadsheet and Autocrat let me create all the files and even drop them into folders by homeroom class. But that’s not what this is about.

After teachers started entering grades, the principal noticed that the grading key was missing some information. The easy fix would have been to recreate the docs and have the teachers start over. But this would have created extra work for teachers on one of their least enjoyable tasks.

A quick Google search led to a script that would allow me to edit the documents en masse. Not surprisingly, the starter script came to me via Amit at https://labnol.org. It takes the folder path containing the docs to be edited, the “search” text and the “replace” text. Then it goes through all the docs in the folder, makes a backup and then does the search/replace in each doc.

Amit’s script is effective but limited to a basic find/replace in the body text of Google Docs in one folder.  I expanded it to allow formatting text in addition to find/replace in Google Doc headers, bodies, and footers across multiple folders. You can find my script here.

Just as I expanded Amit’s script, when you’re ready to expand mine, visit the Class Text section of Google Apps Script tutorials for more options.

Featured

Roughneck Totes as Checked Baggage

Rubbermaid’s 25 gallon Roughneck Tote is the best container to use as checked baggage when transporting a lot of stuff via intercontinental flights. The length + width + height dimensions are the maximum allowed by airlines, maximizing the volume per piece of baggage, they stack nicely on airport carts and the handles make loading and unloading convenient. I have used this method since 2008, moving back and forth from the U.S. to Kuwait, Nigeria and South Korea, taking a couple dozen of these totes on several dozen flights. I’ve never had a problem and never lost a tote or lid.

They’re available at most Ace Hardware stores. On Amazon.com, the shipping charges are prohibitive. But if you visit my favorite hometown Blain’s Farm and Fleet, you can get 4 of them shipped just about anywhere in the US for around $20 each. While you’re there, throw in your 14″ zip ties, colored duct tape, packing tape, and Sharpie.

The only challenge is closing them up to remain sealed during travel. But, there is an easy way of handling this. I use a 5/16″ drill bit (8mm) to put 12 holes strategically placed around the lid which accept plastic zip-ties or 16 gauge solid-core copper wire to keep the tote sealed.

It’s important to put the holes in the right place so that if you use several totes, any lid will be able to be used on any tote with the lid and tote holes lining up perfectly. Check out my video for the details:

Featured

Get notification when Autocrat fails to run

We have several leave request forms that get processed by the Autocrat Add-on. Inevitably, Autocrat seems to get stuck and fails to run. This is an issue for us when staff are submitting forms expecting that they will be acted upon while our crack team of support staffers remain unaware that the requests have been submitted.

Whenever Autocrat runs, a note logging successful completion is written into the cell in the last row, last column of this spreadsheet. If this cell is blank, Autocrat has failed to execute. So, I came up with this little script. It checks the form responses spreadsheet’s last row and column every 6 hours (adjustable). If that cell is empty, indicating an Autocrat failure, an email is sent to me which looks like this:

Click here for the script I use to do this.

PowerSchool Contract Tracing with DDE

Suppose a student tests positive for COVID-19. Here’s how we quickly identify potentially exposed classmates. There’s a sample spreadsheet here that you can use. Just make a copy, follow the steps below and paste the export into cell A1 of the PS_DataDump tab. If that link breaks, you can download an Excel version here.

  1. In DDE, select the CC Table
  2. Search for current TermID and StudentID
  3. Switch over to Sections Table
  4. Search for Sections in current term
  5. Match Selection on CC table
  6. Switch to CC Table
  7. Select all enrollments from current term
  8. Match Selection with Sections Table
  9. Export Records with the following records:

[1]Student_Number
[1]ID
SectionID
Course_Number
Expression
[2]Course_Name
[1]First_Name
[1]Last_Name
[1]Grade_Level
[1]Gender
[1]Home_Room
[183]Student_EMail
[5]First_Name
[5]Last_Name
[5]Email_Addr
[1]MotherDayPhone
[1]FatherDayPhone
[1]cnt1_email
[1]cnt2_email
[1]GuardianEmail

Password Protecting Student Reports

In our ever increasing efforts to protect student confidentiality and personal information, we password protect student report cards and test results when emailing them to parents. This helps protect the information in the unlikely event that an email gets sent to the incorrect address.

To do this, we generate the reports from PowerSchool or Google Docs, typically in a Firstname Lastname grade # Progress Report.pdf format. These are put into a folder (in this case, the folder is ES_T3_PDFs) there is also a filedata.csv file that has each file’s password in the first column, the original filename in column 8, and the new filename in column 9.

The python script below then runs, opens each report, creates a new file object, password protects it, and writes it to a new folder (Secure_ES_T3_PDFs).

import PyPDF2
import csv
import sys
#Open csv with password,filename,newfilename
c = open('filedata.csv', 'r')
# Create a reader object to store the data in filedata.csv
reader = csv.reader(c, delimiter=',')
# Process each row of data
count = 0
for row in reader:
    # The password located in the first column
    password = str(row[0])
    # The current (original) filename in "firstname lastname grade # Progress Report - Student_Number" format
    currFileName = row[7]
    # New filename is the same as original but without the Student_Number 
    newFileName = row[8]
    # Skip the header row
    if (password != "Password"): # Skip the first row with "Password" in first column
        # print row # every 10th row - just to monitor progress
        if (count % 10 == 0):
            print(count)
        # Open non-encrypted file
        pdfFile = open("PasswordProtect/ES_T3_PDFs/"+currFileName, 'rb')
        #coverLetter = open("PasswordProtect/coverLetter.pdf", 'rb')
        # Create reader and writer objects
        pdfReader = PyPDF2.PdfFileReader(pdfFile)
        #pdfReader02 = PyPDF2.PdfFileReader(coverLetter)
        pdfWriter = PyPDF2.PdfFileWriter()
        # The next 2 lines put the welcome letter at the beginning of the new file
        #print("Inserting Cover Letter")
        #for pageNum in range(pdfReader02.numPages):
        #    pdfWriter.addPage(pdfReader02.getPage(pageNum))
        # Add all pages to writer for each page in input file, add it to the output file
        for pageNum in range(pdfReader.numPages):
            pdfWriter.addPage(pdfReader.getPage(pageNum))
        # Encrypt with password
        pdfWriter.encrypt(password)
        # Write it to an output file
        resultPdf = open("PasswordProtect/Secure_ES_T3_PDFs/"+newFileName, 'wb')
        pdfWriter.write(resultPdf)
        resultPdf.close()
    count += 1

Pro Tips

Open Google Docs/Sheets, etc. without owner knowing you’ve done so.

If you want to open a shared Google Drive file without the owner seeing that you’re in the doc, you can just make a copy of it rather than opening the actual doc. To do this, copy the link of the shared file, paste it into your browser’s address bar, and change the “/edit?…” part at the end to “/copy”.

Replace everything after the last slash with “/copy”

Google will prompt you with a blue “Make a Copy” button. Click it, and you’ve got your own copy of the file that you can peruse without the owner knowing you’ve accessed it.

Preventing Problems while Permanently Storing Grades in PowerSchool

Peculiar enrollment situations can lead to issues when permanently storing grades at the end of a term. For instance, if a student changes from one section of a course to another during a term, one grade could get stored for each section if the “Exclude enrollments” dates are not selected wisely.

"Exclude Enrollments" options

While we keep a file of “Special Cases” to review at the end of each term, but there are often such cases that sneak by us. To catch them, I track down all enrollment anomalies before storing grades.

To do this, I use DDE (…/admin/tech/dde/):

In DDE:

  • Select the CC table
  • TermID >= current year term (2900, 3000, 3001, etc.)
  • SchoolID = 200 for MS or 300 for HS (in our case)
  • DateEnrolled >= “A couple weeks after the start of the term”
  • Export to a spreadsheet
  • Sort by course name and delete non-courses: clubs, sports, A-Block, etc.
  • Sort by DateEnrolled and look for any peculiar dates later in the term
  • Decide appropriate cutoff date for “enrolled… after”
  • Note any enrollments that may be improperly included or rejected for later followup
  • Repeat above steps changing DateEnrolled to DateLeft and find an appropriate time for “dropped… before”

I typically check “Exclude … dropped before…” about a week before the end of the current term. Checking for outlier dates can help identify individual students and enrollments that may need attention.

Receive Raspberry Pi’s ip address on boot

I do a lot of Raspberry Pi projects – often creating them in one place and using them in another. While it is easy enough to use SSH, VNC or HTML to access a device, if it’s being moved from one network to another, it can be challenging to know its ip address. Sure, you may be able to log into a local router and look it up, but I thought it would be easier if I just had the RPi email me with its current ip address.

Thankfully, Cody Giles created a tutorial on elinux.com outlining how to do this. His Python script runs on the RPi on startup, finds its ip address(es) and then emails this information to a predetermined address. I’ve replicated his project (and code) below to ensure I’ll have access to it in the future.

In order to allow a Python script to send email through a Gmail account, you may need to enable an “App Password” for your sending account.

Step #1: Create the Python Script

Copy the following and paste it into a text editor. You must change the ‘to’ address, as well as the ‘gmail_user’ and ‘gmail_password’ for the sending account in the lines below the pound sign lines about 25 lines from the top. Save this file as a python script in the home directory named startup_email.py.

__author__ = 'Cody Giles'
__license__ = "Creative Commons Attribution-ShareAlike 3.0 Unported License"
__version__ = "1.0"
__maintainer__ = "Cody Giles"
__status__ = "Production"

import subprocess
import smtplib
from email.mime.text import MIMEText
import datetime

def connect_type(word_list):
    """ This function takes a list of words, then, depeding which key word, returns the corresponding
    internet connection type as a string. ie) 'ethernet'.
    """
    if 'wlan0' in word_list or 'wlan1' in word_list:
        con_type = 'wifi'
    elif 'eth0' in word_list:
        con_type = 'ethernet'
    else:
        con_type = 'current'

    return con_type

########################################################################
## Change the 'to' address,'gmail_user', and 'gmail_password' below.  ##
########################################################################
to = 'username@email.com' # Email to send to.
gmail_user = 'username@gmail.com' # Email to send from. (MUST BE GMAIL)
gmail_password = 'gmailpassword' # Gmail password.
smtpserver = smtplib.SMTP('smtp.gmail.com', 587) # Server to use.

smtpserver.ehlo()  # Says 'hello' to the server
smtpserver.starttls()  # Start TLS encryption
smtpserver.ehlo()
smtpserver.login(gmail_user, gmail_password)  # Log in to server
today = datetime.date.today()  # Get current time/date

arg='ip route list'  # Linux command to retrieve ip addresses.
# Runs 'arg' in a 'hidden terminal'.
p=subprocess.Popen(arg,shell=True,stdout=subprocess.PIPE)
data = p.communicate()  # Get data from 'p terminal'.

# Split IP text block into three, and divide the two containing IPs into words.
ip_lines = data[0].splitlines()
split_line_a = ip_lines[1].split()
split_line_b = ip_lines[2].split()

# con_type variables for the message text. ex) 'ethernet', 'wifi', etc.
ip_type_a = connect_type(split_line_a)
ip_type_b = connect_type(split_line_b)

"""Because the text 'src' is always followed by an ip address,
we can use the 'index' function to find 'src' and add one to
get the index position of our ip.
"""
ipaddr_a = split_line_a[split_line_a.index('src')+1]
ipaddr_b = split_line_b[split_line_b.index('src')+1]

# Creates a sentence for each ip address.
my_ip_a = 'Your %s ip is %s' % (ip_type_a, ipaddr_a)
my_ip_b = 'Your %s ip is %s' % (ip_type_b, ipaddr_b)

# Creates the text, subject, 'from', and 'to' of the message.
msg = MIMEText(my_ip_a + "\n" + my_ip_b)
msg['Subject'] = 'IPs For RaspberryPi on %s' % today.strftime('%b %d %Y')
msg['From'] = gmail_user
msg['To'] = to
# Sends the message
smtpserver.sendmail(gmail_user, [to], msg.as_string())
# Closes the smtp server.
smtpserver.quit()

Step #2: Make the script executable.

Using Terminal, navigate to the home directory and make the script executable.

cd ~
sudo chmod +x startup_email.py

Setp #3: Run the Script on Startup.

We’ll add a line to boot.rc to run the script whenever the RPi boots.

sudo nano /boot/boot.rc

Add the following lines to boot.rc

python /home/pi/Code/startup_email.py

Now, reboot the Pi and in about a minute, you should receive an email with your device’s IP address.

Forcing DNS on macOS in Recovery Mode

When resetting a 2020 MacBook Air, we ran into an issue where the device just wouldn’t connect to Apple’s servers. It felt like a DNS issue, but I couldn’t find a way to see or set DNS values. After some searching, I came across this Stack Overflow post with the solution. As that site was sold to a tech investment firm in June, 2021 (promising not to change a thing), I thought it best to preserve it here.


Here it is:

Usually DNS-server setup in the shell is done with sudo networksetup ... – a tool not available in the Base OS X System of the Recovery Mode.

You should still be able to change the DNS server with scutil in Terminal.app:

  1. Open Terminal.app in the menubar > Utilities
  2. Enter scutil --dns to get your current DNS config
  3. Enter scutil to reach interactive mode
  4. Enter list to get a list of all keys in the data store
  5. If you have several interfaces (you’ve found several State:/Network/Service/SERVICE_ID/IPv4 entries) determine the one connected to the Internet (based on e.g. your router and its internal network IP settings) – example:get State:/Network/Service/EB40E2FC-8248-48F2-8567-257D940A31EB/IPv4 d.show Example output:<dictionary> { Addresses : <array> { 0 : 192.168.0.8 } ConfigMethod : Manual SubnetMasks : <array> { 0 : 255.255.255.0 } } If your router has the IP-address 192.168.0.1 this should be the proper interface. If your router has e.g. the IP address 192.168.1.1 the interface found above would be the wrong one and you have to search for an interface with an IP in the range 192.168.1.2-192.168.1.254.
  6. Enter get State:/Network/Service/EB40E2FC-8248-48F2-8567-257D940A31EB/DNS use the service ID of the interface connected to the Internet you have found previously (here EB40E2FC-8248-48F2-8567-257D940A31EB)Entering d.show should show something like:<dictionary> { SearchDomains : <array> { 0 : some.domain } ServerAddresses : <array> { 0 : 192.168.0.1 } } Depending on the DHCP setup of your router the SearchDomains entry and array may be missing.
  7. Enter d.add ServerAddresses * 8.8.8.8 9.9.9.9 – add one or more DNS-server (here Google’s 8.8.8.8 and quad9’s 9.9.9.9)
  8. Enter set State:/Network/Service/EB40E2FC-8248-48F2-8567-257D940A31EB/DNS
  9. Enter d.show to check the modified dict entry. It should show something like:<dictionary> { SearchDomains : <array> { 0 : some.domain } ServerAddresses : <array> { 0 : 8.8.8.8 1 : 9.9.9.9 } }
  10. Enter quit to leave the interactive mode of scutil and return to the shell.
  11. Enter scutil --dns or dig to verify your new DNS config.

KIS/KORCOS Disc Golf Scorekeeping with Google Forms and Sheets

We put together a disc golf tournament scoring system using a Google form and some spreadsheet magic for golf tournament organizers. The organizer can enter the number of holes, teams, and golfer names into a spreadsheet which creates a convenient alphabetized list of golfers’ names. This list of names can be copied and pasted into a pre-made Google form.

The form allows golfers or managers to select a golfer and enter score(s) for any hole(s) that have been completed. The form’s spreadsheet automagically tallies the scores for each player and team, creating tables for both individual and team leaderboards. If an error is made entering any score(s), one can just go back and re-enter the score for the affected hole(s) and all is good again. The spreadsheet uses the most recent score for each golfer on each hole and ignores previous entries.

Instructions are found below and can be found in this Google doc.

Note: do not edit anything except what is in the instructions below. There are a few extra sheets (Leaderboard, CurrentUnsortedScores, SubmissionsByReverseTime) that are made up entirely of formulas using data from the ManagementData and Form Response tabs. If anything is wrong, it is almost certainly in the ManagementData tab not in the other sheets.

  1. Click this link to make a copy of the form
  2. Click this link to make a copy of the spreadsheet
  3. In the form, click on the “Responses” link, then the green spreadsheet icon on the right, and click “Select response destination”.
  4. Select the spreadsheet that you copied from the link above
  5. When you selected the response spreadsheet in the previous step, a new tab was added. In my example below, it is named “Form Responses 2”.In order to get the formulas in the several tabs to work with your data, we need to copy the data from the new tab (Form Responses 2 in my case) to the “Form Responses 1” tab. This is how to do this:

      1. Open the “Form Responses 1” tab
      2. Delete all the data in this tab. Just click in a cell, Command-A and hit the delete button.
      3. In cell A1 of the Form Responses 1 tab, enter the following. If necessary, update the ‘Form Responses 2’ part with the name of the new tab in Step #3 above:

        =QUERY(‘Form Responses 2’!A1:T,”SELECT *”)


    This formula imports everything from the Form Responses 2 tab into the Form Responses 1 tab from which all other formulas pull data.

  6. Now, in the spreadsheet’s “ManagementData” tab, type the number of holes for your course in B1.
  7. Still in “ManagementData” type team names in cells B2:K2 (Currently Group One, Group Two…) up to 10 teams, deleting any leftovers.
  8. Under each team name, type each team’s member names (Currently Dave Archer, Matt Quade…)up to 10 golfers per team, deleting any leftovers:
  9. Once all players names have been entered, copy the alphabetized list in column M of the ManagementData tab and in the form, paste these into the answers list for the “Name” question. This is critical!:
    1. Copy all of the names in the alphabetized list (M2:M? in ManagementData)
    2. In the form, delete all but the last name listed in the Name question.
    3. Highlight that one remaining name and “Paste” the names you just copied.
    4. The only choosable responses to the Name question should be the golfers you just pasted. If not, edit them as needed.
  10. Your tournament should now be ready to roll.
  11. “Send” the form to your golfers and watch the results in the “Leaderboard” tab of the spreadsheet.
  12. If you share the spreadsheet with your golfers, PLEASE only share it as “read-only”. If people who don’t know better start going in trying to correct scores on their own, they’ll screw it up in an unrecoverable fashion.

Troubleshooting

Problems? Let’s look at what might be happening.

  1. If you have extra names showing up in your form, you prolly didn’t follow steps 9.1-9.4 exactly right. The short version is that in column M of the ManagementData tab, you should see an alphabetized list of your golfers’ names. An alternative method of getting where you need to be is to copy this list to the clipboard, go to edit your form, click in the first name in your “Name” choices and “paste” your names. Now, just go through your list and delete any duplicates or incorrect golfer names.
  2. Still stuck? Well, just leave a comment on this post. If I’m able, I’ll get back to you asap. If you’re willing to make me an editor on your form and sheet, I can probably help fix it for you.

AP Chinese and Japanese Exam Setup on Non-English Installed Windows

Intro

In November 2018, I emailed the College Board’s AP Chinese & Japanese technical support staff to clarify a note on the 2018-2019 AP Coordinator’s Manual stating that Apple computers would not be supported for use on the AP Chinese (or Japanese) exam (See “Note:” on page 127 of the manual). The response was that Apple computers have never been supported and that test sites are expected to use native Windows computers exclusively.

Our solution for the 2019 exam is to rent 40 Windows 7 Ultimate laptops from a shop in the Yongsan Electronics Mart. In December 2018 we rented one laptop to test the setup and we ran into some difficulty. It seems that if Windows was installed with an non-English “Install Language”, the AP C&J Setup CD may be unable to auto-install the Input Method Editor(s) needed for the exam. This becomes evident once running the sample tests during which the IME settings fail. We had great success following the steps below prior to running the AP C&J Setup CD.

[Note: We found that the install language for our laptops was Korean. This is found by looking in the Windows registry for
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\Language
On our systems, the value of this key was 0412 (for Korean) and not 0409 (for English)

Go through the IME and Headset steps below before running the AP C&J Setup CD. 

Chinese Language Input Method Editor (IME) Settings

The language needs to be set to English and the following keyboards need to be installed in Control Panel > Clock, Language, and Region > Change keyboards or other input methods. Under the “Keyboards and Languages” tab, select “Change Keyboards” and click “Add”. Select the following keyboards.

  • Chinese (Simplified, PRC)
    • Chinese (Simplified) – Microsoft Pinyin ABC Input Style
    • Chinese (Simplified) – Microsoft Pinyin New Experience
    • Chinese (Simplified) – US Keyboard
  • Chinese (Traditional, Taiwan)
    • Chinese (Traditional) – New Phonetic
    • Chinese (Traditional) – New Quick
    • Chinese (Traditional) – Phonetic
    • Chinese (Traditional) – Quick
    • Chinese (Traditional) –  US Keyboard
  • Japanese (Japan)
    • Japanese
    • Microsoft IME

USB Headset Setup

We also needed to change a few Sound settings to get our headsets to work properly. If you are following this at another school, you might want to do the same.

Connect the Koss CS-100 headset to the PC and open the sound settings (Control Panel > Hardware and Sound > Sound > Manage audio devices) Under the “Playback” tab, select the “USB Audio Device” and click the “Properties” button (Speaker Properties). Select the Levels tab, unmute any muted setting, move any volume sliders to the middle, and click the “OK” button.

Under the “Recording” tab, do the same as above for the microphone levels.

AP Chinese and Japanese Setup CD Steps

Once the above settings are complete, it’s time to run the AP C&J Setup CD, following the prompts. Afterward, we ran the sample exam and had no issues with typing or audio levels.