Run script if cell change in Google Sheets

Question

I have this code, it works if i run it from Script Editor, but doesnt run when i change the cell to "Send Melding". The script runs until it comes to MailApp.sendEmail, then it stops..

Heres the code :

function onEdit(e) {
  var s = SpreadsheetApp.getActiveSheet(), r, colCell;
  if(s.getName() === 'SkapOversikt') { //checks that we're on the correct sheet
    r = s.getActiveCell();
    colCell = r.getColumn();
    if(colCell === 1 || colCell === 6) { //checks the column
      nextCell = r.offset(0, 0);
      if(nextCell.getValue() === 'Send Melding') { //Inneholder "Send Melding", kjør script

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell().getA1Notation();
  var row = sheet.getActiveRange().getRow();
  var cellvalue = ss.getActiveCell().getValue().toString();
  var recipients = sheet.getRange(7, 7).getValue();      
  var subject = 'Skapoversikt har blitt oppdatert på : '+sheet.getRange(4, 2).getValue();
  var body = 'Blåse info på ' + sheet.getRange(4, 2).getValue() + ' har blitt oppdatert. ' + '\n' + '\nFølg link for å se endringer : ' + ss.getUrl() + '\n' + '\n' + 'Melding fra melder : ' + '\n' + '" ' + sheet.getRange(6, 7).getValue() + ' " ' + '\n ' + '\n (Dette er en generert mail fra Google Sheets og vil ikke kunne besvares) ' + '\n' + '\n - AK AS ';
        MailApp.sendEmail(recipients, subject, body);

  var sheet = SpreadsheetApp.getActive().getSheetByName('SkapOversikt');
  sheet.getRange('G6').setValue('Ingen Melding');
  sheet.getRange('A6').setValue('Velg');
  Browser.msgBox('Takk for det - Meldingen er sendt!' , 'Meldingen er sendt til : '+sheet.getRange(7, 7).getValue()+ '\n' + '  - (Fra epost : )', Browser.Buttons.OK);
  }
  }
  }
}

score 0 · Accepted Answer · answered Feb 15 '16 at 13:10

0

I don't think you can send emails on a simple onEdit trigger. Try changing the name of your function (e.g: sendMails(e) ) and add an installable trigger by going into Resources > add a trigger > sendMails > from spreadsheet > on Edit.

See if it then works ?

answered Feb 15 '16 at 13:10

JPV

26,499
4
33
48

Thanks, I was using onEdit(e), but added a project trigger. Thanks for your help! – André K Feb 15 '16 at 14:55
do you know how to use the script on tablet? The script do not run when i change cell in Sheet app on android ore ios..? – André K Feb 15 '16 at 19:33
I don't think this will work in mobile view. See also: http://stackoverflow.com/questions/10851641/how-to-get-scripts-to-work-with-phones-and-tablets – JPV Feb 15 '16 at 21:46
Hi, Seems that the only solution is to run it in desktop mode. I hope the Google team will make a fix for this in the future. Thanks again :) – André K Feb 16 '16 at 06:52

Run script if cell change in Google Sheets

1 Answers1