Wednesday, February 22, 2017

Google Sites - Overview of "New" Sites


Google's updating of Sites was much needed.  It was feeling/looking outdated and it was still in the world of WYSIWYG website creators. 

There are many reasons to switch to the updated Sites, including the following:
  • Resides within Google Drive: you can create new sites and access your existing sites right within Drive, no going to a different location
  • User interface is more intuitive: actions are much more intuitive on the new sites
  • ADA Compliance: the new sites works hard to force you to be compliant, which means a limit in some features.  It is still the user content that can break this, but the dynamic layouts that adjust to devices, color contrasting within the themes and ease in adding Alt Text to images are game changers from the old sites
Some of the cons:
  • Loss of the page-level sharing: this will only impact a small percentage of people, but for me it was key in having part of a site viewable to the public and part of it private to select people.
  • Addition of more features: as it is still developing, there are many features that may not be there...yet.  In just the last week one of these appeared (adding a divider, AKA horizontal rule line), so I list this as a con, but it will be less of one as time passes and more features are added. 
Here's a short video that overviews the new Google Sites:

Tuesday, February 21, 2017

Google Script - Form to Email Part 2: Creating the Email

I've been working with our counselors to create an online request form that replaces the "pink slips" currently in use by students.  We are hoping this solution increases efficiencies at several levels (i.e. students can put in the appointment request at any time including at home, pink slips are not lost, etc).

There were two pieces in this.  The first was setting the counselor's email, while the second is to create and send the email.  You may want to visit the first part if you have information to add to the spreadsheet prior to creating and sending the email. 

Part 2. Creating and Sending the Email
If you already have the email you will be sending to collected via the form, you will only need to use these directions. 

First, here is the sample script you can put into the Script Editor -

function sendEmail(){
 
   var sheet = SpreadsheetApp.getActiveSheet();
 
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow();   // Number of rows to process
  var numColumns = sheet.getLastColumn();
 
  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
  var data = dataRange.getValues();
 
  var complete = "sent";
 
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var semail = row[1];  //student account/email
    var counselor = row[4];  //counselor selected
    var reason = row[5]; //reason for request
    var student = row[2]; //student name
    var cemail = row[6]; //counselor email
    var emailed = row[7];

   
    if (emailed != complete){
    var sent = sheet.getRange(startRow + i, numColumns);
    var email = cemail;
    var subject = student + " requests an appointment"
    var body = student + " from " + program + " would like to see you. \n\nThe reason for the request: " + reason + "\n\n" + "Student's email: " + semail;
   
    MailApp.sendEmail(email, subject, body);
     
      sent.setValue(complete);
    }

  }
}


The whole first section is connecting to the current spreadsheet (with the form responses), establishing the first row of data, the number of rows and columns with data within the spreadsheet, then grabbing the values within that range of data.

I've then established a new variable   var complete = "sent";  This variable will allow me to do a match so I am not sending the same emails over and over - they will only send if the appropriate cell is not marked as Sent. 

The next section is gathering the data in a particular row -
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var semail = row[1];  //student account/email
    var counselor = row[4];  //counselor selected
    var reason = row[5]; //reason for request
    var student = row[2]; //student name
    var cemail = row[6]; //counselor email
    var emailed = row[7];

I've established i, which will look at each row incrementally to the last row of data.  The new variables set up after pulling the data correspond to specific columns in the spreadsheet (this is a case where the numbering starts at 0 - so my semail is in the 2nd column, counselor is in the 5th column, etc)

