CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscursorcursor-position
Nick Liu
Nick Liu

Reputation: 125

Cursor automatically moved to Cell A1 in google spreadsheet with app script after each edit

I have generated the following codes in app script in order to send emails automatically if users changed some values in google spreadsheet.

Then I connected my script to the google spreadsheet by changing "current project's trigger". e.g:

my trigger.jpg

The code is working fine. The only problem is that after users insert/edit new values in any cells (e.g: B1, B12, C12,etc) in google spreadsheet, it is going to trigger my scripts, and then the cursor will always be automatically moved to cell A1, which is annoying.

My best guess (which I could be wrong) is that it goes back to cell A1 as a "signal" as it finishes running the app script codes every time?

Is there anything I could do to stop this automatic cursor moving thing? Do I need to change the "current project's trigger"? 

function PODTool() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);

  var sheet = spreadsheet.getActiveSheet();
  var lastRow = sheet.getLastRow();

  var startRow = 2;

  var range = sheet.getRange(2,7,lastRow-startRow+1,1 );
  var numRows = range.getNumRows();
  var LinksToPODForm = range.getValues();

  var rangeColumnReadyToSendEmail =sheet.getRange(2,9,lastRow-startRow+1,1);
  var sendEmailOrNot = rangeColumnReadyToSendEmail.getValues();


  var warning_count = 0;
  var msg = "";

  // Loop over the values 
  for (var i = 0; i <= numRows - 1; i++) {
    var EachLinkValue = LinksToPODForm[i][0];


    if (EachSendEmailOrNotValue=="yes" && sheet.getRange(i+2,10).getValue()=='Email not Sent' &&EachEmailRecipient=="Someone"){
    var BillingOfLadingShowingInEmailNotification = Billing_of_lading_number[i][0];


    msg = msg +"Requester:       "+EachEmailRecipient+"
    warning_count++;
    var subject ="Billing of Lading: "+BillingOfLadingShowingInEmailNotification;
      sheet.getRange(i+2,10).setValue('Sent');
    var recipient = "[email protected]";
  }

  if(warning_count) {
    MailApp.sendEmail(recipient, subject,msg)
  }

};

Thx for any advice!!

Upvotes: 1

Views: 1901

Answers (1)

Alan Wells
Alan Wells

Reputation: 31310

I would change this: 

var sheet = spreadsheet.getActiveSheet();

to:

var sheet = spreadsheet.getSheets()[0];

and delete this line: 

SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);

Upvotes: 1

Related Questions