I’m pretty good with regular sheet formulas but scripting is new to me. I found some code online and tried to modify it but I’m having trouble.
What I want to do is simple. When I enter text in column A of my main sheet, I need it to automatically copy to column A of two different sheets. The text should go to the next empty row in each sheet. Most importantly, I need to prevent duplicates from being added.
Here’s what I’ve been working with:
function onEdit(e) {
var workbook = SpreadsheetApp.getActiveSpreadsheet();
var currentSheet = e.source.getActiveSheet();
var editedRange = e.source.getActiveRange();
if(currentSheet.getName() == "MainData" && editedRange.getColumn() == 26 && editedRange.getValue() == "x") {
var rowNumber = editedRange.getRow();
var totalColumns = currentSheet.getLastColumn();
var firstTarget = workbook.getSheetByName("DataList1");
var destination1 = firstTarget.getRange(firstTarget.getLastRow() + 1, 1);
currentSheet.getRange(rowNumber, 1, 1, totalColumns).copyTo(destination1);
var secondTarget = workbook.getSheetByName("DataList2");
var destination2 = secondTarget.getRange(secondTarget.getLastRow() + 1, 1);
currentSheet.getRange(rowNumber, 1, 1, totalColumns).copyTo(destination2);
currentSheet.deleteRow(rowNumber);
}
}
The problem is this script behaves inconsistently. Sometimes it copies to both sheets, sometimes only one, sometimes it creates duplicates, and sometimes it just deletes the original without copying anywhere.
Also, I don’t like having to put “x” in column Z to trigger the move. It copies that column too which will cause issues later.
The script doesn’t check for duplicates either. I tried using Google Sheets data validation but it doesn’t work well with scripts.
I work as a pet groomer so coding isn’t really my thing. I just enjoy working with spreadsheets as a hobby. Any help would be appreciated!
onEdit triggers break constantly when you mess with sheet structure while they’re running. Don’t delete rows during execution - it causes bizarre bugs. Use a status column instead. Mark rows as “processed” rather than deleting them. For duplicates, loop through your target sheet first before copying anything over.
Your script’s got timing and logic problems - I ran into the same issues building similar automation. The main culprit is onEdit triggers interfering with each other when multiple operations fire at once, especially while copying and deleting in the same function. I’d scrap this approach entirely. Skip column Z as a trigger and watch column A directly. When new text shows up, check if it exists in your target sheets before copying. Just grab all column A values from both destination sheets with getValues() and compare your new entry against those arrays. That deleteRow() operation is probably what’s causing your inconsistent behavior - it runs before copying finishes. Apps Script doesn’t always process operations in order. Learned this the hard way when my data kept vanishing randomly. For duplicates, write a simple function that searches existing data in both target sheets. Only copy if the value doesn’t exist in either spot. Way more reliable than depending on data validation rules.
The inconsistent behavior could stem from the onEdit trigger firing multiple times or conflicting with other operations. I faced similar issues when I began using Apps Script.
Consider avoiding onEdit; it can be unreliable. A manual trigger, like a button or menu item, might give you better control over execution.
To prevent duplicates, first check the target sheets for existing data before copying. Use getValues() to collect all entries from column A in each target sheet, and then compare with your new entry using indexOf() or a loop.
To address your trigger column issue, adjust the range to skip column Z: use getRange(rowNumber, 1, 1, totalColumns - 1). This way, you won’t copy unwanted data.
As for deleteRow(), it could introduce timing problems. Inserting Utilities.sleep(100) before deletion might ensure the copying process completes properly. Instead of deleting right away, consider marking processed rows.