Methods to Mechanically Format Google Type Responses in Google Sheets

When somebody submits a brand new Google Type response, the shape solutions are automaticaly added as a brand new row within the Google Sheet that’s linked to the shape. The one downside right here is that Google Forms won’t add any formatting or kinds to the brand new row that you might have utilized to the earlier rows of the sheet.

Let me illustrate this with an instance.

Right here’s a screenshot of a Google Sheet that’s storing Google Type 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.

The formatting appears to be like good however as quickly as a brand new Google Type submissions is acquired, the brand new response row appended to the Google Sheet will lose all of the formatting utilized to the earlier rows.

As you may see within the screenshot beneath, the cell alignment just isn’t preserved, the customized date codecs are ignored and so is the default font measurement and font household.

Auto Format New Rows in Google Sheets

Since there’s no manner for us to override this Google Kinds conduct, we are able to take the assistance of Google Apps Script to routinely format new rows in Google Sheets which are added by way of Google Kinds.

To get began, open the Google Sheet and format the final row with the kinds that you just wish to apply to incoming kind responses. Please guarantee that there’s at the very least one kind response within the Google Sheet the place you may apply the specified formatting that you just need to be utilized to new rows.

Add Google Apps Script to Google Sheet

Subsequent, go to Extensions > Apps Script menu inside Google Sheets and copy-paste the Google Script beneath.

/**
 * @OnlyCurrentDoc
 */

const formatRowOnFormSubmit = formEvent => {
  strive {
    const { vary } = formEvent || {};
    if (!vary) throw new Error("This perform ought to solely be triggered by kind submissions");

    const sheet = vary.getSheet();
    const currentRow = vary.getRowIndex();
    const endColumn = sheet.getLastColumn();

    // Skip formatting if that is the primary or second row
    if (currentRow <= 2) return;

    // Copy formatting from earlier row to new row
    const sourceRange = sheet.getRange(currentRow - 1, 1, 1, endColumn);
    sourceRange.copyFormatToRange(sheet, 1, endColumn, currentRow, currentRow);
  } catch (error) {
    console.error(`Error formatting new response: ${error.message}`);
  }
};

Save the script. Subsequent, we’ll create an onFormSubmit set off contained in the Google Sheet that can execute the formatRowOnFormSubmit perform every time a brand new kind is submitted. This set off will take no matter formatting that has been utilized to the earlier row and apply that to the present row.

To create the set off, go to the Triggers part within the sidebar and click on + Add Set off. Below the Occasion sort dropdown, choose On kind submit and save the set off. That’s it!

A earlier model of the script used the copyTo methodology to repeat formatting. Whereas this method works, the present copyFormatToRange methodology is extra environment friendly because it’s particularly designed for copying solely formatting between ranges.

const targetRange = sheet.getRange(currentRow, 1, 1, endColumn);
sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT);

Conditional Formatting in Google Sheets

Study extra about conditional formatting in Google Sheets that lets you apply automated formatting to cells in spreadsheets that meet sure standards.

Additionally see: Automate Google Forms through Workflows