从Google Sheet行创建一次性日历事件的麻烦

2 回答

• 0

  我得到了它的工作 . 我最初在值检查行中引用了错误的列 . 傻我 .

  //push new events to calendar
  function pushToCalendar() {
  
    //spreadsheet variables
    var sheet = SpreadsheetApp.getActiveSheet();
    var lastRow = sheet.getLastRow(); 
    var range = sheet.getRange(2,1,lastRow,16);
    var values = range.getValues(); 
  
    //calendar variables
    var defaultCalendar = CalendarApp.getDefaultCalendar()
  
    var numValues = 0;
    for (var i = 0; i < values.length; i++) {     
      //check to see if Age, Competition, Start Date, Start Time and End Date & Time are filled out
    if ((values[i][0]) && (values[i][1]) && (values[i][3]) && (values[i][4]) && (values[i][5]) && (values[i][6])) 
    {
  
        //check if it's been entered before  
        if (values[i][8] != 'y') { 
  
          //create event https://developers.google.com/apps-script/class_calendarapp#createEvent
          var newEventTitle = 'Game: ' + values[i][0] + ' - ' + values[i][1];
          var startDay = Utilities.formatDate(new Date(values[i][3]), 'America/Chicago', 'MMMM dd, yyyy');
          var startTime = Utilities.formatDate(new Date(values[i][4]), 'America/Chicago', 'HH:mm');
          var start = startDay + ' ' + startTime;
          var endDay = Utilities.formatDate(new Date(values[i][5]), 'America/Chicago', 'MMMM dd, yyyy');
          var endTime = Utilities.formatDate(new Date(values[i][6]), 'America/Chicago', 'HH:mm');
          var end = endDay+ ' ' + endTime;
          var newEvent = defaultCalendar.createEvent(newEventTitle, new Date(start), new Date(end), {location: values[i][12]});
          //get ID
          var newEventId = newEvent.getId();
  
          //mark as entered, enter ID
          sheet.getRange(i+2,8).setValue('y');
          sheet.getRange(i+2,9).setValue(newEventId);
  
        } //could edit here with an else statement
      }
  
      numValues++;
    }
  }
  

  • 电子表格有 Headers 行

  • 第1列（值[i] [0]）是事件 Headers 信息

  • 第2列（值[i] [1]）是事件 Headers 信息

  • 第3列（值[i] [2]）是事件 Headers 信息

  • 第4列（值[i] [3]）是开始日期

  • 第5列（值[i] [4]）是开始时间

  • 第6列（值[i] [5]）是结束日期

  • 第7列（值[i] [6]）是结束时间

  • 第8列（值[i] [7]）如果事件已被推送到日历，则包含'y'

  • 第9列（value [i] [8]）包含脚本创建的事件的EventID

  • 之后的列具有可添加到事件描述的相关事件信息

  回复于 2024-04-28T23:39:41+08:00
• 0

  我修改了一些代码但不多 . 这里是 . 我能够在日历上获得所有六个事件而且几乎没有变化 .

  function pushToCalendar() {
  
    //spreadsheet variables
    var sheet = SpreadsheetApp.getActiveSheet();
    var lastRow = sheet.getLastRow(); 
    var range = sheet.getRange(2,1,lastRow,16);
    var values = range.getValues();   
    var numValues = 0;
    for (var i = 0; i < values.length; i++) {     
      //check to see if Age, Competition, Start Date, Start Time and End Date & Time are filled out
      if ((values[i][0]) && (values[i][1]) && (values[i][4]) && (values[i][5]) && (values[i][6]) && (values[i][7])) 
      {
        //check if it's been entered before  
        if (!values[i][8]) 
        { 
          //create event https://developers.google.com/apps-script/class_calendarapp#createEvent
          var newEventTitle = 'Game: ' + values[i][0] + ' - ' + values[i][1];
          var startDay = Utilities.formatDate(new Date(values[i][4]), 'GMT-5', 'MMMM dd, yyyy');
          var startTime = Utilities.formatDate(new Date(values[i][5]), 'GMT-5', 'HH:mm');
          var start = startDay + ' ' + startTime;
          var endDay = Utilities.formatDate(new Date(values[i][6]), 'GMT-5', 'MMMM dd, yyyy');
          var endTime = Utilities.formatDate(new Date(values[i][7]), 'GMT-5', 'HH:mm');
          var end = endDay+ ' ' + endTime;
          var newEvent = CalendarApp.getDefaultCalendar().createEvent(newEventTitle, new Date(start), new Date(end), {location: values[i][12]});
          //get ID
          var newEventId = newEvent.getId();
          //mark as entered, enter ID
          sheet.getRange(i+2,9).setValue(newEventId);//Why i+2
        } //could edit here with an else statement
      }
      numValues++;
    }
  }
  

  这是我的测试数据 .

  Title1  Title2          startDay    startTime   endDay  endTime completed               Location
  Event1  Day1            4/1/2017    10:00 AM    4/1/2017    2:00 PM sbcfnqdevvc16vs4q237k6udnk@google.com               Denver
  Event1  Day2            4/2/2017    10:00 AM    4/2/2017    2:00 PM oj8ui4m5h3jcpqmj7ev10ih91k@google.com               Honolulu
  Event1  Day3            4/3/2017    10:00 AM    4/3/2017    12:00 PM    n6fro1u4rcgieu6720ujudents@google.com               San Francisco
  Event2  Day1            4/4/2017    10:00 AM    4/4/2017    2:00 PM kq3m6cel3jg5j6kiljtlvhgppg@google.com               Denver
  Event2  Day2            4/5/2017    10:00 AM    4/5/2017    2:00 PM nbmfsb9bar5ucsvgaddqsbvss8@google.com               Honolulu
  Event2  Day3            4/6/2017    10:00 AM    4/6/2017    12:00 PM    p7kc6p82gbvf94terd6cl1aiuo@google.com               San Francisco
  

  回复于 2024-04-28T23:39:41+08:00