The next section is first evaluating if the email has been sent - the != essentially says "is not equal to the value of the variable complete" (in my example, it does not have the word sent in that cell).
    if (emailed != complete){
It then is establishing that the variable sent is in a specific column - this is actually the same cell as emailed in my example, but I establish it here so that I can change the value later when the email has been sent.
    var sent = sheet.getRange(startRow + i, numColumns);


The next three lines are where I am establishing the email, subject and body of my email.  (I personally establish these as email, subject and body whenever doing an email for clarity when I am looking back at my scripts.)  You will notice that you can use your earlier variable in conjunction with fixed text - be sure to use a plus sign between joined pieces, and parenthesis around any fixed text.  Additionally, if you want to establish line breaks in the body of your email use the \n.
    var email = cemail;
    var subject = student + " requests an appointment"
    var body = student + " from " + program + " would like to see you. \n\nThe reason for the request: " + reason + "\n\n" + "Student's email: " + semail;
   

The next line is sending the email:
    MailApp.sendEmail(email, subject, body);

My last line is putting the word "sent" in the cell I established earlier so that when the script runs again it will disregard that line of data and not send the email again.
      sent.setValue(complete);

You will want to set up a trigger if you want this to run automatically.

I hope that if you are adding information to the spreadsheet - like I did with the couselor's email - you see how part 1 and part 2 interface very nicely together.   They can even be put together into one script file.  

Monday, February 20, 2017

2017 #MACUL Conference - Tipsheet #2


The 2017 MACUL Conference is now less than a month away!

(This is the second in a series of 4 to help you get ready and excited about going to the conference. Tipsheet #1 contains information about hydration, food considerations and special happenings at the conference. )

The conference sessions are all locked and loaded...meaning you can peruse the list at your leisure.  Here are some suggestions that may help you better come to the the conference feeling more prepared-

Know What You Want: have some idea(s) of what you want to walk away from the conference with.  Use these ideas to help you plan your time so you meet those goals. It's easy to become overwhelmed with all the options, and identifying YOUR walk-aways will make sure you take back meaningful learning.  For example, you are looking for new uses of Google Apps in your classroom, look for sessions about that.  Or, if you are involved in looking for new school purchases make sure to block out some time to spend in the exhibit hall. 

Conference App: Check out the Conference app here.
This year’s conference app will let you download the app on your iOS or Android device, or you can view on the web from any device with a web browser.  Other app features:

When looking at a session:
  1. a notes feature allows you to tie your personal notes to the session (these are only seen by you)
  2. you can rate the sessions within the app
  3. you can bookmark sessions to find them more easily
  4. there is spot to get help or more information

Other app features beyond session and speaker information:
  • a local places icon - information on local restaurants and attractions
  • maps icon - maps of Devos/Amway are uploaded to help you find your way
  • friends icon - connect with other conference attendees
At the top of the window, alerts are splashed across the page so you cannot miss them -

You will be able to access all areas after you create a login!

Some other tips:

Pick Multiple Sessions: It’s good to mark out several sessions for each time slot - some factors (such as an overcrowded room, or distance between sessions later in the day) may make it so you miss a desired session.  If you can’t attend your first choice, having a backup in mind ahead of time helps make your day go more smoothly.

Featured Speakers: Featured speakers are featured speakers for a reason.  If you are having a hard time finding a session to go to, look for featured speaker sessions.

Other Types of Speakers:  Sessions are marked with many pieces of information that can help you make smart decisions in your session choices.  Some pieces to look at:
  • Speaker’s organization - do they work for an educational institution? or a company? Sometimes this can impact both their purpose and approach
  • Speaker’s job role - there are a variety of job roles represented among the speakers.  Again, their role can impact both their purpose and approach
  • Session Strands - many sessions are marked with topical strands.  If a strand interests you, you can keyword search it to find sessions related to that topic.
And remember, the conference is about learning AND having fun... make sure to pick out one session that is "just for the fun of it."

Looking forward to seeing you at the conference!

Friday, February 17, 2017

Google Slides - Inserting Google Drive Videos (guest post from Jeremy Badinar)

Guest post from +Jeremy Badinar at Badinar Bytes & Tech Tidbits -

Thanks to Jeremy for agreeing that I could cross post this :-)

It is finally here! The long awaited ability to insert Google Drive videos into Slides! We have always been able to insert YouTube videos, but now all those videos you have stored and shared in Drive are now able to be inserted into a Google Slide. This is big news, especially for all those who have students share their video work through Google Classroom or Drive. Follow the steps below to learn how to insert all those Google Drive videos directly into a Slide!


Insert Google Drive Videos into Slides

  1. Open the Slide you want to insert the video into.
  2. Click on “Insert”.
  3. Click on “Video”.


  1. Select “Google Drive”.


  1. All your available Dive videos will be listed. Select the one you want to insert and click “Insert”.


  1. You can move, resize, and add border. Click on the video, then click:
: adds a border and allows you to select the thickness.
: choose the border style
: adjust the color of the border.


  1. For more options, click “Video options” to bring up options on the right.


  1. Autoplay when presenting” allows you to have the video start playing or require a click to start in presentation mode.
  2. Mute Audio” turn off the audio when the video plays.



Start adding your Google Drive videos to your Slides today!


If you want these steps in a document, click Here!

Thursday, February 16, 2017

Google Script - Form to Email Part 1: Establishing Email

I've been working with our counselors to create an online request form that replaces the "pink slips" currently in use by students.  We are hoping this solution increases efficiencies at several levels (i.e. students can put in the appointment request at any time including at home, pink slips are not lost, etc).

There were two pieces in this.  The first is setting the counselor's email, while the second is to create and send the email. 

Part 1. Establishing Email
One piece in creating this and putting it into place was having the counselors' names on the form, then using a script to add the counselor's email into another column.

Here is the script to put into the Script Editor -

function setEmail() {

  var sheet = SpreadsheetApp.getActiveSheet();
 
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow();   // Number of rows to process
  var numColumns = sheet.getLastColumn();  //Number of columns
 
  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);

 var data = dataRange.getValues();

  for (var i = 0; i < data.length; ++i) { //
    var row = data[i];
    var counselor = row[4];  //column with counselor selected
       
      if (counselor == "Mrs. Doe - Counselor") {    
           var cemail = sheet.getRange(startRow + i, 7);
      cemail.setValue("doej@domain.org");
    } else if (counselor == "Mrs. Smith - Counselor") {
            var cemail = sheet.getRange(startRow + i, 7);
      cemail.setValue("smitht@domain.org");
    } else if (counselor == "Mr. Green - Counsleor") {
            var cemail = sheet.getRange(startRow + i, 7);
      cemail.setValue("greenr@domain.org");
    }

  }
}  


