onOpen not working on Google Script

Question

I'm trying to get a piece of code to work when the spreadsheet is open. I added the onOpen script, set the script trigger to "when spreadsheet is open", and waited for....nothing happened. What am I doing wrong?

The script basically copies a row from one sheet to another based on the value of a cell. I want the script to copy all the rows that have the specified value to the next sheet only when the sheet

function onOpen(){
 moveThisRow();
 }

function moveThisRow(){

 var sheetNameToWatch = "Sheet1";

  var columnNumberToWatch = 7; // column A = 1, B = 2, etc.
  var valueToWatch = "move it";
  var sheetNameToMoveTheRowTo = "Sheet2";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();
  var fill = sheet.getActiveRange-2;


  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {

    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(range.getRow());
  }
}

Help!

Open the execution transcript (View > Execution transcript). Is there an error in that log? If so, what is it? — Brian
This line returns NaN var fill = sheet.getActiveRange-2; The 'getActiveRange()' method returns an instance of the Range class. If you'd like to offset the range or perform other actions with it, you must use Range methods. — Anton Dementiev

Dan Dan · Accepted Answer · 2018-02-16T05:37:50

Your code is actually running correctly. The problem is the conditions in your if statement are never getting met. I've added a else section to your code with an alert to more easily show this.

function onOpen(){
  moveThisRow();
 }

function moveThisRow(){

  var sheetNameToWatch = "Sheet1";

  var columnNumberToWatch = 7; // column A = 1, B = 2, etc.
  var valueToWatch = "move it";
  var sheetNameToMoveTheRowTo = "Sheet2";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();
//  var fill = sheet.getActiveRange - 2;

  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(range.getRow());
    SpreadsheetApp.getUi().alert('Conditions are true')
  } else {
    SpreadsheetApp.getUi().alert('Conditions are false')
  }
}

If you update your onOpen function to create a menu with the code below, you can see that when the conditions you specify are actually met, the code is doing what you've told it to.

function onOpen(){
  SpreadsheetApp.getUi().createMenu('Test')
      .addItem('Run', 'moveThisRow')
      .addToUi();
 }

Additionally, as pointed out earlier above, your fill variable is NaN but that doesn't cause a problem since it isn't actually used in the code anywhere (I just commented it out). Hope this helps!

onOpen not working on Google Script

1 Answers