将多个Google日历提取到单个Google工作表 [英] Extract multiple Google calendars to single Google sheet

问题描述

我使用Google脚本从资源日历中提取数据。该脚本按预期提取单个日历，但我想将多个资源日历提取到1个工作表中。有人能指引我朝着正确的方向吗？我已经着眼于创建一个包含多个日历到'mycal'的数组，但是我无法运行它。谢谢

I'm using a Google script to extract data from a resource calendar. The script works as intended to extract a single calendar, but I would like to extract multiple resource calendars into 1 sheet. Can someone point me in the right direction? I've looked at creating an array to include multiple calendars into 'mycal', but I couldn't get it to run. Thanks

function export_gcal_to_gsheet(){

var mycal = "[e-mail]";
var cal = CalendarApp.getCalendarById(mycal);

var today = new Date();
var events = CalendarApp.getCalendarById(mycal).getEventsForDay(today);

var sheet = SpreadsheetApp.getActiveSheet();

sheet.clearContents();  

var header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event"]]
var range = sheet.getRange(1,1,1,14);
range.setValues(header);

for (var i=0;i<events.length;i++) {
var row=i+2;
var myformula_placeholder = '';
var details=[[mycal,events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), myformula_placeholder, ('' + events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent()]];
var range=sheet.getRange(row,1,1,14);
range.setValues(details);

var cell=sheet.getRange(row,7);
cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))');
cell.setNumberFormat('.00');  

}
}

推荐答案

如果你将mycal变成一个数组，你将需要第二个循环来遍历该数组，并且基于mycal的变量赋值将需要在该循环中。你已经拥有的事件循环也需要在循环中。由于每个日历中的事件数量可能不同，因此维护行计数器的最佳方式是在两个循环之前声明它，并在写入行之后将其增加。

If you make mycal into an array you will need a second loop to step thru that array and the variable assignment based on mycal will need to be in that loop. the events loop you already have will also need to be inside that loop. Because the number of events in each calendar could be different the best way to maintain the row counter is to declare it before both loops and increment it after you write to a row.

function export_gcal_to_gsheet(){


  var mycal = ["[e-mail]",
             "calenderaddress2",
             "calenderaddress3",
             "calenderaddress4"];//note , between address, no , after last




  //here we do all the things that need to be done only once.

  var today = new Date();
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clearContents();  

  var header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event"]]
  var range = sheet.getRange(1,1,1,14);
  range.setValues(header);


  var row=2;// this needs to be outside the loops in order to use it as a counter in nested loops


  for (var j = 0; j< mycal.lengh; j++){
    //here we do the things we do once per calander

    var events = CalendarApp.getCalendarById(mycal[j]).getEventsForDay(today);//refer to the calender in mycal based on loop counter
    var cal = CalendarApp.getCalendarById(mycal[j]);//refer to the calender in mycal based on loop counter
    //incerting a header for each calander would be done here. make sure to increment row

    for (var i=0;i<events.length;i++) {

      var myformula_placeholder = '';
      var details=[[mycal,events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), myformula_placeholder, ('' + events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent()]];
      var range=sheet.getRange(row,1,1,14);
      range.setValues(details);

      var cell=sheet.getRange(row,7);
      cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))');
      cell.setNumberFormat('.00');  

      row++; //increment row counter.

    }

  }
}

这篇关于将多个Google日历提取到单个Google工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！