Google-sheets – Duplicate row values to next row, based off specific column

google sheetsgoogle-apps-scriptjavascript

I'm trying to set up a button that will copy information from the last row and paste it into the row right underneath (based on where column D last has a value). Almost like a "duplicate" button, because I am inputting information that will repeat.

I had a script that was doing the copy perfectly. However, I realized an issue — because I have a unique ID pre-loaded to fill up all rows in the first column (causing the added row to come after this), I wanted this script to seek the last row based on the FOURTH column, not the FIRST.

On a similar pattern, I am looking to ONLY copy the information from column 4, 5, 6, and 7, into the row underneath — not any other info.

My idea was that I could basically combine these two concepts — copying and pasting the last row into the row underneath, and using GetColumnHeight so that I can control which columns should be selected.

function addRow() {
  var sh = ss.getActiveSheet(), lRow = sh.getLastRow();
  var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,4,lCol);
  sh.insertRowsAfter(lRow, 1);
  range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});

  var vs=sh.getRange(e.range.rowStart,4,1,3).getValues()
  tsh.getRange(getColumnHeight(3,tsh,e.source)+1,4,1,3).setValues(vs);
    }
  }
function getColumnHeight(col,sh,ss){
  var ss=ss||SpreadsheetApp.getActive();
  var sh=sh||ss.getActiveSheet();
  var col=col||sh.getActiveCell().getColumn();
  var v=sh.getRange(1,col,sh.getLastRow(),1).getValues().map(function(r){return r[0];});
  var s=0;
  var h=0;
  v.forEach(function(e,i){if(e==''){s++;}else{s=0;}h++;});
  return (h-s);
}

I'm really all over the place right now, and struggling to piece something together that will copy and paste the values in column 4,5,6, and 7 into the row below, ignoring the filled values in column A. Any suggestions?

Here is a visual to further explain my goal:

https://docs.google.com/spreadsheets/d/1aUQJr8mdizNR9LHavHHt_K34Yf0ZVI4nl38FD9o-LHo/edit?usp=sharing

Best Answer

Final solution (with great help of @Tedinoz):

function copyrow(){
var ss = SpreadsheetApp.getActiveSheet();

  var Dvals = ss.getRange("D1:D").getValues();
  var Dlast = Dvals.filter(String).length;
 
  var lCol = ss.getLastColumn(), range = ss.getRange(Dlast,4,1,lCol);
  range.copyTo(ss.getRange(Dlast+1, 4, 1,lCol-4+1), {contentsOnly:false});
 
}
Related Topic