Auto-create google calendar events from multiple sheets in a workbook using a trigger and without cr-CodePudding

I am woefully ignorant here, so any help is much appreciated. My apps script code is sloppy af and piecemealed together from examples online. I know it is trash and does not account for most of what I need. That's why I'm here. My goal is to auto-populate google calendar events from multiple sheets in a workbook and create a trigger so that anytime content is edited in those cells a new event populates (or the event edited) but repeat events aren't created.

The cells I'd be pulling data from will be the same for each sheet: C4:C24 (these cells hold the title of the event) and E4:F24 (these cells hold start date and time and end date and time respectively).

The trigger I have set up is on edit but when I make an edit, duplicate events get created each time, which I do not want.

Any help is incredibly appreciated. Thank you!

// function to retrieve data from Sheet and add to Calendar 
function simpleSheetsToCalendar() { 

// get spreadsheet 
var spreadsheet = SpreadsheetApp.getActiveSheet(); 
var eventCal = 
CalendarApp.getCalendarById('actualCalendarId');  
var signups = spreadsheet.getRange("C4:F24").getValues();
for (x=0; x<signups.length;x  )
{
var shift = signups[x];
var task= shift[0];
var startTime = shift[2];
var endTime = shift[3];
eventCal.createEvent(task, startTime, endTime);} }

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the values from the cells "C4:F24" from multiple sheets in a Google Spreadsheet.
- The columns "C", "E", and "F" are the event title, start time, and end time, respectively.
You want to create a new event to a Google Calendar using the retrieved values. At that time, you don't want to create the duplicated event title.

Sample script:

In this sample script, in order to retrieve all titles of the calendar event, Calendar API is used. So before you use this script, please enable Calendar API at Advanced Google services. And, please set sheetNames and calendarId.

function simpleSheetsToCalendar() {
  var sheetNames = ["Sheet1", "Sheet3",,,]; // Please set the sheet names you want to use.
  var calendarId = "###"; // Please set your calendar ID.

  // 1. Retrieve all events from the Calendar and create an object for checking the duplicated titles.
  var events = [];
  var pageToken = "";
  do {
    var res = Calendar.Events.list(calendarId, {maxResults: 2500, fields: "nextPageToken,items(summary)", pageToken});
    events = events.concat(res.items);
    pageToken = res.nextPageToken;
  } while(pageToken);
  var obj = events.reduce((o, e) => Object.assign(o, {[e.summary]: true}), {});

  // 2. Retrieve sheets from a Google Spreadsheet and retrieve the values from "C4:F24", and create an object for creating new events.
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var values = spreadsheet.getSheets().reduce((o, sheet) => {
    if (!sheetNames.includes(sheet.getSheetName())) return o;
    var signups = sheet.getRange("C4:F24").getValues();
    signups.forEach(([title,,startTime,endTime]) => {
      if (!obj[title] && title && startTime && endTime && !o[title]) o[title] = {startTime,endTime};
    });
    return o;
  }, {});

  // 3. Create new events.
  var ar = Object.entries(values);
  if (ar.length == 0) return;
  var eventCal = CalendarApp.getCalendarById(calendarId);
  ar.forEach(([title, {startTime, endTime}]) => eventCal.createEvent(title, startTime, endTime));
}

When this script is run, all event titles in the calendar are retrieved, and the values are retrieved from "C4:F24" on the multiple sheets in a Google Spreadsheet. And then, new events are created by checking the duplicated title.

Note:

In this sample script, the event title is checked for checking the duplicated events. If you want to check other properties, please modify the above sample script.

Sample script:

Note:

References: