Google Sheets to Google Calendar by Column AND Rows

Question

I figured how to create all day calendar in rows, but I kept tweaking the codes and somehow I am unable to get it right. Please help ):

This works if both dates & event names are in rows. But I need the dates to be in column & event names in rows

My ideal sheet looks like this: https://i.sstatic.net/3H25A.jpg

    const roster = CalendarApp.getCalendarById("XXXcalendar.google.com");

function createMultipleEvents() {
  const ws = SpreadsheetApp.getActiveSpreadsheet();
  const ss = ws.getActiveSheet();
  for (var i = 2; i < ss.getLastRow(); i++) {
    const eventNames = ss.getRange(i, 1).getValue();
    const date = ss.getRange(i, 2).getValue();
    var startingDate = new Date(date)
    roster.createAllDayEvent(eventNames, startingDate)
  }

Answer 1

I believe your goal is as follows.

• By modifying your script, you want to retrieve the values from your showing image and create events in the Calendar.

In your showing script, the columns "A" and "B" are event names and dates. In the case of your showing image, 1st row has the dates, and the rows from 2nd row have the event names. In order to achieve your goal, how about the following modification?

Modified script:

function createMultipleEvents() {
  const roster = CalendarApp.getCalendarById("XXXcalendar.google.com");
  const ws = SpreadsheetApp.getActiveSpreadsheet();
  const ss = ws.getActiveSheet();
  const values = ss.getDataRange().getValues();
  const transposed = values[0].map((_, c) => values.map(r => r[c]).filter(String));
  transposed.forEach(([d, ...v]) => {
    const startingDate = new Date(d); // If "d" is the date object, you can directly use it.
    v.forEach(eventName => {
      roster.createAllDayEvent(eventName, startingDate);
    });
  });
}

• In this modification, the values retrieved from the active sheet are transposed. And, each event is created using the transposed values.

References:

• map()
• forEach()