Trouble with creating one-time calendar event from

Question

I'm having some trouble with my Google Apps Script code trying to have it take the information on one row of my sheet and create a single, Calendar event, with beginning and ending times.

    //push new events to calendar
function pushToCalendar() {

  //spreadsheet variables
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow(); 
  var range = sheet.getRange(2,1,lastRow,16);
  var values = range.getValues();   

  var numValues = 0;
  for (var i = 0; i < values.length; i++) {     
    //check to see if Age, Competition, Start Date, Start Time and End Date & Time are filled out
    if ((values[i][0].length == '<>') && (values[i][1].length == '<>') && (values[i][4].length == '<>') && (values[i][5].length == '<>') && (values[i][6].length == '<>') && (values[i][7].length == '<>')) {

      //check if it's been entered before  
      if ((values[i][8] == '')||(values[i][8] == null)) { 

        //create event https://developers.google.com/apps-script/class_calendarapp#createEvent
        var newEventTitle = 'Game: ' + values[i][0] + ' - ' + values[i][1];
        var startDay = Utilities.formatDate(new Date(values[i][3]), 'America/Chicago', 'MMMM dd, yyyy');
        var startTime = Utilities.formatDate(new Date(values[i][4]), 'America/Chicago', 'HH:mm');
        var start = startDay + ' ' + startTime;
        var endDay = Utilities.formatDate(new Date(values[i][5]), 'America/Chicago', 'MMMM dd, yyyy');
        var endTime = Utilities.formatDate(new Date(values[i][6]), 'America/Chicago', 'HH:mm');
        var end = endDay+ ' ' + endTime;
        var newEvent = CalendarApp.getDefaultCalendar().createEvent(newEventTitle, new Date(start), new Date(end), {location: values[i][12]});
        //get ID
        var newEventId = newEvent.getId();

        //mark as entered, enter ID
        sheet.getRange(i+2,8).setValue(newEventId);

      } //could edit here with an else statement
    }
    numValues++;
  }
}

I have pertinent information (used in the event title) in the first three columns, and Start Date in column 4, Start Time in column 5, End Date in column 6, and End Time in column 7. The other columns contain information that I'll add to the description of the event.

The problem I'm mostly running into is filtering the data set into events that have not been pushed to the Calendar yet, as well as the script accurately recording once it does push an event over, such that it will be able to filter it on the next cycle of the script.

Answer 1

I modified the code a little but not much. Here it is. I was able to get all six events on the calendar with very few changes.

function pushToCalendar() {

  //spreadsheet variables
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow(); 
  var range = sheet.getRange(2,1,lastRow,16);
  var values = range.getValues();   
  var numValues = 0;
  for (var i = 0; i < values.length; i++) {     
    //check to see if Age, Competition, Start Date, Start Time and End Date & Time are filled out
    if ((values[i][0]) && (values[i][1]) && (values[i][4]) && (values[i][5]) && (values[i][6]) && (values[i][7])) 
    {
      //check if it's been entered before  
      if (!values[i][8]) 
      { 
        //create event https://developers.google.com/apps-script/class_calendarapp#createEvent
        var newEventTitle = 'Game: ' + values[i][0] + ' - ' + values[i][1];
        var startDay = Utilities.formatDate(new Date(values[i][4]), 'GMT-5', 'MMMM dd, yyyy');
        var startTime = Utilities.formatDate(new Date(values[i][5]), 'GMT-5', 'HH:mm');
        var start = startDay + ' ' + startTime;
        var endDay = Utilities.formatDate(new Date(values[i][6]), 'GMT-5', 'MMMM dd, yyyy');
        var endTime = Utilities.formatDate(new Date(values[i][7]), 'GMT-5', 'HH:mm');
        var end = endDay+ ' ' + endTime;
        var newEvent = CalendarApp.getDefaultCalendar().createEvent(newEventTitle, new Date(start), new Date(end), {location: values[i][12]});
        //get ID
        var newEventId = newEvent.getId();
        //mark as entered, enter ID
        sheet.getRange(i+2,9).setValue(newEventId);//Why i+2
      } //could edit here with an else statement
    }
    numValues++;
  }
}

and here's my test data.

Title1  Title2          startDay    startTime   endDay  endTime completed               Location
Event1  Day1            4/1/2017    10:00 AM    4/1/2017    2:00 PM [e-mail]               Denver
Event1  Day2            4/2/2017    10:00 AM    4/2/2017    2:00 PM [e-mail]               Honolulu
Event1  Day3            4/3/2017    10:00 AM    4/3/2017    12:00 PM    [e-mail]               San Francisco
Event2  Day1            4/4/2017    10:00 AM    4/4/2017    2:00 PM [e-mail]               Denver
Event2  Day2            4/5/2017    10:00 AM    4/5/2017    2:00 PM [e-mail]               Honolulu
Event2  Day3            4/6/2017    10:00 AM    4/6/2017    12:00 PM    [e-mail]               San Francisco

Answer 2

I got it to work. I was referencing the wrong columns initially in the value check line. Silly me.

//push new events to calendar
function pushToCalendar() {

  //spreadsheet variables
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow(); 
  var range = sheet.getRange(2,1,lastRow,16);
  var values = range.getValues(); 

  //calendar variables
  var defaultCalendar = CalendarApp.getDefaultCalendar()

  var numValues = 0;
  for (var i = 0; i < values.length; i++) {     
    //check to see if Age, Competition, Start Date, Start Time and End Date & Time are filled out
  if ((values[i][0]) && (values[i][1]) && (values[i][3]) && (values[i][4]) && (values[i][5]) && (values[i][6])) 
  {

      //check if it's been entered before  
      if (values[i][8] != 'y') { 

        //create event https://developers.google.com/apps-script/class_calendarapp#createEvent
        var newEventTitle = 'Game: ' + values[i][0] + ' - ' + values[i][1];
        var startDay = Utilities.formatDate(new Date(values[i][3]), 'America/Chicago', 'MMMM dd, yyyy');
        var startTime = Utilities.formatDate(new Date(values[i][4]), 'America/Chicago', 'HH:mm');
        var start = startDay + ' ' + startTime;
        var endDay = Utilities.formatDate(new Date(values[i][5]), 'America/Chicago', 'MMMM dd, yyyy');
        var endTime = Utilities.formatDate(new Date(values[i][6]), 'America/Chicago', 'HH:mm');
        var end = endDay+ ' ' + endTime;
        var newEvent = defaultCalendar.createEvent(newEventTitle, new Date(start), new Date(end), {location: values[i][12]});
        //get ID
        var newEventId = newEvent.getId();

        //mark as entered, enter ID
        sheet.getRange(i+2,8).setValue('y');
        sheet.getRange(i+2,9).setValue(newEventId);

      } //could edit here with an else statement
    }

    numValues++;
  }
}

• Spreadsheet has header row
• Column 1 (value[i][0]) is Event Title info
• Column 2 (value[i][1]) is Event Title info
• Column 3 (value[i][2]) is Event Title info
• Column 4 (value[i][3]) is Start Date
• Column 5 (value[i][4]) is Start Time
• Column 6 (value[i][5]) is End Date
• Column 7 (value[i][6]) is End Time
• Column 8 (value[i][7]) contains 'y' if event has been pushed to Calendar
• Column 9 (value[i][8]) contains EventID of event created by script
• Columns after that have pertinent event info that can be added to the event description