Automatically move rows in Google Sheets based on cell value

Other Questions Google Sheets
1

I’m trying to set up a function in Google Sheets that moves a row when a specific cell value changes. 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 needs. But I’m getting an error about the ‘source’ property on line 6. Can anyone help me fix this?

Here’s what I’m working with:

function moveRowOnEdit(e) {
  const spreadsheet = SpreadsheetApp.getActive();
  const activeSheet = e.range.getSheet();
  const editedRange = e.range;

  if (activeSheet.getName() === 'Tasks' && editedRange.getColumn() === 6 && editedRange.getValue() === 'Done') {
    const rowNum = editedRange.getRow();
    const colCount = activeSheet.getLastColumn();
    const destSheet = spreadsheet.getSheetByName('Completed Tasks');
    const destRange = destSheet.getRange(destSheet.getLastRow() + 1, 1);
    activeSheet.getRange(rowNum, 1, 1, colCount).moveTo(destRange);
    activeSheet.deleteRow(rowNum);
  }
}

Any help would be great. I’ll add ‘Learn JavaScript’ to my to-do list too!

2

I’ve been using Google Sheets for a while now, and I’ve found that automating tasks like this can be a real time-saver. Your code looks pretty solid, but I think I see where the issue might be. Instead of using ‘e.range’, try ‘e.source.getActiveRange()’ as MarkSeeker91 suggested. That should resolve the ‘source’ property error.

One thing I’d add from my experience: make sure you’re handling potential errors. For instance, what if the ‘Completed Tasks’ sheet doesn’t exist? You might want to add a check for that and create it if it’s missing. Also, consider adding some logging statements to help debug if issues crop up in the future.

Keep at it with JavaScript - it’s incredibly powerful for customizing Sheets. I started where you are and now I’m automating all sorts of processes. It’s definitely worth the effort!

3

I’ve encountered similar challenges when transitioning from Excel to Google Sheets. The ‘e.range’ issue others mentioned is indeed the likely culprit. However, I’d like to point out another potential improvement. Consider using the ‘getDataRange()’ method instead of ‘getLastColumn()’ to determine the range to move. This ensures you’re only moving populated cells, which can be more efficient, especially with large spreadsheets.

Also, don’t forget to set up an installable trigger for the ‘onEdit’ event in the script editor. This is crucial for your function to run automatically when edits are made. Lastly, while learning JavaScript, explore the ‘Apps Script’ documentation. It’s a goldmine for Google Sheets automation techniques.

4

hey luna23, i had a similar issue. try changing e.range to e.source.getActiveRange(). that fixed it for me. also, make sure you’ve set up the trigger correctly in the script editor. good luck with learning javascript, it’s pretty fun once you get the hang of it!