OUseful.Info, the blog…

Trying to find useful things to do with emerging technologies in open education

Updating Google Calendars from a Google Spreadsheet

I got a request today along the lines of:

We’re in the process of creating a master calendar of events spreadsheet relevant to [various things]. These [various things] will all then have their own Google calendar so they can be looked at individually, embedded etc and everyone could of course have access to all and view them all via their personal Google calendar, turn different calendars on or off, sync with Outlook etc. etc.

X said “wouldn’t it be great if we made the master spreadsheet with Google docs and it could somehow automate and complete the calendars”.

Sigh…;-) So – is it possible?

I’ve only had a quick play so far with Google Apps script, but yes, it seems to be possible…

Take one spreadsheet, liberally sprinkled with event name, description, start and end times, an optional location, and maybe a even a tag or too (not shown):

The time related columns I specified as a date type using the “Data Validation…” form from the Tools menu:

Now take one Google apps script:

function caltest1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 5);
  var data = dataRange.getValues();
  var cal = CalendarApp.getDefaultCalendar();
  for (i in data) {
    var row = data[i];
    var title = row[0];  // First column
    var desc = row[1];       // Second column
    var tstart = row[2];
    var tstop = row[3];
    var loc = row[4];
    //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
    cal.createEvent(title, tstart, tstop, {description:desc,location:loc});
 }
}

(This was largely a copy and paste from Sending emails from a Spreadsheet Tutorial, which I’d half skimmed a week or two ago and seemed to remember contained a howto for bulk mailing from a spreadsheet, and the Apps script Calendar class documentation.)

And here’s the result of running the function:

The email tutorial adds a bit of gloss that allows a further column to contain state information about whether an email has already been set; we could do something similar to specify whether or not an event has been automatically added to the calendar, and if not, add it when the function is run.

Because it can be a pain having to go into the script editor to run the function, it’s easier to just create a menu option for it:

Here’s how:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Shove stuff in calendar", functionName: "caltest2"} ];
  ss.addMenu("OUseful", menuEntries);
}

