英文:
Basic Google Apps Script Function
问题
我正在尝试使用Google Apps Script创建一个函数，当我在我的Google表格中运行自动化时，它会在共享的Google日历上创建一个事件（或多个事件）。
我是编程新手，正在尝试根据客户的要求完成这个任务，因此非常希望能得到任何帮助/反馈！
我已经观看了一些视频，并参考了一些文档。
以下是我的当前代码状态：
function createcalendarevent() {
let communityCalendar = CalendarApp.getCalendarById("c_189f99fba6aef7d890a31b1bc2781f510052cab494ff152db93cf6cb7cb0d2e1@group.calendar.google.com");
let sheet = SpreadsheetApp.getActiveSheet();
let schedule = sheet.getDataRange().getValues();
var rangeList = sheet.getRangeList(['D4', 'B2:C3']);
sheet.setActiveRangeList(rangeList);
}
附上表格的截图。
英文:
I'm trying to create a function using Google Apps Script where when I run the automation in my Google Sheet it then creates an event (or multiple events) on a shared Google Calendar.
I am new to coding and trying to complete this per a client request so would love any help/feedback!
I've watched a few videos and have referenced a few docs.
Here is the current state of my coding:
function createcalendarevent() {
let communityCalendar = CalendarApp.getCalendarById("c_189f99fba6aef7d890a31b1bc2781f510052cab494ff152db93cf6cb7cb0d2e1@group.calendar.google.com");
let sheet = SpreadsheetApp.getActiveSheet();
let schedule = sheet.getDataRange().getValues();
var rangeList = sheet.getRangeList(['D4', 'B2:C3']);
sheet.setActiveRangeList(rangeList);
};
Attaching a screengrab of the sheet.
答案1
得分: 0
SUGGESTION
您可以在脚本中使用名为createEvent()的CalendarApp服务方法，结合getDisplayValues()使用它。此外，根据您的表格数据，有一些行尚未设置开始时间和结束时间，需要在创建事件之前进行筛选，以减少错误的发生。您可以尝试下面微调过的脚本：
function createcalendarevent() {
let communityCalendar = CalendarApp.getCalendarById(`c_189f99fba6aef7d890a31b1bc2781f510052cab494ff152db93cf6cb7cb0d2e1@group.calendar.google.com`);
let sheet = SpreadsheetApp.getActive().getActiveSheet();
let rawData = sheet.getRange(`A1:C`).getDisplayValues().filter(x => x.join().length > 2);
/** 遍历工作表中的每一行，包括A、B、C列
* 但只处理包含有效时间值的行（具有开始时间和结束时间的行）
* 并为每个有效数据创建一个事件 */
rawData.forEach(y => new Date(y[1]) != `Invalid Date` && new Date(y[2]) != `Invalid Date` ? createEvent(y[0],y[1],y[2],communityCalendar) : null )
};
function createEvent(eventName,startDate,endDate,communityCalendar) {
// 创建事件
var event = communityCalendar.createEvent(eventName,
new Date(startDate),
new Date(endDate));
Logger.log(`已创建“${eventName}”事件，事件ID为：${event.getId()}`);
}
演示
日志结果:
创建的示例事件:
英文:
SUGGESTION
You may use this CalendarApp service method called createEvent() in your script with getDisplayValues(). Also, based on your sheet data, you have some rows that don't have any Start time & End time yet, which needs to be filtered before creating the events, reducing any error in the process. You could try this tweaked script below:
function createcalendarevent() {
let communityCalendar = CalendarApp.getCalendarById(`c_189f99fba6aef7d890a31b1bc2781f510052cab494ff152db93cf6cb7cb0d2e1@group.calendar.google.com`);
let sheet = SpreadsheetApp.getActive().getActiveSheet();
let rawData = sheet.getRange(`A1:C`).getDisplayValues().filter(x => x.join().length > 2);
/** Iterate through each rows in your sheet Columns A, B, C
* but only process rows with valid time values (rows with Start Time & End Time)
* & create an event for each valid data */
rawData.forEach( y => new Date(y[1]) != `Invalid Date` && new Date(y[2]) != `Invalid Date` ? createEvent(y[0],y[1],y[2],communityCalendar) : null )
};
function createEvent(eventName,startDate,endDate,communityCalendar) {
// Creates an event
var event = communityCalendar.createEvent(eventName,
new Date(startDate),
new Date(endDate));
Logger.log(`Created "${eventName}" with Event ID: ${event.getId()}`);
}
Demo
Log Result:
>
Sample events created:
>
>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论