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]);
}

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]);
}

• google-sheets
• google-apps-script
• copy
• range

• I think you have used the wrong method of getValue() instead of getValues() if you are pulling multiple data on sheets. – Lime Husky Commented Feb 14 at 18:46
• Hi, thank you. i tried values instead of value but that didn't work. – Vicki Allan Commented Feb 14 at 18:52
• 1 Kindly share a minimal, reproducible example—no sensitive data with your desired output—that the community can use to replicate and see what you'd like to do. You may use Tables or tools like Table to Markdown that can easily be copied and pasted. If necessary, as a last resort, Make an anonymous sample document. – Saddles Commented Feb 14 at 18:53
• 1 The link is an example of the whole thing. It will vary (quantity of lines/rows) depending on how many stock items they request a claim on. – Vicki Allan Commented Feb 14 at 19:13
• 1 Edit the question instead of posting the update as a comment so that crucial information won't be left out. If the posted answer also helped you out, consider accepting it by clicking the checkmark on it's left. – Saddles Commented Feb 14 at 19:39

1 Answer 1

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:

• getValues()
• appendRow()
• forEach()