从Google表格行创建一次性日历事件时遇到问题 [英] Trouble with Creating One-Time Calendar Event from Google Sheet row

问题描述

我的Google Apps Script代码出现问题。
我试图让它将信息放在我的工作表的一行上，并以开始和结束时间创建单个日历事件。

<$ p $ （）;
$ b //电子表格变量
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 ++）{
//检查年龄，比赛，开始日期，开始时间和结束日期，如果（（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 ='游戏：'+ 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 ++;


$ / code $ / pre

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

我最常遇到的问题是将数据集过滤为尚未推送到日历的事件，以及脚本一旦执行就会准确记录一个事件结束，这样它就可以在脚本的下一个循环过滤它。

请让我知道你是否需要我提供更多信息。

p>

预先感谢您的帮助！
Adam

解决方案

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

  //将新事件推送到日历
 function pushToCalendar（）{
 
 //电子表格变量
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var lastRow = sheet.getLastRow（）; 
 var range = sheet.getRange（2,1，lastRow，16）; 
 var values = 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]）&& amp;（ （[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 ++; 
 
 
  

• 电子表格有标题行


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


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


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


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


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


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


• 第8列（值[i] [7]）如果事件已被推送到
  Calendar


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


• 之后的列包含可添加到
  事件描述中的相关事件信息

I'm having some trouble with my Google Apps Script code. I'm trying to have it take the info 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 info (used in the event title) in the first 3 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 info 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.

Please let me know if you need further information from me.

Thanks in advance for your help! Adam

解决方案

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屋！