How to Auto Format Google Form Responses in Google Sheets

Discover ways to robotically protect the formatting in Google Sheet when new Google Form responses are submitted.

If you submit a Google Form, it shops a duplicate of the shape response as a brand new row within the Google Sheet. The one drawback right here is that Google Types won’t add any formatting or types to the brand new row that you’ll have utilized to the earlier rows of the sheet. Let me illustrate this with a small instance.

Right here’s a Google Sheet that’s storing Google Form responses. I’ve modified the default font household to Droid Sans, center-aligned the Nation and Age column and in addition utilized a distinct date format to the Date of Beginning column.

All the things seems to be good however as quickly as a brand new type submissions is made, the brand new row appended to the Google Sheet through Google Types will lose all of the formatting.

The cell alignment just isn’t preserved, the customized date codecs are ignored and so is the default font dimension and font household. Right here’s a screenshot of the identical sheet however with a brand new row added by Google Types.

Google Sheets

Auto Format New Rows in Google Sheets

Since there’s no means for us to override this Google Types habits, we are able to take the assistance of Google Apps Script to robotically format new rows in Google Sheets which are added by Google Types.

  RPSC ASO Admit Card 2022-Hall Ticket Download

The thought is easy. We’ll create an onFormSubmit set off contained in the Google Sheet that can be executed at any time when a brand new type is submitted. This set off will take no matter formatting that has been utilized to the earlier row and apply to the present row.

To get began, open the Google Sheet and format the final row with the types that you just want to apply to incoming type responses.

Subsequent, go to Extensions > Apps Script menu and copy-paste the Google Script under. Run the createTrigger and you’re good 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);
  }
};