CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
TC76
TC76

Reputation: 870

Insert a new row and copy data validation only from a row below

Creating a "Task List" spreadsheet where each time a task is entered in the first non-header row (row 2), it is first sorted into the rest of the already entered tasks and then a new line is added to the top (row 2) for the next entry (I'm not married to the order of operations as long as the end result is the same).

Here's a link to the spreadsheet, if needed: https://docs.google.com/spreadsheets/d/1clySVeeoq1CBskDiaQywIuHZcr0zrTJIDDc1OPIc0wQ/edit?usp=sharing

I'm not familiar with writing GAS, but I am somewhat familiar enough to piece snippets together.

I've found the following bits and pieces that have almost gotten me where I want to be, but I'm either getting both data validation as well as contents or neither. All I want is data validation with empty contents.

I've found this question asked and answered, but maybe the solutions are outdated because they're not working for me. Or maybe I'm just doing something wrong. Here's what I have currently...

Thank you for any assistance!

Trey

function onEdit(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var editedCell = sh.getActiveRange().getColumnIndex();


  // Update after "Task" column is edited
  if(editedCell == 6) { 
    sh.getRange("A2:F").sort({column: 2}).sort({column: 3}).sort({column: 1});

    var firstRow = 2;
    var lCol = sh.getLastColumn();
    var range = sh.getRange(firstRow, 1, 1, lCol);
    var formulas = range.getFormulas();
    sh.insertRowsAfter(1, 1);
    newRange = sh.getRange(firstRow, 1, 1, lCol);

    var rowBelow = firstRow + 1;
    var maxCols = sh.getMaxColumns();
    var rangeToCopy = sh.getRange(rowBelow, 1, 1, maxCols);
    rangeToCopy.copyTo(sh.getRange(firstRow, 1, 1, maxCols), {validationsOnly:true});    
  }
}

Upvotes: 1

Views: 426

Answers (1)

Cooper
Cooper

Reputation: 64072

I think this may be closer to what you want. But you didn't do anything with the formulas and there are no formulas in your example so that makes it difficult to figure out what you might be trying to accomplish.

function onEdit(e){
  var sh = e.range.getSheet();
  if(e.range.columnStart == 6) { 
    sh.getRange("A2:I").sort({column: 2}).sort({column: 3}).sort({column: 1});//I extended this range all the way to cover the headers.  That may not be what you want.
    var range = sh.getRange(2, 1, 1, sh.getLastColumn());
    var formulas = range.getFormulas();
    sh.insertRowsAfter(1, 1);
    newRange = sh.getRange(2, 1, 1, sh.getLastColumn());
    var maxCols = sh.getMaxColumns();
    var rangeToCopy = sh.getRange(3, 1, 1, maxCols);
    rangeToCopy.copyTo(sh.getRange(2, 1, 1, maxCols),SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION,false);    
  }
}

I don't have a link for the e.range.columnStart. I saw others using and just did a Logger.log(JSON.stringify(e)); to see it for myself. I've been using it for a long time so it must be one of those undocumented features.

Here's What it Looks Like:

{"authMode":{},"range":{"columnStart":6,"rowStart":2,"rowEnd":2,"columnEnd":6},"source":{},"user":{"nickname":"jimesteban","email":"[email protected]"},"value":"6"}

Upvotes: 1

Related Questions