Add events from Google sheets to google calendar Using the Excel CSV Template to Google Calendar Import format and Reminders

Question

Using @Tanaike's 2nd model script from this answer, how would one restructure it such that it would fit the Excel CSV template to Google Calendar import format?

For example using this Excel CSV like format input:

Subject	Start Date	Start Time	End Date	End Time	All Day Event	Description	Location	Private	Reminder
MyEvent1	07/19/2023	10:00:00	07/19/2023	10:03:00	FALSE	MyDesc1	MyLocation1	FALSE	3
MyEvent2	07/19/2023	14:00:00	07/19/2023	14:03:00	FALSE	MyDesc2	MyLocation2	TRUE
MyEvent3	07/19/2023	18:15:00	07/19/2023	18:18:00	FALSE	MyDesc3	MyLocation3	FALSE	3

Tanaike's Code with the stages where I get difficulties with my modifications:


 /*
  var subj = ss.getRange("A2:A" + lr).getValues(); // data[i][1]
  var stad = ss.getRange("B2:B" + lr).getValues(); // data[i][2]
  var Stat = ss.getRange("C2:C" + lr).getValues(); // data[i][3]
  var endd = ss.getRange("D2:D" + lr).getValues(); // data[i][4]
  var endt = ss.getRange("E2:E" + lr).getValues(); // data[i][5]
  var alde = ss.getRange("F2:F" + lr).getValues(); // data[i][6]
  var desc = ss.getRange("J2:J" + lr).getValues(); // data[i][7]
  var loca = ss.getRange("H2:H" + lr).getValues(); // data[i][8]
  var priv = ss.getRange("I2:I" + lr).getValues(); // data[i][9]
  var remi = ss.getRange("J2:J" + lr).getValues(); // data[i][10]
  var crea = ss.getRange("K2:K" + lr).getValues(); // data[i][11]
 */

function addEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var cal = CalendarApp.getCalendarById("c_fe882662e583725f15fd4faa8c8fdf5124f3affe543778ecdcf0838d6eb17f26@group.calendar.google.com");
  var data = ss.getRange("A2:K" + lr).getValues();
  while (data[data.length - 1][0] == '') data.pop();
  var rangeList = [];
  for (var i = 0; i < data.length; i++) {
    if (data[i][11] == "created") continue;
    cal.createEvent(data[i][2]*data[i][3], data[i][4]*data[i][5], data[i][6], { location: data[i][8], description: data[i][7] }); // not sure about boolean logic use to combine date and time
    // ? cal.addPrivate(data[i][9]); // How do we add the private data?
    cal.addPopupReminder(data[i][10]);
    rangeList.push(`R${i + 3}`); // not sure about that line (It seems to add to the empty list at i +3 https://spreadsheet.dev/array-method-push-in-apps-script
  }
  if (rangeList.length == 0) return;
  ss.getRangeList(rangeList).setValue("created");
}

Google Calendar people seem not to have included the simple reminder requirement in the Excel CSV template.

How can we address that? The idea would be to have a CSV like Google sheet sheet importing the 11 Column of the example above into Google Calendar.

I found multiple similar questions, old and recent yet no workable suggestion:

Two of the most relevant questions found:

https://webapps.stackexchange.com/questions/112517/importing-reminders-to-google-calendar-using-csv-file

Add events from Google sheets to google calendar

and tutorial material:

https://wafflebytes.blogspot.com/2017/06/google-script-setting-up-reminder-dates.html

For reminders:

Google Script All Day Calendar Event with Notification On The Day

https://developers.google.com/apps-script/reference/calendar/calendar-event-series#addPopupReminder(Integer)

Prevent Duplicates:

Create Google Calendar Events from Spreadsheet but prevent duplicates

Excel CSV format:

https://www.reddit.com/r/excel/comments/k38xsf/comment/ge19zu6/?utm_source=share&utm_medium=web2x&context=3

Class Calendar:

https://developers.google.com/apps-script/reference/calendar/calendar

EDIT:

@Tanaike — Can I ask you about the detail of your input and output situation? , @SputnikDrunk2 — How do you use the Excel CSV file in your use case? Do you upload it to your Drive, or do you view it in a spreadsheet file?

Sure, thanks guys for asking for clarifications, sorry for my poor formulation.

I will import the Google Sheet Input directly from Google Sheets, using a modified version of your / @Tanaike's script, not from a CSV file, but I would like to use the same format as the default Excel CSV file sample sightly augmented as with the 11 columns from the table below:

