将Google日历数据导入到现有的Google电子表格中 [英] Import google calendar data into existing google spreadsheet

问题描述

我正在尝试在Google电子表格中保留Google日历条目的记录，以进一步处理数据.我有以下代码，是我从其他来源借来的:

I am trying to keep a record of google calendar entries in a google spreadsheet to further process the data. I have the following code, which I borrowed from other sources:

function importEvents(){
  var startOfDay = new Date();
      startOfDay.setUTCHours(0);
      startOfDay.setMinutes(0);
      startOfDay.setSeconds(0);
      startOfDay.setMilliseconds(0);  
  var endOfDay = new Date(startOfDay.getTime() + 24 * 60 * 60 * 1000);

  var Calendar = CalendarApp.getCalendarById("[calendarIDhere]");
  var events = Calendar.getEvents(startOfDay, endOfDay)
  var events_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ImportedEvents");
  var lr = events_sheet.getLastRow();

  var eventarray = new Array();

  var i = 0; // edited
    for (i = 0; i < events.length; i++) {
      line = new Array();
      line.push(events[i].getTitle());
      line.push(events[i].getDescription());
      line.push(events[i].getStartTime());
      line.push(events[i].getEndTime());
      eventarray.push(line);
    }

    events_sheet.getRange('A1:D' + (i)).setValues(eventarray);

  var l = events_sheet.getLastRow();
  var m = events_sheet.getMaxRows();
  events_sheet.deleteRows(l+1,m-l);
}

由于某种原因，每次导入一天的条目时，都会在ImportedEvents工作表中添加几个空行.这就是为什么我删除工作表末尾的空行的原因.

For some reason, every time I import a day's entries, several empty rows are added to the ImportedEvents sheet. This is why I delete the empty rows at the end of the sheet.

我想做的是让此脚本在ImportedEvents工作表中的现有条目下方附加新的导入条目.由于我的编程技巧有限，我无法弄清这一点(同样，上面的代码是实现其他地方提供的解决方案的结果.)

What I would like to do is to have this script append new imported entries below existing entries in the ImportedEvents sheet. I can't figure this out because of my limited programming skills (again, the code above is the result of implementing solutions provided elsewhere).

任何帮助将不胜感激！

编辑:根据Lothar的建议，我尝试了其他操作:

Based on Lothar's suggestion, I tried something else:

events_sheet.getRange("A"+(lr+1)+":D"+(lr+i)).setValues(eventarray);

它奏效了！另外，非常感谢所有赞成-1的人，以鼓励他们进行更深入的研究.

And it worked! Also, a very special thank you to whomever voted -1 for the incentive to dig deeper.

干杯！

推荐答案

尝试重写此行，以便

events_sheet.getRange('A1:D' + (lr)).setValues(eventarray);

如果您尝试将新(事件)数据附加到电子表格的末尾.否则，您将把数据放在与要获取的事件数相对应的行中.

if you tried to append the new (event) data to the end of the spreadsheet. Otherwise you will be putting the data in the row corresponding to the number of events you are getting.

这篇关于将Google日历数据导入到现有的Google电子表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！