无法从Google表格行创建一次性日历事件 [英] Trouble with creating one-time calendar event from Google Sheets row

问题描述

我在使用Google Apps脚本代码时遇到了一些麻烦，试图使其在工作表的一行上获取信息并创建一个带有开始和结束时间的Calendar事件。

  //将新事件推送到日历
函数pushToCalendar（）{
 
 //电子表格变量
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var lastRow = sheet.getLastRow（）; 
 var range = sheet.getRange（2,1，lastRow，16）; 
 var值= range.getValues（）; 
 
 var numValues = 0; 
 for（var i = 0; i< values.length; i ++）{
 //检查年龄，比赛，开始日期，开始时间和结束日期&如果（（values [i] [0] .length =='<>）&&（values [i] [1] .length =='< '）&&（values [i] [4] .length =='<>'）&&（values [i] [5] .length =='<>'）& &（values [i] [6] .length =='<>'）&&（values [i] [7] .length =='<>'））{
 
 //检查是否在
之前输入了if（（values [i] [8] ==''）||（values [i] [8] == null））{
 
 //创建事件https://developers.google.com/apps-script/class_calendarapp#createEvent 
 var newEventTitle ='Game：'+ values [i] [0] +'-'+值[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 = CalendarApp.getDefaultCalendar（）。createEvent（newEventTitle，new Date（start），new Date（end），{location：values [i] [12]}）; 
 //获取ID 
 var newEventId = newEvent.getId（）; 
 
 //标记为已输入，输入ID 
 sheet.getRange（i + 2,8）.setValue（newEventId）; 
 
} //可以用else语句
}在这里编辑
 numValues ++; 
} 
} 
  

我有相关信息（用于活动标题）前三列中的开始日期，第4列中的开始时间，第6列中的结束日期，第7列中的结束时间。其他列包含我将添加到事件描述中的信息。 / p>

我最常遇到的问题是将数据集过滤到尚未推送到Calendar的事件中，以及脚本一旦推送就准确记录事件结束，这样它就可以在脚本的下一个周期对其进行过滤。

解决方案

我知道了工作。我最初在值检查行中引用了错误的列。

  //将新事件推送到日历
函数pushToCalendar（）{
 
 //电子表格变量
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var lastRow = sheet.getLastRow（）; 
 var range = sheet.getRange（2,1，lastRow，16）; 
 var值= range.getValues（）; 
 
 //日历变量
 var defaultCalendar = CalendarApp.getDefaultCalendar（）
 
 var numValues = 0; 
 for（var i = 0; i< values.length; i ++）{
 //检查年龄，比赛，开始日期，开始时间和结束日期&如果（（values [i] [0]）&&（values [i] [1]）&&（values [i] [3]）&&（ values [i] [4]）&&（values [i] [5]）&&（values [i] [6]））
 {
 
 //检查是否已在
之前输入了if（values [i] [8]！='y'）{
 
 //创建事件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]}）;; 
 //获取ID 
 var newEventId = newEvent.getId（）; 
 
 //标记为输入，输入ID 
 sheet.getRange（i + 2,8）.setValue（’y’）; 
 sheet.getRange（i + 2,9）.setValue（newEventId）; 
 
} //可以用else语句
}在这里编辑
 
 numValues ++; 
} 
} 
  

• 电子表格具有标题行


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


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


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


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


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


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


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


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


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


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

I'm having some trouble with my Google Apps Script code trying to have it take the information on one row of my sheet and create a single, Calendar event, with beginning and ending times.

    //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();   

  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].length == '<>') && (values[i][1].length == '<>') && (values[i][4].length == '<>') && (values[i][5].length == '<>') && (values[i][6].length == '<>') && (values[i][7].length == '<>')) {

      //check if it's been entered before  
      if ((values[i][8] == '')||(values[i][8] == null)) { 

        //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 = 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,8).setValue(newEventId);

      } //could edit here with an else statement
    }
    numValues++;
  }
}

I have pertinent information (used in the event title) in the first three columns, and Start Date in column 4, Start Time in column 5, End Date in column 6, and End Time in column 7. The other columns contain information that I'll add to the description of the event.

The problem I'm mostly running into is filtering the data set into events that have not been pushed to the Calendar yet, as well as the script accurately recording once it does push an event over, such that it will be able to filter it on the next cycle of the script.

解决方案

I got it to work. I was referencing the wrong columns initially in the value check line. Silly me.

//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++;
  }
}

• Spreadsheet has header row
• Column 1 (value[i][0]) is Event Title info
• Column 2 (value[i][1]) is Event Title info
• Column 3 (value[i][2]) is Event Title info
• Column 4 (value[i][3]) is Start Date
• Column 5 (value[i][4]) is Start Time
• Column 6 (value[i][5]) is End Date
• Column 7 (value[i][6]) is End Time
• Column 8 (value[i][7]) contains 'y' if event has been pushed to Calendar
• Column 9 (value[i][8]) contains EventID of event created by script
• Columns after that have pertinent event info that can be added to the event description

这篇关于无法从Google表格行创建一次性日历事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！