带有onEdit触发器的Google日历Google日历事件自动刷新 [英] Google Calendar events to Google Sheers automatic refresh with onEdit trigger

问题描述

我正在尝试将时间事件从我的Google日历中提取到Google电子表格中。

I am trying to grab time events from my Google Calendar into a Google Spreadsheet.

在我的Google日历中创建新的时间事件时，该事件应自动同步到我的Google电子表格中。这应该由 onEdit 事件触发器自动完成。

When a new time-event is created in my Google Calendar this event should be automatically synchronized into my Google Spreadsheet. This should be done automatically by an onEdit event trigger.

目前仅通过刷新Google Spreadsheet来运行。

At the moment it is only running by refreshing the Google Spreadsheet.

也许有人可以通过更好的解决方案我的挑战。这是我的代码：

Maybe someone has a better solution for my challenge. Here is my code:

function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('myCalendar')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

function myCalendar(){

  var now=new Date();

 // Startzeit
  var startpoint=new Date(now.getTime()-60*60*24*365*1000);
  // Endzeit
  var endpoint=new Date(now.getTime()+60*60*24*1000*1000);

  var events=CalendarApp.getCalendarById("your-calendar-ID").getEvents(startpoint, endpoint);

  var ss=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TEST"); 

  ss.clear(); 

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

         ss.getRange(i+1,1 ).setValue(events[i].getTitle());
         ss.getRange(i+1,2).setValue(events[i].getDescription());
         ss.getRange(i+1,3).setValue(events[i].getStartTime());
         ss.getRange(i+1,4).setValue(events[i].getEndTime());
   }
 }

推荐答案

问题

在创建Google日历中的事件时执行更新电子表格的功能。

Execute a function updating a spreadsheet when an event in Google Calendar is created.

解决方案

使用每次事件触发的 EventUpdated 可安装触发器在日历中被修改（例如，创建，更新或删除-请参见参考）。从那里，您可以采用简单的方法（使用内置的 CalendarApp 类更新电子表格中的所有数据）或采用困难的方法（更新通过增量同步更改的数据） -请参阅官方指南。）

Use the EventUpdated installable trigger that is fired each time an event is modified in Calendar (e.g. created, updated, or deleted - see reference). From there, you can go the easy way (update all data in the spreadsheet with a built-in CalendarApp class) or the hard way (update data that was changed with incremental sync - see official guide).

第0部分-安装触发器

/**
 * Installs Calendar trigger;
 */
function calendarTrigger() {
  var trigger = ScriptApp.newTrigger('callback name here')
  .forUserCalendar('calendar owners email here')
  .onEventUpdated()
  .create();
}

第1部分-回调（日历->电子表格）

/**
 * Updates spreadsheet;
 * @param {Object} e event object;
 */
function updateSpreadsheet(e) {  
  //access spreadsheet;
  var ss = SpreadsheetApp.openById('target spreadsheet id');
  var sh = ss.getSheetByName('target sheet name');
  var datarng = sh.getDataRange(); //assumed that data is only calendar data;

  //access calendar;
  var calendar = CalendarApp.getCalendarById(e.calendarId);

  //set timeframes;
  var start = new Date();
  var end =new Date();

  //get year before and three after;
  start.setFullYear(start.getFullYear()-1);
  end.setFullYear(end.getFullYear()+3);

  //get events;
  var events = calendar.getEvents(start, end);

  //map events Array to a two-dimensional array of values;
  events = events.map(function(event){
    return [event.getTitle(),event.getDescription(),event.getStartTime(),event.getEndTime()];
  });

  //clear values;
  datarng.clear();

  //setup range;
  var rng = sh.getRange(1,1, events.length, events[0].length);

  //apply changes;
  rng.setValues(events);
}

注释

1. 根据Tanaike的评论-如果事件是通过脚本或请求触发的，则将触发（简单和可安装）触发为不触发非常重要（请参阅限制参考）。要启用此功能，您必须引入轮询或将其与 WebApp ，脚本将在创建事件后调用（请参见下文中的捆绑示例）。


2. 您的解决方案更适合向后流动：在电子表格中进行编辑->编辑


3. 使用 Date 内置的日历（如果您对其进行修改以在日历上执行操作而不是更新电子表格，ofc）。 -in对象的方法，例如 getFullYear（）（请参阅其他方法的参考资料），以使您的代码更灵活，更易于理解。顺便说一句，我会将一天中的ms数据存储为常量（ 86400000 ）。


