copy values under conditions to a different sheet

Question

Daily, I add a row to the sheet, (using onEdit() and I timestamp it - this function is not shown here) Problem occurs at the end.

Please see below :

First sheet

My purpose is to copy values to another sheet called "Payment", choosing one type of payment : PPAL, CASH, or others. (PPAL in this example)

function typesOfPayment() {

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("John");
var rows = ss.getMaxRows();// *because I add a line per day, on the top*
var columns = ss.getMaxColumns();// *I probably never will add columns...*
var values = ss.getRange(1,1,rows,columns).getValues();
var target = new Array();

     for(i=0;i<values.length;i++) {
        if (values[i][2]=="PPAL"){ // *if condition is true copy the whole row to target*
        target.push(values[i]);// *copy the whole row*
        }  
     }

var sDest=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Payment");
sDest.getRange(1,1,rows,columns).setValues(target); 
}

I get a final error message : "0 was specified, but waiting for 8". The var rows seems wrong ?

I am lost. What to do ?

Thanks for your help. Eric

Tanaike Tanaike · Accepted Answer · 2017-09-10T22:26:06

Modification points :

The index of array starts from 0. So the index of column with PPAL is 1.
The numbers of rows and colums for getRange() which is used at setValues() are the size of target which is used at setValues().

When these are reflected to your script, the modified script is as follows.

Modified script :

function typesOfPayment() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("John");
  var rows = ss.getMaxRows();// *because I add a line per day, on the top*
  var columns = ss.getMaxColumns();// *I probably never will add columns...*
  var values = ss.getRange(1,1,rows,columns).getValues();
  var target = new Array();
  for(i=0;i<values.length;i++) {
    if (values[i][1]=="PPAL"){ // *if condition is true copy the whole row to target*
      target.push(values[i]);// *copy the whole row*
    }  
  }
  var sDest=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Payment");
  sDest.getRange(1,1,target.length,target[0].length).setValues(target); 
}

If I misunderstand your question, I'm sorry.

copy values under conditions to a different sheet

1 Answers

Modification points :

Modified script :