[GAS] register to Google Calendar from SpreadSheet #spreadsheet #calendar #google

main.js

const URL_BOOK = 'Spread Sheet URL (public)';
const SHEETNAME = 'Sheet Name';
const CALENDAR_ID = 'xxxxx@group.calendar.google.com';

function main() {
  register2Calendar();
}

function register2Calendar() {
  // create JSON (from SpreadSheet)
  const sheet = getSheet(URL_BOOK, SHEETNAME);
  const json = convertSheet2Json(sheet);

  // register to calendar
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);
  for (let i = 0; i < json.length; i++) {
    const event = json[i];
    const title = event.title;
    const date = event.date;
    const options = { description: event.description };
    Utilities.sleep(500);
    calendar.createAllDayEvent(title, date, options);
  }
}

function getSheet(bookUrl, sheetName) {
  const book = SpreadsheetApp.openByUrl(bookUrl);
  return book.getSheetByName(sheetName);
}

function convertSheet2Json(sheet) {
  // first line (title)
  const firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  const firstRowValues = firstRange.getValues();
  const titleColumns = firstRowValues[0];

  // after the second line (data)
  const lastRow = sheet.getLastRow();
  let rowValues = [];
  for (let rowIndex = 2; rowIndex <= lastRow; rowIndex++) {
    const colStartIndex = 1;
    const rowNum = 1;
    const range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    const values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  let jsonArray = [];
  for (let i = 0; i < rowValues.length; i++) {
    let line = rowValues[i];
    let json = {};
    for (let j = 0; j < titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}