Subject	Start Date	Start Time	End Date	End Time	All Day Event	Description	Location	Private	Reminder
MyEvent1	07/19/2023	10:00:00	07/19/2023	10:03:00	FALSE	MyDesc1	MyLocation1	FALSE	3
MyEvent2	07/19/2023	14:00:00	07/19/2023	14:03:00	FALSE	MyDesc2	MyLocation2	TRUE
MyEvent3	07/19/2023	18:15:00	07/19/2023	18:18:00	FALSE	MyDesc3	MyLocation3	FALSE	3

I corrected and adapted the my previous version of @Tanaike's code with Andres Duarte's suggestion help as follows:

 /*
  var subj = ss.getRange("A2:A" + lr).getValues(); // data[i][0]
  var stad = ss.getRange("B2:B" + lr).getValues(); // data[i][1]
  var stat = ss.getRange("C2:C" + lr).getValues(); // data[i][2]
  var endd = ss.getRange("D2:D" + lr).getValues(); // data[i][3]
  var endt = ss.getRange("E2:E" + lr).getValues(); // data[i][4]
  var alde = ss.getRange("F2:F" + lr).getValues(); // data[i][5]
  var desc = ss.getRange("J2:J" + lr).getValues(); // data[i][6]
  var loca = ss.getRange("H2:H" + lr).getValues(); // data[i][7]
  var priv = ss.getRange("I2:I" + lr).getValues(); // data[i][8]
  var remi = ss.getRange("J2:J" + lr).getValues(); // data[i][9]
  var crea = ss.getRange("K2:K" + lr).getValues(); // data[i][10]
 */

function addEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var cal = CalendarApp.getCalendarById("YOURGOOGLECALENDARIDHERE");

  var data = ss.getRange("A2:K" + lr).getValues();

  var dateANDTimeArr = ss.getRange("A2:K" + lr).getDisplayValues(); // getDisplayValues is needed for the .substring() methods below to work

  while (data[data.length - 1][0] == '') data.pop();
  var rangeList = [];
  for (var i = 0; i < data.length; i++) {
    if (data[i][11] == "created") continue;

    var theEventStartDate = dateANDTimeArr[i][1]; // Extract the Date values from Column B
    var startHour = dateANDTimeArr[i][2].substring(0, 2); // Extract The Hour values from Column C
    var startMin = dateANDTimeArr[i][2].substring(3, 5); // Extract The Minutes values from Column C
    
    var theEventEndDate = dateANDTimeArr[i][3]; // Extract the Date values in Column D
    var endHour = dateANDTimeArr[i][4].substring(0, 2); // Extract The Hour values from Column E
    var endMin = dateANDTimeArr[i][4].substring(3, 5); // Extract The Minutes values from Column E

    Logger.log("theEventStartDate : " + theEventStartDate);
    Logger.log("startHour : " + startHour);
    Logger.log("startMin : " + startMin);

    Logger.log("theEventEndDate : " + theEventEndDate);
    Logger.log("endHour : " + endHour);
    Logger.log("endMin : " + endMin);

    var startDate = new Date(theEventStartDate + " " + startHour + ":" + startMin + ":00"); // Combine the Dates and The Times values from Columns B and C into one value as needed by createEvent() method
    var endDate = new Date(theEventEndDate + " " + endHour + ":" + endMin + ":00"); // Combine the Dates and The Times from Columns D and E into one value as needed by createEvent() method

    // Logger.log("startDate : " + startDate);
    // Logger.log("endDate : " + endDate);

    cal.createEvent(data[i][0], startDate, endDate, { location: data[i][7], description: data[i][6] });
    
    cal.isAllDayEvent(data[i][5]); // need to set it according to Column 5 input
    
    // Logger.log("cal.setVisibility(CalendarApp.Visibility.PRIVATE)(data[i][8]) : " + cal.setVisibility(CalendarApp.Visibility.PRIVATE)(data[i][8]));
    
    cal.setVisibility(CalendarApp.Visibility.PRIVATE)(data[i][8]); // https://developers.google.com/apps-script/reference/calendar/calendar-event#setvisibilityvisibility https://stackoverflow.com/a/34909992/10789707

    cal.addPopupReminder(data[i][9]);

    rangeList.push(`R${i + 3}`); // not sure about that line (It seems to add to the empty list at i +3 https://spreadsheet.dev/array-method-push-in-apps-script
  }
  if (rangeList.length == 0) return;
  ss.getRangeList(rangeList).setValue("created");
}

Remaining Problems:

1st problem:

All Day Event Column:

