Google Script to add events to a calendar

[ThatBoringBloke]

Hi Guy's,

 

Firstly, I'm not a coder/script guru. I have never looked at GScript before so have very little idea about it.

 

I've been asked to try and setup a process where by our staff enter events onto a Google Form, which then automatically records the returns to a Google Sheet. From the Google Sheet I want to run and add-on script that automatically creates All Day events in the calendar. All of the events created need to be All Day events and its this which I'm struggling with.

 

The form that I'm using is this one, though I have modified it slightly to match the questions and responses we want.

 

This is the Script that is part of the template:

var EVENT_EXPORTED = "EVENT_EXPORTED";
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menuEntries = [{name: "Add assignments to calendar", functionName: "importCalendar"}];
 ss.addMenu("Script: Upload Assignments", menuEntries);
}


function importCalendar() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var startcolumn = 1;  // First column of data to process
   var numcolumns = 500;   // Number of columns to process
   var dataRange = sheet.getRange(startcolumn, 1, numcolumns, 8)   // Fetch values for each column in the Range.
   var data = dataRange.getValues();
 for (var i = 0; i < data.length; ++i) { // this is a "for loop" that asks the script to run through all rows.
   var column = data[i];
   var title = column[1];        // 1st column in spreadsheet "Title"
   var date = column[2];         // 2nd column in spreadsheet "Due Date"
   var location = column[3];     // 3rd column in spreadsheet "Category"
   var description = column[4];  // 4th column in spreadsheet "Description"
   var startDate = column[5];    // 5th column in spreadsheet "Start Date and Time"
   var endDate = column[6];      // 6th column in spreadsheet "End Date and Time"
   var eventImported = column[7];// 7th column in spreadsheet "Import Status"
   
   
   var setupInfo = ss.getSheetByName("Setup");
    var calendarName = setupInfo.getRange("A3").getValue();
   
  

   if (eventImported  != EVENT_EXPORTED && title != "") {  // Prevents importing duplicates
   var cal = CalendarApp.openByName(calendarName);
   var advancedArgs = {description: description, location: location};
     
  
    try
 {
 if ( startDate == "" )
     { cal.createAllDayEvent(title, date, advancedArgs);
     }
      else
      {  cal.createEvent(title, new Date(startDate), new Date(endDate), advancedArgs);
      }
 }
catch(err)
 {
var name = Browser.inputBox('Missing Calendar Name', 'Enter Name Here', Browser.Buttons.OK_CANCEL);

var sheet1 = ss.getSheetByName('Setup');
     
var cell = sheet1.getRange("A3");
cell.setValue(name);

   
 }
    
 var sheet2 = ss.getSheetByName('Calendar Data');
 sheet.getRange(startcolumn + i, 8).setValue(EVENT_EXPORTED);
  
     SpreadsheetApp.flush();  
   
 }
}
}

 

On my Google Form I have simply modified the questions so that only the ones I want responses to are shown, i.e. Event title, Date of Event, event category. These go into the appropriate cells on the linked Google sheet.

 

Whenever I create an event without a set date and time they go into the calendar starting at 6am. Help!!!