gistfile1.js

/*   
   Copyright 2011 Martin Hawksey

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
*/

// Usage - have a column titled username with recipient google account email addresses. 
// Guests need to enable SMS notifications on invites in their own Google Calendar.
// More info http://mashe.hawksey.info/2011/01/send-free-sms-via-google-calendar-guest-notification 
function sendSMSviaCalendarGuest(){
  var cal = CalendarApp.getDefaultCalendar(); // get default calendar - could also use CalendarApp.getCalendarsByName("a calendar name")[0] for other calendars
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getActiveSheet(); // get the sctive sheet - could also use doc.getSheetByName("a sheet name");
  var header = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0]; // assumes column heading are in first row
  var usernameCol = false; // initialises usernameCol
  for (i in header){
    if (header[i]=="username"){ // loops through headings until it finds one called uesrname - this is the column where email addresses for the notifications are 
      usernameCol = parseInt(i)+1;
      break;
    }
  }
  if (usernameCol){ // if we find the username column do more stuff 
    var data = sheet.getRange(2,usernameCol,sheet.getLastRow(),1).getValues(); // gets the column of emails as an array
    var emaillist = data.join(", "); // comma seperates array to get a string of emails
    var now = new Date().getTime(); // get time now as used in http://code.google.com/googleapps/appsscript/articles/gmail_filter_sms.html
    // create a calendar event - title is used as message (could also be read or user input), set time +1 minute add guest list and send invites
    cal.createEvent("please read your feedback on the Effect Feedback assignment",new Date(now+60000),new Date(now+60000), {guests:emaillist,sendInvites:true});
    Browser.msgBox("Notification sent"); // let user know it's been completed 
  } else {
    Browser.msgBox("Could not find column named 'username' with email addresses"); // let user something went wrong 
  }
}