As you can see in the screenshot above, I found the isAllDayEvent() method but it does not accept parameters and thus I cannot feed it the premade list of hardcoded boolean values set in column 6 I prepared in the Google Sheet input. What workaround would you suggest to allow the column 6 input to set the AllDayEvent parameter? I looked for a setAllDayEvent() method in the docs but it wasn't supplied by the google calendar people.

2nd Problem:

Private / setVisibility() Column:

TypeError: cal.setVisibility is not a function

Same issue as before but with the setVisibility() method. How do I use a workaround to feed it the premade list of hardcoded boolean values set in column 8 I prepared in the Google Sheet input.

3rd Problem:

Private / addPopupReminder() Column:

TypeError: cal.addPopupReminder is not a function

Same issue as before but with the addPopupReminder() method. How do I use a workaround to feed it the premade list of hardcoded integers values set in column 9 I prepared in the Google Sheet input.

SputnikDrunk2 · Accepted Answer

ALTERNATIVE SUGGESTION

NOTE: If you think your question has been misinterpreted, kindly clarify it again and include examples of your data and desired results.

Based on your question, I understand that you are doing this process:

Add an event or an all-day event based on the Created header from the sheet.
- If a row has an empty value, add it as an event.
- Otherwise, do nothing (means the row has already been created).
When creating the event, it will be based on the All Day Event header from the sheet.
- If the row has a true value, add it as an all-day event.
- If false, add it as an event.
Once the event or the all-day event has been added, update the row's value under Created header with "created".

I have refactored your script and split it into manageable pieces. You may check the tweaked script below.

Tweaked Script [UPDATED]

function addEvents() {
  var range = SpreadsheetApp.getActive().getActiveSheet().getDataRange();
  var rawData = range.getDisplayValues().filter(row => !row.join('').trim().length <= 0 ); //Filter 'rawData' & not include empty rows.

  //Add events based on the header named 'Created'. If a row has an empty value, it will call the function 'addCurrentEvent'; otherwise, it will be ignored.
  var result = rawData.map(column => {
    return !column[10].match(/created/i) ? column.map((cell, index) => index == 10 ? addCurrentEvent(column) : cell) : column
  });

  //Sets the sheet range (w/ actual rows that have data) with updated values.
  SpreadsheetApp.getActive().getActiveSheet().getRange(range.getA1Notation().replace(/(K)\d+$/, `$1${result.length}`)).setValues(result);
}

function addCurrentEvent(column) {
  var cal = CalendarApp.getCalendarById("••••••••••••••");
  var startDate = new Date(`${column[1]} ${column[2]}`);
  var endDate = new Date(`${column[3]} ${column[4]}`);
  var visibility = column[8].toLowerCase() == 'true' ? CalendarApp.Visibility.PRIVATE : CalendarApp.Visibility.PUBLIC;

  //'addEvent' will be called to run when creating an EVENT.
  const addEvent = (data) => {

    cal.createEvent(data[0], startDate, endDate, {
      location: data[7],
      description: data[6]
    }).setVisibility(visibility).addPopupReminder(parseFloat(data[9]));

    //Log for review
    console.log(`"${data[0]}" event has been added!

DETAILS

Start date: ${startDate}
End date: ${endDate}
Location: ${data[7]}
Description: ${data[6]}
Privacy: ${visibility}`);

    return `created`; //returns a 'created' value once the sheet row has been added
  };

  //'addAllDayEvent' will be called to run when creating an ALL DAY EVENT.
  const addAllDayEvent = (data) => {

    cal.createAllDayEvent(data[0], startDate, {
      location: data[7],
      description: data[6]
    }).setVisibility(visibility).addPopupReminder(parseFloat(data[9]));

    //Log for review
    console.log(`"${data[0]}" all day event has been added!

DETAILS

Start date: ${startDate}
End date: ${endDate}
Location: ${data[7]}
Description: ${data[6]}
Privacy: ${visibility}`);

    return `created`; //returns a 'created' value once the sheet row has been added
  };

  //Check and run the current data if it is an 'All Day Event' or an 'Event'
  return column[5].toLowerCase() == 'true' ? addAllDayEvent(column) : addEvent(column);
}

Demo

Dummy Sheet
E.g. MyEvent2 will be an all day event and should be private.

After running the addEvents function:

The events have been created:

The updated dummy sheet:

The MyEvent2 was added as an all day event and it is private.

The other events.

Add events from Google sheets to google calendar Using the Excel CSV Template to Google Calendar Import format and Reminders

EDIT:

Remaining Problems:

Answers (2)

ALTERNATIVE SUGGESTION

Modification points:

Modified script:

Note:

References:

Related Questions