我正在使用Apps Script为Google Sheets构建一个侧边栏，它是一个从Google日历导入数据的工具。脚本没有那么复杂：
- 一个用于选择日历、开始和结束日期的表单，以及一个包含标题的复选框
- 一些客户端javascript
- 一些服务器端函数
1.我有一个文件Code.gs，负责创建自定义菜单和呈现侧边栏
function onOpen() {
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.createMenu('Custom Menu')
.addItem('Show sidebar', 'showSidebar')
.addToUi();
}
function showSidebar() {
var html = HtmlService.createTemplateFromFile('index').evaluate()
.setTitle('Extract data from calendar')
.setWidth(300);
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.showSidebar(html);
}
function include(filename){
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
2.Index.html：侧边栏的内容
<!DOCTYPE html>
<html>
<head>
</head>
<body style="margin: 10px;">
<!-- Compiled and minified CSS -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
<!-- Compiled and minified -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
<div class="row">
<form class="col s12">
<!-- CHOOSE CALENDAR ROW -->
<div class="row">
<div class="input-field col s12">
<select id='calendar'>
<? var calendars = CalendarApp.getAllOwnedCalendars();
calendars.forEach((calendar) => { ?>
<option value="<?= calendar.getId() ?>"><?= calendar.getName() ?></option>
<? }) ?>
</select>
<label>Calendar</label>
</div>
</div>
<!-- START TIME ROW -->
<div class="row">
<div class="input-field col s12">
<input placeholder="Start date" id="startDate" type="date" class="validate">
<label for="startDate">Start date</label>
</div>
</div>
<!-- END TIME ROW -->
<div class="row">
<div class="input-field col s12">
<input placeholder="End date" id="endDate" type="date" class="validate">
<label for="endDate">End Date</label>
</div>
</div>
<!-- INCLUDE HEADER -->
<div class="row">
<label>
<input type="checkbox" class="filled-in" checked="checked" id="includeHeader" />
<span>Include Header</span>
</label>
</div>
<!-- EXTRACT BUTTON -->
<div class="row">
<button class="btn waves-effect waves-light" id="extractBtn">EXTRACT</button>
</div>
</form>
</div>
<?!= include('index-js') ?>
<script>
document.addEventListener('DOMContentLoaded', function() {
var elems = document.querySelectorAll('select');
var instances = M.FormSelect.init(elems, {});
});
</script>
</body>
</html>
3.index-js.html：向提取按钮添加事件并调用服务器端函数
<script>
var extractBtn = document.getElementById('extractBtn');
extractBtn.addEventListener('click', function(e) {
e.preventDefault();
var calendarId = document.getElementById('calendar').value;
var startDate = new Date(document.getElementById('startDate').value);
var endDate = new Date(document.getElementById('endDate').value);
var includeHeader = document.getElementById('includeHeader').checked;
if(startDate.getTime() < endDate.getTime()) {
e.target.textContent = 'Extracting...';
google.script.run.withSuccessHandler(extractCompleted).writeData(calendarId, startDate, endDate, includeHeader);
} else {
M.toast({html: "Please enter an end date starting after the selected start date"});
}
});
function extractCompleted(){
M.toast({html: 'Extract completed'});
extractBtn.textContent = 'Extract';
}
</script>
4.extractCalendar.gs负责获取数据并将其添加到Google工作表
function extractCalendar(calendarId, startDate, endDate) {
var calendar = CalendarApp.getCalendarById(calendarId);
var events = calendar.getEvents(startDate, endDate);
// Initialize variables that will hold information
var rows = [];
events.forEach((event) => {
// Get basic information
var eventTitle = event.getTitle();
var startTime = event.getStartTime();
var endTime = event.getEndTime();
var description = event.getDescription();
var isRecurring = event.isRecurringEvent();
// Get the guest list
var guests = event.getGuestList();
var emailsParticipantsArray = [];
var emailsParticipants = '';
// Add each guest email to the array
guests.forEach(guest => emailsParticipantsArray.push(guest.getEmail()));
// Convert the array to a string
emailsParticipants = emailsParticipantsArray.join(',');
rows.push([eventTitle, startTime, endTime, emailsParticipants, description, isRecurring]);
}
);
return rows;
}
function writeData(calendarId, startDate, endDate, includeHeader=True){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var rows = extractCalendar(calendarId, startDate, endDate);
var numRows = rows.length;
var lastRow = sheet.getLastRow();
(includeHeader) && rows.unshift(['Title', 'Start Date', 'End Date', 'Participants Email', 'Description', 'Recurring']);
sheet.getRange(lastRow + 1, 1, numRows, 6).setValues(rows);
}
下面的行没有运行并生成错误消息：Uncaught TypeError:由于属性中的非法值而失败：12769757186-mae_html_user_bin_i18n_mae_html_user.js:61
google.script.run.withSuccessHandler(extractCompleted).writeData(calendarId, startDate, endDate, includeHeader);
这里发生了什么，为什么服务器端函数没有运行？
谢谢你的帮助！
参数和返回值
您可以用标准字符串格式传递年、月、日或日期，并使用date((构造函数重新创建日期。