Google sheets script for copying data and pasting in another sheet on the same row as a specific numer

Question

I have the following data:

1. [Source] Sheet 1 (Quote Generator):

   • Sheet 1, Cell A4: Dropdown menu to select a job reference
   • Sheet 1, Cells K4>Q4:= figures pulled from google maps & calculations dependant on A4 menu selection

2. [Destination] Sheet 2 (Quote Log):

   • Sheet 2, Column B: Quote References
   • Sheet 2, Columns V>AB: Travel data and calculations from sheet 1

I'm after a script that will copy the data in Sheet 1, Cells K4:Q4, and paste them in to Sheet 2, Columns V:AB on the same row as the selected job ref '100001' on Sheet 1, Cell A4, which will also be present in Sheet 2, Column B

Any help would be greatly appreciated TIA

Answer 1

The data range you have specified is large than the range you want to put data in hence I have reduced the source range in the code. Try this.

function copyrow() {
  var ss = SpreadsheetApp.getActive();
  var source = SpreadsheetApp.openById('SheetID');
  var dest = SpreadsheetApp.openById('SheetID');
  var sourcerange = source.getRange('B4:H4');
  var sourcerangevalue = sourcerange.getValues();
  var checkval = source.getRange('A4').getValue();
  var lastrow = dest.getLastRow();
  var destrange = dest.getRange('B1:B');
  var destrangevalue = destrange.getValues();
  for (i = 1; i <= lastrow; i++) {
    if (checkval == destrangevalue[i]) {
      dest.getRange('A15').setValue(destrangevalue[i])
      dest.getRange("V" + i + ":AB" + i).setValues(sourcerangevalue)
    };
  };
};

Answer 2

function myFunction() {
  // get active spreadsheet
  var ss   = SpreadsheetApp.getActiveSpreadsheet();
  
  // get data from Sheet1
  var s    = ss.getSheetByName("Sheet1");
  var cel  = s.getRange("A4").getValue();
  var data = s.getRange("K4:Q4").getValues();

  // get to Sheet2 and get values from column 'B'
  var s    = ss.getSheetByName("Sheet2");
  var col  = s.getRange("B1:B").getValues();

  // find the row number in columnn B that contain the value
  var row  = col.flat().findIndex( c => c == cel ) + 1;

  // put the 'data' into the cells of this row
  if (row) s.getRange("V" + row + ":AB" + row).setValues(data);
}

---

Updated version:

function onEdit(e) {
    if (e.range.getA1Notation() !== "Q6") return;

    var ss   = SpreadsheetApp.getActiveSpreadsheet();
    
    var s    = ss.getSheetByName("Sheet1");
    var cel  = s.getRange("A4").getValue();
    var data = s.getRange("K4:Q4").getValues();

    var s    = ss.getSheetByName("Sheet2");
    var col  = s.getRange("B1:B").getValues();
    var row  = col.findIndex(c => c[0] == cel) + 1;

    if (!row) return;
    s.getRange("V" + row + ":AB" + row).setValues(data);
    
    var s    = ss.getSheetByName("Sheet1");
    ss.getRange("A1").clear();
    ss.getRange("F5").clear();  
}