AppScript for transferring single row between sheets in Google Spreadsheet

Other Questions Google Sheets
1

Help needed with my script! It’s moving two rows instead of one.

I’m working on a Google Sheets script to move a row from one sheet to another when a checkbox is ticked. But there’s a problem. When I click the checkbox, it moves both the row I clicked and the one right under it. I’ve tried adding delays, but the issue persists.

Here’s a sample of what I’m trying to achieve:

function moveRow(event) {
  let sourceSheet = event.source.getActiveSheet();
  let targetSheet = event.source.getSheetByName('Finished');
  let clickedCell = event.range;

  if (sourceSheet.getName() === 'Active' && clickedCell.getColumn() === 10) {
    if (clickedCell.getValue() === true) {
      let rowData = sourceSheet.getRange(clickedCell.getRow(), 1, 1, sourceSheet.getLastColumn()).getValues()[0];
      targetSheet.appendRow(rowData);
      sourceSheet.deleteRow(clickedCell.getRow());
    }
  }
}

Can anyone help me figure out why it’s moving two rows instead of just the one I selected?

2

hey nate, i had similar issue before. check if you have any merged cells in your sheet. sometimes that can cause weird behavior with row operations. also, try using getRange().clearContent() instead of deleteRow() and see if that helps. good luck!

3

I’ve encountered this issue before, and it’s often related to how the script interacts with the spreadsheet’s event handling. Try modifying your code to use SpreadsheetApp.getActiveSpreadsheet() instead of event.source. This approach can sometimes provide more reliable results.

Additionally, consider adding a Logger.log() statement to print out the row number being processed. This can help you verify if the script is actually detecting the correct row. If the logging shows the correct row but two are still being moved, the problem might be in how the sheet is structured or how Google Sheets is interpreting your commands.

Lastly, ensure your checkbox column doesn’t have any hidden formulas or conditional formatting that might be affecting adjacent cells. These can sometimes trigger unexpected behavior in scripts.

4

I’ve dealt with similar issues in my projects. One thing that often helps is wrapping your code in a try-catch block. This can prevent unexpected behavior and give you more insight into what’s happening. Here’s a modified version of your script that might work better:

function moveRow(event) {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sourceSheet = ss.getSheetByName('Active');
    const targetSheet = ss.getSheetByName('Finished');
    const range = event.range;

    if (sourceSheet.getName() === 'Active' && range.getColumn() === 10 && range.getValue() === true) {
      const rowNum = range.getRow();
      const rowData = sourceSheet.getRange(rowNum, 1, 1, sourceSheet.getLastColumn()).getValues()[0];
      targetSheet.appendRow(rowData);
      sourceSheet.deleteRow(rowNum);
    }
  } catch (error) {
    Logger.log('Error: ' + error.toString());
  }
}

This approach has worked well for me in the past. It uses SpreadsheetApp.getActiveSpreadsheet() for more reliable sheet access and includes error logging. Give it a try and let us know if it resolves the issue!