Automatically move rows in Google Sheets based on cell value

Other Questions Google Sheets
1

I’m trying to set up an automatic row mover in Google Sheets. I want it to work like this: when a cell in column F says “Cleared”, the whole row should move to another sheet. I used to do this in Excel with VBA, but I’m new to Google Sheets and JavaScript.

I found some code online and tweaked it for my sheet. But it’s not working. The error says it can’t read the “source” property on line 6. I think line 7 might have the same problem.

Here’s the code I’m using:

function moveRowOnEdit(e) {
  const mainSheet = "Tasks";
  const archiveSheet = "Completed Tasks";
  const triggerColumn = 6;
  const triggerValue = "Done";

  let sheet = e.range.getSheet();
  let cell = e.range;

  if (sheet.getName() === mainSheet && cell.getColumn() === triggerColumn && cell.getValue() === triggerValue) {
    let rowNum = cell.getRow();
    let lastCol = sheet.getLastColumn();
    let destSheet = SpreadsheetApp.getActive().getSheetByName(archiveSheet);
    let destRange = destSheet.getRange(destSheet.getLastRow() + 1, 1);
    sheet.getRange(rowNum, 1, 1, lastCol).moveTo(destRange);
    sheet.deleteRow(rowNum);
  }
}

Can anyone help me fix this? Also, I’ve added “Learn JavaScript” to my to-do list!

2

As someone who’s worked extensively with Google Sheets automation, I can see where your script is running into trouble. The error you’re encountering is likely because the script is trying to access properties of ‘e’ that don’t exist when the function runs automatically.

Here’s a tip: Instead of relying on the ‘e’ event object, you can modify your script to check the entire column F for the ‘Done’ value. This approach is more robust and doesn’t depend on edit events.

Also, consider using a time-driven trigger to run your script periodically. This way, it’ll check and move rows even if you’re not actively editing the sheet.

One last thing - while learning JavaScript is great, Google Apps Script (which is based on JavaScript) has some quirks of its own. I’d recommend familiarizing yourself with the Spreadsheet Service documentation specifically. It’ll save you a lot of headaches down the road.

Keep at it! Automating tasks like this can be a real time-saver once you get it working.

3

I’ve faced similar challenges when transitioning from Excel to Google Sheets. The main issue with your current setup is that it’s event-driven, which can be unreliable in Sheets. Instead, consider using a time-triggered function that scans the entire ‘Tasks’ sheet periodically.

Here’s a quick outline of how you could approach this:

  1. Create a function that loops through all rows in column F of ‘Tasks’.
  2. For each ‘Done’ value found, move that entire row to ‘Completed Tasks’.
  3. Set up a time-based trigger to run this function every hour or so.

This method is more robust and doesn’t rely on edit events. It also allows for batch processing, which can be more efficient if you’re dealing with a large number of tasks.

Remember to test thoroughly on a copy of your sheet before implementing in your main workflow. Good luck with your automation project!

4

hey stella, i’ve run into this too. the problem’s probably cuz ur script’s tryin to use event stuff that ain’t there. try makin a function that checks the whole column instead.

also, settin up a time trigger to run ur script every so often cud work better. that way it’ll move rows even when ur not editing.

good luck w/ learning javascript! it’s tricky but worth it