时间戳

首页 > 解决方案 > 谷歌日历列表事件，谷歌表格中未显示输出

google-apps-script - 谷歌日历列表事件，谷歌表格中未显示输出

问题描述

我正在尝试生成一份报告以列出日历中的当前事件，但不知何故我在谷歌表格中看不到输出。有人可以帮我下面的代码吗？我一直在寻找和调整，但没有运气（脑死 ATM）

函数列表事件（）{

var ss, range, ui, rcalendarId, ask, askDate, askEnd,cal, start, end, events, num, dateString, eStart,sDate, sTime, string, eEnd, eDate, eTime, data;
ss = SpreadsheetApp.openById('SheetIDHERE').getSheetByName('SHEETNAMEHERE');
range = ss.getRange(1,1,1,8).setValues([ ["Date Created","Title","Description","Location","Start Date","End Date", "Id", "Creator"] ]);

ui = SpreadsheetApp.getUi();
ask = ui.prompt('Search for resource calendar list. Enter the resource calendar id');

askDate = ui.prompt('Enter the Start Date. Date must be "Month Date, Year" format (i.e. May 1, 1970)');
start = new Date(askDate.getResponseText());
askEnd = ui.prompt('Enter the End Date. Date must be "Month Date, Year" format (i.e. May 1, 1970)');

end = new Date (askEnd.getResponseText()); 
cal = CalendarApp.getCalendarById(ask.getResponseText());

events = cal.getEvents(start,end); 
num = events.length;

for (var i = 0; i < num; i ++) {

// Column: Start Date
eStart = events[i].getStartTime();
sDate = eStart.toDateString();
sTime = sDate.toTimeString();
// Column: End Date
eEnd = events[i].getEndTime();
eDate = eEnd.toDateString();
eTime = eDate.toTimeString();
ss.getRange(i+2,1,1,8).setValues[ [events[i].getDateCreated(),
                                events[i].getTitle(),
                                events[i].getDescription(),
                                events[i].getLocation(),
                                sDate + " " + sTime,
                                eDate + " " + eTime,
                                events[i].getId(),
                                events[i].getCreators()] ];

} }

我希望这些值会发布在谷歌表格中。

标签： google-apps-scriptgoogle-sheetsgoogle-calendar-api

解决方案

这个改装怎么样？

修改点：

  1. 请使用toTimeString()日期对象。
  2. 在您的脚本中，需要修改setValues[ [events[i].getDateCreated(),,,of 。ss.getRange(i+2,1,1,8).setValues[ [events[i].getDateCreated(),,,

当以上几点反映到您的脚本时，它变成如下。

修改后的脚本：

从： 
eStart = events[i].getStartTime();
sDate = eStart.toDateString();
sTime = sDate.toTimeString();
// Column: End Date
eEnd = events[i].getEndTime();
eDate = eEnd.toDateString();
eTime = eDate.toTimeString();
ss.getRange(i+2,1,1,8).setValues[ [events[i].getDateCreated(),
                                events[i].getTitle(),
                                events[i].getDescription(),
                                events[i].getLocation(),
                                sDate + " " + sTime,
                                eDate + " " + eTime,
                                events[i].getId(),
                                events[i].getCreators()] ];
至： 
var eStart = events[i].getStartTime();
var sDate = eStart.toDateString();
var sTime = eStart.toTimeString(); // Modified
    // Column: End Date
var eEnd = events[i].getEndTime();
var eDate = eEnd.toDateString();
var eTime = eEnd.toTimeString(); // Modified
ss.getRange(i+2,1,1,8).setValues([ [events[i].getDateCreated(), // Modified
                                events[i].getTitle(),
                                events[i].getDescription(),
                                events[i].getLocation(),
                                sDate + " " + sTime,
                                eDate + " " + eTime,
                                events[i].getId(),
                                events[i].getCreators()] ]); // Modified

或者，我认为您也可以使用toString()如下。

var eStart = events[i].getStartTime();
var sDate = eStart.toString(); // Modified
var eEnd = events[i].getEndTime();
var eDate = eEnd.toString(); // Modified
ss.getRange(i+2,1,1,8).setValues([ [events[i].getDateCreated(),
                                events[i].getTitle(),
                                events[i].getDescription(),
                                events[i].getLocation(),
                                sDate, // Modified
                                eDate, // Modified
                                events[i].getId(),
                                events[i].getCreators()] ]);

参考：

如果我误解了你的问题，我很抱歉。

编辑：

以下示例脚本是我测试的脚本。在我的环境中，我可以确认脚本有效。当您使用它时，请检查电子表格 ID。

function listEvents(){
  var ss, range, ui, rcalendarId, ask, askDate, askEnd,cal, start, end, events, num, dateString, eStart,sDate, sTime, string, eEnd, eDate, eTime, data;
  ss = SpreadsheetApp.openById('1p8VOhUD4agzq_seWWWiIYn658zBh1ZY4a2n_OFu5An4').getSheetByName('Sheet1');
  ss.clear();  
  range = ss.getRange(1,1,1,8).setValues([ ["Date Created","Title","Description","Location","Start Date","End Date", "Id", "Creator"] ]);

    //ASK FOR CALENDAR ID
  ui = SpreadsheetApp.getUi();
  ask = ui.prompt('Search for resource calendar list. Enter the resource calendar id');
  cal = CalendarApp.getCalendarById(ask.getResponseText());

    //ASK FOR START DATE CALENDAR SEARCH  -- enter June 22, 2019
  askDate = ui.prompt('Enter the Start Date. Date must be "Month Date, Year" format (i.e. May 1, 1970)');
  start = new Date(askDate.getResponseText());
    //ASL FOR END DATE CALENDAR SEARCH  -- enter June 23, 2019
  askEnd = ui.prompt('Enter the End Date. Date must be "Month Date, Year" format (i.e. May 1, 1970)');
  end = new Date (askEnd.getResponseText()); 

  events = cal.getEvents(start, end);
  num = events.length;

  for (var i = 0; i < num; i++) {

    // Column: Start Date
    eStart = events[i].getStartTime();
    sDate = eStart.toDateString();
    sTime = eStart.toTimeString(); // Modified sTime = sDate.toTimeString();
    // Column: End Date
    eEnd = events[i].getEndTime();
    eDate = eEnd.toDateString();
    eTime = eEnd.toTimeString(); // Modified eTime = eDate.toTimeString();

    // DISPLAY RESULT IN SPREADSHEET
    ss.getRange(i+2,1,1,8).setValues([ [events[i].getDateCreated(),
                                        events[i].getTitle(),
                                        events[i].getDescription(),
                                        events[i].getLocation(),
                                        sDate + " " + sTime,
                                        eDate + " " + eTime,
                                        events[i].getId(),
                                      events[i].getCreators()] ]);
  }
}

推荐阅读