Apps Script novice here, so any help/direction you could provide would be greatly appreciated!
I have an existing Apps Script within Google Sheets that was iterating over lines, and pushing each line individually to a new sheet. I'm trying to modify it to grab the entire array, and push it all at once to make it more efficient now that it's getting utilized more.
Here's an example of the data I'm trying to work with:

With this much code, I can successfully filter to only rows that have values, iterate over them and make sure that all required values are filled out.
function userSubmitTest() {
var ss = SpreadsheetApp.openById('1ozOeBPLzouZTEm0SECW0JZwfxHMcr3dr-augiTra6tY');
var dataSheet = ss.getSheetByName('Data Entry');
var downloadSheet = ss.getSheetByName('ExportForDownload');
var historicalSheet = ss.getSheetByName('ExportHistorical');
var spreadsheetTimeZone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
var submittedDate = Utilities.formatDate(new Date(), spreadsheetTimeZone, "MM/dd/yyyy' 'HH:mm:ss");
// Data Sheet Details
var downloadNumColumns = downloadSheet.getLastColumn();
var downloadIndex = downloadSheet.getLastRow();
//Historical Sheet Details
var historicalNumColumns = historicalSheet.getLastColumn();
var historicalIndex = historicalSheet.getLastRow();
// Initial Sheet Values
var newLastColumn = dataSheet.getLastColumn();
var newLastRow = dataSheet.getLastRow();
var dataToProcess = dataSheet.getRange(2, 1, newLastRow - 1, newLastColumn).getValues().filter(function(row) {
return row[0]
})
// Get Spreadsheet UI
var ui = SpreadsheetApp.getUi();
var dLen = dataToProcess.length;
var row = 2;
// Loop through processing lines to make sure A:D are filled out for each row
for (var i = 0; i < dLen; ++i) {
var bolCheck = dataToProcess[i][0];
var sapCheck = dataToProcess[i][1];
var notesCheck = dataToProcess[i][2];
var reasonCheck = dataToProcess[i][3];
if (bolCheck == "") { // Check all processing lines to make sure delivery docs are entered
var noBOL = ui.alert(
'Warning!',
'Please ensure all lines have a Delivery Document number in A2:A, or clear them, before submitting.',
ui.ButtonSet.OK);
return
} else if (sapCheck == "") { // Check all processing lines to make sure order numbers are entered
var noSAP = ui.alert(
'Warning!',
'Please ensure all lines have a SAP/ECC Order Number number in B2:B, or clear them, before submitting.',
ui.ButtonSet.OK);
return
} else if (notesCheck == "") { // Check all processing lines to make sure notes are entered
var noNotes = ui.alert(
'Warning!',
'Please ensure all lines have notes listed in C2:C, or clear them before submitting.',
ui.ButtonSet.OK);
return
} else if (reasonCheck == "") { // Check all processing lines to make sure reasons are entered
var noNotes = ui.alert(
'Warning!',
'Please ensure all lines have a reason selected in D2:D, or clear them before submitting.',
ui.ButtonSet.OK);
return
}
}
I can tell that much is working, because in debug, I can see the following 2D array:

However, I need to take that array, and insert some values between the ordinal position, and add some new ones to the end for our download process. When iterating over the rows, it was pretty easy because I would basically say "for this row, what's this value?", and then create a new row "array" of all the values I want to push. However, how could I get all of the values in column A:A into a single row, and start building a new array based off those ordinal positions and some other text/dates that I'm trying to insert? Once the array is built, set the values all at the same time, rather than iterate line by line.
This is what I am currently using when iterating over the lines:
for (var j = 0; j < dLen; ++j) {
var bolCheck = dataToProcess[j][0];
var sapCheck = dataToProcess[j][1];
var notesCheck = dataToProcess[j][2];
var reasonCheck = dataToProcess[j][3];
var rdd = dataToProcess[j][4];
if (rdd) {
rdd = Utilities.formatDate(rdd, spreadsheetTimeZone, "MM/dd/yyyy");
}
var submittedBy = dataToProcess[j][5].toString();
var submittedOn = dataToProcess[j][6];
if (submittedOn) {
submittedOn = Utilities.formatDate(submittedOn, spreadsheetTimeZone, "MM/dd/yyyy' 'HH:mm:ss");
}
var array = [];
array.push(bolCheck, "Company Submitted", sapCheck, reasonCheck, rdd, "Company Order Desk", "1-800-999-9999", "", notesCheck, submittedBy, submittedBy, submittedOn, submittedDate, "Company Submitted " + submittedDate, "Company Submitted " + submittedDate, submittedDate);
downloadSheet.insertRowsAfter(downloadIndex, 1).getRange(downloadIndex + 1, 1, 1, historicalNumColumns).setValues([array]);
historicalSheet.insertRowsAfter(historicalIndex, 1).getRange(historicalIndex + 1, 1, 1, historicalNumColumns).setValues([array]);
If any of this is unclear, definitely let me know so I can add more context. The Google Sheet is editable by anyone with the link, so you should be ablet o see what I was trying to attempt.
Thanks for looking!