Step-by-Step Guide: Auto Formatting Google Form Responses in Sheets

Step-by-Step Guide: Auto Formatting Google Form Responses in Sheets

With the help of this detailed, step-by-step tutorial, learn how to automatically style Google Form responses in Google Sheets. To retain consistency and save time, abandon manual formatting and embrace automation.

Welcome to our detailed tutorial on formatting Google Form responses automatically in Google Sheets! This article will help you automate the procedure and keep consistency in your sheets if you’ve ever been annoyed by the formatting that gets lost when fresh form submissions are made. Let’s get started and see how easily you can format your Google Form responses in Sheets.

A Google Form’s response is saved as a new row in a Google Sheet when you submit it. The problem, though, is when Google Forms doesn’t remember any formatting or styles that were used on earlier rows in the sheet. Please allow me to give you a small illustration of this.

Check out this Google Sheet, where we’ve changed the Date of Birth column’s date format, centered the Country and Age columns, and changed the font family to Droid Sans.

Everything appears to be working OK, but after a new form submission, the freshly added row from Google Forms in the Google Sheet loses all formatting.

We can use Google Apps Script to automatically format new rows in Google Sheets that are added through Google Forms to get around this Google Forms constraint.

Though basic, the concept is powerful. The Google Sheet will have a onFormSubmit trigger that will be activated each time a new form is submitted. The formatting from the prior row will then be carried over and applied to the current row by this trigger.

Let’s get going. To apply the desired styles to the receiving form replies, open the Google Sheet and format the last row as requested.

Then, copy-paste the following Google Script into the Extensions > Apps Script menu. Run the createTrigger function once you’re finished, and you’re ready to go!

/**
 * @OnlyCurrentDoc
 */

const createTrigger = () => {
  ScriptApp.getProjectTriggers().forEach((trigger) => {
    ScriptApp.deleteTrigger(trigger);
  });
  ScriptApp.newTrigger('formRowOnFormSubmit').forSpreadsheet(SpreadsheetApp.getActive()).onFormSubmit().create();
};

const formRowOnFormSubmit = (e) => {
  if (!e) {
    throw new Error('Please do not run this function manually!');
  }
  const { range } = e;
  const row = range.getRowIndex();
  if (row > 2) {
    const sheet = range.getSheet();
    // Select the previous row range
    const sourceRange = sheet.getRange(`${row - 1}:${row - 1}`);
    // Get the recently added row
    const targetRange = sheet.getRange(`${row}:${row}`);
    // Copy the format only from the previous row to the current row
    sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  }
};

This configuration ensures consistency in the Google Sheet by preserving the formatting from the preceding row for any new form submissions.

Additionally, look into conditional formatting if you want to learn more about Google Sheets’ more complex formatting choices. It gives you the option to format cells automatically depending on predetermined criteria, giving your spreadsheets even more control.

Leave a Comment