Build a content-submission queue

Northwind invites readers to pitch guest posts and topic ideas. A linked Google Sheet already records every Form response, but a raw responses tab is awkward to work from — the columns sit in Form order, there is no review status, and the editorial team ends up copying rows into a separate tracker by hand.

This script turns each submission into a clean queue row the moment it arrives. It picks out the fields the editors actually care about, adds a timestamp and a pending review status, and appends them to a dedicated queue sheet. From there the team can sort, filter and move pitches through review without ever touching the messy responses tab.

What you’ll need

• A Google Form with at least three questions: Author email, Title and Pitch.
• A separate Google Sheet to act as the queue, with a header row: submitted, authorEmail, title, pitch, status.
• The queue spreadsheet’s ID, copied from its URL.
• Edit access to the Form so you can attach the trigger.

The trigger

// The spreadsheet that holds the review queue.
const QUEUE_SHEET_ID = '1abcContentQueueId';

/**
 * Runs on every Form submission. Pulls the fields the editors need out of
 * the response and appends a tidy row to the review queue.
 *
 * @param {Object} e - The Form-submit event, with named answers in
 *   e.namedValues (each value is an array of strings).
 */
function onFormSubmit(e) {
  // 1. Guard against a malformed or test event with no answers.
  if (!e || !e.namedValues) {
    Logger.log('No form data on this event — nothing to queue.');
    return;
  }

  // 2. Read each answer by its question title. namedValues holds arrays,
  //    so take the first entry; fall back to '' if the field is missing.
  const answer = (name) => (e.namedValues[name] || [''])[0];

  // 3. Append a single tidy row to the queue, in the editors' column order,
  //    stamped with the time and a starting status.
  const sheet = SpreadsheetApp.openById(QUEUE_SHEET_ID).getSheets()[0];
  sheet.appendRow([
    new Date(),                  // submitted
    answer('Author email'),      // authorEmail
    answer('Title'),             // title
    answer('Pitch'),             // pitch
    'pending review',            // status
  ]);
}

How it works

1. onFormSubmit runs automatically each time someone submits the Form. The event object e carries the answers in e.namedValues, keyed by question title.
2. It first checks that e.namedValues exists — a manual test run or an empty event has none, and bailing out early avoids an error.
3. The small answer helper reads one question by title. namedValues stores every answer as an array, so it takes the first element, and falls back to an empty string if the question is missing or unanswered.
4. It opens the queue spreadsheet and appends one row in the editors’ preferred column order: a timestamp, the author’s email, the title, the pitch, and a starting status of pending review.
5. Because it uses appendRow, each submission simply drops onto the bottom of the queue with no risk of overwriting an existing row.

Example run

Someone fills in the Form with:

• Author email: [email protected]
• Title: Automating invoice reminders
• Pitch: A walkthrough of chasing late invoices with a scheduled script.

A new row appears at the bottom of the queue sheet:

submitted	authorEmail	title	pitch	status
2025-10-09 14:22	[email protected]	Automating invoice reminders	A walkthrough of chasing late invoices…	pending review

The editors now work entirely from this tab — sorting by submitted, filtering on status, and moving each pitch forward without opening the Form’s raw responses sheet.

Run it

This script runs on a trigger, not by hand:

1. Open the script bound to (or with access to) your Form.
2. In the Apps Script editor, open Triggers (the clock icon).
3. Click Add Trigger, choose onFormSubmit, set the event source to From form and the event type to On form submit.
4. Save and approve the authorisation prompt.

Submit a test response to confirm a fresh row lands in the queue sheet.

Watch out for

• The question titles in the code — Author email, Title, Pitch — must match the Form’s question text exactly, including capitalisation. Rename a question and the matching answer(...) call returns an empty string.
• An On form submit trigger only fires for new submissions. Responses collected before the trigger existed will not be queued; copy those across once by hand.
• If you edit the Form to add or remove questions, the queue’s column layout will not change automatically — update the appendRow array to match.
• The trigger runs as the person who created it, so that account needs edit access to the queue spreadsheet.
• Form-submit triggers are subject to Apps Script’s daily trigger quota. For a normal submission volume this is never a concern, but a sudden spike of hundreds of entries could hit the limit.