javascript - 有没有办法将脚本中的日期自动更新到最后一个月的第一天和最后一天？

Question

我试图找到一种方法让这两个日期在每个月初更改为前一个月的第一天和最后一天。

我在工作表中更新了所需的日期，但不能将这些单元格用作参考。

function export_gcal_to_gsheet(){
  var mycal = "[e-mail]";
  var cal = CalendarApp.getCalendarById(mycal);

  var events = cal.getEvents(
      new Date("1 September, 2018 00:00:00 GMT"), // this date
      new Date("30 September, 2018 23:59:59 GMT"), // and this one
      {search: 'Keba & Jenga'});

  var sheet = SpreadsheetApp.getActiveSheet();

  var header = [["Invoice"]]
  var range = sheet.getRange(1,1,1,1);
  range.setValues(header);

  for (var i = 0; i < events.length; i++) {
    var row = i + 12;
    var myformula_placeholder = '';

    var details = [[events[i].getDescription(), events[i].getStartTime()]];
    var range = sheet.getRange(row, 1, 1, 2);
    range.setValues(details);

    var cell = sheet.getRange(row, 3);
    cell.setFormula('=(HOUR(F' + row + ')+(MINUTE(F' + row + ')/60))-(HOUR(E' + row + ')+(MINUTE(E' + row + ')/60))');
    cell.setNumberFormat('');
  }
}

标签： javascriptdatetimegoogle-apps-scriptgoogle-sheets

Answer 1

如果你想使用你的服务器本地时间，你可以使用它：

var today=new Date();
var startDate=new Date(today.getFullYear(), today.getMonth()-1, 1, 0,0,0); 
var endDate=new Date(today.getFullYear(), today.getMonth(), 1, 0,0,0); 
endDate.setSeconds(-1);

document.write(startDate);
document.write('<br>');
document.write(endDate);

如果您更喜欢始终使用 GMT+0 时间，则必须使用以下代码：

var today=new Date();
today=new Date(today.valueOf() +  today.getTimezoneOffset() * 60000);
var startDate=new Date(today.getFullYear(), today.getMonth()-1, 1, 0,0,0); 
var endDate=new Date(today.getFullYear(), today.getMonth(), 1, 0,0,0); 
endDate.setSeconds(-1);
console.log(today);
console.log(startDate);
console.log(endDate);

您的代码将是这样的（当地时间）：

function export_gcal_to_gsheet(){
  var mycal = "[e-mail]";
  var cal = CalendarApp.getCalendarById(mycal);

var today=new Date();
var startDate=new Date(today.getFullYear(), today.getMonth()-1, 1, 0,0,0); 
var endDate=new Date(today.getFullYear(), today.getMonth(), 1, 0,0,0); 
endDate.setSeconds(-1);

  var events = cal.getEvents(
      startDate, // this date
      endDate, // and this one
      {search: 'Keba & Jenga'});

  var sheet = SpreadsheetApp.getActiveSheet();

  var header = [["Invoice"]]
  var range = sheet.getRange(1,1,1,1);
  range.setValues(header);

  for (var i = 0; i < events.length; i++) {
    var row = i + 12;
    var myformula_placeholder = '';

    var details = [[events[i].getDescription(), events[i].getStartTime()]];
    var range = sheet.getRange(row, 1, 1, 2);
    range.setValues(details);

    var cell = sheet.getRange(row, 3);
    cell.setFormula('=(HOUR(F' + row + ')+(MINUTE(F' + row + ')/60))-(HOUR(E' + row + ')+(MINUTE(E' + row + ')/60))');
    cell.setNumberFormat('');
  }
}

或者您的代码可能是这样的（对于 GMT+0 时间）：

function export_gcal_to_gsheet(){
  var mycal = "[e-mail]";
  var cal = CalendarApp.getCalendarById(mycal);

 var today=new Date();
today=new Date(today.valueOf() +  today.getTimezoneOffset() * 60000);
var startDate=new Date(today.getFullYear(), today.getMonth()-1, 1, 0,0,0); 
var endDate=new Date(today.getFullYear(), today.getMonth(), 1, 0,0,0); 
endDate.setSeconds(-1);


  var events = cal.getEvents(
      startDate, // this date
      endDate, // and this one
      {search: 'Keba & Jenga'});

  var sheet = SpreadsheetApp.getActiveSheet();

  var header = [["Invoice"]]
  var range = sheet.getRange(1,1,1,1);
  range.setValues(header);

  for (var i = 0; i < events.length; i++) {
    var row = i + 12;
    var myformula_placeholder = '';

    var details = [[events[i].getDescription(), events[i].getStartTime()]];
    var range = sheet.getRange(row, 1, 1, 2);
    range.setValues(details);

    var cell = sheet.getRange(row, 3);
    cell.setFormula('=(HOUR(F' + row + ')+(MINUTE(F' + row + ')/60))-(HOUR(E' + row + ')+(MINUTE(E' + row + ')/60))');
    cell.setNumberFormat('');
  }
}