how to copy a single dimension array to google sheets-CodePudding

using JavaScript to access google sheets via google API.

I am copying the entire content of a "google spreadsheet" "Parent sheet" into a two dimension array vaParent

  var ssParent = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Parent");
 var vaParent = ssParent.getRange(2, 1, ssParent.getLastRow(), ssParent.getLastColumn() ).getValues();

I am extracting 6 array elements

  var vaNewParent01 = vaParent[i].slice(3, 9);

Now, I would like to copy vaNewParent01 to a new google "spreadsheet" "company sheet"

  ssCompany.getRange( 2, 1 ).setValues( vaNewParent01 );

above code does not work??

I've found that vaNewParent01 is a single dimension array with 6 elements

however, ssCompany.getRange( 2, 1 ).setValues( vaNewParent01 only works if vaNewParent01 is an array of array

question: a/ how to extract few cells from a sheet and copy to another using a single statement.

thanks in advance for any help

cheers

CodePudding user response：

arr is single dimension array

let vs = arr.map(e => [e]); //for a column;
let vs = [arr]; //for a row

Sheet.getRange(1,1,vs.length, vs[0].length).setValues(vs);

CodePudding user response：

From your showing script, if your script is like the following script,

var ssParent = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Parent");
var vaParent = ssParent.getRange(2, 1, ssParent.getLastRow(), ssParent.getLastColumn()).getValues();
for (var i = 0; i < vaParent.length; i  ) {
  var vaNewParent01 = vaParent[i].slice(3, 9);
  ssCompany.getRange(2, 1).setValues(vaNewParent01);
}

vaNewParent01 is 1 dimensional array. In order to use setValues(values), values is required to be 2 dimensional array. And about ssCompany.getRange( 2, 1 ).setValues( vaNewParent01 );, in this case, please include the numbers of rows and columns of the values you want to put. If my understanding of your situation is correct, I think that the reason for your issue is due to this.

About how to extract few cells from a sheet and copy to another using a single statement., in this case, how about the following sample script?

Sample script 1:

This script is modified from your showing script by guessing your tested script.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssParent = ss.getSheetByName("Parent");

var ssCompany = ss.getSheetByName("sample"); // Please set the destination sheet name.

var vaParent = ssParent.getRange(2, 1, ssParent.getLastRow(), ssParent.getLastColumn()).getValues();
var values = [];
for (var i = 0; i < vaParent.length; i  ) {
  var vaNewParent01 = vaParent[i].slice(3, 9);
  values.push(vaNewParent01);
}
ssCompany.getRange(2, 1, values.length, values[0].length).setValues(values);

Sample script 2:

As other sample script, how about the following script? In this sample script, copyTo of Class Range is used. From your showing script, I thought that you wanted to copy the values from D2:I of the source sheet to A2 of the destination sheet.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssParent = ss.getSheetByName("Parent");

var ssCompany = ss.getSheetByName("sample"); // Please set the destination sheet name.

var src = ssParent.getRange("D2:I"   ssParent.getLastRow());
src.copyTo(ssCompany.getRange(2, 1), { contentsOnly: true });

Sample script 1:

Sample script 2:

References: