Advanced · A1

Forms and Slides

Forms collect information and Slides present it. In this chapter a script builds a form or a self-marking quiz from a sheet of questions, reacts the moment someone submits, and turns data into a slide deck.

A form in code

Code.gs

function makeFeedbackForm() {
  const form = FormApp.create('Lesson feedback')
    .setDescription('Two minutes, no names needed. Thank you!')
    .setProgressBar(true);

  form.addMultipleChoiceItem()
    .setTitle('Which room are you in?')
    .setChoiceValues(['M.5/1', 'M.5/2', 'M.5/3'])
    .setRequired(true);

  form.addScaleItem()
    .setTitle('How clear was today’s lesson?')
    .setBounds(1, 5)
    .setLabels('Lost', 'Crystal clear');

  form.addCheckboxItem()
    .setTitle('What helped you most?')
    .setChoiceValues(['Examples', 'Practice tasks', 'Quiz', 'Asking a friend']);

  form.addParagraphTextItem().setTitle('One thing I still don’t understand…');

  console.log('Share:', form.getPublishedUrl());
  console.log('Edit: ', form.getEditUrl());
}

Execution log

Share: https://docs.google.com/forms/d/e/1FAIpQL…/viewform
Edit:  https://docs.google.com/forms/d/1Abc…/edit

Open the Share link to see the form as a student would. Each add…Item() returns the item, so you chain its settings.

Method	Question type
`addTextItem`	Short answer
`addParagraphTextItem`	Long answer
`addMultipleChoiceItem`	Pick one (radio buttons)
`addCheckboxItem`	Pick any
`addListItem`	Drop-down
`addScaleItem`	Linear scale, e.g. 1–5
`addDateItem`	A date
`addSectionHeaderItem` / `addPageBreakItem`	A heading / a new page

Form-wide settings include setCollectEmail, setLimitOneResponsePerUser, setShuffleQuestions, setConfirmationMessage and setAcceptingResponses(false) to close it.

Next: a quiz from a sheet →

Write questions in a sheet, get a self-marking quiz

Teachers find it easier to write questions in rows than to click through the Forms editor. Make a Questions tab:

	A	B	C	D	E	F
1	Question	A	B	C	D	Answer
2	Which keyword can't be reassigned?	var	let	const	fixed	C
3	Arrays start at index…	0	1			A

Code.gs

// Sheet "Questions": Question | A | B | C | D | Answer (the letter)
function makeQuizForm() {
  const rows = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Questions').getDataRange().getValues().slice(1);

  const form = FormApp.create('Apps Script quiz').setIsQuiz(true);
  form.addTextItem().setTitle('Your name').setRequired(true);

  rows.forEach(([question, a, b, c, d, answer]) => {
    const item = form.addMultipleChoiceItem().setTitle(question).setPoints(1);
    const options = { A: a, B: b, C: c, D: d };
    item.setChoices(
      Object.entries(options)
        .filter(([, text]) => text !== '')
        .map(([letter, text]) => item.createChoice(String(text), letter === answer))
    );
  });
  console.log(form.getPublishedUrl());
}

setIsQuiz(true) turns on marking, setPoints(1) gives each question a score, and createChoice(text, isCorrect) marks the right option. Empty option cells are filtered out, so a question can have two, three or four options.

Next: responses →

React to every submission

Code.gs

// Install with: Triggers → Add Trigger → onFormSubmit, From form/spreadsheet, On form submit.
// (Or put this in the project bound to the form's response spreadsheet.)
function onFormSubmit(e) {
  // For a form trigger, e.response is the FormResponse.
  const answers = e.response.getItemResponses();
  const lines = answers.map((r) => `${r.getItem().getTitle()}: ${r.getResponse()}`);

  MailApp.sendEmail(
    Session.getActiveUser().getEmail(),
    'New feedback received',
    lines.join('\n')
  );
}

Open the form's edit page → ⋮ → Script editor (a form-bound project), and paste the code.
⏰ Triggers → Add Trigger → function onFormSubmit, event source From form, event type On form submit. Save and authorize.
Submit the form yourself. The email arrives within seconds.

Form trigger or spreadsheet trigger?

If the form sends responses to a sheet, you can instead put the trigger in the sheet's project (event source "From spreadsheet"). Then the event has e.values (an array, in column order) and e.namedValues (an object keyed by question title) instead of e.response, plus e.range, the new row.

Read all responses later

Code.gs

// Read every response of a form and write a summary to the sheet.
function summariseFeedback() {
  const form = FormApp.openById('PASTE_THE_FORM_ID_HERE');
  const responses = form.getResponses();

  const scores = [];
  responses.forEach((resp) => {
    resp.getItemResponses().forEach((ir) => {
      if (ir.getItem().getType() === FormApp.ItemType.SCALE) scores.push(Number(ir.getResponse()));
    });
  });

  const avg = scores.reduce((s, n) => s + n, 0) / scores.length;
  console.log(`${responses.length} responses, average clarity ${avg.toFixed(2)} / 5`);
}

Execution log

27 responses, average clarity 3.85 / 5

A form's ID is in its edit URL (/forms/d/ID/edit), not the shorter share link. Each response has item responses, and each of those knows its question (getItem()) and answer (getResponse(); checkbox answers come back as an array).

Next: Slides →

A deck from data

Code.gs

