Resolve issues preventing installation of MacOS

I recently tried installing a fresh copy of MacOS Mojave on a student’s MacBook. After selecting the “Install MacOS” option, I received a message saying “This copy of the install MacOS Mojave application is damaged and can’t be used to install MacOS.” For some reason, this seems to be caused by an incompatible time showing in the system. There are similar error messages that seem to be resolvable by the same method. These messages include:

  • The installer payload failed signature check.
  • This copy of the install MacOS Mojave application can’t be verified. It may have been corrupted or tampered with during downloading.

To resolve the issue, restart, booting to your install flash drive, connect to WiFi, and run the Terminal app from the Utilities dropdown. In Terminal, enter the following:

ntpdate -u time.apple.com

Then try installing MacOS.

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.

 

Reset Codiad password

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.

SD Card re-formatter

Sometimes after setting up a micro SD card for use in a Raspberry Pi, it can be difficult to get it back to a normal out-of-the-box state. But the SD Card Formatter Tool from the SD Association can take care of it in seconds. It doesn’t give a lot of fancy options, but when it’s done, you’ve got a plain ol’ SD card like it came from the factory.

Get it here.

Android SMS Forwarding in a Foreign Land

Google's Project FiUnfortunately, 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.

SK Telecom LogoI 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

Assign Students to Teams in PowerSchool

  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”

Install iMovie 9 or iTunes 12.6.2 on El Capitan – OS X 10.11

We had a student needing to get iMovie on her Mid 2012 MacBook Pro running El Capitain 10.11.6. Apple makes it hard to get older apps running on old OSes, but we have a workaround.
NOTE: this method also works for older versions of iTunes like 12.6.2 (the last version to allow purchasing iPad apps) and other older Mac versions. Just change “iMovie” to “iTunes” or whatever the app name is.

Here’s what to do:

Download the legit iMovie 9.0.9 from Apple’s site here and double click the downloaded imovie9.0.9update.dmg file. This will open a folder looking like the pic below. DO NOT OPEN the .pkg file. Just move that window to the side of your screen (but keep it open).

iMovie

Open your Terminal app (Applications > Utilities > Terminal.app) and type in “pkgutil –expand ” (NOTE: there is a space between pkgutil and which are two hyphens and a space after the word expand). Then drag the iMovie9.0.9Update.pkg file and the iMovie folder into the terminal app like this:

Now, add a space after iMovie9.0.9Update.pkg and carefully add the following into Terminal replacing “username” with your username (usually shown just before the $ in Terminal):

/Users/username/Desktop/newfolder/

It should look like this when you’re done:

Now press “Enter” and if all goes well, you shouldn’t see much happen for a minute or two. When pkgutil is done running, you will see a folder named newfolder on your Desktop. Open the newfolder folder and the the iMovie folder within it and you should see this:

iMovie folder

Right-click iMovie9.0.9Update.pkg and select “Show package contents”. You should see this:

Right-click the Payload file, select the Rename option and add the extension “.zip” and when asked if you’re sure you want to do so, click the “add” button. Once you see the Payload.zip icon, double-click it to expand the contents. In a minute or two you should see a Payload 2 folder. If you open that folder and the Applications folder within, you will see iMovie.app:

Drag the iMovie.app file to your Applications folder in Finder. Now, you can open iMovie and edit movies to your heart’s content. If it refuses to open the first time, right-click the icon, select “Open” and when told the app is from an unverified developer, click the “Open” option like the rebel you were born to be.

Using a VirtualBox Virtual Machine to Maintain iPad App Purchasing in iTunes – Post High Sierra

The recent High Sierra MacOS update brings us iTunes 12.7 which loses the ability to purchase apps for iPads associated with an Apple ID from within iTunes. There are convoluted methods of downgrading iTunes 12.7 to 12.6 to regain that capability, but it’s a pain to keep preventing Apple’s updates from forcing a user to lose this capability.

For most individual users, this isn’t much of an issue, but for institutional users, it makes it more complicated to acquire and distribute apps to iPads.

My solution is to build a VirtualBox machine with an original (un-updated) High Sierra MacOS with iTunes version 12.6. Whenever I need to access an Apple ID tied to a specific iPad account, I just fire up the MacOS VirtualBox, log into the associated iTunes account, acquire the needed app(s), and then log out. Since I have the iPads set up to auto-download apps purchased with the account, the apps typically show up on the device without additional intervention.

Let me know if you could benefit from a clone of my MacOS machine or need additional guidance on using VirtualBox.

The Mystery of the Unsharable Calendar

I was working with my good buddy Rotimi on a Google Calendar issue tonight. His school (like mine) uses a Google Calendar for campus events with an iframe used to display events on the school website’s calendar page.

Unfortunately, something changed so that events only showed “busy” with  no descriptions or details when viewed by anyone who wasn’t logged in to a school-managed Google account. Rotimi logged into the calendar owner’s account but he was not able to change the sharing settings to show all event details to the public as that option was greyed out and unavailable:

I thought that there may have been a change in the Google Admin Control Panel for the account’s OU, but that turned out not to be the case. Rotimi went back to the user account that owned the calendar and looked more closely at the list of people in the “Share with specific people” section. Interestingly, two of those who were able to make changes and manage sharing were domain admins. When Rotimi accessed the sharing page from his Google Administrator account (rather than the calendar owner’s account) he was able to access the “See all event details” option in the calendar sharing settings. As soon as he saved this setting, all event details were once again showing on the website even if the viewer was not logged into a school Google account.

We think that one of the domain admins may have inadvertently  changed the sharing settings for this calendar to hide event details from viewers who are not logged into a school account. Apparently, if a domain administrator changes the sharing settings of an individual user’s calendar, it overrides the sharing settings available to the calendar’s owner so that he/she can no longer share it more openly than what the admin user set. In other words, it looks as though if an admin changes a user’s calendar sharing setting, the user can make the sharing more restrictive, but not more open than the setting made by the admin.