Google Apps for Work

Just thinking about Google Apps Script and how powerful it it is I thought I'd write up about the benefits of having Google Apps for Work even it its just for a few users.  I have had a Google Apps for Your Domain (which is now Google Apps for Work) account for over 8 years and it has been great, I can have my own email at my own domain, add aliases, set a catch all account, and more that I can't think of now but will have to add to this post as they pop in my head.

• A custom email address is key to continue building your brand and it’s included when you setup your Gmail account.
• You can add domains to you account too so you can have custom email addresses on all of you subdomains but still only inbox to manage.
• In addition to Gmail for Work, each user gets 30GB of online storage with Google Drive that works with all file types and syncs with all your devices.
• Host video meetings with your coworkers and customers using Google Hangouts. Save money and time on travel by connecting from anywhere at any time.
• The admin controls allow you to setup and manage your users easily and safely, so your data always stays safe.  
• I actually use my Google Apps For Work for immediate family email accounts, makes troubleshooting way easier.

What is Google Apps?
Google Apps is a cloud-based productivity suite that helps teams communicate, collaborate and get things done from anywhere and on any device. It's simple to set up, use and manage, so your business can focus on what really matters.
Millions of organizations around the world count on Google Apps for professional email, file storage, video meetings, online calendars, document editing and more.
Watch a video or find out more here.
Here are some highlights:
Business email for your domain
Looking professional matters, and that means communicating as you@yourcompany.com. Gmail’s simple, powerful features help you build your brand while getting more done.
Access from any location or device
Check email, share files, edit documents, hold video meetings and more whether you’re at work, at home or in transit. You can pick up where you left off from a computer, tablet or phone.
Enterprise-level management tools
Robust admin settings give you total command over users, devices, security and more. Your data always belongs to you, and it goes with you if you switch solutions.



Start free trial

Nest temperature logging

I had been wanting a Nest thermostat for awhile and this past October we finally decided to get one.  Mainly because our local energy smart program with our utility providers offered a 50% off rebate on it!  But even without a rebate Nest thermostats can save $131-$145/year (or less than a two year ROI and Nest now has the data to prove it).

We had just bought this house so I did not have any historical utility bills to validate these savings.  I could have went a year to get a baseline but who wants to do that.  I use google sheets to do my monthly bills/budgeting and I have over 5 years of longitudinal data, if we had stayed in the same house and bought the nest it would have been interesting to actually see the savings.

I was just curious and wanted to see the how the temperature fluctuates based on the schedule and the weather.   I thought it would be interesting to see how outside temp impacts inside temp and how hard my furnace has to work to keep up.  The Nest app does a nice job of simple showing you how much time your furnace has run each day, and if you tap on a day it will show you the breakdown of what time it ran for and how long.  It's interesting but I was curious how much the indoor temp fluctuated as an indicator to how well my house was insulated.  Or even just a graph that shows the indoor temp over the course of a day or week.  Below is screen shot from my Nest app's Energy History, pretty basic.

So naturally I searched around for a way to pull this data and found a method using a LAMP stack and an unofficial nest-api to query this data from your nest account and add it the mysql db then present it on a php webserver local or public.  This worked as expected but then I searched around some more and found a thread on the Nest Community which uses Google Sheets... brilliant.  I had not used the Google Apps Script yet so thought this was a perfect opportunity to see what it can do.  Luckily the script on the Nest Community site worked pretty much out of the box just by adding your nest credentials and zip code.  

The only thing missing was having this script get data on a schedule w/o out errors.  When I set it up it worked great and pulled data every time I manually ran the script, but if I set it to run hourly it would work at best 20% of the time.  It didn't make sense, why would it run on demand without error when ran manually but not during the trigger?  I kept thinking about it and thought maybe its how it gets run via trigger when I am not there (not logged in) vs when I manually do it while logged in.  

More searching... and found this little hint on Issue 2758: Trigger based UrlFetchApp.fetch() fails, where you just make your main function a doGet() and publish as a web app (it seems that you do have to execute it as yourself and let anyone access it, even anonymous for it to work).

So basically you just put the meat of the script in a doGet() function, publish you web app then create another function that does a UrlFetchApp.fetch('webapp url') then you can set your timebased() trigger on and it should run on that schedule.

 

So without further ado here's the script I use to query the indoor temperature, target temperature and humidity along with outdoor temperature and humidity.

PowerSchool Scheduled data export, minimal install

There are several reasons we need to export data from PowerSchool and many times the built-in AutoSend feature will not do.  The AutoSend feature can only export data on active students for either the district or the school, sometimes we need a more specific set of kids as well as the fields that we can pull.

