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

Building Student Name Index for Yearbooks

Edward, one of our 11th grade students on the school’s yearbook team wanted to build an index of page numbers where each student appeared. Traditionally, this would entail multiple students going through every page and recording the page number for each student appearing on that page. Inevitably, some students get missed, errors are made, and some pages get skipped.

Edward, recognizing an opportunity to leverage his programming skills, wrote a Python script to collect the information in a much more efficient manner. His script reads two columns from a Google Spreadsheet into lists named name and nameraw. Then, for each name in the lists, the script searches each page of the yearbook pdf and logs each page number where the name is located. Finally, this data is written back to the Google sheet.

This script leverages Google sheets, but with minor alteration, it could easily use locally stored files instead. Note that this script depends on a number of dependencies, so don’t forget to add those that you may not yet have installed (PyPDF2, re, pathlib, and the relevant Google auth and client libraries). If you’re unfamiliar with the Google python libraries, well, that will need to be another Google search. However, if you use local files rather than reading from a Google sheet, those libraries (and the code between the dashed lines) could be excluded. You would just need to replace those lines with a couple others that would open your csv file with the list(s) of names.

This is Edward’s script and I had no hand in creating or using it. If you want to use it yourself, you’re welcome to do so. If you get stuck, check with one of your programming students or teachers first. If you’re still stuck, comment below and I’ll help out if I can.

from __future__ import print_function
import os.path      
from PyPDF2 import PdfFileReader, PdfFileWriter
from pathlib import Path
import re
import PyPDF2

#-----------------------------------------------------
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from google.oauth2 import service_account

# Note that you will need to set up your own Google API credentials if
# you're using Google Sheets. This part is beyond the scope of this post.
# But you can find how to do this with a quick google search. 
SERVICE_ACCOUNT_FILE = 'keys.json'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
creds = None
creds = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)

# The ID and range of the spreadsheet. Note that the ID below is fake
# (we're not going to post the real one on the interwebs)
# Make sure you update the ID to refer to your spreadsheet
SPREADSHEET_ID = '1DQyjh1-7T9jjU7kmg6-TNcjsCtnCspWiMbv3t2mJeM'

service = build('sheets', 'v4', credentials=creds)

# Call the Sheets API
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SPREADSHEET_ID,
                            range="Sheet1!B1:B508").execute()
result2 = sheet.values().get(spreadsheetId=SPREADSHEET_ID,
                            range="Sheet1!A1:A508").execute()
#------------------------------------------------------------
values = result.get('values')
values2 = result2.get('values')

name = []
nameraw = []
#put names from values and values2 into lists
for x in values:
        for i in x:
                name.append(i)

for x in values2:
        for i in x:
                nameraw.append(i)

#Open the Yearbook pdf file
pdf = PdfFileReader('yearbook1.pdf')

#create a 2-D array for names & associated page numbers
pagesfinal = [[]]

#This is where everything happens. For each name, add it to pagesfinal.
#Then open each page in the pdf file and search it for the current name.
#If a version of the name is found, append the page number to the student's page list.

for i in range(len(name)):
        print(nameraw[i])
        pagesfinal.append([])
        for page_num in range (pdf.numPages):    
                pageobj = pdf.getPage(page_num)
                pageinfo = pageobj.extractText()
                pageinfo = ''.join(pageinfo.split())
                name[i]= ''.join(str(name[i]).split())
                nameraw[i] = ''.join(str(nameraw[i]).split())
                if (re.search(name[i], pageinfo)) or (re.search(nameraw[i], pageinfo)) or (re.search(name[i].upper(), pageinfo)):
                        pagesfinal[i].append(page_num +1)
                        print(pagesfinal[i])

#When the above loop completes, the following line writes the data back to the Google sheet
        request = sheet.values().update(spreadsheetId=SPREADSHEET_ID, range="Sheet1!C1", valueInputOption = "USER_ENTERED", body={"values":pagesfinal}).execute()
Featured

Air Quality Monitoring on a Corporate Scale

NOTE: This project started out small, simple, easy-peasy. It has expanded well beyond what can comfortably be contained in a single blog post. If you’re looking into setting up air monitoring on your campus, I would be very happy to help you as much as you’d like – from a general “Here’s a website to look at” to a complete “Here’s a fully functional pm2.5/pm10/temp/humidity/barometric pressure sensor and dashboard”, I’m probably the guy you want to talk to.
My current setups use $75 DYI sensors as well as a multi-thousand dollar commercial sensor, Python scripts, api calls, HTML, CSS, Google Sheets, and JavaScript to create responsive displays showing current and recent AQI readings. Let me know if you have any questions or would like some help. Otherwise, feel free to continue on to the post I made a few years back . . .


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 are 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, it adds a note logging successful completion 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:

To use this yourself, click here for the script. Copy the code and paste it into a new script in your form’s spreadsheet (Tools > Script Editor). Then set a trigger for the script to run at your preferred time interval.

Raspberry Pi: Auto-Mount Veracrypt Volumes at Startup

I use a Raspberry Pi with four drives housed in a Yottamaster USB enclosure. These are encrypted using VeraCrypt which can be a pain if/when the Pi loses power and restarts since the volumes become unmounted and inaccessible. The solution is to auto-mount the drives when the Pi starts up via the crontab.

Please note: This method requires the drive passwords to be stored in a text (shell script) file. If you need stronger security, this method is not for you.

Part 1: find the UUID of the drives:

ls /dev/disk/by-id

In my case, this results in:

mmc-SC64G_0x5ac51d9c
mmc-SC64G_0x5ac51d9c-part1
mmc-SC64G_0x5ac51d9c-part2
usb-JMicron_Generic_DISK00_0123456789ABCDEF-0:0
usb-JMicron_Generic_DISK00_0123456789ABCDEF-0:0-part1
usb-JMicron_Generic_DISK00_0123456789ABCDEF-0:0-part2
usb-JMicron_Generic_DISK01_0123456789ABCDEF-0:1
usb-JMicron_Generic_DISK01_0123456789ABCDEF-0:1-part1
usb-JMicron_Generic_DISK01_0123456789ABCDEF-0:1-part2
usb-JMicron_Generic_DISK02_0123456789ABCDEF-0:2
usb-JMicron_Generic_DISK02_0123456789ABCDEF-0:2-part1
usb-JMicron_Generic_DISK02_0123456789ABCDEF-0:2-part2
usb-JMicron_Generic_DISK03_0123456789ABCDEF-0:3
usb-JMicron_Generic_DISK03_0123456789ABCDEF-0:3-part1
usb-JMicron_Generic_DISK03_0123456789ABCDEF-0:3-part2

From experience, trial and error, I know the lines I want are those ending in “-part2”.

Part 2: Create a shell script:

I now create a shell script (mount.sh) on the Desktop with the following. This script will run at startup, sleep 20 seconds to let the Pi finish all its startup stuff, and then mount the drives:

nano /home/root/Desktop/mount.sh

…and add the following. Don’t forget to replace the usb-JMicron_Generic…part2 with your drive ids from Step 1. Notice also that there is a space between part2 and /media/veracrypt1.

#!/bin/bash
sleep 20
veracrypt -t --non-interactive -p 'diskPasswordHere' /dev/disk/by-id/usb-JMicron_Generic_DISK00_0123456789ABCDEF-0:0-part2 /media/veracrypt1
sleep 5
veracrypt -t --non-interactive -p 'diskPasswordHere' /dev/disk/by-id/usb-JMicron_Generic_DISK01_0123456789ABCDEF-0:1-part2 /media/veracrypt2
sleep 5
veracrypt -t --non-interactive -p 'diskPasswordHere' /dev/disk/by-id/usb-JMicron_Generic_DISK02_0123456789ABCDEF-0:2-part2 /media/veracrypt3
sleep 5
veracrypt -t --non-interactive -p 'diskPasswordHere' /dev/disk/by-id/usb-JMicron_Generic_DISK03_0123456789ABCDEF-0:3-part2 /media/veracrypt4

Don’t forget to make the script executable:

sudo chmod +x /home/root/Desktop/mount.sh