I had a little play to see if I could trivially get an RSS feed into the spreadsheet using an =importFeed() formula, and use the details from that to populate the calendar, but for some reason the feed importer function didn’t appear to be working?:-( When I tried using CSV data from a Yahoo RSS2CSV proxy pipe via a =importData() formula, the test function I’d written didn’t appear to recognise the date format…

PS Arghh… the test formula assumes a Date type is being passed to it… Doh!

Hack round importFeed still not working by grabbing a CSV version of the feed into the spreadsheet:
=importdata(“http://pipes.yahoo.com/ouseful/proxy?_render=csv&url=http%3A%2F%2Fopen2.net%2Ffeeds%2Frss_schedule.xml”)

Tweak the calendar event creation formula:

cal.createEvent(title, new Date(tstart), new Date(tstop), {description:desc});

Run the function:

Heh heh :-)

PS it’s also possible to move content from a Google Calendar to a Google spreadsheet, as Grabbing Google Calendar Event Details into a Spreadsheet shows…

PPS it strikes me that the spreadsheets2calendar route provides one way of generating an iCal feed from a list of event times held in a spreadsheet, by popping the events into a Calendar and then making the most of its output formats? A bit like using Yahoo pipes as a quick’n’easy KML generator?

[UPDATE: related, via @mhawksey - Using Google Apps Script for a event booking system (Spreadsheet to Calendar & Site | Form to Spreadsheet, Email and possible Contacts)]

Re: not creating duplicate entries, check out this thread

Written by Tony Hirst

March 4, 2010 at 8:20 pm

49 Responses

Subscribe to comments with RSS.

  1. Interesting script!

    Wondering if the other way around is possible? Or should I make the calendar public and import in Google Docs? It would be great for logging hours for projects or declaring working hours ;-)

    Joost Plattel

    March 4, 2010 at 8:56 pm

  2. [...] Published March 5, 2010 Google Apps Leave a Comment Tags: calendar, spreadsheet A comment to Updating Google Calendars from a Google Spreadsheet, where I showed how to get events described [...]

  3. [...] March 7, 2010 Google Apps Leave a Comment Tags: calendar, spreadsheet In the post Updating Google Calendars from a Google Spreadsheet, I described a recipe for adding events to a Google Calendar from a Google Spreadsheet using Google [...]

  4. If data from spreadsheet to calendar is possible, I guess it must be also posssible to push data to the gmail contactslist.
    This would be very easy when people can fill in there own adress,phone, birtday etc using google form. All that data is collected in to the spreadsheet and at the end of the day we run a script and all that goes to your contactlist. Whishfull thinking? you never know :-)

    Joris

    March 11, 2010 at 6:36 pm

  5. I really like this script. There is an app thats way too complicated to configure

    http://code.google.com/apis/gdata/articles/spreadsheets_calendar_base_mashup.html

    Can I ask if there is a way to stop duplicated entry? Everey time you run the function it just creates another calendar entry. Can it be configured to update existing entries using title as a unique field?

    Mat Agbaba

    September 13, 2010 at 10:17 pm

    • “Can I ask if there is a way to stop duplicated entry? Every time you run the function it just creates another calendar entry. Can it be configured to update existing entries using title as a unique field?”

      Could help me with this process? We’d like to use this at my institution.

      Matthew K. Seibert

      November 18, 2011 at 9:10 pm

  6. spoke to soon, noticed you already found a way :)

    Mat Agbaba

    September 14, 2010 at 8:58 am

    • I was just trying to figure out how to do this thanks! I can’t seem to make it work without doing creating duplicates. How did you get it to update the existing events.

      david

      david

      December 3, 2010 at 4:28 pm

  7. This is awesome

    Matt P

    August 10, 2011 at 6:26 pm

  8. how do i update an event that is already created? I don’t want to duplicate with new dates and leave the old behind. Also single day events show up about 50% of the time.

    I have copied the script above, and edited the cal.createEvent with the new commands but still isn’t working.

    I would greatly appreciate some help.

    Thank you

    Craig Misak (@cmisak)

    September 6, 2011 at 6:59 pm

    • @craig It’s been some time since I looked at the API, but when I cobbled together the script I seem to remember that updating pre-existing events wasn’t an option… I will try to make time to see if that functionality is now supported – but I can’t guarantee when…

      Tony Hirst

      September 6, 2011 at 7:29 pm

  9. Did anyone got the solution for updating exisiting events and avoiding duplicate items in calendar. Thanks

    Pramod

    November 17, 2011 at 5:05 am

  10. I was wondering if we could work together to make it so that it does not duplicate events when the script runs.

    I am a Professor and Librarian looking to create a bookings module, which this would allows me to do.

    Ultimately, what I want to to is make a Google Form, that is also a spreadsheet, and then have a script run that exports all new events from the spreadsheet into a Google Calendar.

    I look forward to your reply.

    Matt

    Matthew K. Seibert

    November 18, 2011 at 8:02 pm

    • I am wondering the exact same thing, using this and the reprise of this post I have been able to make it publish to a specific calendar, but every time I run the function it recreates the event.

      Any help would be great!

      MJChibanga

      November 23, 2011 at 7:08 pm

  11. I found this in the Calendar API. Maybe this would help with the updating events problem, and stop the creation of duplicates. I’m really interested in solving this problem and so are my co-workers and other academic institutions. I look forward to working with you.

    http://code.google.com/apis/calendar/data/2.0/developers_guide_java.html#UpdatingEvents

    Matthew K. Seibert

    December 9, 2011 at 5:04 pm

    • Here is a page of JavaScript features, including a calendar update

      Matthew K. Seibert

      December 9, 2011 at 5:12 pm

  12. Has a solution been found? I would really be interested in this functionality too, regarding the updating of times without creating a duplicate.

    Rob

    January 24, 2012 at 4:40 am

  13. For many years I have wanted to be able to use, administer and update my relational database information in an active spreadsheet, linked to an events list or even a call list. Programmers right and left have pishawed me and said it ain’t going to happen.

    I hoped Google Apps would have this feature, but I sure can’t find it. For instance, I would like to see all my Google calendar events on a spreadsheet and be able to update that calendar on either the event view or in that synchronized active spreadsheet view.

    This would not involve any exporting or importing of course.

    Any suggestion?

    iP

    February 27, 2012 at 1:38 am

  14. [...] Maintaining a Google Calendar from a Google Spreadsheet and Google Spreadsheet and Google Calendar Linkup Update [...]

  15. Hi,

    Great tutorial! This is exactly what I’m trying to do at work, managing hundreds of meetings for individual clients.

    Anyone know how I can add the info to a particular “sub” calendar that’s not my default? I’ve tried various functions but just can’t get it to work.

    Ben

    March 7, 2012 at 5:29 pm

  16. Why do I get this error?

    Event start time must be before event end time. (line 16) Dismiss

    Current Script

    function caltest1() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2; // First row of data to process
    var numRows = 2; // Number of rows to process
    var dataRange = sheet.getRange(startRow, 1, numRows, 5);
    var data = dataRange.getValues();
    var cal = CalendarApp.getDefaultCalendar();
    for (i in data) {
    var row = data[i];
    var title = row[0]; // First column
    var desc = row[1]; // Second column
    var tstart = row[2];
    var tstop = row[3];
    var loc = row[4];
    //cal.createEvent(title, new Date(“March 3, 2010 08:00:00″), new Date(“March 3, 2010 09:00:00″), {description:desc,location:loc});
    cal.createEvent(title, new Date(tstart), new Date(tstop), {description:desc});
    }
    }

    kylejwalter

    June 6, 2012 at 3:28 pm

  17. hola a tod@s solicitando su valiosa ayuda de porque obtengo este error:

    Cannot find method createEvent(string,string,string,object). (Línea 15)Ignorar

    agradecere su valioso apoyo saludos.

    benja

    June 25, 2012 at 11:10 pm

  18. Wow, thank you for sharing. !!

    I have a calendar account, I have a form that spopulates a spreadsheet, and I hope to puplate the calendar with the entries the form captures into the spreadsheet.

    I just have to figure out what to do with that function code you provided near the top of this page!

    Does it go into the spreadsheet…nop, the calendar, right?

    Thanks, in any event!

    Tom

    Tom

    August 17, 2012 at 7:23 pm

  19. [...] the post Updating Google Calendars from a Google Spreadsheet, I described a recipe for adding events to a Google Calendar from a Google Spreadsheet using Google [...]

  20. Is it possible to make it create an “all day” event in the calendar rather than one with a start/stop time/date? And thanks btw for these great tutorials. So helpful!

    Cassidy

    August 28, 2012 at 8:45 pm

    • I figured it out. New to scripts – didn’t think the reference would be that easy to use.

      Cassidy

      August 29, 2012 at 7:39 pm

  21. Hi this is very nice thanks…. but how can i do for change another calendar for example a calendar that i have create?? thaks for your help…

    José

    September 18, 2012 at 10:45 pm

  22. Regarding the duplicate, I added a function that checks first if an event with the same title exists that day. You have to iterate through the returned CalendarEvent object and set a validator to false. Then, if the validator is still true after that recursive function, you can go ahead and add the event :)

    check = cal.getEventsForDay(new Date(tstop))
    for(event in check){
    var obj = check[event];
    if(title = obj.getTitle())
    validator = false
    }

    Stefan Broda

    September 28, 2012 at 11:56 pm

    • @Srefan – brilliant – thanks for contributing that… I guess I need to do a refresh of the post to make these insights easier to find:-)

      Tony Hirst

      September 30, 2012 at 11:33 am

    • Oh and you better trim the titles before you compare them:

      for(event in check){
      var obj = check[event];
      found_title = obj.getTitle();
      if(trim(title) == trim(found_title)){
      validator = false
      }

      You can use a trim function like this:
      function trim(s) {
      return s.replace(/^\s\s*/, ”).replace(/\s\s*$/, ”);
      }

      Next, i’ll see if it is possible to update an event. Or delete / create it again.

      Stefan Broda

      September 30, 2012 at 6:37 pm

    • I am an amateur at programming, so could you please tell me how to solve this error:
      “Cannot call method “replace” of undefined” . I get this error for the “replace” in the trim function.

      Nusrat

      October 4, 2012 at 9:42 am

    • hi I am new to script.. where do I put the check script for duplicate?

      function caltest1() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var startRow = 2; // First row of data to process
      var numRows = 100; // Number of rows to process
      var dataRange = sheet.getRange(startRow, 1, numRows, 5);
      var data = dataRange.getValues();
      var cal = CalendarApp.getDefaultCalendar();
      for (i in data) {
      var row = data[i];
      var title = row[0]; // First column
      var startDate= row[2];
      var desc = row[1]; // Second column
      var endDate= row[3];
      var name= row[4];
      //cal.createEvent(title, new Date(“March 3, 2010 08:00:00″), new Date(“March 3, 2010 09:00:00″), {description:desc,location:loc});
      cal.createEvent(title,new Date(startDate), new Date(endDate), {description: desc, location: name});
      sheet.getRange(startRow + i, 6)
      }
      }

      Mick

      July 10, 2013 at 9:25 am

  23. Very interested in a delete/update option.

    Cassidy

    October 1, 2012 at 9:09 pm

  24. Hi, great thread here :) i’ve been trying this and took a look at the duplicated event thread, however if we want to run the script to create event A and then want to update event A again how do we do it? How do we get the eventid from the event A we just created so that we can maybe update it?

    Thanks!

    Pedro

    October 19, 2012 at 6:18 pm

  25. I’ve been looking for/working on a two way sync and here’s a proof of concept I got working. It still needs to be fined tuned, but I thought I would post it in case anyone has insight in optimizing the code, and for anyone that would find it useful. Basically the code creates an array from the calendar, and the spreadsheet, combines them. Sorts it on last modified (date last updated for event, and the standard scripted last modified column for spreadsheet), removes duplicates, and submits to both the calendar (currently after deleting all events) and the spreadsheet. I was planning on adding a key, and having a list where you would type the key to remove the items from both locations, the upside being you can add spreadsheet rows from calendar and vice versa. Thanks in advance for any helpful input.
    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [ {name: "Sync Spreadsheet to Calendar", functionName: "calsync"}];
    //{name: “Sync”, functionName: “myimport”}];
    ss.addMenu(“Calendar Sync”, menuEntries);
    }

    function calsync()
    {
    // This function should be executed from the
    // spreadsheet you want to export to the calendar
    var mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Test123″);

    var myCalendar = CalendarApp.openByName(“Test”);

    //calendar event array
    var events = myCalendar.getEvents(new Date(“January 1, 2011 EST”),
    new Date(“January 1, 2014 EST”));
    if (events[0]) {
    var eventarray = new Array();
    var line = new Array();
    line.push(‘Title’);
    line.push(‘Start Date’);
    line.push(‘End Date’);
    line.push(‘Description’);
    line.push(‘Last Modified’);
    eventarray.push(line);

    var i = 0;
    for (i = 0; i < events.length; i++) {
    line = new Array();
    line.push(events[i].getTitle());
    line.push(events[i].getStartTime());
    line.push(events[i].getEndTime());
    line.push(events[i].getDescription());
    line.push(events[i].getLastUpdated());
    //line.push(events[i].getLocation());
    eventarray.push(line);
    }
    } else {
    Browser.msgBox('nothing between ' + startDate + ' till ' + endDate);
    }

    var dataRange = mySpreadsheet.getRange("A2:E53");
    var data = dataRange.getValues();

    if (data[0]) {
    var dataarray = new Array();
    var line2 = new Array();

    var j = 0;
    for (j = 0; j < data.length; j++) {
    var row = data[j];
    line2 = new Array();
    line2.push(row[0]);
    line2.push(row[1]);
    line2.push(row[2]);
    line2.push(row[3]);
    line2.push(row[4]);
    //line.push(events[i].getLocation());
    //line.push((events[i].getEndTime() – events[i].getStartTime()) / 3600000);
    dataarray.push(line2);
    }
    } else {
    Browser.msgBox('nothing between ' + startDate + ' till ' + endDate);
    }

    var newarray = eventarray.concat(dataarray);
    uniquedata(newarray);

    }

    //found at https://developers.google.com/apps-script/articles/removing_duplicates
    function uniquedata(data) {
    var newData = new Array();
    var data2 = sort(data, 4, false);
    for(i in data2){
    var row = data2[i];
    var duplicate = false;
    for(j in newData){
    if(row[0] == newData[j][0]){
    duplicate = true;
    }
    }
    if(!duplicate){
    newData.push(row);
    }
    }
    var filtered = sort(newData, 4 , false);
    UpdateSpreadsheet(filtered);
    UpdateCalendar(filtered);
    // var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test123");
    // sheet.clearContents();
    // sheet.getRange(1, 1, filtered.length, filtered[0].length).setValues(filtered);
    }

    function UpdateSpreadsheet(data) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test123");
    sheet.clearContents();
    sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
    }

    function UpdateCalendar(data)
    {
    var myCalendar = CalendarApp.openByName("Test");

    // optional – delete existing events
    var events = myCalendar.getEvents(new Date("January 1, 2011 EST"),
    new Date("January 1, 2013 EST"));
    for (var i = 0; i 0) {
    if (typeof columnIndex != “number” || columnIndex > data[0].length) {
    throw ‘Choose a valide column index';
    }
    var r = new Array();
    var areDates = true;
    for (var i = 0; i < data.length; i++) {
    var date = new Date(data[i][columnIndex]);
    if (isNaN(date.getYear()) && data[i][columnIndex] != '') areDates = false;
    else if (data[i][columnIndex] != '') data[i][columnIndex] = date;
    r.push(data[i]);
    }
    return r.sort(function (a, b) {
    if (ascOrDesc) return ((a[columnIndex] b[columnIndex]) ? 1 : 0));
    return ((a[columnIndex] > b[columnIndex]) ? -1 : ((a[columnIndex] < b[columnIndex]) ? 1 : 0));
    });
    }
    else {
    return data;
    }
    }

    Cheney

    October 22, 2012 at 8:17 pm

  26. I found a solution for updating events, with the help of Waqar Ahmad. I’ve spent the last half hour watching events disappear and then reappear on my calendar. The code is available here:

    http://stackoverflow.com/questions/13005699/how-do-i-modify-a-google-calendar-event-using-google-apps-script/13019195#13019195

    Todd

    October 22, 2012 at 8:29 pm

  27. Here’s an updated version of the code I post. I’ve cleaned up the code, and added some comments, the biggest change is now I pull multiple columns into the description, so those can be updated in both places but the spreadsheet will keep the columns organized. It was the best solution I could think of without being able to add actual fields to the calendar event. I also put in some code to replace variations of initials and first names, my solution was hardcoded after I gave up finding a better way to do it, I would love to hear some suggestions on how to handle that batter. Hopefully this helps someone looking for something similiar to what I needed, I still have to work this in at a more global level to our task tracking workbooks, so I may or may not have another major update. I’m getting a funky extra title row, but won’t be able to revisit it until next week, I think I need to exclude a header row in one of the for loops.

    Hopefully someone finds this useful, suggestions welcomed:

    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [ {name: "Sync Spreadsheet to Calendar", functionName: "calsync"},
    {name: "Clear Calendar", functionName: "clearcalendar"}];
    //{name: “Sync”, functionName: “myimport”}];
    ss.addMenu(“Calendar Sync”, menuEntries);
    }

    function calsync()
    {
    // // Here are the steps the script follows
    // 1.Builds events array from calendar, if null ignores, i add a feature, or facility on a sheet level with a template sheet, so
    // 2.Get calendar from sheet, combine columns, and add headers so that many columns fit in description
    // 3. Concatenate arrays
    // 4.Filter for last modified, take most recent data
    // 5. replace initials or first name with full name
    // 6. array is ready to be sent back to calendar
    // 7. split out columns based on header (currently it depends on the order, I would like to be able to find based on header and header rank, and then send them in the standard order,
    // but since no one is adding new calendar events, just edditing its not a big deal.
    // Just a side note, I use the flagged for discussion column to query all sheets on all workbooks where a team member would like to raise issue, to achieve maximum visibility on questions/concerns.

    var myCalendar = CalendarApp.openByName(“Test2″);
    var events = myCalendar.getEvents(new Date(“January 1, 2011 EST”),
    new Date(“January 1, 2014 EST”));
    if (events[0]) {
    var eventarray = new Array();
    var line = new Array();
    line.push(‘Title’);
    line.push(‘Start Date’);
    line.push(‘End Date’);
    line.push(‘Description’);
    line.push(‘Last Modified’);
    eventarray.push(line);

    var i = 0;
    for (i = 0; i < events.length; i++) {
    line = new Array();
    line.push(events[i].getTitle());
    line.push(events[i].getStartTime());
    line.push(events[i].getEndTime());
    line.push(events[i].getDescription());
    line.push(events[i].getLastUpdated());

    eventarray.push(line);
    }
    }

    var mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test2");
    var last_row = mySpreadsheet.getLastRow();
    var last_column = mySpreadsheet.getLastColumn();
    var dataRange = mySpreadsheet.getRange("A2:"+last_column+last_row);
    var data = dataRange.getValues();

    if (data[0]) {
    var dataarray = new Array();
    var line2 = new Array();

    var j = 0;
    for (j = 0; j 0) {
    if (typeof columnIndex != “number” || columnIndex > data[0].length) {
    throw ‘Choose a valide column index';
    }
    var r = new Array();
    var areDates = true;
    for (var i = 0; i < data.length; i++) {
    var date = new Date(data[i][columnIndex]);
    if (isNaN(date.getYear()) && data[i][columnIndex] != '') areDates = false;
    else if (data[i][columnIndex] != '') data[i][columnIndex] = date;
    r.push(data[i]);
    }
    return r.sort(function (a, b) {
    if (ascOrDesc) return ((a[columnIndex] b[columnIndex]) ? 1 : 0));
    return ((a[columnIndex] > b[columnIndex]) ? -1 : ((a[columnIndex]
    0){
    var i = 0;
    var dataArray = new Array();
    var line = new Array();
    line.push(‘Title’);
    line.push(‘Start Date’);
    line.push(‘End Date’);
    line.push(‘Description’);
    line.push(‘Last Modified’);
    dataArray.push(line);
    for (i = 0; i < data.length; i++) {
    line = new Array();
    var row = data[i];
    var temp = row[3];// for the initial replacement my solution is unacceptable longterm, I have to add people individually I know I could just do a simple table,
    // but I wanted to cover variation so from a spreadsheet I build out what I paste in, it's replace.(Resource: Initials, Full Name) or replace.(firstname) for everyone and all likely variations….
    // it ends up being a very long line of code, suggestions welcomed!
    line.push(row[0]);
    line.push(row[1]);
    line.push(row[2]);
    line.push(temp);
    line.push(row[4]);
    dataArray.push(line);
    }
    }
    UpdateCalendar(dataArray);
    Logger.log(dataArray);

    var dataarray2 = new Array();
    var line2 = new Array();
    line2.push('Flagged for Discussion')
    line2.push('Title');
    line2.push('Title');
    line2.push('Resource');
    line2.push('Notes');
    line2.push('Status');
    line2.push('Category');
    line2.push('Start Date');
    line2.push('Duration');
    line2.push('End Date');
    line2.push('Worksheet Link');
    line2.push('Last Modified');
    dataarray2.push(line2);

    var j = 0;
    for (j = 1; j < data.length; j++) {
    line2 = new Array();
    var row = dataArray[j];

    var length = row[3].length;
    var locnotes = row[3].indexOf('Notes:');
    var locresource = row[3].indexOf('Resource:');
    var locstatus = row[3].indexOf('Status:')
    var loccategory= row[3].indexOf('Category: ')
    var locflagged = row[3].indexOf('Flagged for Discussion:');
    var locwslink = row[3].indexOf('Worksheet Link:');

    var splittitle = row[0].split("- ");
    var shorttitle = splittitle[2];
    Logger.log(shorttitle);

    var notes = row[3].slice("notes: ".length+locnotes,locresource);
    var resource = row[3].slice( "resource: ".length+locresource,locstatus);
    var status = row[3].slice("status: ".length+locstatus,loccategory);//will be ,loccategory); if we add it
    var category = row[3].slice("category: ".length+loccategory,locflagged);
    var flagged = row[3].slice("Flagged for Discussion: ".length+locflagged,locwslink);
    var worksheetlink = row[3].slice("Worksheet Link: ".length+locwslink,length);

    line2.push(flagged);
    line2.push(row[0]);
    line2.push(splittitle[2]);
    line2.push(resource);
    line2.push(notes);
    line2.push(status);
    line2.push(category);
    line2.push(row[1]);
    line2.push(row[2]-row[1]); //this needs to be 7 if we delete duration
    line2.push(row[2]);
    line2.push(worksheetlink);
    line2.push(row[4]);
    dataarray2.push(line2);
    }
    UpdateSpreadsheet(dataarray2);
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test3");
    sheet.clear();
    sheet.getRange(1, 1, dataarray2.length, dataarray2[0].length).setValues(dataarray2);
    }
    function UpdateCalendar(data)
    {
    var myCalendar = CalendarApp.openByName("Test2");

    // optional – delete existing events
    var events = myCalendar.getEvents(new Date("January 1, 2011 EST"),
    new Date("January 1, 2013 EST"));
    Logger.log(events);
    for (var i = 0; i < events.length; i++)
    {
    events[i].deleteEvent();
    }

    for (i in data)
    {
    var row = data[i];
    // assume that each row contains a date entry and a text entry
    var theTitle = row[0]; // First column of row
    var theStartDate = row[1]; // Second column of row
    var theEndDate = row[2]; // Third column of row
    var theDescription = row[3]; // Fourth column of row
    myCalendar.createEvent(theTitle, new Date(theStartDate),new Date(theEndDate),{description: theDescription});
    }

    }

    function UpdateSpreadsheet(data) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test2");
    sheet.clear();
    sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
    }

    function ClearCalendar()
    {
    var myCalendar = CalendarApp.openByName("Test2");

    // optional – delete existing events
    var events = myCalendar.getEvents(new Date("January 1, 2011 EST"),
    new Date("January 1, 2013 EST"));
    for (var i = 0; i < events.length; i++)
    {
    events[i].deleteEvent();
    }
    }

    cheney

    October 25, 2012 at 4:12 pm

    • These our my column headers that pull into the workbook in order:

      [Flagged for Discussion,Title,Title,Resource,Notes,Status,Category,Start Date,Duration,End Date,Worksheet Link,Last Modified]
      Handled in discussion for calendar event, in this order:
      Notes:
      Resource:
      Status:
      Category:
      Flagged for Discussion:

      cheney

      October 25, 2012 at 4:28 pm

  28. Hi cheney,

    Thanks for posting this script. This is exactly what I was looking for. One question… I continue to get the following error when pasting your script into the spreadsheet script editor.

    “Missing ) in parenthetical. (line 72)” Can you offer me any help with this? I’ve done my best to solve the syntax error but I’m beginner with google apps script.

    Thanks,

    nparson

    December 8, 2012 at 5:09 pm

    • Sure, feel free to share the workbook with me at: cjoseph@cmredis.com, and i’ll take a look at it. It may be helpful if you go ahead and share the calendar as well.

      cheney

      December 10, 2012 at 9:46 pm

      • Hi, thanks for the reply. I ended finding the problem in the missing parenthetical and I simplified the script so it just updates the calendar like i needed.

        thanks again for the help

        nparson

        December 10, 2012 at 11:12 pm

  29. Thanks a lot for this! I made an SMS alert system using Spreadsheet and Calendar.

    Subigya

    May 13, 2013 at 10:37 am

  30. Guys, sorry, i’m a complete newbie to Google Script. Can someone please help with getting this script work with the following three fields: “Name”, “Surname”, “Date”, which would result in an annual event entry in the calendar like this: “Name Surname Birthday” [at a corresponding date every year]?

    Pavel

    May 31, 2013 at 8:58 pm

  31. Quality articles is the main to invite the viewers to go to see the web
    page, that’s what this site is providing.

  32. […] used a combination of this helpful tutorial on OUseful.Info from 2010 along with Google’s Apps Scripts documentation to make it […]


Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 812 other followers

%d bloggers like this: