Copy/paste values using google app script

Question

Here is the sheet with an example of the data that I'm using

https://docs.google.com/spreadsheets/d/1_k3xclEgdREfMMks7H2-uk0tzxXqCaoeVW_G8ase-3o/edit?usp=sharing

I only put the formulas on the rows without color, and the information about the schedule comes from other tab.

the colors in green are the ones with dates, where I store inside the numbers, and the blue ones also contain formulas and I wanna skip those columns when pasting the values because is correlated to another worksheet, so I can't paste the values

I've tryied two ways

this first one I receive an erro message: Exception: The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues.

//destination = sheet.getRange(1,colA[j],table.length,1)
//destination.setValues(table); // where we paste the values by column 

For this one, it paste on other tab on the first columns

//sheet.getRange(1,colA[j],table.length,1).copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

Here is the script that Im using

function PasteValues() {
  var ss =  SpreadsheetApp.openById("...");
  var sheet = ss.getSheetByName("Testsheet");
  var rows = sheet.getDataRange().getValues();
  var dates = rows[2];
  //Logger.log(dates)
  var yesterday = new Date(Date.now() - 864e5);
  var numbers = [];
  
  
  for(var i = 2; i < dates.length; i++) {
    let columns = i
    if (dates[i]!==""  &&  dates[i] !== null){
      numbers.push(columns);
    }
    
    if (dates[i]==="") {
    continue;
    }
    if (dates[i].getDate() == yesterday.getDate() && dates[i].getMonth() == yesterday.getMonth() ){
      break;
    }
  }
  colA=numbers.slice(-5)


  var table = [];
  Logger.log(rows.length)
  Logger.log(colA)
  for(var j=0;j<colA.length;j++)
  { 
    table =[];
    for (var i = 0; i < rows.length;i++ )
    { 
     table[i] = rows[i][colA[j]];
    }
    Logger.log("the number of the column is: "+colA[j]);
    Logger.log(table);
    // where I paste the data

  }
  
}

This is the example on how my data is to copy/paste it based on the column number

Answer 1

When you retrieve values from the spreadsheet, getValues() already returns them to you in a 2-D array - there is no need to manually transfer them into another array

You can either do:

var table = sheet.getDataRange().getValues();
destination = sheet.getRange(1,statColumn,table.length,table[0].length);
destination.setValues(table);

Or:

sheet.getDataRange().copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

UPDATE

Exception: The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues.

means that you are trying to assign a row (1-D array) to a range (2-D array).

Also, table.length will retrieve you the number of columns and not rows if table is a row.

This can be easily solved by defining: table = [table];

Sample snippet:

  for(var j=0;j<colA.length;j++)
  { 
    table =[];
    for (var i = 0; i < rows.length;i++ )
    { 
      table[i] = rows[i][colA[j]];
    }
    Logger.log("the number of the column is: "+colA[j]);
    table = [table];
    Logger.log(table);
    // where I paste the data
    destination = sheet.getRange(1,colA[j],table.length,1)
    destination.setValues([table]); // where we paste the values by column 
  }

UPDATE

If what you want is to copy paste selected data column by column, you need to create a 2D array table and populate it as following:

  for(var i = 2; i < dates.length; i++) {
    let columns = i
    if (dates[i]!==""  &&  dates[i] !== null){
      numbers.push(columns);
    }
    
    if (dates[i]==="") {
      continue;
    }
    if (dates[i] instanceof Date && dates[i].getDate() == yesterday.getDate() && dates[i].getMonth() == yesterday.getMonth() ){
      break;
    }
  }
  colA=numbers.slice(-5)
  
  
  var table = [];
  Logger.log(rows.length)
  Logger.log(colA)
  for(var j=0;j<colA.length;j++)
  { 
    
    for (var i = 0; i < rows.length;i++ )
    { 
      table[i] =[];
      table[i][0] = rows[i][colA[j]];
    }
    Logger.log("the number of the column is: "+colA[j]);
    Logger.log(table);
    // where I paste the data
    destination = sheet.getRange(1,colA[j],table.length,1)
    destination.setValues(table); // where we paste the values by column 
  }

It is important to make sure that the array is 2-dimensional and that its dimensions (rows and columns) correspond to the dimensions of the range into which you want to set the data.