What is code to count last row and add cell data to new blank row

Question

The code copies data from cell M4 and to cell L45, which is good but if I change the date in M4 and run the script it will overwrite cell L45 with the new info instead of copying to the next blank row below (cell L46) What is the adjustment needed and where would it go in the script

Here is code:

function moveValuesOnly() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var source = ss.getRange('M4');
 source.copyTo(ss.getRange('L45'), {contentsOnly: true});

You might want to consider using [google-apps-script] tag. SpreadsheetApp is an apps script class. — Cooper, Mar 08 '19 at 23:57
This answer is very similar to what you're looking for https://stackoverflow.com/a/54321278/8223070 — Cat, Mar 09 '19 at 00:37
Thanks Cat, could you tell me where to put this function in the script above — Nuclear 420, Mar 09 '19 at 02:30

score 0 · Answer 1 · answered Mar 09 '19 at 04:15

This function will append any changes in 'M4' to the bottom of column L.

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()!='Sheet1') {return;};//edit Sheet name for your requirements
  if(e.range.getA1Notation()=='M4') {
    e.range.getSheet().getRange(getColumnHeight(12,sh,e.source) + 1,12).setValue(e.value);
  }
}

function getColumnHeight(col,sh,ss){
  var ss=ss || SpreadsheetApp.getActive();
  var sh=sh || ss.getActiveSheet();
  var col=col || sh.getActiveCell().getColumn();
  var rg=sh.getRange(1,col,sh.getLastRow(),1);
  var vA=rg.getValues();
  while(vA[vA.length-1][0].length==0){
    vA.splice(vA.length-1,1);
  }
  return vA.length;
}

What is code to count last row and add cell data to new blank row

1 Answers1