Can I dynamically add rows when a column is full?-CodePudding

Is it possible for my Google sheet to automatically append a new formatted row to the end of a column when it fills up?

I would like to avoid having to manually create, say, 1000 pre-formatted empty rows in my sheet.

CodePudding user response：

Format the sheet down to the last row, as tested here in this GIF using Sequence(20), Sequence(2000).

CodePudding user response：

This is possible using Google Apps Script onEdit() trigger.

Try:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(1, 1, sheet.getLastRow(), 3); //Range is set from COL A - COL C up to last row with data
  var lastRow = sheet.getLastRow();
  var val = range.getValues();
  var lastVal = val[val.length - 1];

  //Check if the edit happened within Columns A-C
  var range = e.range
  var col = range.getColumn();

  //Set sheetname and cell range where the options are coming from
  var options = ss.getSheetByName("Names").getRange("A2:A") 
  var dropdownCell1 = sheet.getRange(lastRow   1, 1);
  var dropdownCell2 = sheet.getRange(lastRow   1, 2);

  if (col>= 1 && col <= 3 && !lastVal.includes('')) { //checks if the last row of data is filled

    //Sets the data validation rule to the cells
    var rule = SpreadsheetApp.newDataValidation().requireValueInRange(options)
    dropdownCell1.setDataValidation(rule)
    dropdownCell2.setDataValidation(rule)
  }
}

Result:

Explanation:

This uses onEdit() trigger which runs every time you make edit to a cell value. This then checks if the edit happens within Columns A-C and checks if the last row from Columns A-C are filled. It then adds the data validation rule to the cells. The dropdown comes from a sheet called 'Names' and the options are listed there.

I just did not add a code to reverse it if a data is deleted. You may edit the range if you are using different columns. Hope this helps!