Event Calendar Script

[Lucas Hammonds]

Good morning. I am working on automating a process by integrating google sheets with google calendar. This way, when we update our on-call sheet, it will send out a calendar invite. The script works, but not exactly as intended. Right now, when I run the macro, it will push to a calendar, but it will push to the calendar over and over each time I run the macro. First problem is, how can I get it to run a date and time only once? I am also wanting to target multiple calendars in the script. For example, our on-call calendar as well as individual calendars. Is this possible? Here is the script. 

Enter code here...   function scheduleShifts() {

/**
  Task 1) Open the Event Calendar.
**/
var spreadsheet = SpreadsheetApp.getActive();
var calendarID = spreadsheet.getRange("C2").getValue();
var eventCal = CalendarApp.getCalendarById(calendarID);

/**
  Task 2) Pull each shift information into the code, in
  a form that the code can understand. 
**/
var signups = spreadsheet.getRange("A5:C100").getValues();

/**
    [
      [11/9/2019 0:00:00,11/10/2019 0:00:00,Werdah On-Call],
      [11/10/2019 0:00:00,11/11/2019 0:00:00,Lucas On-Call],
      [11/16/2019 0:00:00,11/17/2019 0:00:00,Aiden On-Call],
      [11/17/2019 0:00:00,11/18/2019 0:00:00,Werdah On-Call],
      [11/23/2019 0:00:00,11/24/2019 0:00:00,Mayra On-Call],
      [11/24/2019 0:00:00,11/25/2019 0:00:00,Kyle On-Call],
      [11/30/2019 0:00:00,12/1/2019 0:00:00,Cavan On-Call],
      [12/1/2019 0:00:00,12/2/2019 0:00:00,Adam On-Call],
      [12/7/2019 0:00:00,12/8/2019 0:00:00,Hillary On-Call],
      [12/8/2019 0:00:00,12/9/2019 0:00:00,Melissa Pino On-Call],
      [12/14/2019 0:00:00,12/15/2019 0:00:00,Kristi On-Call],
      [12/15/2019 0:00:00,12/16/2019 0:00:00,Kevin On-Call],
      [12/21/2019 0:00:00,12/22/2019 0:00:00,Alice On-Call],
      [12/22/2019 0:00:00,12/23/2019 0:00:00,Carro On-Call],
      [12/28/2019 0:00:00,12/29/2019 0:00:00,Sruthi On-Call],
      [12/29/2019 0:00:00,12/30/2019 0:00:00,Tara On-Call]
    ]  
**/

/**
  Task 3) Do the work!
**/
for (x=0; x<signups.length; x++) {

var shift = signups[x];

var startTime = shift[0];
var endTime = shift[1];
var oncall = shift[2];

eventCal.createEvent(oncall, startTime, endTime);
  }
}

[Andrew Roberts]

I'd add another column to your GSheet called "Status", and once the event is created for that row set the status as something like "PROCESSED". The script can then check this field before creating the event each time.

[Lucas Hammonds]

Hey,

Thanks for the insights here. So, let's say I column D as the Status/Processed column, how can I add that to the script in the script editor? 

[Clay Smith]

Hi Lucas, I made some code adjustments below. I went along with your code so it can be followable. Basically You expand the range of data you retrieve. Then check your status column. If the status column doesn't have the specific word specified ('Added' in this case) it will push the appointment to the calendar. 

Hope this works  for you.

Clay

function scheduleShifts() {

/**
  Task 1) Open the Event Calendar.
**/
var spreadsheet = SpreadsheetApp.getActive();
var calendarID = spreadsheet.getRange("C2").getValue();
var eventCal = CalendarApp.getCalendarById(calendarID);

/**
  Task 2) Pull each shift information into the code, in
  a form that the code can understand. 
**/

var signups = spreadsheet.getRange("A5:D100").getValues(); //Expanded the range to be Column D

/**
    [
      [11/9/2019 0:00:00,11/10/2019 0:00:00,Werdah On-Call],
      [11/10/2019 0:00:00,11/11/2019 0:00:00,Lucas On-Call],
      [11/16/2019 0:00:00,11/17/2019 0:00:00,Aiden On-Call],
      [11/17/2019 0:00:00,11/18/2019 0:00:00,Werdah On-Call],
      [11/23/2019 0:00:00,11/24/2019 0:00:00,Mayra On-Call],
      [11/24/2019 0:00:00,11/25/2019 0:00:00,Kyle On-Call],
      [11/30/2019 0:00:00,12/1/2019 0:00:00,Cavan On-Call],
      [12/1/2019 0:00:00,12/2/2019 0:00:00,Adam On-Call],
      [12/7/2019 0:00:00,12/8/2019 0:00:00,Hillary On-Call],
      [12/8/2019 0:00:00,12/9/2019 0:00:00,Melissa Pino On-Call],
      [12/14/2019 0:00:00,12/15/2019 0:00:00,Kristi On-Call],
      [12/15/2019 0:00:00,12/16/2019 0:00:00,Kevin On-Call],
      [12/21/2019 0:00:00,12/22/2019 0:00:00,Alice On-Call],
      [12/22/2019 0:00:00,12/23/2019 0:00:00,Carro On-Call],
      [12/28/2019 0:00:00,12/29/2019 0:00:00,Sruthi On-Call],
      [12/29/2019 0:00:00,12/30/2019 0:00:00,Tara On-Call]
    ]  
**/

/**
  Task 3) Do the work!
**/
  for (x=0; x<signups.length; x++) {
    var shift = signups[x];

    if(shift[3] != 'Added'){ //This checks the column "D" for the Status. If it does NOT have the word "Added" it sets the variables and pushes to the calendar as well as marking the Status cell as "Added"

      var startTime = shift[0];
      var endTime = shift[1];
      var oncall = shift[2];
      eventCal.createEvent(oncall, startTime, endTime

);
      spreadsheet.getRange((x+1),4).setValue('Added'); //Gets the range os the Status cell for the row and marks it as Added
    }
  }
}

[Lucas Hammonds]

Awesome. Thanks, man. I will test it out and i am sure i can adjust accordingly with this jumping off point. Thanks, all. 

[Lucas Hammonds]

Hey. I forgot to include the last part of my code in my original post, which included a macro to push the script. When I try to add it to the new coding, I am not seeing the macro button on the sheet. I think I am off by a bracket somewhere. Here is the code. Where would this fall in the code you assisted with by adding column D previously?

Enter code here...function onOpen(){

  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sync to Calendar')
      .addItem('Schedule shifts now','scheduleShifts')
      .addToUi();

On Thursday, November 14, 2019 at 8:09:01 AM UTC-5, Clay Smith wrote:

[Clay Smith]

Feel free to post your full code. from this function it looks like the final function bracket is missing. functions like the onOpen are declared as a function with a name and any arguments then the code:

function onOpen(){ //the declaration of the function called onOpen with no arguments()
//your code
}//end of function

[Lucas Hammonds]

Cool. I was able to get the macro. Final thing I noticed is I am getting a message on line 52. "

Enter code here...      spreadsheet.getActiveRange((x+1),4).setValue('Added'); //Gets the range of the Status cell for the row and marks it as Added

TypeError: Range is not a function, it is object."