Google Sheets Copy one Cell and paste into another Cell

0
votes

I want to copy a number from a cell and paste it into another cell on the same sheet using a button.

The cell also has a formula so I only want the number to be taken, not the formula. That uses a RANDBETWEEN.

But to also have the numbers continue to drop down a column but not on the last row.

    function copytest() 
{var sheet2 = SpreadsheetApp.getActive().getSheetByName('A');
sheet2.getRange("B4").copyTo(sheet2.getRange(sheet2.getLastRow()+1,1,1,1),
{formatOnly:false , contentsOnly:true});
}

IMAGE: https://i.stack.imgur.com/Po1Sa.jpg

1
google-apps-scriptcopypaste

1 Answers

2
votes

This function places the value in the next empty row at the bottom of the current column.

function copytest() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  const rg=sh.getActiveCell();
  const col=rg.getColumn();
  rg.copyTo(sh.getRange(getColumnHeight(col,sh,ss)+1,col),{formatOnly:false , contentsOnly:true});
}

Function to find the next empty row in a column

function getColumnHeight(col,sh,ss){
  var ss=ss||SpreadsheetApp.getActive();
  var sh=sh||ss.getActiveSheet();
  var col=col||sh.getActiveCell().getColumn();
  const rcA=sh.getRange(1,col,sh.getLastRow(),1).getValues().reverse()
  let s=0;
  for(let i=0;i<rcA.length;i++) {
    if(rcA[i][0].toString().length==0) {
      s++;
    }else{
      break;
    }
  }
  return rcA.length-s;
}