Skip to main content

Create Calendar Event on Form Submit

The following will create an event on the designated calendar when a user submits the form. With the spreadsheet of responses open, activate the Apps Script editor and use this:

var calendarId = "CHANGE ME!!"; //this is the ID of the calendar where you want the event to show up, this is found on the calendar settings page of the calendar in question
var formTimeStampId = 1; //timestamp of the form submission
var userId = 2           //autologs the username based on google account- this is a form setting which helps you restrict who can make calendar entries but may be overkill
var startDtId = 3;       //start date, which is the day the item is checked out
var iniId = 4;           //initials of the submitter - make sure you check the time box in the Google form
var descId = 5;          //description, which is the list of items checked out
var notesId = 6;         //notes entered by submitter

function getLatestAndSubmitToCalendar() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var lr = rows.getLastRow();
  
  //date/time is entered here but the pattern is sheet.getRange(lr,VARIABLE,1,1)getValue();
  var startDt = sheet.getRange(lr,startDtId,1,1).getValue();
  var ini = sheet.getRange(lr,iniId,1,1).getValue();
  var userName = sheet.getRange(lr,userId,1,1).getValue();
  var notes = "Notes:"+"\n"+sheet.getRange(lr,notesId,1,1).getValue()+"\n";
  var subOn = "Submitted on:"+"\n"+sheet.getRange(lr,formTimeStampId,1,1).getValue()+"\n";
  var desc = "Items Checked Out:"+"\n"+sheet.getRange(lr,descId,1,1).getValue()+"\n"+"\n"+subOn+"\n"+"Submitted By:"+"\n"+sheet.getRange(lr,iniId,1,1).getValue()+" - "+sheet.getRange(lr,userId,1,1).getValue()+"\n"+"\n"+notes+"\n";
  var title = "Check Out "+" "+sheet.getRange(lr,iniId,1,1).getValue()+" "+sheet.getRange(lr,descId,1,1).getValue();
    createAllDayEvent(calendarId,title,startDt,desc);
}

function createAllDayEvent(calendarId,title,startDt,desc) {
 
  var cal = CalendarApp.getCalendarById(calendarId);
  var start = new Date(startDt);
  var event = cal.createAllDayEvent(title, start, {
      description : desc,
  });
};

 

Back to top