3

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: enter image description here

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: enter image description here

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!

1 Answer 1

3

I think you have nearly figured it all out. The last thing to do is add your single row array to another "all rows" array, in your last function. Like this:

var allRows = []; //define outside your loop
for (var j = 0; j < dLen; ++j) {
    var bolCheck = dataToProcess[j][0];

    //... same code you already have ...

    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);

    allRows.push(array);
} //close your for loop

downloadSheet
    .insertRowsAfter(downloadIndex, allRows.length)
    .getRange(downloadIndex + 1, 1, allRows.length, historicalNumColumns)
    .setValues(allRows);
historicalSheet
    .insertRowsAfter(historicalIndex, allRows.length)
    .getRange(historicalIndex + 1, 1, allRows.legnth, historicalNumColumns)
    .setValues(allRows);

ps: I haven't tested this since this snippet was not complete

Sign up to request clarification or add additional context in comments.

1 Comment

Sorry for the delay, @Henrique. I was able to take your suggestion and slightly modify it to get it to work. Thanks so much!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.