Apps Script for Google Sheets: setValues parameters-CodePudding

I've been writing a script to copy some data from an input sheet to a database to keep track of some data. I've managed to successfully write the code for linear arrays (only one row) but when I try to copy an entire 15x15 cells range I get an error stating the parameters are not correct (suggesting the dimension of the arrays are not correct) but I can't seem to understand why.

I tried both copying directly the entire 15x15 range and creating a for loop to copy row by row 15 times but I can't mangage to make it work.

Here is the main structure:

  // active spreadsheet and source   target sheets
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = activeSheet.getSheetByName('Data Entry');
  const dstTOTSheet = activeSheet.getSheetByName('DataBaseTOT');

  var firstEmptyRowTOT = dstTOTSheet.getLastRow() 1;

For loop test:

  for (var i=0; i=14;i=i 1) {
// source cells
    var RoundInfo = srcSheet.getRange(10 i, 2, 1, 15); // 15x15 B10:P24
// target cells
    var dstTOTRoundInfo = dstTOTSheet.getRange(firstEmptyRowTOT   i, 21, 1, 15); // I am starting from column 21 because of some other data 
    // set value
    dstTOTRoundInfo.setValues(RoundInfo);
  }

Direct 15x15 test:

// source cells
var RoundInfo = srcSheet.getRange("B10:P24"); // 15x15 B10:P24
// target cells
var dstTOTRoundInfo = dstTOTSheet.getRange(firstEmptyRowTOT, 21, 15, 15);
// set value
dstTOTRoundInfo.setValues(RoundInfo);

CodePudding user response：

The error you are receiving is because the condition in the for loop is incorrect. In the condition, you are using the assignment operator = instead of the comparison operator ==. This causes the condition to always evaluate to true, and the loop will run indefinitely. You should change the condition to i < 15 or i <= 14 to fix the error.

Here is an example of the for loop with the correct condition:

for (var i=0; i < 15; i  ) {
// source cells
var RoundInfo = srcSheet.getRange(10 i, 2, 1, 15); // 15x15 B10:P24

// target cells
var dstTOTRoundInfo = dstTOTSheet.getRange(firstEmptyRowTOT   i, 21, 1, 15); // I am starting from column 21 because of some other data
// set value
dstTOTRoundInfo.setValues(RoundInfo);
}

Alternatively, you can use the setValues() method to copy the entire range in one call, like this:

// source cells
var RoundInfo = srcSheet.getRange("B10:P24").getValues(); // 15x15 B10:P24
// target cells
var dstTOTRoundInfo = dstTOTSheet.getRange(firstEmptyRowTOT, 21, 15, 15);
// set value
dstTOTRoundInfo.setValues(RoundInfo);

Note that when using setValues(), the input range must be a two-dimensional array, which is why we are using getValues() to convert the range to an array. Also, the target range must have the same dimensions as the input array, which is why we are specifying the dimensions of the range explicitly.

CodePudding user response：

It is actually not that difficult to understand the 2D array concept for spreadsheet, it works like this:

// inner arrary is always one row of values:
// the 1st value (array index: 0)  of the inner-array is always the cell value of the 1st column of the given row.
const row_1 = ['row_value_1-1','row_value_2-1','row_value_3-1','row_value_4-1'];
const row_2 = ['row_value_2-1','row_value_2-2','row_value_3-2','row_value_4-2'];
const row_3 = ['row_value_3-1','row_value_2-3','row_value_3-3','row_value_4-3'];

// the outter-array is always the container of every rows you have in range,
// the leangth of rows (which is the leangth of each inner-array) must be the same for this structure to work in spreadsheet.
const data = [
  row_1, // the 1st value (array index: 0) of the outter-array is always the 1st row,
  row_2, // the 2nd value (array index: 1) of the outter-array is always the 2nd row, etc.
  row_3
  ];

// when you loop over such kind of 2D array, it is easier to understand with a "for... of" loop than the classic "for" loop:
for (const row of data) { // << this means "for each row of the given data"
  for (const col of row) { // << this means "for each column of the given row"
    // this structure will go through each cell of row 1, than each cell of row 2, etc., until all rows of the given data are iterated.
    console.log(col);
  }
}
/**output:
row_value_1-1
row_value_2-1
row_value_3-1
row_value_4-1
row_value_2-1
row_value_2-2
row_value_3-2
row_value_4-2
row_value_3-1
row_value_2-3
row_value_3-3
row_value_4-3
*/

// and if you need to works with the indexes of some cell, you can get the index by this:
for (const [rIndex,row] of data.entries()) {
  for (const [cIndex,col] of row.entries()) {
    console.log(rIndex,cIndex,col);
  }
}
/** output:
0 0 row_value_1-1
0 1 row_value_2-1
0 2 row_value_3-1
0 3 row_value_4-1
1 0 row_value_2-1
1 1 row_value_2-2
1 2 row_value_3-2
1 3 row_value_4-2
2 0 row_value_3-1
2 1 row_value_2-3
2 2 row_value_3-3
2 3 row_value_4-3
*/