duplicate row based on cell value

Question

I want to duplicate row based on cell value then delete the cell value(in red) so that when rerun the script wont work on the same row again

I've tried script but it has 2 problems

1- the cell value remain so when rerun script it will cause duplication of same row again and again 2- remove the formulas that I wrote from the entire sheet

the script if you want to check it is still exist in appscript https://docs.google.com/spreadsheets/d/1fJc2ymAADaZ4jtEGRAkUkWRape24Un7OE4jYcgcd9FM/edit#gid=0

Cooper · Accepted Answer · 2020-01-26 18:41:01Z

1

Auto Dupe for Single Row

function AutoDuplicate() {
  var ss=SpreadsheetApp.getActive()
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getDisplayValues();
  var bA=rg.getBackgrounds();
  var v=[];
  var b=[]
  for(var i=0;i<vA.length;i++){
    var t1=bA[i][7];
    var t2=vA[i][7];
    if(bA[i][7]=='#ff0000' && !isNaN(vA[i][7]) && Number(vA[i][7])>0) {
      bA[i][7]='#ffffff';
      for(var j=0;j<vA[i][7];j++) {
        v.push(vA[i]);
      }
      b.push(bA[i]);
      sh.getRange(1,1,v.length,v[0].length).setValues(v);
      sh.getRange(1,1,b.length,b[0].length).setBackgrounds(b);
    }
  }
}

Animation:

Auto Dupe For Multiple Rows

function autoDupeForMultipleRows() {
  var ss=SpreadsheetApp.getActive()
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getDisplayValues();
  var bA=rg.getBackgrounds();
  var v=[];
  var b=[];
  var a=0;
  for(var i=0;i<vA.length;i++){
    if(bA[i][7]=='#ff0000' && !isNaN(vA[i][7]) && Number(vA[i][7])>0) {
      bA[i][7]='#ffffff';
      for(var j=0;j<=vA[i][7];j++) {
        v.push(vA[i]);
        b.push(bA[i]);
      }
    }
  }
  sh.clearContents();
  var org=sh.getRange(1,1,v.length,v[0].length).setValues(v);
  org.setBackgrounds(b);
}

Animation:

edited Jan 26, 2020 at 18:41

answered Jan 26, 2020 at 17:42

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cooper Over a year ago

Your example only showed one row and the duplicates were only applied to row one. So you need to change your description so that we can understand what you really want. I did what you described.

Manaf A.B Over a year ago

I see, that's beutiful thank you very much I saw the animation it wil do the job, I really apreace it

Collectives™ on Stack Overflow

duplicate row based on cell value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related