// One slide per student from the Marks tab: a quick "results" deck.
function marksToSlides() {
  const rows = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Marks').getDataRange().getValues().slice(1);

  const deck = SlidesApp.create('Results – M.5');
  const first = deck.getSlides()[0];                    // a new deck starts with a title slide
  first.getPlaceholder(SlidesApp.PlaceholderType.CENTERED_TITLE).asShape().getText().setText('Term results');
  first.getPlaceholder(SlidesApp.PlaceholderType.SUBTITLE).asShape().getText().setText('Computing · M.5');

  rows.forEach(([name, room, mark]) => {
    const slide = deck.appendSlide(SlidesApp.PredefinedLayout.TITLE_AND_BODY);
    slide.getPlaceholder(SlidesApp.PlaceholderType.TITLE).asShape().getText().setText(name);
    slide.getPlaceholder(SlidesApp.PlaceholderType.BODY).asShape().getText()
      .setText(`Room: ${room}\nMark: ${mark}/100`);
    if (mark >= 80) slide.getBackground().setSolidFill('#e6f4ea');
  });
  console.log(deck.getUrl());
}

Execution log

https://docs.google.com/presentation/d/1Qr…/edit

A presentation contains slides, and a slide contains page elements: shapes, text boxes, images and tables. Each layout comes with placeholders (title, subtitle, body). getPlaceholder(type) finds one, asShape() treats it as a shape, and getText().setText() fills it.

Layout	Placeholders
`TITLE`	`CENTERED_TITLE`, `SUBTITLE`
`TITLE_AND_BODY`	`TITLE`, `BODY`
`SECTION_HEADER`	`TITLE`
`BLANK`	None: add your own with `slide.insertTextBox(text, left, top, width, height)`

Better: fill a designed template

Positioning boxes in code is slow and looks plain. Design a slide by hand with {{name}} and {{award}} on it, then:

Code.gs

// Fill a designed Slides template: copy it, then replace {{tags}} on every slide.
function certificateSlide(name, award) {
  const TEMPLATE_ID = 'PASTE_THE_SLIDES_TEMPLATE_ID_HERE';
  const copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy(`Award – ${name}`);
  const deck = SlidesApp.openById(copy.getId());
  deck.replaceAllText('{{name}}', name);
  deck.replaceAllText('{{award}}', award);
  deck.saveAndClose();
  return copy.getAs(MimeType.PDF);       // ready to attach to an email
}

replaceAllText changes every slide at once and keeps your fonts and colours. Loop over the Marks tab, call this per student, and attach each PDF with I4's code.

Next: practice →

Practice

1. Club sign-up form Easy

Make a form that collects email, full name, class (drop-down), days available (checkboxes) and experience, and sends responses to the current spreadsheet.

Show a solution

Code.gs

function clubSignUp() {
  const form = FormApp.create('Coding club sign-up').setCollectEmail(true);
  form.addTextItem().setTitle('Full name').setRequired(true);
  form.addListItem().setTitle('Class').setChoiceValues(['M.4', 'M.5', 'M.6']).setRequired(true);
  form.addCheckboxItem().setTitle('Which days can you come?').setChoiceValues(['Mon', 'Tue', 'Wed', 'Thu', 'Fri']);
  form.addMultipleChoiceItem().setTitle('Have you coded before?').setChoiceValues(['Never', 'A little', 'Yes, a lot']);

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());   // responses land in a new tab
  console.log(form.getPublishedUrl());
}

2. Lesson deck Medium

From an array of five agenda items, make a deck: a title slide, an agenda slide listing them all, then a section-header slide for each item after the first.

Show a solution

Code.gs

function agendaDeck() {
  const items = ['Welcome', 'Last week’s homework', 'New topic: loops', 'Practice', 'Exit quiz'];
  const deck = SlidesApp.create('Lesson plan');
  const title = deck.getSlides()[0];
  title.getPlaceholder(SlidesApp.PlaceholderType.CENTERED_TITLE).asShape().getText().setText('Today');
  const s = deck.appendSlide(SlidesApp.PredefinedLayout.TITLE_AND_BODY);
  s.getPlaceholder(SlidesApp.PlaceholderType.TITLE).asShape().getText().setText('Agenda');
  s.getPlaceholder(SlidesApp.PlaceholderType.BODY).asShape().getText().setText(items.join('\n'));
  items.slice(1).forEach((it) => {
    deck.appendSlide(SlidesApp.PredefinedLayout.SECTION_HEADER)
      .getPlaceholder(SlidesApp.PlaceholderType.TITLE).asShape().getText().setText(it);
  });
  console.log(deck.getUrl());
}

3. Instant thank-you Medium

With setCollectEmail(true) on the sign-up form, change onFormSubmit so it emails the person who submitted (e.response.getRespondentEmail()) a thank-you with the days they chose.

4. Feedback dashboard Challenge

Extend summariseFeedback to count the "What helped you most?" options and write the counts to a sheet. Add a chart from the sheet by hand and watch it update as responses arrive.

Next: quiz →

Check yourself

1. Which item type gives a 1-to-5 rating?

2. How do you mark the correct answer in a quiz form?

3. In an installable form-submit trigger on a FORM, which field holds the answers?

4. Why must onFormSubmit be an installable trigger, not a simple one?

5. A new deck from SlidesApp.create starts with…

6. Best way to make good-looking generated slides?