This post will focus on a method that should be able to be run on any Windows 2008 r2 server and above without an extra installs.  It uses PowerShell, .NET 4 and ODP.NET.  Also note that this is what you use once you have your sql query and you just want to set it to run on a schedule to get a .csv or some other delimited file.

I chose this method instead of others because PowerSchool has to be installed on Windows Servers and quite frankly we're a Windows Shop when it comes to servers.  You can run this directly on the server that runs PowerSchool but I chose to use a server we use to warehouse, scrub and analyze a lot of our data.  We also have lots of other servers that run specific applications for transportation/routing, food service, library systems and etc. and all of these are Windows boxes that have the base requirements.

Once I decided this would be my best method I used this post on the Hey, Scripting Guy! Blog to build my basic .ps1 scripts using ODP.NET.  I wasn't as concerned with speed but more the 'simplify' part.  So in summary of what that post talks about is:

1. ODP.NET  from Oracle 
2. Make sure you have .Net 4
3. the ability to run .Net 4 in PowerShell
• Configure PowerShell to use newer .net.  To do this, you need to create a config file, named PowerShell.Exe.Config according to (http://tfl09.blogspot.com/2010/08/using-newer-versions-of-net-with.html)
4. bypassing the block (run PowerShell as Admin):  Set-ExecutionPolicy RemoteSigned
5. run this script by:  .\query1.ps1 

So get ODP.NET:

1. create a folder called c:/autoexport or somewhere else on your Windows 7+ or 2008r2+ box.
2. download the proper ODP.NET from Oracle 
3. extract that in your new folder under another folder called c:/autoexport/odp

Check if you have .NET 4

Configure PowerShell to use newer .NET.  To do this, you need to create a config file, named PowerShell.Exe.Config according to (http://tfl09.blogspot.com/2010/08/using-newer-versions-of-net-with.html),  then put this in the same folder as powershell.exe (%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe)

then bypassing the block (run PowerShell as Admin):
 Set-ExecutionPolicy RemoteSigned

Heres the sample script for Naviance Student import:
To run this in PowerShell you need to do the ol' 'dot slash' in front of the actual script:  ./queryFor_NavianceStudents.ps1

now that you have your delimited export file, you can use PowerShell and WinSCP to upload it.  I'll probably write that part up too soon.

Wrap those up in a .bat file then schedule that with task scheduler!

Custom Alert Icon on Class Attendance page of PowerTeacher

The main goal was to add custom alerts to the class attendance page on PowerTeacher, this page is visited by teachers daily and shows all students in the chosen class in one screen.

Alternatives require a teacher to click through each student on the /teachers/studentpages and also requires customizations be made for each of those pages.

The target page for this post is /teachers/classattendance.html but before we get to that we have first add a custom field for the alert, I just added a boolean field in an advanced DB extension called U_Info and added the column/field named Alert_customalertfield with default value of 0.

Then we need an alert icon, I used a .gif that was basically the favicon.ico of the alert type's website.  But any 16x16 .gif will do, add this to /images so that https://yourPSserver/images/alert_customalert.gif will show.

Next we have to add a place in PowerSchool to check this box, for this we will only allow folks with permission on the admin side to check.  I chose the "Other Information" page for a student on the PowerSchool Admin side.

just add a custom insertion point by adding this code just above the ending /table> tag on /admin/students/misc.html:
Then add this 'page fragment' called "misc.alert_customalert.custom.studentalerts.txt"
with this in it in the same directory as misc.html:
To add this alert icon to the admin side its a simple page fragment called:"title_student_end_css.alert_customalert.student.alert.txt"inserting into: "~[cust.insertion_point:student.alert]"
to do this create a file named "title_student_end_css.alert_customalert.student.alert.txt" in this directory  /wildcards/ with this in it

So to get back to the main goal of this post, adding this to the Class Attendance 'Single Entry' tab, is to add the code below to /teachers/classattendance.html directly after '[alerts]' tag, should be line 245 of the original page. It's not elegant but it does work:
Like I said, it's not elegant and I have not load tested it but it does work in a environment where ~450 teachers login on a daily basis.  It works by using a stored procedure variable called [ccid] that is only available on that page and actually iterates through the listing of students in that class, unfortunately it does not work with a page fragment, for some reason that variable/stored procedure doesn't carry through to the page fragment.  Also other commonly used DATs like ~(studentname) or ~(studentfrn)   don't work.  So I just use a tlist_sql query joining the CC, Students and U_info tables and finding if the student with that [ccid] has a 1 in the customfieldalert, then do a case statement that replaces the value with the html code i want.  Definitely some hackery but like I said it works and teachers are happy.