Loop through each row on one sheet coping specific cells of each row to a second sheet until an empty row(last row) is found

Question

I'm new to sheets script. I want to Loop through each row on one sheet (Driver List) while copying specific cells of each row to a second sheet (Driver List2) until an empty row (last row) is found. I'm able to do one row at a time and just copy the code for the next row but this is clearly not the right way to loop through data. Any help would be greatly appreciated. This is what I have now:

function copycells() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A4').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver list2'), true);
  spreadsheet.getRange('A4').activate();
  spreadsheet.getRange('\'Driver List \'!A4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver List '), true);
  spreadsheet.getRange('B4').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver list2'), true);
  spreadsheet.getRange('B4').activate();
  spreadsheet.getRange('\'Driver List \'!B4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver List '), true);
  spreadsheet.getRange('C4').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver list2'), true);
  spreadsheet.getRange('C4').activate();
  spreadsheet.getRange('\'Driver List \'!C4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver List '), true);
  spreadsheet.getRange('M4').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver list2'), true);
  spreadsheet.getRange('M4').activate();
  spreadsheet.getRange('\'Driver List \'!M4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

Answer 1

You want to loop through the data on a given sheet ("Driver List") and copy the data values from columns A, B, C and M to a second sheet ("Driver List2").

There are many precedents for this but you have attempted to write the code (which appears to constructed from a series of macros), and hopefully this answer will assist you to better understand the process involved.

Logic

• var ss = SpreadsheetApp.getActiveSpreadsheet(): enables you to define the spreadsheet once, and then use the variable ss anywhere later in the script
• similarly, the next four lines enable you to define the names of the respective sheets, and to define the sheets using getSheetByName().Again, these variables can be used anywhere later in the script.
• var list1data = list1.getDataRange().getValues();: getDataRange is a shortcut to get define the range for ALL of the data on a sheet.
• for (i=0;i<list1data.length;i++){: for is the basis of the loop that you use to step through the data. There are several other options for loops- there is a link at the bottom of this answer
• i starts with a value of "0" and increments by one each time through the loop.
• on each of the next four lines a value similar to list1data[i][0] is used. Let's break this down:
  • list1data- this is the name of the data array obtained with `getValues. Note that this is a 2 dimensional array (Rows AND Columns) that it is zero-based. So...
  • list1data[i][0] - if i=0, then this is a value for Row 1,Column A. When/if i=1, then this is Row 2, Column A. The syntax is that the row is the first attribute, and the column is the second attribute and both are surrounded by square brackets []
• list1data[i][1] - these will be values for Column B
• list1data[i][2] - these will be values for Column C
• list1data[i][13] - these will be values for Column M
• each of these lines define a specific range on "Driver List2" and setValue for the value for the row from sheet "Driver List". This is pretty inefficient (setValue is a time-intensive command) but it is the easiest way for you to understand how the value is copied from one sheet to another.

---

function loopandcopy() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet()

  // define the two sheet names
  var driver1 = "Driver List"
  var driver2 = "Driver List2"
  var list1 = ss.getSheetByName(driver1)
  var list2= ss.getSheetByName(driver2)

  // get the data on list1
  var list1data = list1.getDataRange().getValues();

  // loop through the data rows on List1
  for (i=0;i<list1data.length;i++){
    
    // update the value in Columns A, B, C and M 
    list2.getRange(i+1,1).setValue(list1data[i][0])
    list2.getRange(i+1,2).setValue(list1data[i][1])
    list2.getRange(i+1,3).setValue(list1data[i][2])
    list2.getRange(i+1,13).setValue(list1data[i][1])

  }

}

---

REFERENCES

getDataRange

for

Loops and iteration