Function is making duplicates of entries every time Function is ran.

9 views
Skip to first unread message

Ryan Weaver

unread,
Oct 3, 2022, 2:52:25 PMOct 3
to Google Apps Script Community
Good Afternoon All,

I have a stock sheet where the items are moved from the stock checkout sheet if the array formula says move. This occurs onopen. But when the function runs sometimes it makes copies of the already moved entries. I have no idea why it is doing this. To test this just delete a row in Carpet or LVP Stock List, then close and reopen the sheet.

Here is the Sheet


Here is the script I have

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var targetSheet = ss.getSheetByName('Past Checkouts'); // destination sheet
  var excludes = ['LVP Stock List','Carpet Stock List','Misc Stock','Data']; // sheets to exclude
  var dataColumns = [1,2,3,4,5,6,7,8]; // columns to copy values from
  sheets.forEach(function(sheet) { // copy values
    if (excludes.indexOf(sheet.getName()) != -1) return; // check sheet is not excluded
    var found = 0;
    sheet
      .getDataRange()
      .getValues()
      .forEach(function(row, i) {
        if (row[8] == 'Move') { // Gets the rows that have the 4th col Move
          row.forEach(function(val, j) {
            if (dataColumns.indexOf(j + 1) != -1) return;
            row[j] = '';
          });
          targetSheet.appendRow(row);
          sheet.deleteRow(i + 1 - found); // delete the source row
          found += 1;
        }
      });
  });
}

Keith Andersen

unread,
Oct 4, 2022, 12:42:49 AMOct 4
to google-apps-sc...@googlegroups.com
After much trial/error and working through glitches etc.....It's finally done.
Please watch this video first. Then make a copy of this sheet (it is view only for security). 
Once you've made the copy, go through permissions as per video.

Any questions - email me.

Video

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c2ab278f-be28-492b-935e-cd0d1249b578n%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages