CODEWITHSUNDEEP
javascriptgoogle-apps-script
pycod333
pycod333

Reputation: 764

sending data from a google spreadsheet program to google calender

Here is my case. A user fills a form for event booking, the submitted form is stored in a google spreadsheet which I have synced to a google calender so that it automatically sends the data to it. Everything is working fine apart from the fact that event times could clash. When customers book an event centre for let's say on 13/3/2015 T 10:00AM, if another user enters the same date and time, the entry should not be accepted.

To summarise it, I want to avoid a clash of events booking. Thank you all.

here is my script.

var calendarId = "mycalenderid";

//below are the column ids of that represents the values used in the spreadsheet (these are non zero indexed)
var startDtId = 9;
var endDtId = 10;
var titleId = 6;
var descId = 11;
var formTimeStampId = 1;

function getLatestAndSubmitToCalendar() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var lr = rows.getLastRow();
  var startDt = sheet.getRange(lr,startDtId,1,1).getValue();

  //set to first hour and minute of the day.
  //startDt.setHours(0);
  //startDt.setMinutes(00);

  var endDt = sheet.getRange(lr,endDtId,1,1).getValue();
  //set endDt to last hour and minute of the day
  //endDt.setHours(23);
 //endDt.setMinutes(59);

var subOn = "Submitted on:"+sheet.getRange(lr,formTimeStampId,1,1).getValue();
  var desc = "Added by :"+sheet.getRange(lr,descId,1,1).getValue()+"\n"+subOn;

  var title = sheet.getRange(lr,titleId,1,1).getValue()+"DIA";
    createEvent(calendarId,title,startDt,endDt,desc);
}
  
  function createEvent(calendarId,title,startDt,endDt,desc) {
  var cal = CalendarApp.getCalendarById(calendarId);

  var start = new Date(startDt);

  var end = new Date(endDt);

  var loc = 'Script Center';

  var event = cal.createEvent(title, start, end, {
      description : desc,
      location : loc
  });
    
    
};

Upvotes: 0

Views: 129

Answers (1)

pointNclick
pointNclick

Reputation: 1632

Here's a pseudocode of what you're trying to do:

function findEvent(desiredDateTime)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var range = sheet.getDataRange();
  var data = range.getValues();
  var lRow = range.getLastRow();

  var flag = true;
  var count = 0;
  while (flag == true || count < lRow)
  {
    if (desiredDateTime >= data[count][startDtId] && desiredDateTime <= data[count][endDtId])
    {
      flag = false;
    }
    else
    {
      count++;
    }
  }

  if (flag == true)
  {
    //Call function to add event
  }else{
    //Tell user desired date-time is not available.
    //If you're asking for user's email address, 
    //simplest approach would be to send an email.
  }
}

You might have to modify other bits and pieces of your code as well to accommodate this but it shouldn't be too hard. Hope this provides you with a certain direction to follow through.

Upvotes: 1

Related Questions