How to send Google Form responses to Google Calendar

How it works

There is no switch in Google Forms that sends answers to a calendar. What you can do is link the form to a spreadsheet, which Forms does for free, and let a small Apps Script in that spreadsheet react each time a new row arrives. The chain looks like this:

1. Someone submits the form.
2. Forms adds a row to the Form Responses 1 tab of the linked spreadsheet.
3. An On form submit trigger runs the script with that row.
4. The script reads the name, date and time, creates the event in the calendar you choose, and writes the event’s ID into a new Event ID column on the same row.

That ID is what makes the script safe to run again: a row with an ID already has its event, and if the respondent edits their answer the script moves that event instead of creating a second one. Everything runs under your Google account, at no cost, within Apps Script’s normal daily limits for calendar events, which are far beyond what a booking form produces.

Set up the form

Build the form as you normally would, but pay attention to the questions the script reads. Their titles become the column headers in the spreadsheet, and the script finds the columns by those titles.

Two good ways to ask for the time:

• A Date question and a separate Time question, as above. Easiest for people on phones.
• One Date question with “Include time” turned on (the three-dot menu on the question). Then set time: '' in the script, and it takes the time from the date cell.

If the form has only a Date question with no time, the script creates all-day events, which suits leave requests, deliveries and deadlines.

To record who booked, turn on Settings → Responses → Collect email addresses. Forms then adds an Email Address column, which the script puts in the description. Leave Include year on for the Date question, so the sheet stores a full date.

Now link the spreadsheet: on the form’s Responses tab, click Link to Sheets and create a new spreadsheet. Submit one test response yourself and check that the headers in row 1 read exactly Name, Date, Start time and so on.

The free script

Everything you might want to change is at the top: the calendar, the question titles, the title prefix, the length of each event, and two switches.

// Google Form responses -> Google Calendar, one event per response.
// Paste this into Extensions > Apps Script of the form's RESPONSE spreadsheet, then add the trigger:
// Triggers (clock icon) > Add Trigger > createEventFromForm > From spreadsheet > On form submit.
// The names below must match row 1 of the responses sheet (your question titles) exactly.
const CALENDAR_ID = 'primary';   // or a Calendar ID from the calendar's Settings > Integrate calendar
const COL = {
  name: 'Name',               // goes into the event title
  date: 'Date',               // a Date question (with or without "Include time")
  time: 'Start time',         // a separate Time question, or '' if the Date question includes the time
  email: 'Email Address',     // collected email or an email question, or ''
  notes: 'Notes'              // any extra question for the description, or ''
};
const TITLE_PREFIX = 'Booking: ';
const DURATION_MIN = 60;
const INVITE_RESPONDENT = false;  // true = add the respondent as a guest and let Google email them the invitation
const SKIP_IF_BUSY = true;        // true = do not book on top of an existing event in that calendar
const ID_HEADER = 'Event ID';     // the script adds this column and writes the event ID or the reason it skipped

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Form to Calendar')
    .addItem('Create events for rows without one', 'createEventsForAllRows')
    .addToUi();
}

function createEventFromForm(e) {   // runs on every form submission
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);             // two people submitting at once cannot both get the same slot
  try { processRow_(e.range.getSheet(), e.range.getRow()); } finally { lock.releaseLock(); }
}

function createEventsForAllRows() { // backfill: old responses, or rows that were skipped before
  const sheet = SpreadsheetApp.getActiveSheet();
  let done = 0;
  for (let r = 2; r <= sheet.getLastRow(); r++) {
    const cell = sheet.getRange(r, idColumn_(sheet)).getValue();
    if (cell === '' || String(cell).indexOf('Not created') === 0) { processRow_(sheet, r); done++; }
  }
  SpreadsheetApp.getUi().alert(done + ' rows processed. Check the ' + ID_HEADER + ' column for the result of each.');
}

