Add a single cell's value to the first row of array being written via google script

Question

Goal:
Use Google Script to write data from one sheet to another "database" sheet.

Current Status:
When the "Form" tab is filled out and the green button on the clicked, a script is triggered that writes data from "Form" to either the "Change Order DB" tab or the "Line Item DB" tab. Writing data to the Change Order DB tab works as desired, no issues. Writing data to "Line Item DB" tab fails.

Issue Encountered:
Rows 10-50 on the "Form" tab should be written to the "Line Item DB" tab when the script runs. I would like to add the ID# found on Form Tab B7 to be added to each line item of the array being written. I've attached a screenshot that shows the desired results. The script should write data to the Line Item DB tab as shown in the screenshot. Column A is the change order # (which is from Cell B7 on Form Tab and then each line is the array from rows 10-50.

Current Script:

function SubmitChangeOrder() {
         var sheet = SpreadsheetApp.getActiveSpreadsheet();  
         var sheet = SpreadsheetApp.getActiveSpreadsheet();
         var form = sheet.getSheetByName('Form');
         var codb = sheet.getSheetByName('Change Order DB');
         var lidb = sheet.getSheetByName('Line Item DB');

         var lirows = form.getLastRow();
         var lineitems = form.getRange(10,1,lirows,3);
         var lidestination = lidb.getRange(lidb.getLastRow()+1,2,lirows,3);
         var sourcedata = lineitems.getValues();

         var date = form.getRange('E7').getValue();
         var customer = form.getRange('B3').getValue();
         var re = form.getRange('B5').getValue();
         var coid = form.getRange('B7').getValue();
         var total = form.getRange('G5').getValue();
         var status = form.getRange('G3').getValue();
         var codestination = codb.getRange(codb.getLastRow()+1,1,1,6);


         codestination.setValues([ [coid, date, customer, re, total, status] ]);
         lidestination.setValues(sourcedata);

}

Script Issue:
The last part of the code, lidestination.setValues(sourcedata); writes the data from rows 10-50 correctly, I would just like to add the ID# to each row that is being written.

I have a google sheet with a few tabs. link to the example

Answer 1

You need to loop through the sourcedata array add coid to the beginning of each nested array. There are multiple ways to do this, but I chose to create a new array using concat(). (Given the variable name, I would assume that it should not be modified as it would otherwise not be a "source".)

I made some other small tweaks to your code, which you can see in the comments. Most importantly, the original code was mixing getLastRow() with the numRows parameter needed for getRange().

function SubmitChangeOrder() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var form = sheet.getSheetByName('Form');
  var codb = sheet.getSheetByName('Change Order DB');
  var lidb = sheet.getSheetByName('Line Item DB');

  const li_start_row = 10; // Use a variable for clarity
  var numRows = form.getLastRow() - li_start_row + 1; // Calculate the number of line item rows
  var lineitems = form.getRange(li_start_row,1,numRows,3); // Select number of rows, not the row limit
  var lidestination = lidb.getRange(lidb.getLastRow()+1,1,numRows,4); // 4 columns, because adding Change Order #
  var sourcedata = lineitems.getValues();

  var date = form.getRange('E7').getValue();
  var customer = form.getRange('B3').getValue();
  var re = form.getRange('B5').getValue();
  var coid = form.getRange('B7').getValue();
  var total = form.getRange('G5').getValue();
  var status = form.getRange('G3').getValue();
  var codestination = codb.getRange(codb.getLastRow()+1,1,1,6);

  // Create a new table with sourcedata, but with the Change Order #
  // added to the beginning of each row
  var li_db_values = [];
  for (var i = 0; i < sourcedata.length; i++) {
    li_db_values.push([coid].concat(sourcedata[i]));
  }

  codestination.setValues([ [coid, date, customer, re, total, status] ]);
  lidestination.setValues(li_db_values);
}

There is a bit more you can do to clean up your code and improve performance. If you have time, I highly recommend checking out the best practices, specifically the batch operations section.