Part 3: Add it to the crontab

Those drives won’t do much good if they’re not mounted. To resolve this, edit your crontab to get the job done on startup.

crontab -e

Add the following to the end of the file:

@reboot /home/root/Desktop/./mount.sh &

Now, when your Raspberry Pi reboots, it should mount your Veracrypt volumes automatically.

Raspberry Pi – Auto-start Kiosk Mode Browser

OK, this one is the simps. I have a Raspberry Pi connected to a monitor. Whenever the Pi starts up, I want it to automatically load my air quality dashboard in full-screen (kiosk) mode. I don’t want to use a keyboard, mouse or other user action, I just want it to start up and show a webpage, using the full-screen kiosk mode.

Here’s how to do it:

Edit the lxsession autostart file with the following:

sudo nano /etc/xdg/lxsession/LXDE-pi/autostart

At the bottom of the file, add:

@/usr/bin/chromium-browser --kiosk https://kis.support/h200

That’s it. Whenever your Pi reboots, it’ll load up the webpage (in this case https://kis.support/h200).

Pro Tip: The Rapsberry Pi’s default screensaver can be a pain in the neck. I disabled it by installing xscreensaver with:

sudo apt install xscreensaver

Then, click the Raspberry “Home” button > Preferences > Screensaver. In the top dropdown “Mode” menu, select “Disable Screen Saver”. Then sit back and get ready for some serious burn-in.

Campus Time Machine Backup Server Setup


Note: This is a draft post - published for Paul to find more easily. It's not complete as long as this paragraph is here, but the initial steps will be needed before progressing further.

Install Linux Mint

Setup VNC Access (per LinuxMint tutorial)

1. Remove the default Vino server:

sudo apt-get -y remove vino

2. Install x11vnc:

sudo apt-get -y install x11vnc

3. Create the directory for the password file:

sudo mkdir /etc/x11vnc

4. Create the encrypted password file:

sudo x11vnc –storepasswd /etc/x11vnc/vncpwd

You will be asked to enter and verify the password.  Then press Y to save the password file.

5. Create the systemd service file for the x11vnc service:

sudo nano /lib/systemd/system/x11vnc.service

Copy/Paste this code into the empty file:

[Unit]
Description=Start x11vnc at startup.
After=multi-user.target

[Service]
Type=simple
ExecStart=/usr/bin/x11vnc -auth guess -forever -noxdamage -repeat -rfbauth /etc/x11vnc/vncpwd -rfbport 5900 -shared

[Install]
WantedBy=multi-user.target

6. Reload Daemon

sudo systemctl daemon-reload

7. Enable the x11vnc service at boot time:

sudo systemctl enable x11vnc.service

8. Start the service:

Either reboot or

sudo systemctl start x11vnc.service

Enable ssh access

sudo apt install openssh-server

Nebula VPN Setup

For external VPN access, use node16 (Paul knows)

Static ip Address Setup

Info for setting static ip address and access from off campus

Samba Server Setup

Install Samba

sudo apt update
sudo apt install samba

Samba setup

mkdir /home/<user>/sambashare/
sudo nano /etc/samba/smb.conf

Add the following to smb.conf:

[sambashare]
    comment = Samba on Ubuntu
    path = /home/username/sambashare
    read only = no
    browsable = yes

Restart service and allow firewall traffic:

sudo service smb restart
sudo ufw allow samba

Set up User Accounts

sudo smbpasswd -a username

Raspberry Pi Sound Effects Player

Long story short? I made an RPi sound clip player using a tft touchscreen, pygame, and movie quotes found on YouTube. It starts with a small, 3.5″ touchscreen whose drivers are installed using the instructions here. For your convenience, they are replicated below.

Just enter these lines into Terminal to get the screen set up.

sudo rm -rf LCD-show
git clone https://github.com/goodtft/LCD-show.git
chmod -R 755 LCD-show
cd LCD-show/
sudo ./LCD35-show

This should get the screen running properly. If you have issues with axes being reversed on the screen (like you touch the right side and the cursor shows up on the left) the page linked above has some suggestions. If those don’t help, check out Teng Fone’s post on Medium.

OK, now (hopefully) the screen is working and we can work on the code. I used code from Garth Vander Houwen’s Pi tft menu project as a starting point, added some audio files, and came up with a project that presently shows this screen on my Raspberry Pi:

Sounds Player Screen

You might guess some (or all) of the audio files I used. If not, you can download them from here.

The Python script to get it all running is below (or access it here). Connect the audio output to your speakers or headphones and click away. Examining the code should make it relatively easy to modify it to change the sounds, number of options, etc.

import sys, pygame, time, subprocess, os
from pygame.locals import *
from pygame import mixer
from subprocess import *

os.environ["SDL_FBDEV"] = "/dev/fb1"
os.environ["SDL_MOUSEDEV"] = "/dev/input/touchscreen"
os.environ["SDL_MOUSEDRV"] = "TSLIB"

# Initialize pygame modules individually (to avoid ALSA errors) and hide mouse
pygame.font.init()
pygame.display.init()
pygame.mouse.set_visible(0)
pygame.display.toggle_fullscreen

# define function for printing text in a specific place with a specific width and height with a specific colour and border
def make_button(text, xpo, ypo, height, width, colour):
    font=pygame.font.Font(None,42)
    label=font.render(str(text), 1, (colour))
    screen.blit(label,(xpo,ypo))
    pygame.draw.rect(screen, blue, (xpo-10,ypo-10,width,height),3)

# define function for printing text in a specific place with a specific colour
def make_label(text, xpo, ypo, fontsize, colour):
    font=pygame.font.Font(None,fontsize)
    label=font.render(str(text), 1, (colour))
    screen.blit(label,(xpo,ypo))

# define function that checks for touch location
def on_touch():
    # get the position that was touched
    touch_pos = (pygame.mouse.get_pos() [0], pygame.mouse.get_pos() [1])
    #  x_min                 x_max   y_min                y_max
    # button 1 event
    if 30 <= touch_pos[0] <= 240 and 30 <= touch_pos[1] <=85:
            button(1)
    # button 2 event
    if 260 <= touch_pos[0] <= 470 and 30 <= touch_pos[1] <=85:
            button(2)
    # button 3 event
    if 30 <= touch_pos[0] <= 240 and 105 <= touch_pos[1] <=160:
            button(3)
    # button 4 event
    if 260 <= touch_pos[0] <= 470 and 105 <= touch_pos[1] <=160:
            button(4)
    # button 5 event
    if 30 <= touch_pos[0] <= 240 and 180 <= touch_pos[1] <=235:
            button(5)
    # button 6 event
    if 260 <= touch_pos[0] <= 470 and 180 <= touch_pos[1] <=235:
            button(6)
    # button 7 event
    if 30 <= touch_pos[0] <= 240 and 255 <= touch_pos[1] <=310:
            button(7)
    # button 8 event
    if 260 <= touch_pos[0] <= 470 and 255 <= touch_pos[1] <=310:
            button(8)

# Define each button press action
def button(number):
    print("You pressed button", number)

    if number == 1:
        #time.sleep(0.2) #do something interesting here
	mixer.init()
	mixer.music.load('Illbeback.mp3')
	mixer.music.set_volume(1)
	mixer.music.play()
	time.sleep(5)
        #sys.exit()

    if number == 2:
        #time.sleep(5) #do something interesting here
        #sys.exit()
	mixer.init()
        mixer.music.load('wickedsmart.mp3')
        mixer.music.set_volume(1)
        mixer.music.play()
        time.sleep(5)

    if number == 3:
        #time.sleep(5) #do something interesting here
        #sys.exit()
	mixer.init()
        mixer.music.load('thatlldopig.mp3')
        mixer.music.set_volume(1)
        mixer.music.play()
        time.sleep(5)

    if number == 4:
        #time.sleep(5) #do something interesting here
        #sys.exit()
	mixer.init()
        mixer.music.load('killingmesmalls.mp3')
        mixer.music.set_volume(1)
        mixer.music.play()
        time.sleep(5)

    if number == 5:
        #time.sleep(5) #do something interesting here
        #sys.exit()
	mixer.init()
        mixer.music.load('failuretocommunicate.mp3')
        mixer.music.set_volume(1)
        mixer.music.play()
        time.sleep(5)

    if number == 6:
        #time.sleep(5) #do something interesting here
        #sys.exit()
	mixer.init()
        mixer.music.load('goodafternoon.mp3')
        mixer.music.set_volume(1)
        mixer.music.play()
        time.sleep(5)

    if number == 7:
        time.sleep(2) #do something interesting here
        #sys.exit()

    if number == 8:
        time.sleep(5) #do something interesting here
        sys.exit()

#colors     R    G    B
white   = (255, 255, 255)
red     = (255,   0,   0)
green   = (  0, 255,   0)
blue    = (  0,   0, 255)
black   = (  0,   0,   0)
cyan    = ( 50, 255, 255)
magenta = (255,   0, 255)
yellow  = (255, 255,   0)
orange  = (255, 127,   0)

# Set up the base menu you can customize your menu with the colors above

#set size of the screen
size = width, height = 480, 320
screen = pygame.display.set_mode(size)

# Background Color
screen.fill(black)

# Outer Border
pygame.draw.rect(screen, blue, (0,0,480,320),10)

# Buttons and labels
# First Row
make_button("I'll be back", 30, 30, 55, 210, blue)
make_button("Wicked Smart", 260, 30, 55, 210, blue)
# Second Row
make_button("That'll Do", 30, 105, 55, 210, blue)
make_button("Killing me", 260, 105, 55, 210, blue)
# Third Row
make_button("Failure", 30, 180, 55, 210, blue)
make_button("Good Night", 260, 180, 55, 210, blue)
# Fourth Row
make_button("Don't do nothin", 30, 255, 55, 210, blue)
make_button("Exit", 260, 255, 55, 210, blue)

#While loop to manage touch screen inputs
while 1:
    for event in pygame.event.get():
        if event.type == pygame.MOUSEBUTTONDOWN:
            pos = (pygame.mouse.get_pos() [0], pygame.mouse.get_pos() [1])
            on_touch()

        #ensure there is always a safe way to end the program if the touch screen fails
        if event.type == KEYDOWN:
            if event.key == K_ESCAPE:
                sys.exit()
    pygame.display.update()
    ## Reduce CPU utilisation
    time.sleep(0.1)

Ubuntu and WiFi on 2011 MacBook Pro

We had tried a few years ago to get some ancient MacBooks up and running with various Linux distros. But our particular model had challenging WiFi hardware which made it impractical.

I recently took another shot at reinvigorating our Early 2011 MacBook Pros with Ubuntu and found that a few terminal commands would get things working. I used Andy Bleaden’s steps from this AskUbuntu answer. I’ve pasted the steps below to ensure I always have ready access to it.

From Andy Bleaden:
I always recommend removing and reinstalling the broadcom drivers using your terminal

In a terminal type the following command

sudo apt-get purge bcmwl-kernel-source

then

sudo apt-get install bcmwl-kernel-source

This will then rebuild your driver.

You can either restart your pc or if this is a pain type the following commands in the terminal which will ‘switch on’ your wireless

sudo modprobe -r b43 ssb wl

then

sudo modprobe wl 

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.


Link to a cell or range in Google Sheets

You can link to a cell or range by selecting the range, right-clicking, select “More cell actions” and select “Link to this range.” Alternatively, just add “&rangeA3:B7” to the end of the sheet’s URL:

https://docs.google.com/spreadsheets/d/19Cw1wEVH9jQtSiFjEhq-6jEfH7Jkk6kN14cynI2ZSk4/edit#gid=0&range=A3:B7

Mount a remote ssh folder locally

You can mount the remote directory with sshfs which gives you easy access to all the files. E.g.:

sshfs user@server:/directoryToMount /localDirectory

Find all triggers set up for your Google Apps Scripts

It took me the longest time to figure out how to locate all the Google Apps Script triggers I had attached to various sheets, docs, and forms. Turns out it’s the simps; you just visit:

https://script.google.com/home/triggers