时间戳

首页 > 解决方案 > 谷歌日历到谷歌表格，脚本没有正确选择时间

google-apps-script - 谷歌日历到谷歌表格，脚本没有正确选择时间

问题描述

出于某种原因，我运行的脚本将事件时间缩短了一个小时。如果我有一个下午 4 点的时间，它会将它显示为下午 3 点。事件日期和时间被拉为 CST，但显示不正确。这条线是否需要以某种方式更改以调出当地时间？

我不确定要尝试什么。现在使用 CST 提取日期/时间，但脚本行的输出带来了错误的时间。我可以添加一些不同的东西吗？

var events = cal.getEvents(new Date("October 20, 2019 00:00:00 CST"), new Date("November 2, 2019 23:59:59 CST"), {search: ''});


var sheet = SpreadsheetApp.getActiveSheet();
// Uncomment this next line if you want to always clear the spreadsheet content before running - Note people could have added extra columns on the data though that would be lost
// sheet.clearContents();  

// Create a header record on the current spreadsheet in cells A1:N1 - Match the number of entries in the "header=" to the last parameter
// of the getRange entry below
sheet.clearContents();
var header = [["EVENT / SHOW", "VENUE / LOCATION", "DELIVERY NOTES", "DATE", "TIME"]]
var range = sheet.getRange(1,1,1,5);
range.setValues(header);


// Loop through all calendar events found and write them out starting on calulated ROW 2 (i+2)
for (var i=0;i<events.length;i++) {
var row=i+2;
var myformula_placeholder = '';
// Matching the "header=" entry above, this is the detailed row entry "details=", and must match the number of entries of the GetRange entry below
// NOTE: I've had problems with the getVisibility for some older events not having a value, so I've had do add in some NULL text to make sure it does not error
var details=[[events[i].getTitle(), events[i].getLocation(), events[i].getDescription(), events[i].getStartTime(), Utilities.formatDate(events[i].getStartTime(), Session.getScriptTimeZone(), "hh:mma")]];
var range=sheet.getRange(row,1,1,5);
range.setValues(details);

预期的结果是让这个拉到谷歌日历上显示的确切时间。

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

解决方案

仅供参考，这是我用来测试格式字符串的代码。

function runTwo() {
  var ss=SpreadsheetApp.getActive();
  var calA=CalendarApp.getAllCalendars();
  var html='';
  var today=new Date();
  var s=new Date(today.getFullYear(),today.getMonth(),today.getDate());
  var e=new Date(today.getFullYear(),today.getMonth(),today.getDate()+15);
  calA.forEach(function(c){
    var events=c.getEvents(s,e);  
    if(events.length>0) {
      html+=Utilities.formatString('<br />%s<br />',c.getName());
    }
    html+='<style>th,td{border:1px solid black;}</style><table>';
    if(events.length>0) {
      html+=Utilities.formatString('<tr><th>%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s</th>',"EVENT/SHOW","VENUE/LOCATION","DELIVERY NOTES","DATE","TIME");
    }
    events.forEach(function(ev){
      var sd=Utilities.formatDate(new Date(ev.getStartTime()),Session.getScriptTimeZone(),"E MM dd, yyyy");
      var st=Utilities.formatDate(new Date(ev.getStartTime()),Session.getScriptTimeZone(),"hh:mma");
      html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',ev.getTitle(),ev.getLocation(),ev.getDescription(),sd,st);
    });
    html+='</table>';
  });
  if(html) {
    var userInterface=HtmlService.createHtmlOutput(html).setWidth(1000);
    SpreadsheetApp.getUi().showModelessDialog(userInterface, "My Event Data");
  }else{
    SpreadsheetApp.getUi().alert('No Events Found');
  }
}

它将每个单独日历的所有事件数据加载到一个单独的小 html 表中，并在一个无模式对话框中显示所有这些数据。

推荐阅读