function myFunction() {
  var source = SpreadsheetApp.getActive().getSheetByName('Mechanic Sheet #1');  
  var target = SpreadsheetApp.getActive().getSheetByName('Fox');
  target.activate();                                                             
  
  var i = 19;
  var range = target.getRange("B19:B500");
  var data = range.getValues();
 

  for (var i =19; i < data.length; i  ){

    if(!data[i][0]){

      var sourceWorkDone = source.getRange("L10:S10").getValues(); 
      var sourceLicensePlate = source.getRange("L13:S13").getValues(); 
      var sourceCompanyProfit = source.getRange("I10").getValues(); 
      var sourceMechanicFee = source.getRange("I9").getValues();

      var targetWorkDone = target.getRange("B"  i ); 
      var targetLicensePlate = target.getRange("C"   i); 
      var targetCompanyProfit = target.getRange("D"   i); 
      var targetMechanicFee = target.getRange("E"   i);

      targetWorkDone.setValue(sourceWorkDone);
      targetLicensePlate.setValue(sourceLicensePlate);
      targetCompanyProfit.setValue(sourceCompanyProfit);
      targetMechanicFee.setValue(sourceMechanicFee); 

      break;
      
    }
  }
} 

I have 'Mechanic's Sheet #1' and another sheet called 'Fox' one is the prices and work orders and the second is like a receipt to keep track of work and employee pay.

I need to send the values of L10:S10 of Mechanic's Sheet #1 to Fox B19 I need to send the values of L13:S13 of Mechanic's Sheet #1 to Fox C19 I need to send the values of I10 of Mechanic's Sheet #1 to Fox D19 I need to send the values of I9 of Mechanic's Sheet #1 to Fox E19

but I need to check if B19 is empty if its not empty and contains an already submitted order, i need to send all of data to the next iteration which would be B20,C20,D20,E20.

but i still need it check I need it to check if B19 is empty until it finds an empty spot to plug in the info from the mechanics sheet #1.

It is populating the new sheet with the correct info in slot B19 but it isn't populating B20 with anything and so on. if i change i it will populate the proper spot in the sheet. I can't tell if its the loop or what. First time using this language and would like to see where my mistake is.

Images of First Sheet https://i.imgur.com/XAjujAl.png

Images of Second sheet where i need info to loop. https://i.imgur.com/TP8fWE8.png

Copy of Document.

https://docs.google.com/spreadsheets/d/1Zol7OFwNvkncQfaDSCACFfHZ0ndyb7lBGwtDzjGT2zE/edit?usp=sharing

CodePudding user response：

Based on your problem statement

Try this sample script:-

function pasteValues() {   
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssSource = ss.getSheetByName('Mechanic Sheet #1')
  const ssTarget = ss.getSheetByName('Fox')
  
  const sourceWorkDone = ssSource.getRange("L10").getValue(); 
  const sourceLicensePlate = ssSource.getRange("L13").getValue(); 
  const sourceCompanyProfit = ssSource.getRange("I10").getValue(); 
  const sourceMechanicFee = ssSource.getRange("I9").getValue();
  
  const checkEmptyRow = ssTarget.getRange('B18:B').getValues().flat().filter(r=> r).length
  
  const emptyRowRange = ssTarget.getRange(`B${checkEmptyRow 18}`) // get Empty Row adding 18 as your header starts from it
  
  emptyRowRange.setValue(sourceWorkDone)
  emptyRowRange.offset(0, 1).setValue(sourceLicensePlate)
  emptyRowRange.offset(0, 2).setValue(sourceCompanyProfit)
  emptyRowRange.offset(0, 3).setValue(sourceMechanicFee)
  
}

• You don't have to use L10:S10 and so on if cells are merge

• Also you don't have to loop through data, just get range and filter the non empty range and get the length

Reference

offset