Search code examples
google-sheetsgoogle-apps-scriptcopyrange

My function is only copying the first line. I would like it to copy all line within a range. E.g ("I7:I24")

I am using the function copyData below within another script and it works great in the original script but for another one it is only copying the first line. I would like it to copy all line within a range. E.g ("I7:I24"). There are multiple ranges.

I believe I am missing a quick solution but cannot see it. I have looked through various similar questions. Thank you in advance for any assistance.

The code is below:

function copyData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Monthly Commission Claim");
  var destSheet = ss.getSheetByName("Vans Claimed On");

  var dateClaimed  = sourceSheet.getRange("I7:I24").getValue();
  var salesAdvisor = sourceSheet.getRange("H7:H24").getValue();
  var grossProfit = sourceSheet.getRange("D7:D24").getValue();
  var stockNumber = sourceSheet.getRange("A7:A24").getValue();
  var model = sourceSheet.getRange("B7:B24").getValue();
  var year = sourceSheet.getRange("C7:C27").getValue();
  var amount = sourceSheet.getRange("L7:L24").getValue();

  destSheet.appendRow([dateClaimed, salesAdvisor, grossProfit, stockNumber, model, year, amount]);
}
Solution

  • Ensure Array Output Dimension is in 2D

    As was discussed in the comments section, you should change all getValue() methods into getValues() because you are trying to get multiple data from multiple cells. This will result to a 2D array which will invalidate the line:

    destSheet.appendRow([dateClaimed, salesAdvisor, grossProfit, stockNumber, model, year, amount]);

    It is because the appendRow method only accepts 1D arrays. Thus, you must distribute the appendRow method per row of the processed data by using a for loop (in this case I used forEach). The new script should look like this:

    function copyData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Monthly Commission Claim");
  var destSheet = ss.getSheetByName("Vans Claimed On");

  var dateClaimed = sourceSheet.getRange("I7:I24").getValues();
  var salesAdvisor = sourceSheet.getRange("H7:H24").getValues();
  var grossProfit = sourceSheet.getRange("D7:D24").getValues();
  var stockNumber = sourceSheet.getRange("A7:A24").getValues();
  var model = sourceSheet.getRange("B7:B24").getValues();
  var year = sourceSheet.getRange("C7:C27").getValues();
  var amount = sourceSheet.getRange("L7:L24").getValues();

  var out = [];
  dateClaimed.forEach((x, i) => out.push([dateClaimed[i][0], salesAdvisor[i][0], grossProfit[i][0], stockNumber[i][0], model[i][0], year[i][0], amount[i][0]]));
  out.forEach(y => destSheet.appendRow(y));
}

    References: