Exception: The number of columns in the data does not match the number of columns in the range. The-CodePudding

I am very new to javascript and have searched around a ton for this and can't seem to find the issue with my code. I am attempting to simply write a code that will copy the values in a column from a pivot table sheet in Google Sheet and then paste the values in another sheet. However, before pasting the values, I want each individual value to be duplicated 12 times (for 12 months). So, assuming I have 10 unique values (A, B, C, D, E, F, G, H, I, J) that I am copying, I want to return value A 12 times in a row, then value B 12 times in a row, etc.

I run getValues, which seems to put the values in a 2 dimensional array. I've then taken this temp_array that I had created and used a for loop to duplicate each value 12 times in a new array.

However, when I setValues, I am pasting the values in my spreadsheet correctly, but I get this error message regardless (The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 1.), any ideas why?

Here is a small example of what my input could look like (1st image) and what I would want the output to look like (2nd image)

function test2() {
  
// activating current spreadsheet for use
  var spreadsheet = SpreadsheetApp.getActive();
  //empty array
  var array_dept_temp = [];

  // returns cell position (ex: C5) of the last row of the pivot table 1 sheet that has content in column 1
  var last_row = spreadsheet.getSheetByName("Pivot Table 1").getRange("A:A").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRowIndex();
  //subtracting 1 from last row because we are excluding the headers. This gives us our row_length
  var row_length = last_row - 1

  var array_dept = [[]]
  array_dept = new Array(row_length*12)
  
  //new Array(row_length*12);


  // Get value in pivot table 1 from range of row 2 (dept name, but exclude the header), column 1, all the way to last row
  // Then paste it in sheet5 from row 1, column 3, all the way to the last row defined above
  
  array_dept_temp = spreadsheet.getSheetByName("Pivot Table 1").getRange(2,1, last_row).getValues();

  for (var i = 1; i < row_length; i   )
  {
  //get value and then paste it in a destination
  array_dept.fill(array_dept_temp[i-1], (-12   (12*i)) , 12*i);
  }


  var destination_dept = spreadsheet.getSheetByName("Sheet5").getRange(2,3,row_length*12);
  destination_dept.setValues(array_dept);


}

CodePudding user response：

Suggestion / Alternate solution:

Try:

function test() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName("Pivot Table 1");
  var array_dept_temp = sheet.getRange(2,1, sheet.getLastRow()-1).getValues();
  var array_dept = [];

  for (var i = 0; i < array_dept_temp.length; i  ) {
    array_dept = [...array_dept, ...Array.apply(null, Array(12)).map(function(){return array_dept_temp[i]})]
  }

  var destination_dept = spreadsheet.getSheetByName("Sheet5").getRange(2,3,array_dept.length);
  destination_dept.setValues(array_dept);
}

Result:

Another way without using fill or from.

Also some modification, you can just use .getLastRow() function to get the last row, however take not that if there is data below it will count all the rows including the blank until the row that has data. And you may also use .length on your data to setValue.

CodePudding user response：

From your showing sample input and output situations, how about the following modified script?

Modified script:

function test2_sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = ss.getSheetByName("Pivot Table 1");
  var dstSheet = ss.getSheetByName("Sheet5");
  var srcValues = srcSheet.getRange("A2:A"   srcSheet.getLastRow()).getValues();
  var dstValues = srcValues.flatMap(a => Array(12).fill(a));
  dstSheet.getRange(2, 3, dstValues.length).setValues(dstValues);
}

When this script is run using your sample input sheet, I think that your expected output values are obtained.
Now, I thought that var dstValues = srcValues.flatMap(([a]) => Array(12).fill(a).map(e => [e])); can be modified to var dstValues = srcValues.flatMap(a => Array(12).fill(a));. This is simpler.

From your reply of Are you able to explain what this does? var dstValues = srcValues.flatMap(([a]) => Array(12).fill(a).map(e => [e]));, in this script, var dstValues = srcValues.flatMap(([a]) => Array(12).fill(a).map(e => [e])); can be also modified as follows. I thought that this might also help to understand it.

function test2_sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = ss.getSheetByName("Pivot Table 1");
  var dstSheet = ss.getSheetByName("Sheet5");
  var srcValues = srcSheet.getRange("A2:A"   srcSheet.getLastRow()).getValues();

  var dstValues = [];
  for (var i = 0; i < srcValues.length; i  ) {
    dstValues = dstValues.concat(Array(12).fill(srcValues[i]));
  }

  dstSheet.getRange(2, 3, dstValues.length).setValues(dstValues);
}

Note:

As additional information, when your showing script is modified, how about the following modification? In your script, I thought that it is required to add the values to array_dept in the loop. And, it is required to flatten the elements in the array.

function test2() {
  var spreadsheet = SpreadsheetApp.getActive();
  var array_dept_temp = [];
  var last_row = spreadsheet.getSheetByName("Pivot Table 1").getRange("A:A").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRowIndex();
  var row_length = last_row - 1
  var array_dept = []
  array_dept_temp = spreadsheet.getSheetByName("Pivot Table 1").getRange(2, 1, last_row).getValues();
  for (var i = 0; i < row_length; i  ) {
    array_dept = [...array_dept, ...Array(12).fill(array_dept_temp[i])];
  }
  var destination_dept = spreadsheet.getSheetByName("Sheet5").getRange(2, 3, array_dept.length);
  destination_dept.setValues(array_dept);
}

Reference:

flatMap()