Home make a schedule from Google Spreadsheet to Google Calendar using Apps Script

Questions

make a schedule from Google Spreadsheet to Google Calendar using Apps Script

May 1, 2023

Please give me feedback.
I would like to add a schedule from Google Spreadsheets to Google Calendar automatically.

Here is the script I wrote and when it runs, the values that I want to show appears well.

However, it still does not make a new schedule for Google Calendar.

function goCreate() {
  const calID = ""
  var eventCal = CalendarApp.getCalendarById(calID);
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var startDay = console.info( ss.getRange("D13").getValue());
  var endDay = console.info( ss.getRange("G13").getValue());
  var guests = console.info( ss.getRange("C8").getValue());
  var location = console.info( ss.getRange("D14").getValue());
  var description = console.info( ss.getRange("D16").getValue());
  var details = {'description': description, 'location' : location};
  
  eventCal.createAllDayEvent("Test",new Date(startDay),details);
}

function onOpen(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Sync to Cal").addItem('Create Events Now', 'goCreate').addToUi();
}

>Solution :

In your script, it seems that the value of console.info is directly used as the variable for createAllDayEvent. But, console.info returns no value. I think that this is the reason for your current issue. If your showing script is modified, how about the following modification?

Modified script:

function goCreate() {
  const calID = "";
  var eventCal = CalendarApp.getCalendarById(calID);
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var startDay = ss.getRange("D13").getValue();
  var endDay = ss.getRange("G13").getValue();
  var guests = ss.getRange("C8").getValue();
  var location = ss.getRange("D14").getValue();
  var description = ss.getRange("D16").getValue();

  // console.log({ startDay, endDay, guests, location, description }); // If you want to see the values in the log, please use this line.

  var details = {'description': description, 'location' : location}; // or { 'description': description, 'location': location, 'guests': guests };
  eventCal.createAllDayEvent("Test", new Date(startDay), details);
}

When this script is run, a new all-day event is created using the values of startDay, location, description.
By the way, in your script, endDay and guests are not used. In the case of createAllDayEvent method, only one date is used. And, if you want to use guests, please include it in details like var details = { 'description': description, 'location': location, 'guests': guests };