google-app script for extracting calendar data

sheet-cal.js

function listUpcomingEventsInternal(today, calendarId, colorMap, targets) {
  var startOfWeek = new Date(today);
  startOfWeek.setHours(0, 0, 0, 0);
  if (startOfWeek.getDay() !== 1) {
    startOfWeek.setHours(-24 * (startOfWeek.getDay() - 1));
  }
  var endOfWeek = new Date(startOfWeek);
  endOfWeek.setHours(+24 * 7);
  var optionalArgs = {
    timeMin: startOfWeek.toISOString(),
    timeMax: endOfWeek.toISOString(),
    showDeleted: false,
    singleEvents: true,
    maxResults: 1999,
    orderBy: 'startTime'
  };
  var response = Calendar.Events.list(calendarId, optionalArgs);
  var events = response.items;
  var eventDtoList = [];
  for (i = 0; i < events.length; i++) {
    var event = events[i];
    var when = event.start.dateTime;
    if (!when) {
      when = event.start.date;
    }
    var until = event.end.dateTime;
    if (!until) {
      until = event.end.date;
    }
    if (when.length == 10) {
      when = `${when} 00:00:00+05:30`
    }
    if (until.length == 10) {
      until = `${until} 00:00:00+05:30`
    }
    var whenDate = new Date(when);
    var untilDate = new Date(until);
    var summary = event.summary;
    var colorId = event.colorId;
    if (!colorId) {
      colorId = '';
    }
    var domain = 'unknown';
    if (colorMap[colorId] !== undefined) {
      domain = colorMap[colorId];
    }
    var duration = untilDate.getTime() - whenDate.getTime();
    var durationMin = duration / 60000;

    var conferenceLink = '';
    var conferenceData = event.conferenceData;
    if (!!conferenceData) {
      var entryPoints = conferenceData.entryPoints;
      if (!!entryPoints && entryPoints.length > 0) {
        var googleMeet = entryPoints.find(x => x.entryPointType == "video");
        conferenceLink = googleMeet.uri;
      }
    }
    
    var object = {
      'summary': summary,
      'start': whenDate,
      'end': untilDate,
      'colorId': colorId,
      'domain': domain,
      'durationMillis': duration,
      'duration': durationMin,
      'link': conferenceLink,
    };

    Logger.log('DEBUG: event %d: %s, %s', i, object, event);
    eventDtoList.push(object);
  }

  var durationByDomain = {};
  eventDtoList.forEach(event => {
    var current = durationByDomain[event.domain];
    if (current === undefined) {
      current = 0;
    }
    durationByDomain[event.domain] = current + event.duration;
  });

  var durationByDomainToday = {};
  eventDtoList.forEach(event => {
    if (event.start.getFullYear() == today.getFullYear() &&
      event.start.getMonth() == today.getMonth() &&
      event.start.getDate() == today.getDate()) {
      var current = durationByDomainToday[event.domain];
      if (current === undefined) {
        current = 0;
      }
      durationByDomainToday[event.domain] = current + event.duration;
    }
  });

  var targetsMap = targets.map((domainTarget) => {
    var [domain, weeklyTarget, dailyTarget] = domainTarget;
    var currentWeek = durationByDomain[domain];
    var currentDay = durationByDomainToday[domain];
    if (currentWeek === undefined) currentWeek = 0;
        if (currentDay === undefined) currentDay = 0;
    
    var achievedWeek = (currentWeek / weeklyTarget).toFixed(2);
    var achievedDay = (currentDay / dailyTarget).toFixed(2);


    return [domain, weeklyTarget, dailyTarget, currentWeek, currentDay, achievedWeek, achievedDay];
  });

 
  return {
    'events': eventDtoList,
    'targets': targetsMap
  };
}