4. 从不使用 getRange（）， getValue（）， setValue（）和类似方法循环（通常尽可能少地调用它们）-它们是I / O方法，因此速度很慢（您可以尝试在循环中写入> 200行来亲自查看）。首先获取所需的范围/值，进行修改并批量写入（例如，使用 setValues（）方法）。

1. As per Tanaike's comment - it is important to account for triggers (both simple and installable) to not firing if event is triggered via script or request (see restrictions reference). To enable such feature you will have to introduce polling or bundle with a WebApp that the script will call after creating an event (see below for a bundling sample).
2. Your solution is better suited for backwards flow: edit in spreadsheet -> edit in Calendar (if you modify it to perform ops on Calendar instead of updating the spreadsheet, ofc).
3. Make use of Date built-in object's methods like getFullYear() (see reference for other methods) to make your code more flexible and easier to understand. Btw, I would store "ms in a day" data as a constant (86400000).
4. Never use getRange(), getValue(), setValue() and similar methods in a loop (and in general call them as little as possible) - they are I/O methods and thus are slow (you can see for yourself by trying to write >200 rows in a loop). Get ranges/values needed at the start, perform modifications and write them in bulk (e.g. with setValues() method).

参考

1. EventUpdated 事件参考;


2. 日历增量同步指南;


3. Date 内置对象引用;


4. setValues（）方法参考;


5. 在Google Apps脚本中使用批处理；


6. 可安装和简单会触发限制；

1. EventUpdated event reference;
2. Calendar incremental synchronization guide;
3. Date built-in object reference;
4. setValues() method reference;
5. Using batch operations in Google Apps Script;
6. Installable and simple triggers restrictions;

---

WebApp捆绑

第0部分-前提条件

如果您要创建/更新/删除日历通过脚本执行事件，您可以将目标脚本与一个简单的WebApp捆绑在一起。您需要确保：

If you want to create / update / remove calendar events via script executions, you can bundle the target script with a simple WebApp. You'll need to make sure that:

1. 部署WebApp的访问权限设置为任何人，甚至是匿名（强烈建议引入某种形式的请求身份验证）；


2. WebApp代码具有名为的一个函数doPost 接受事件对象（通常命名为 e ，但由您决定）作为单个参数。

1. The WebApp is deployed with access set as anyone, even anonymous (it is strongly recommended to introduce some form of request authentication);
2. WebApp code has one function named doPost accepting event object (conventionally named e, but it's up to you) as a single argument.

第1部分-构建WebApp

此构建假定所有修改均在WebApp，但例如，您可以返回回调名称以在成功的请求上运行并处理调用脚本中的更新。由于在上面的回调中仅使用事件对象的 calendarId 属性，因此我们可以将仅具有以下属性集的自定义对象传递给它：

This build assumes that all modifications are made in the WebApp, but you can, for example, return callback name to run on successfull request and handle updates in the calling script. As only the calendarId property of the event object is used in the callback above, we can pass to it a custom object with only this property set:

/**
 * Callback for POST requests (always called "doPost");
 * @param {Object} e event object;
 * @return {Object} TextOutput;
 */
function doPost(e) {
  //access request params;
  var body = JSON.parse(e.postData.contents);

  //access calendar id;
  var calendarId = body.calendar;

  if(calendarId) {
    updateSpreadsheet({calendarId:calendarId}); //callback;
    return ContentService.createTextOutput('Success');
  }else {
    return ContentService.createTextOutput('Invalid request');
  }
}

第2部分-示例调用脚本

此构建假定调用脚本和WebApp是同一脚本项目（因此可以通过 ScriptApp.getService（）来访问其网址。 .getUrl（），否则粘贴在WebApp部署期间提供给您的内容）。熟悉 UrlFetchApp （请参见

This build assumes that calling script and the WebApp are the same script project (thus its Url can be accessed via ScriptApp.getService().getUrl(), otherwise paste the one provided to you during WebApp deployment). Being familiar with UrlFetchApp (see reference) is required for the build.

/**
 * Creates event;
 */
function createEvent() {
  var calendar = CalendarApp.getCalendarById('your calendar id here');

  //modify whatever you need to (this build creates a simple event);
  calendar.createEvent('TEST AUTO', new Date(), new Date()); 

  //construct request parameters;
  var params = {
    method: 'post',
    contentType: 'application/json',
    muteHttpExceptions: true,
    payload: JSON.stringify({
      calendar: calendar.getId()
    })
  };

  //send request and handle result;
  var updated = UrlFetchApp.fetch(ScriptApp.getService().getUrl(),params);
  Logger.log(updated); //should log "Success";
}

这篇关于带有onEdit触发器的Google日历Google日历事件自动刷新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！