function processRow_(sheet, row) {
  const width = sheet.getLastColumn();
  const head = sheet.getRange(1, 1, 1, width).getValues()[0].map(String);
  const vals = sheet.getRange(row, 1, 1, width).getValues()[0];
  const shown = sheet.getRange(row, 1, 1, width).getDisplayValues()[0];
  const get = (name, list) => (name && head.indexOf(name) > -1 ? list[head.indexOf(name)] : '');
  const out = sheet.getRange(row, idColumn_(sheet));
  const tz = sheet.getParent().getSpreadsheetTimeZone();
  const date = get(COL.date, vals);
  if (!(date instanceof Date)) { out.setValue('Not created: no date in the "' + COL.date + '" column'); return; }
  const ymd = Utilities.formatDate(date, tz, 'yyyy-MM-dd');
  let start = null, allDay = false;
  if (COL.time) {
    const t = String(get(COL.time, shown)).match(/(\d{1,2}):(\d{2})(?::\d{2})?\s*([AaPp])?/);
    if (!t) { out.setValue('Not created: no time in the "' + COL.time + '" column'); return; }
    let h = Number(t[1]);
    const ap = t[3] ? t[3].toUpperCase() : '';   // no AM/PM means a 24-hour time
    if (ap === 'P' && h < 12) h += 12;
    if (ap === 'A' && h === 12) h = 0;
    start = Utilities.parseDate(ymd + ' ' + h + ':' + t[2], tz, 'yyyy-MM-dd H:mm');
  } else if (/\d:\d\d/.test(get(COL.date, shown))) {
    start = date;                    // the Date question included the time
  } else {
    allDay = true;                   // date only: all-day event
  }
  const cal = CALENDAR_ID === 'primary' ? CalendarApp.getDefaultCalendar() : CalendarApp.getCalendarById(CALENDAR_ID);
  const title = TITLE_PREFIX + (get(COL.name, vals) || 'Form response');
  const email = String(get(COL.email, vals) || '').trim();
  const opts = { description: String(get(COL.notes, vals) || '') + (email ? '\nEmail: ' + email : '') };
  if (INVITE_RESPONDENT && email) { opts.guests = email; opts.sendInvites = true; }
  const end = allDay ? null : new Date(start.getTime() + DURATION_MIN * 60 * 1000);
  const oldId = String(out.getValue());
  const old = oldId && oldId.indexOf('Not created') !== 0 ? cal.getEventById(oldId) : null;
  if (old) {                         // response edited: move the existing event instead of adding a second one
    old.setTitle(title);
    if (allDay) old.setAllDayDate(date); else old.setTime(start, end);
    return;
  }
  if (SKIP_IF_BUSY && !allDay && cal.getEvents(start, end).length > 0) {
    out.setValue('Not created: ' + Utilities.formatDate(start, tz, 'MMM d, h:mm a') + ' is already booked');
    return;
  }
  const ev = allDay ? cal.createAllDayEvent(title, date, opts) : cal.createEvent(title, start, end, opts);
  out.setValue(ev.getId());
}

function idColumn_(sheet) {
  const head = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(String);
  let c = head.indexOf(ID_HEADER) + 1;
  if (!c) { c = head.length + 1; sheet.getRange(1, c).setValue(ID_HEADER); }
  return c;
}

What it does with each response:

• Time zone. Times are read as they appear in the sheet and built in the spreadsheet’s time zone (File → Settings), so 2:30 PM on the form is 2:30 PM on the calendar. It understands both 2:30 PM and 14:30.
• Double bookings. With SKIP_IF_BUSY = true, if anything already sits in that calendar at that time, no event is created and the row says, for example, Not created: Nov 3, 2:30 PM is already booked. The lock at the top means two people submitting in the same second can’t both get the slot.
• Edited responses. If you allow “Edit after submit”, an edit updates the same row, the trigger runs again, and the script moves the existing event to the new date and time.
• Missing answers. A row without a usable date or time gets a “Not created” note instead of an event at the wrong time.

Install it and add the trigger

1. Open the response spreadsheet (not the form) and choose Extensions → Apps Script.
2. Delete the sample code, paste the script, and change COL if your question titles are different. Click Save.
3. To use a calendar other than your main one, open Google Calendar, go to the calendar’s Settings and sharing → Integrate calendar, copy the Calendar ID, and paste it in place of 'primary'. A separate “Bookings” calendar is worth it: you can share or hide it as a whole, and a mistake is easy to clean up.
4. Click the clock icon (Triggers) on the left, then Add Trigger. Choose the function createEventFromForm, event source From spreadsheet, event type On form submit, and save.
5. Google asks you to authorise the script. Because it’s your own script and not a published app, you’ll see “Google hasn’t verified this app”: click Advanced, then Go to your project, and allow access to your spreadsheets and calendar.
6. Submit a test response. Within a few seconds the Event ID column fills in and the event appears in Google Calendar.