Let's break it down -
The top part of the script is getting the active spreadsheet (the one with the form responses), establishing the first data row and the number of Rows and Columns with data (remember - these are all numerical calls).

The dataRange variable is establishing the range of data to pull - in this case our starting row to our last row, and the first column to the last column.  The last row is numRows-1 as we are starting at the second row; if we just used numRows we would be pulling an extra row of data.

The data variable is actually pulling the values in those cells in our range.  Note:  This call can actually be combined with the establishing the range; I have them as two lines as I prefer it for when I'm troubleshooting or making modifications.

Next we begin our loop in matching specific criteria and then creating a new variable - cemail - depending on what the criteria is.
First the   for (var i = 0; i < data.length; ++i)  - this is establishing a variable i which grows by increments (the ++i) from 0 to the data length.  Our for is saying we will do this for each i value that falls under this criteria.

var row = data[i];  pulls the data for that specific row

    var counselor = row[4];  this establishes which of the columns has the variable we will be checking a match against - in my form, it falls in the 5th column; since counting starts at 0 that makes it 4 in the code.

The if-else statement then establishes first what match to check for; the == is used as I want an exact value match.   It then creates and assigns a new variable cemail if the criteria is matched in the desired cell (this is a case where you use the actual column - so mine is going in the 7th column).   I use else if to look for the other matches.  Because these possible responses come from a multiple choice list, I have a finite number of possibilities so do not include a fine else statement - everything should match prior to needing it.  

Don't forget to set triggers if you want this to happen automatically on a form submit!

That establishes the emails needed for part 2.