Automatically Populate a Row of Data When Adding New Items to a Column

Someone recently asked me: “How do I automatically populate a row of data whenever I add a new entry to one column?”

In his example, he wanted to add a row of checkboxes. That’s not possible yet (as you can’t programmatically add checkboxes, even by copying a range), but you can add anything else, e.g. the visual representation of checkboxes.

Basically, this is a way to automatically populate a row of data whenever you add another item to one column, like the leftmost column.

So I wrote a Google Apps Script function that

1. Detects when the sheet is edited via onEdit()
2. Verifies that the edit is in the leftmost column
3. Adds a range of content to the columns to the right.

The Apps Script to Automatically Populate a Row of Data

Because I like to answer first, here’s the script. You can take it and modify it as you wish.

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var source_range = sheet.getRangeByName('source_range');
  var lastRow = getLastDataRow (sheet);

  if (e.range.getColumn() == 1 && e.range.getRow() == lastRow) {
    trange = sheet.getRange("B"+lastRow+":N"+lastRow);
    trange.setValues(source_range.getValues());   
  }
}

function getLastDataRow(sheet) {
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange("A" + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }              
}

How it Works

The way in which I automatically populate a row of data for every new entry in the leftmost column is by using a Google Apps Script.

Firstly, start with onEdit(e). This is a trigger that starts executing every time an element “e” is edited.

When this happens, I detect what element “e” is being edited, specifically to check what column it’s in, and to make sure it’s the last row.

If that validation is true, I copy a predefined row of data (called source_range in my spreadsheet) and paste it into the target range.

In my case, I made a range called source_range with these elements in it:

That’s the range of data to add to any new row. Whenever a new entry is added to column A, it automatically populates a row of data to the right of it.

Technically, this trigger could also happen when deleting the row, but that’s not something that would happen in my case. You could add a further validation that the target cell is blank.

How to Use This Script

Same as with any script. In your Google Sheet, do the following:

1. Go to Extensions –> Apps Script
2. Replace the code you see (just a template) with the code above
3. Choose “Library” as the deployment type
4. Give it any description you want.
5. Deploy!