i'm new to the script side of sheets and i'm not 100% sure on what i'm doing and just trying to learn a bit more.

I have this script :

function DebtorMailSend(g) {
 If (g.rangecolumnstart != 7 || g.Value != 60) return;
Const rData = g.source.getactivesheet().getrange(g.range.rowStart,1,1,5).getValues()
let c = rData[0][0]
let i = rData[0][1]
let e = rData[0][2]
let b = rData[0][3]
let n = rData[0][4]

let msg = "Hi,"  "I hope you're well." "We have been reviewing our records and understand that invoice number"   n   "relating to UK services have not yet been settled." "We would be grateful if you could expedite payment and let us know when we can expect to receive the remittance." "Should you have any queries or require further details, please do not hesitate to contact me." "Kind Regards," "Ken"
GmailApp.sendEmail(e,c   "- Invoice Reminder - "   n,msg)
}

And i cant save because of an issue with the Return, which i don't fully understand.

Syntax error: SyntaxError: Unexpected string line: 10 file: Code.gs

I have a bunch of stuff i need to add like a 2nd condition and CC's but i'm trying to get the basics first.

Of course i'm not expecting anyone to solve the whole thing, but any explanations or guidance is very much appreciated, feel free to add a tab with guidance or tips!

CodePudding user response：

Try it this way:

function DebtorMailSend(g) {
  if (g.range.columnstart != 7 || g.value != 60) return;
  const sh = g.range.getSheet();
  const rData = sh..getrange(g.range.rowStart, 1, 1, 5).getValues();
  let c = rData[0][0]
  let i = rData[0][1]
  let e = rData[0][2]
  let b = rData[0][3]
  let n = rData[0][4]
  let msg = "Hi,"  "I hope you're well." "We have been reviewing our records and understand that invoice number"   n   "relating to UK services have not yet been settled." "We would be grateful if you could expedite payment and let us know when we can expect to receive the remittance." "Should you have any queries or require further details, please do not hesitate to contact me." "Kind Regards," "Ken"
  GmailApp.sendEmail(e, c   "- Invoice Reminder - "   n, msg)
}

CodePudding user response：

As an example it could be two functions like this:

// set checkboxes in 13th column for the rows that have >60 in 6th column

function set_checkboxes() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var days_column = 6;
  var checkboxes_column = 13;

  var data = sh.getDataRange().getValues();

  data.forEach((row, i) => {
    if (row[days_column-1] > 60) 
      sh.getRange(i 1,checkboxes_column).insertCheckboxes().check();
  });
}


// send emails for evey row that has checked checkbox in 13th column

function send_emails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var checkboxes_column = 13;

  var data = sh.getDataRange().getDisplayValues();

  data.forEach((row,i) => {
    if (row[checkboxes_column-1] == 'TRUE') {
      var msg = "<p>Hi,</p><p>I hope you're well.</p><p>Best regards.</p>"; // html tags
      var addr = row[2];
      var copy = row[3];
      var subj = row[0]   " - Invoice Reminder - "   row[4];

      MailApp.sendEmail(addr, subj, msg, { 'htmlBody': msg, 'cc': copy });
      
      sh.getRange(i 1,checkboxes_column).uncheck();
    }
  });
}

First function sets checkboxes in 13th columns for the rows that have >60 in 6th column.

Second function sends emails for the checked rows. And you can uncheck any checkbox if you don't want to send email for some rows.