How to append unappended rows of data from a source sheet to a target sheet, then mark as APPENDED to prevent duplicate appending

Question

I am trying to write an Apps Script that when run (either from a trigger or a custom menu) it will:

• append source data from the NEWDATA sheet to the ALLDATA sheet
• after a row of source data is added to the ALLDATA sheet, then mark that row as APPENDED and continue to the next row (this is failsafe in visual marker of script success for that row)
• skip any rows that have already been marked as APPENDED

My current issues are:

• for each row of source data that is not yet appended, it causes that many copies of the source data range before marking as appended, so if there are 10 rows in total and 4 are appended and 6 are not, then i will get 60 rows (10 x 6) added to the ALLDATA sheet, and if all 10 rows are not yet appended then i will get 100 new rows (10 x 10).
• when there is 1 row not appended out of 10 source data rows (9 already marked appended) then 9 rows will be marked with the APPENDED flag in column F. when i try to reduce the size of the array to ignore that column i get an error.

NEWDATA (source data)

ALLDATA (target data)

So, i need to know how to stop the script from running by the number of rows that are not appended, and to not include the "APPENDED" column (F), and to only mark each row after they have been appended.

Here is the current script:

function appendToRecords() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
/** set the name of the SOURCE_SHEET sheet */
  var sourceSheet = ss.getSheetByName("NEWDATA");
  var startRow = 2; // First row of data to process
  var numRows = sourceSheet.getLastRow()-1; // Number of rows to process
  var dataRange = sourceSheet.getRange(startRow, 1, numRows, sourceSheet.getLastColumn()); // Fetch the range of cells being used
  var sourceData = dataRange.getValues(); // Fetch values for each row in the Range.
  var APPENDED = 'APPENDED';
  for (var i = 0; i < sourceData.length; ++i) {
    var row = sourceData[i];
    if (row[5] != APPENDED) { // To prevent sending duplicates, check that not already APPENDED
/** set the name of the TARGET_SHEET */
  var targetSheet = ss.getSheetByName("ALLDATA")
/** Append NEWDATA to ALLDATA */
  var lastRow = targetSheet.getLastRow();
  targetSheet.getRange(lastRow + 1, 1, 10, 6)
              .setValues(sourceData);
  sourceSheet.getRange(startRow+i,6).setValue("APPENDED"); // Add APPENDED indication to end of row
    }}}

And here is link to file:

https://docs.google.com/spreadsheets/d/1gOgvvZlCQgZi837sapZ3S8M8m6I98VFnGQwPiTCSY-I/edit?usp=sharing

Any help will be appreciated, thank you.

Answer 1

Looking through your code, the biggest issue I see is when you're setting values on target sheet, you're doing sourcedata instead of row. I cleaned up your code by isolating some constants and making a little more dynamic.. Ultimately you should try to use an array to set values at end of procedure, rather than using line by line updates, but for the sake of answering your question, try this:

/** @OnlyCurrentDoc*/
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = ss.getSheetByName("NEWDATA");
const targetSheet = ss.getSheetByName("ALLDATA");
const startRow = 2; // First row of data to process
const APPENDED = 'APPENDED';

function appendToRecords() {
  const numRows = sourceSheet.getLastRow() - 1; // Number of rows to process
  const dataRange = sourceSheet.getRange(startRow, 1, numRows, sourceSheet.getLastColumn()); // Fetch the range of cells being used
  const sourceData = dataRange.getValues(); // Fetch values for each row in the Range.
  const lastColumn = sourceData[0].length;

  for (var i = 0; i < sourceData.length; ++i) {
    var row = sourceData[i];
    if (row[lastColumn-1] != APPENDED) {
      ///you should not do this line by line, but all at oncw with an array
      row[lastColumn-1] = APPENDED;
      var lastRow = targetSheet.getLastRow();
      targetSheet.getRange(lastRow + 1, 1, 1, row.length).setValues([row]);
      sourceSheet.getRange(startRow + i, lastColumn).setValue("APPENDED");
    }
  }
}