Google日历应用脚本可定期将数据提取到电子表格中 [英] Google Calendar App Script to pull Data into a Spreadsheet periodically

问题描述

我一直在使用此脚本将Google日历中的数据提取到电子表格中.我有几个问题需要改进:

I've been using this script to pull Data from Google Calendar into a spreadsheet. I have a couple questions for improvements:

1. 是否可以将这些数据从多个日历拖到同一电子表格，而又不会相互覆盖?
2. 是否可以在电子表格中添加新的数据列，以标记每行来自哪个日历?
3. 此外，您如何将其设置为每24小时自动运行一次?

谢谢！

function caltest3(){
  var ss = SpreadsheetApp.openById("SPREADSHEET ID");
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var cal=CalendarApp.getCalendarById("CALENDAR ID");
  var sheet = SpreadsheetApp.getActiveSheet();

  var events = cal.getEvents(new Date("January 1, 2013"), new Date("January 13, 2013"));
for (var i=0;i<events.length;i++) {
  //http://www.google.com/google-d-s/scripts/class_calendarevent.html
      var details=[[events[i].getTitle(), events[i].getStartTime(),            
                    events[i].getEndTime(), events[i].getDescription(),
                    events[i].getLocation()]];
  var row=i+1;
  var range=sheet.getRange(row+1,1,1,5);
  range.setValues(details);
    }
  }
}

推荐答案

然后简短回答一个例子:

Short answers then an example:

1. 您不能仅通过调用CalendarApp来执行此操作，但是如果您遍历calendarIds列表并将该日历的结果事件添加到数组中，则可以对该数组进行排序并将其放入电子表格中
2. 只需将日历名称(或id)添加为details数组中的另一项，只需记住将范围列数增加1
3. 在任意时期添加脚本触发器，文档可以最好地描述这一点.

1. You cannot do this with a single call to the CalendarApp, but if you loop through a list of calendarIds and add the resultant events for that calendar to an array, you can then sort this array and put this to the spreadsheet
2. Simply add the calendar name (or id) as another item in your details array, just remember to increase your range column count by 1
3. Add a script trigger to whatever period you want, the documentation describes this best.

无论是从多个日历中构建一个日历事件，还是从一个日历中构建一个日历事件，将它们添加到循环中的数组中，然后在任何循环外将其写入电子表格，都比每次调用时逐行写入更好. getRange()& setValues方法是单独的API调用，对于App脚本而言，这些调用在时间上最昂贵.只需一次调用一个范围适合您的数据的脚本，脚本就会更快地运行一个数量级.

Whether you are building an array of calendar events from multiple calendars or just one, adding them to an array in a loop and then writing them to the spreadsheet outside any loop is better practice than writing row by row as every time you call the getRange() & setValues methods they are separate API calls and these calls are most expensive in time for Apps Script. Call each only once with a range to suit your data and your script will run an order of magnitude quicker.

下面的脚本说明了答案1& ;; 2.如果您要为此设置一个计时器，您的需求可能会有所不同，因为您可能希望影响查询的时间段?

Script below illustrates answers 1 & 2. Your needs may vary if you are setting a timer to this as you may be wanting to affect the period of time you are querying?

function caltest3(){

  var ss = SpreadsheetApp.openById( 'spreadsheetId' ),
      sheet = ss.getSheetByName( 'sheetName' ),
      cals = ['id1', 'id2', 'id3'], c, cal, calName,
      start = new Date( 'whenever' ), end = new Date( 'whenever' ),
      events, i, details,
      eventslog = [], e,
      rows = [], range;

  for (c = 0; c < cals.length; c += 1) {

    cal = CalendarApp.getCalendarById(cals[c]);
    calName = cal.getTitle();
    events = cal.getEvents(start, end);

    // add the events of the current calendar to the array of all events
    eventslog = eventslog.concat(
      events.map(function(event) {
        return {
          time: new Date(event.getStartTime()).getTime(), // sort by this
          details: [
            event.getTitle(),
            event.getStartTime(),
            event.getEndTime(),
            event.getDescription(),
            event.getLocation(),
            calName // change calendar info position in array to suit
          ]
        };
      })
    );
  }

  // sort array of event so date order can be either way by reversing a & b
  eventslog.sort(function(a, b) { return a.time - b.time; });

  rows = eventslog.map(function(entry) { return entry.details; });

  range = sheet.getRange(2, 1, rows.length, 6);
  range.setValues(rows);
}

为了清楚起见，代码被隔开

code spaced out for clarity

这篇关于Google日历应用脚本可定期将数据提取到电子表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！