0
votes

This script copies and pastes a block of data in SHEET1 to the last row + 1 in SHEET2.

I want to add a loop so that for each row of data in SHEET 1, I can specify the destination row in SHEET2 instead of block pasting to the last row + 1.

E.g., I have 5 rows of data in SHEET1 in RANGE A1:E6.

  • Column 1: Name
  • Column 2: Case #
  • Col 3: Description
  • Col 4: Date
  • Col 5: Value
  • Col 6: Destination Row in Sheet2

Thus, if the destination row is designated as 50, the row will overwrite the data at Row 50 in Sheet2.

The next row of data specifies destination Row 73 so that row of data is deposited at Row 73 in Sheet2, etc...

function dataTransfer1() {
  var ss = SpreadsheetApp.getActive(); //source sheet/this sheet
  var tss = SpreadsheetApp.openById("oweoeIE8373JDJudjd8383Iwejfi83"); //destination sheet ID

  var sh = ss.getSheetByName("ADD MISS 7") //name of subsheet w/in source sheet
  var tsh = tss.getSheetByName("JR") //name of subsheet w/in destination sheet
  var rowDestination = tsh.getLastRow()+1;
  var numRows = ss.getRange("A1").getValues();

  var copyRange = sh.getRange(2,14,numRows,5).getValues(); //source range
  var dumpRange = tsh.getRange(rowDestination,1,numRows,5); //destination range
   
 
  
  dumpRange.setValues(copyRange);




}
2

2 Answers

1
votes
function dataTransfer1() {
  var ss = SpreadsheetApp.getActive(); //source sheet/this sheet
  var tss = SpreadsheetApp.openById("oweoeIE8373JDJudjd8383Iwejfi83"); //destination sheet ID

  var sh = ss.getSheetByName("ADD MISS 7") //name of subsheet w/in source sheet
  var tsh = tss.getSheetByName("JR") //name of subsheet w/in destination sheet

  var values = sh.getDataRange().getValues();
  var trange = tsh.getRange(1, 14, tsh.getLastRow(), 5);
  var tvalues = trange.getValues();

  for (const value of values) {
    const i = value[5] - 1;
    tvalues[i] = value.slice(0, -1);
  }
  trange.setValues(tvalues);
}
0
votes
function copy2() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName('Sheet1');
  const sh2 = ss.getSheetByName('Sheet2');
  const vs1 = sh1.getRange(1, 1, 6, 6).getValues();
  vs1.forEach(r => sh2.getRange(r[5], 1, 1, 5).setValues([r.slice(0, 5)]));
}