I'm building a sidebar for Google Sheets using Apps Script, it is a tool to import data from Google Calendar. The script is not that complicated:
- A form to choose the calendar, a start and end date, and a checkbox to include a header
- Some client-side javascript
- Some server-side functions
1. I have a file Code.gs in charge of creating a custom menu and rendering the sidebar
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: the content of the sidebar
<!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: add an event to the extract button and call the server-side function
<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 in charge of getting the data and adding it to the Google sheet
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);
}
The line below does not run and generate the error message: Uncaught TypeError: Failed due to illegal value in property: 12769757186-mae_html_user_bin_i18n_mae_html_user.js:61
google.script.run.withSuccessHandler(extractCompleted).writeData(calendarId, startDate, endDate, includeHeader);
What's happening right here, why is the server-side function not running?
Thank you for your help !
