Google script, active cell after script

Question

I have a script that inserts a new row (8)in a spreadsheet and copies data from row 9. The script is triggered from a button linked to the script. Everything works fine except when the script is finished and I start typing I don't see any data. The new row 8/col 3 should be active. It does have a blue box around it, but doesn't accept data until I click on it.

function InsertTran() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Transactions");
  var row = 8;
  var newrow = 9;
  //var lastrow = sheet.getLastRow();
  var lastcolumn = sheet.getLastColumn();
  sheet.getRange('a8').activate();
  sheet.insertRowBefore(sheet.getActiveCell().getRow());
  var source_range = sheet.getRange(row+1,1,1,lastcolumn);
  var target_range = sheet.getRange(row,1,1,lastcolumn);
  source_range.copyTo(target_range);
  sheet.getRange(row,2).activate();
  sheet.getActiveCell().setValue(new Date()).setNumberFormat("MM/DD/YY");
  sheet.getRange(row,3).activate();
  sheet.getActiveCell().setValue("");
  sheet.getRange(row,4).activate();
  sheet.getActiveCell().setValue("");
  sheet.getRange(row,5).activate();
  sheet.getActiveCell().setValue(0);
  sheet.getRange(row,6).activate();
  sheet.getActiveCell().setValue(0);
  sheet.getRange(row,7).activate();
  sheet.getActiveCell().setValue("O");

  sheet.getRange(row,3).activate();
 
}

Nikko J. Nikko J. · Accepted Answer · 2021-02-02T20:52:59

I tried to replicate your code and it is working just fine. I was able to edit the activated cell after running the script. Also, you don't need to activate each cell in order to edit it. You could directly setValue after the getRange.

Here, I modified some parts of your code:

function onOpen(e) {
//Add Test Menu everytime the Sheet is opened.
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'myFunction', functionName: 'myFunction'}
  ];
  spreadsheet.addMenu('Test Menu', menuItems);
}

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Transactions");
  var row = 8;
  var lastcolumn = sheet.getLastColumn();
  sheet.insertRowBefore(sheet.getRange('A8').getRow());
  var source_range = sheet.getRange(row+1,1,1,lastcolumn);
  var target_range = sheet.getRange(row,1,1,lastcolumn);
  source_range.copyTo(target_range);
  sheet.getRange(row,2).setValue(new Date()).setNumberFormat("MM/DD/YY");
  sheet.getRange(row,3).setValue("");
  sheet.getRange(row,4).setValue("");
  sheet.getRange(row,5).setValue(0);
  sheet.getRange(row,6).setValue(0);
  sheet.getRange(row,7).setValue("O");
  sheet.getRange(row,3).activate();  
}

Example:

Output:

Google script, active cell after script

1 Answers

Reference: