Copy and paste the data from Sheet1 to Sheet2 and then delete the entered data without deleting the function in Sheet1

1
votes

Copy the data in Sheet1 from Row 4 to Row 13 and from Column C to Column G and paste it into Sheet2 and clear the entered data without deleting the function in Column d on Sheet1

Example sheet

Explanation

1
google-apps-scriptgoogle-sheets
Could you please explain from one row to 10 rows a little more? Also, would help if you can share whatever you've tried so far so folks from SO community could build from there on forward. - Sourabh Choraria
I am a beginner in programming ... I mean is to migrate data of three or more rows without moving the formula in column d as in the example link from sheet 1 to sheet 2 ... Thanks in advance - ammaeln
Thanks for explaining it. I also want to clarify, after moving the data from Sheet1, do you want to retain the values or clear them? I understand you do intend to retain the formulas from Column D - what about the other cells? - Sourabh Choraria
@SourabhChoraria - Other cells of course I want to clear ... Thanks - ammaeln
@SourabhChoraria - The question has been modified to be clearer of what is required - ammaeln

1 Answers

2
votes
function runOne() {
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName('Sheet2');
  var rg=sh1.getRange(4,3,sh1.getLastRow()-3,5);
  var v=rg.getDisplayValues();
  var lr;
  for(var i=0;i<v.length;i++) {
    if(v[i].join('').length==0) {
      lr=i;
      break;
    }
  }
  var rg1=sh1.getRange(4,3,lr,5);
  var vA=rg1.getDisplayValues();
  var fA=rg1.getFormulas();
  rg1.copyTo(sh2.getRange(sh2.getLastRow()+1,1),{contentsOnly:true});
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {
      if(!fA[i][j]=='') {
        vA[i][j]='';
      }
    }
  }
  rg1.setValues(vA);
  rg1.setFormulas(fA);
}