I am new to API scripts and trying to send emails to addresses listed in Sheet sendTo!B2 onwards. The messages composed are in composeMessage!A2 onwards. My script gives an unknown error. what am I doing wrong? I would appreciate any help

function composeNsend()

{
  // Fetch the message
  var messageRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("composeMessage").getRange("A2"); 
  var message = messageRange.getValue();
  {
    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sendTo").getRange("B2");
    var emailAddress = emailRange.getValue();
  
    // Send Email.
    var message = message;
    var subject = 'ALERT for New 365d Low';
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

My message composed does have blank lines

thanks

CodePudding user response：

You have some extra brackets in the middle of your code. Remove them and the function will work. Edited the code to loop through rows and flag sent rows. The code below worked for me:

function composeNsend()

{
 
  let curr_ss = SpreadsheetApp.getActiveSpreadsheet();
  let subject = 'ALERT for New 365d Low';

  let message_sheet = curr_ss.getSheetByName("composeMessage");
  let last_message_row = message_sheet.getLastRow();

  //get 2D array of values
  let message_array = message_sheet.getRange("A2:A" last_message_row.toString()).getValues();
  //convert to 1D array
  let messages = message_array.map(itm=>itm[0]);
  
  let email_sheet = curr_ss.getSheetByName("sendTo")
  let last_email_row = email_sheet.getLastRow();
  
  //get 2D array of values
  let email_array = email_sheet.getRange("B2:B" last_email_row.toString()).getValues();
  //convert to 1D array
  let emails =email_array.map(itm=>itm[0]);
  //get flags column
  let flags = email_sheet.getRange("C2:C" last_message_row.toString()).getValues().map(itm => itm[0]);
  //empty array for new flag values
  let new_flags = [];
  
  //choose lowest of last rows in messages and emails, don't send emails without body or email address
  let last_row = Math.min(last_email_row,last_message_row);
  
  //for loop to send emails, add flag on column C for each email sent
  //subtract 1 from last row because array starts from second row
  for(let i=0; i<last_row-1; i  ){
    let curr_flag = flags[i];

    //send email only for rows without sent flag
    if(curr_flag != 'sent'){
      let message = messages[i];
      let emailAddress=emails[i];
      MailApp.sendEmail(emailAddress, subject, message);
      }
    new_flags.push(['sent']);
    }

  //write new flags to sheet 
  email_sheet.getRange("C2:C" last_row.toString()).setValues(new_flags);
}