时间戳

首页 > 解决方案 > 谷歌表自动填充谷歌日历

google-apps-script - 谷歌表自动填充谷歌日历

问题描述

我正在尝试从特定的谷歌表格中获取数据以在谷歌日历中创建事件。请参阅电子表格。

https://docs.google.com/spreadsheets/d/1eBEStiTKXI0YPXfQBYzqXjwwv4kZ21033TdtysxdhHI/edit?usp=sharing

基本上，当有人勾选方框时，它会在美国东部标准时间 10:00:00 的日期 (Col B) 为该人（第 2 行）创建一个事件。例如，当有人勾选单元格 P14 中的框时，它会创建一个事件：

名称 - Lily Ahadi - PC 活动日期 - 2020 年 3 月 16 日 活动时间：10:00:00

这是我部分工作的代码，然后让其他人帮忙，我们都没有得到任何地方。我即将放弃，但我想我会与 stackflow 专家进行最后一次尝试。该代码在工作表的脚本编辑器中可用。

 function onEdit(e) {
   try {
  var range =  e.range;
  Browser.msgBox(range);
  var nameSheet =  e.source.getSheetName();
  var rowID = range.rowStart;
  var colID = range.columnStart;
  var res = e;

  var oldValue = res.oldValue;
  var newValue = res.value;

  var data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(nameSheet);
  
  if (oldValue == "FALSE" && newValue == "TRUE" && nameSheet == "Master Client List"){

    var category = data.getRange(3, colID).getValue();
    var date = data.getRange(rowID, 2).getValue(); 
   date = getYesterdaysDate(date) 

    var timeDiff = 60;
    var startTime = "10:00:00";

    if (category == "PC"){
      var name = data.getRange(2, colID - 1).getValue();
     
     Browser.msgBox(name+'-'+category + date);
      
    }else{
      var name = data.getRange(2, colID - 2).getValue();

    }

    // var startDateTime = date+' '+startTime+':00';
    var startDateTime = testMoment1(date, startTime);


  
    var endDateTime = addMins(startDateTime, timeDiff);


    var event = CalendarApp.getDefaultCalendar().createEvent(name+'-'+category,
      new Date(startDateTime),
      new Date(endDateTime),
      {description: ''});
    Logger.log('Event ID: ' + event.getId());

  }
}
catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}




function testMoment(date, time) {
  eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js').getContentText());
  var date = moment(date).format('YYYY/MM/DD');
  // var time = time;

  // Logger.log(moment(date).format('MM/DD/YYYY'));

  // tell moment how to parse the input string
  var momentObj = moment(date + time, 'YYYY-MM-DDLT');

  // conversion
  var dateTime = momentObj.format('YYYY-MM-DDTHH:mm:ss');

  return dateTime;
}

function testMoment1(date, time) {
  eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js').getContentText());
  // var date = moment(date).format('YYYY/MM/DD');
  // var time = time;

  // Logger.log(moment(date).format('MM/DD/YYYY'));

  var c = new Date();
  var n = c.getFullYear();

  // tell moment how to parse the input string
  var momentObj = moment(date + time, 'YYYY-MM-DDLT').set('year', n).add(0, 'days');

  // conversion
  var dateTime = momentObj.format('YYYY-MM-DDTHH:mm:ss');
      // dateTime = moment(dateTime, "YYYY-MM-DDTHH:mm:ss");


  return dateTime;
}

function addMins(dateTime, durationInMinutes){

  eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js').getContentText());
  var startTime = dateTime;
  // var durationInMinutes = '120';

  var endTime = moment(startTime, 'YYYY-MM-DDTHH:mm:ss').add(durationInMinutes, 'minutes').format('YYYY-MM-DDTHH:mm:ss');

  return endTime;
}

function getYesterdaysDate(date1) {
    var date = new Date(date1);
    date.setDate(date.getDate());

    var day = date.getDate();
    var month = (date.getMonth()+1);
    var year = date.getFullYear();

    month = month < 10 ? '0'+month : month;
    day = day < 10 ? '0'+day : day;
    // Logger.log(date.getFullYear() + '-' + (date.getMonth()+1) + '-' + date.getDate());
    return year + '/' + month + '/' + day;
}

function myFunction() {

  var data=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master Client List');
  // var s=ss.getActiveSheet();
  var c=data.getLastColumn();

  for (var i = 3; i <= c; i++) {

    if (i%3 === 0){
      var name = data.getRange(2, i).getValue();
      var dob = data.getRange(1, i).getValue();

      var timeDiff = 60;
      var startTime = "10:00:00";
      Logger.log(name);
 Logger.log(name);
  Logger.log(startTime);
  
    //birthdayevent(name, dob, startTime, timeDiff)
      Logger.log(name);
      // break;
    }
  }
}

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

解决方案

Your code has two main issues:

1. You are querying for if (oldValue == "FALSE")

If you implement into your code the line Logger.log(oldValue); you will realize that an empty checkbox will return you the value "false" and not "FALSE". You need to modfy your if condition accordingly.

2. You are trying to use UrlFetchApp on simple onEdit trigger

As specified under restrictions for simple triggers:

They cannot access services that require authorization.

This problem can be easily solved by transforming your trigger into an installable one. For this:

  • Rename your function onEdit() to something different
  • Bind to the funciton an installable onEdit trigger as described here

After you implement those two modifications your code will run and create an event when you check a checkbox.

Now, I am not familiar enough to judge either the event parameters (data, event title) are retrieved as you desire. For troubleshooting I recommend you to log all variables - this will help you to spot any error quickly.

推荐阅读