If nothing happens, open Executions in the Apps Script editor. Each run is listed there with any error message.

Responses you already have

The trigger only sees new submissions. For responses that arrived before you installed it, reload the spreadsheet: a Form to Calendar menu appears. With the responses tab open, choose Create events for rows without one. It goes through every row with an empty Event ID, and retries rows that were skipped before (for example after you’ve freed up a slot), then tells you how many rows it processed.

Settings worth changing

• DURATION_MIN: the length of every event. For different lengths per service, add a question with the length and replace the constant with a lookup, or use the add-on below, which reads an End time column.
• INVITE_RESPONDENT = true: adds the respondent as a guest, and Google emails them the invitation, so the booking lands in their calendar too. Leave it off if the form is internal or you don’t want to share your calendar’s event with outsiders.
• SKIP_IF_BUSY = false: allows overlaps, for example for a team calendar where several people can book the same hour.
• TITLE_PREFIX: “Booking: ”, “Leave: ”, “Delivery: ” or an empty string.

The script doesn’t delete events. If a booking is cancelled, delete the event in Calendar and type Cancelled in its Event ID cell so the backfill leaves it alone.

Review first, then send: CalendarRows

An automatic trigger is right when every response should become an event. Sometimes you want to check requests first, approve some, change a time, or send a whole week of sign-ups at once. CalendarRows does that from the same response sheet without any code.

1. In the response spreadsheet, add End time (and Guests if you want people added to the event) as extra columns to the right of the form’s columns. Forms leaves extra columns alone.
2. Install CalendarRows and choose Extensions → CalendarRows → Open CalendarRows. On To Calendar, pick the responses tab and your calendar.
3. Check the column mapping. Name is read as the title, Date as the start date and Start time as the start time. Forms’ Email Address isn’t matched automatically, so pick it under Guests if you want respondents on their events, or leave Guests empty.
4. Click Preview. You see how many events would be created and which rows have problems, and nothing changes yet.
5. Click Send to Calendar. Each row gets its event ID and a status. After new responses arrive, send again: rows already sent and unchanged are skipped, and rows you edited update their event.

Guests are added without CalendarRows sending an invitation email, which is the opposite of the script’s invite switch: the event appears on their calendar subject to their own settings, but nobody gets a message from you.

Which to use

That last row matters. If what you really need is self-service booking of free slots, Google Calendar’s appointment schedule feature is built for it. A form plus a script fits when you need questions a booking page doesn’t ask, or requests that are not slot-based, such as leave, equipment loans or delivery dates.

Common problems

• “Not created: no date in the Date column”. The header in row 1 doesn’t match COL.date exactly, including capital letters and spaces. Copy the header text into the script.
• Events are an hour or more off. The spreadsheet’s time zone (File → Settings) is different from the one you expect. Set it to your local zone; the script uses it for every event.
• Everything says “already booked”. An all-day event or a long event sits in the same calendar and overlaps every slot. Use a dedicated Bookings calendar, or set SKIP_IF_BUSY to false.
• The trigger ran but nothing happened. The trigger is attached to a different spreadsheet or function. Delete it and add it again from the response spreadsheet, choosing createEventFromForm.
• Two events for one response. Two triggers exist for the same function, usually after setting it up twice. Keep one in the Triggers list.
• “Exceeded maximum execution time” on the backfill. Very large response sheets can hit Apps Script’s time limit. Run the menu again: rows that already have an Event ID are skipped.

Frequently asked questions

Related

• How to create Google Calendar events from Google Sheets
• How to import a spreadsheet or CSV into Google Calendar
• How to create recurring Google Calendar events from Google Sheets
• CalendarRows documentation
• All guides

Disclosure: we make CalendarRows, the Google Sheets add-on mentioned in this guide. We describe the other methods as fairly as we can, including when they are the better choice.

Google Forms, Google Sheets and Google Calendar are trademarks of Google LLC. CalendarRows and Cellbrook are not affiliated with or endorsed by Google.