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

Question

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 think you have used the wrong method of getValue() instead of getValues() if you are pulling multiple data on sheets. — Lime Husky
– Lime Husky, Commented Feb 14 at 18:46
Hi, thank you. i tried values instead of value but that didn't work. — Vicki Allan
– Vicki Allan, Commented Feb 14 at 18:52
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
– Saddles, Commented Feb 14 at 18:53
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
– Vicki Allan, Commented Feb 14 at 19:13
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
– Saddles, Commented Feb 14 at 19:39

PatrickdC · Accepted Answer · 2025-02-14 19:32:13Z

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:

answered Feb 14 at 19:32

PatrickdC

2,5743 gold badges10 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PatrickdC Feb 14 at 19:33

Also, based on your sample spreadsheet, you might want to adjust the range since row 24 is already outside your sample data.

PatrickdC Feb 14 at 19:46

That is good to hear. Feel free to accept my answer by clicking on the check icon.

Collectives™ on Stack Overflow

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

1 Answer 1

Ensure Array Output Dimension is in 2D

References:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Ensure Array Output Dimension is in 2D

References:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related