CODEWITHSUNDEEP
javagoogle-apps-scriptkey
testing123
testing123

Reputation: 829

copy rows to new sheet by checking key array in GAS

I'm trying to copy rows from one sheet to another by checking a key column 'O'. 

function keyPush(){
  var thisSheet = SpreadsheetApp.openById('##');
  var origSheet = thisSheet.getSheetByName('original');
  var origLastRow = origSheet.getLastRow();
  var newSheet= thisSheet.getSheetByName('copy');
  var tkhLastRow = newSheet.getLastRow();
  var keyCol = 'O';
  var key = origSheet.getRange(keyCol+'2:'+keyCol+origLastRow).getValues();
  for(var row=key.length-1;row>1;row--){
    if( origSheet.getRange('O'+row) != 'done' )
    {
      var orig = origSheet.getRange('A'+row+':N'+row);
      var dest = newSheet.getRange('C'+tkhLastRow+':P'+tkhLastRow);
      orig.copyTo(dest,{contentOnly:true});
      origSheet.getRange('O'+row).setValue('done');
    }
  }
}

I'm missing a loop or something because I can see the rows being iterated through on the new sheet. I need it to only copy the rows not marked done to the new sheet. Right now its pushing all rows from the origSheet into the same row of the newSheet which is why I can see it happen.

Upvotes: 0

Views: 135

Answers (2)

testing123
testing123

Reputation: 829

Here's the working code if it's helpful to anyone.

function pushRow(){
  var thisSheet = SpreadsheetApp.openById('##');
  var origSheet = thisSheet.getSheetByName('original');
  var origLastRow = origSheet.getLastRow();
  var tkhContacts = thisSheet.getSheetByName('contacts');
  var keyCol = 'O';
  var keys = origSheet.getRange(keyCol+'2:'+keyCol+origLastRow);
  var keyRow = keys.getRow();
    for(var row=origLastRow;row>1;row--){
      if( origSheet.getRange(keyCol+row).getValue().trim() != 'done' )
      {
        var orig = origSheet.getRange('A'+row+':N'+row).getValues();
        tkhContacts.appendRow(
          [ '','',orig[0][0],orig[0][1],orig[0][2],orig[0][3],orig[0][4],orig[0][5],orig[0][6],orig[0][7],orig[0][8],orig[0][9],orig[0][10],orig[0][11],orig[0][12],orig[0][13],'','','' ] )
        origSheet.getRange(keyCol+row).setValue('done');
      }
    }
}

Upvotes: 1

Pierre-Marie Richard
Pierre-Marie Richard

Reputation: 2014

You forgot to get the new value from newSheet last row, so it will put it on the same row each time. Here is a correction

function keyPush(){
  var thisSheet = SpreadsheetApp.openById('##');
  var origSheet = thisSheet.getSheetByName('original');
  var origLastRow = origSheet.getLastRow();
  var newSheet= thisSheet.getSheetByName('copy');
  var tkhLastRow = newSheet.getLastRow();
  var keyCol = 'O';
  var key = origSheet.getRange(keyCol+'2:'+keyCol+origLastRow).getValues();
  for(var row=key.length-1;row>1;row--){
    if( origSheet.getRange('O'+row) != 'done' )
    {
      tkhLastRow = newSheet.getLastRow();
      var orig = origSheet.getRange('A'+row+':N'+row);
      var dest = newSheet.getRange('C'+tkhLastRow+':P'+tkhLastRow);
      orig.copyTo(dest,{contentOnly:true});
      origSheet.getRange('O'+row).setValue('done');
    }
  }
}

Also, you should consider using apprendRow(content) method which is atomical and avoid such problem as get the new value of last row each time you write some data.

Upvotes: 0

Related Questions