function writeUpcomingEventsInternal(now, sheetId, sheetName, results) {
  var styles = {

  };
  styles.bold = SpreadsheetApp.newTextStyle().setBold(true).build();

  var now = new Date();
  var events = results['events'];

  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
  sheet.clearContents();
  sheet.clearFormats();

  var writeBack = [];

  var headers = ['domain', 'start-time', 'end-time', 'summary', 'link', 'state'];
  var headerLength = headers.length;
  writeBack.push(headers.map(x => SpreadsheetApp.newRichTextValue()  .setText(x)
  .setTextStyle(styles.bold)
  .build()));

  events.map(event => {
    var eventIsToday = event.start.getFullYear() == now.getFullYear() &&
     event.start.getMonth() == now.getMonth() &&
     event.start.getDate() == now.getDate();
    if (eventIsToday) {
      var startMin = event.start.getHours() * 60 + event.start.getMinutes();
      var endMin = event.end.getHours() * 60 + event.end.getMinutes();
      var nowMin = now.getHours() * 60 + now.getMinutes();

      var state = "";
      if (nowMin > startMin && nowMin < endMin) {
        state = "ONGOING";
      }
      if (nowMin > endMin) {
        state = "ENDED";
      }
      if (startMin > nowMin && (startMin - nowMin) < 30) {
        state = "UPCOMING";
      }

      var link = event.link;
      if (link !== '') {
        var text = link;
        if (text.startsWith('https://meet.google.com/')) {
          text = text.substring('https://meet.google.com/'.length);
        }
        var linkRtv = SpreadsheetApp.newRichTextValue().setText(text).setLinkUrl(link).build();
        link = linkRtv;
      }
      writeBack.push([event.domain, event.start, event.end, event.summary, link, state]);
    }
  });

  writeBack.forEach((row, rowIdx) => {
    row.forEach((column, colIdx) => {
      var cell = sheet.getRange(rowIdx + 1, colIdx + 1);
      Logger.log("%s, %s, %s", rowIdx, colIdx, typeof(column));
      if (typeof(column) == 'object') {
        try {
          cell.setRichTextValue(column);
        } catch (err) {
          Logger.log("failed to set values %s", err);
          cell.setValue(column);
        }
      } else {
        cell.setValue(column);
      }
    })
  });

  sheet.getRange(1, 8).setRichTextValue(
    SpreadsheetApp.newRichTextValue().setText("now").setTextStyle(styles.bold).build()
    );
  sheet.getRange(1, 9).setValue(now);

  var refRow = 3;
  var refCol = 8;
var targets = results['targets'];

  var targetHeaders = ['target', 'current', 'daily', 'percent', 'current', 'weekly', 'percent'];
  sheet.getRange(refRow, refCol, 1, targetHeaders.length).setRichTextValues([
targetHeaders.map(x => SpreadsheetApp.newRichTextValue().setText(x).setTextStyle(styles.bold).build())
  ]);

  targets.forEach((aobj, rowIdx) => {
    var [domain, weeklyTarget, dailyTarget, currentWeek, currentDay, achievedWeek, achievedDay] = aobj;
    var wb = [];
    
    wb.push(
      domain,
      currentDay,
      dailyTarget,
      achievedDay,
       currentWeek,
        weeklyTarget,
         achievedWeek);
         if (domain === 'unknown') {
           wb[3] = ''
           wb[6] = ''
           wb[2] = ''
           wb[5] = ''
         }
    sheet.getRange(refRow + 1 + rowIdx, refCol, 1, wb.length).setValues([wb]);

    sheet.getRange(refRow+1+rowIdx, refCol + 3).setNumberFormat("##.#%");
    sheet.getRange(refRow+1+rowIdx, refCol + 6).setNumberFormat("##.#%");
  });


  sheet.setColumnWidth(1, 100);
    sheet.setColumnWidth(2, 100);
      sheet.setColumnWidth(3, 100);
  sheet.setColumnWidth(4, 300);
    sheet.setColumnWidth(5, 100);

// rgb(252, 229, 205) orange
// rgb(204, 204, 204) grey
// rgb(182, 215, 168) green
// rgb(244, 204, 204) red
  var rule1 = SpreadsheetApp.newConditionalFormatRule()
  .whenFormulaSatisfied('=EQ($F2, "ENDED")')
  .setRanges([sheet.getRange("A2:F1000")])
  .setBackground("rgb(204, 204, 204)")
  .build();
    var rule2 = SpreadsheetApp.newConditionalFormatRule()
  .whenFormulaSatisfied('=EQ($F2, "ONGOING")')
  .setRanges([sheet.getRange("A2:F1000")])
  .setBackground("rgb(182, 215, 168)")
  .build();
      var rule3 = SpreadsheetApp.newConditionalFormatRule()
  .whenFormulaSatisfied('=EQ($F2, "UPCOMING")')
  .setRanges([sheet.getRange("A2:F1000")])
  .setBackground("rgb(252, 229, 205)")
  .build();

  var rule4 = SpreadsheetApp.newConditionalFormatRule()
  .whenFormulaSatisfied('=$K4>1')
  .setRanges([sheet.getRange(refRow+1, refCol, targets.length-1, 4)])
  .setBackground("rgb(244, 204, 204)")
  .build();

  var rules = sheet.getConditionalFormatRules();
  rules.push(rule1, rule2, rule3, rule4);
  sheet.setConditionalFormatRules(rules);
}

function listUpcomingEvents() {
  var now = new Date();
  var calendarId = 'primary';

  var colorMap = {
    '': 'unknown',
    '3': 'product',
    '6': 'infra',
  };

  var targets = [
    ['work', 20 * 60, 3 * 60],
    ['email', 5 * 60, 1 * 60],
    ['unknown', 1, 1],
  ];

  var sheetId = 'zz-xyz';
  var sheetName = 'calendar-sheet';

  results = listUpcomingEventsInternal(now, calendarId, colorMap, targets);
  writeUpcomingEventsInternal(now, sheetId, sheetName, results);
}