Error in Mail Merge using Apps Script

[Charles Allen Jacinto]

Error showing "The starting column of the range is too small."

Here is my code;

/**

 * @OnlyCurrentDoc

*/

 

/**

 * Change these to match the column names you are using for email

 * recipient addresses and email sent column.

*/

const RECIPIENT_COL  = "Recipient";

const EMAIL_SENT_COL = "Email Sent";

 

/**

 * Creates the menu item "Mail Merge" for user to run scripts on drop-down.

 */

function onOpen() {

  const ui = SpreadsheetApp.getUi();

  ui.createMenu('Mail Merge')

      .addItem('Send Emails', 'sendEmails')

      .addToUi();

}

 

/**

 * Sends emails from sheet data.

 * @param {string} subjectLine (optional) for the email draft message

 * @param {Sheet} sheet to read data from

*/

function sendEmails(subjectLine, sheet=SpreadsheetApp.getActiveSheet()) {

  // option to skip browser prompt if you want to use this code in other projects

  if (!subjectLine){

    subjectLine = Browser.inputBox("Mail Merge",

                                      "Type or copy/paste the subject line of the Gmail " +

                                      "draft message you would like to mail merge with:",

                                      Browser.Buttons.OK_CANCEL);

                                     

    if (subjectLine === "cancel" || subjectLine == ""){

    // If no subject line, finishes up

    return;

    }

  }

 

  const emailTemplate = getGmailTemplateFromDrafts_(subjectLine);

 

  const dataRange = sheet.getDataRange();

  const data = dataRange.getDisplayValues();

  const heads = data.shift();

 

  const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);

 

  const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));

  const out = [];

  obj.forEach(function(row, rowIdx){

    if (row[EMAIL_SENT_COL] == ''){

      try {

        const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);

        GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {

          htmlBody: msgObj.html,

 

          attachments: emailTemplate.attachments,

          inlineImages: emailTemplate.inlineImages

        });

        out.push([new Date()]);

      } catch(e) {

 

        out.push([e.message]);

      }

    } else {

      out.push([row[EMAIL_SENT_COL]]);

    }

  });

 

 

  sheet.getRange(2, emailSentColIdx+1, out.length).setValues(out);

 

  /**

   * Get a Gmail draft message by matching the subject line.

   * @param {string} subject_line to search for draft message

   * @return {object} containing the subject, plain and html message body and attachments

  */

  function getGmailTemplateFromDrafts_(subject_line){

    try {

     

      const drafts = GmailApp.getDrafts();

     

      const draft = drafts.filter(subjectFilter_(subject_line))[0];

     

      const msg = draft.getMessage();

     

      const allInlineImages = draft.getMessage().getAttachments({includeInlineImages: true,includeAttachments:false});

      const attachments = draft.getMessage().getAttachments({includeInlineImages: false});

      const htmlBody = msg.getBody();

     

      const img_obj = allInlineImages.reduce((obj, i) => (obj[i.getName()] = i, obj) ,{});

     

      const imgexp = RegExp('<img.*?src="cid:(.*?)".*?alt="(.*?)"[^\>]+>', 'g');

      const matches = [...htmlBody.matchAll(imgexp)];

     

      const inlineImagesObj = {};

     

      matches.forEach(match => inlineImagesObj[match[1]] = img_obj[match[2]]);

      return {message: {subject: subject_line, text: msg.getPlainBody(), html:htmlBody},

              attachments: attachments, inlineImages: inlineImagesObj };

    } catch(e) {

      throw new Error("Oops - can't find Gmail draft");

    }

    /**

     * Filter draft objects with the matching subject linemessage by matching the subject line.

     * @param {string} subject_line to search for draft message

     * @return {object} GmailDraft object

    */

    function subjectFilter_(subject_line){

      return function(element) {

        if (element.getMessage().getSubject() === subject_line) {

          return element;

        }

      }

    }

  }

 

  /**

   * Fill template string with data object

   * @see https://stackoverflow.com/a/378000/1027723

   * @param {string} template string containing {{}} markers which are replaced with data

   * @param {object} data object used to replace {{}} markers

   * @return {object} message replaced with data

  */

  function fillInTemplateFromObject_(template, data) {

   

    let template_string = JSON.stringify(template);

   

    template_string = template_string.replace(/{{[^{}]+}}/g, key => {

      return escapeData_(data[key.replace(/[{}]+/g, "")] || "");

    });

    return  JSON.parse(template_string);

  }

  /**

   * Escape cell data to make JSON safe

   * @see https://stackoverflow.com/a/9204218/1027723

   * @param {string} str to escape JSON special characters from

   * @return {string} escaped string

  */

  function escapeData_(str) {

    return str

      .replace(/[\\]/g, '\\\\')

      .replace(/[\"]/g, '\\\"')

      .replace(/[\/]/g, '\\/')

      .replace(/[\b]/g, '\\b')

      .replace(/[\f]/g, '\\f')

      .replace(/[\n]/g, '\\n')

      .replace(/[\r]/g, '\\r')

      .replace(/[\t]/g, '\\t');

  };

}

The information transmitted through this mail is intended solely for the addressee and may be legally privileged and confidential. Any disclosure, copying, dissemination or any action taken or omitted, to be taken in reliance on it, by persons or entities other than the intended recipient is prohibited. No statement or representation made in this e-mail is binding upon The Moment Group.

[Kulfi Software Services]

I can fix it for you if you can book a consultation with me here: https://www.upwork.com/freelancers/kulfisoftwares

[George Ghanem]

Hi Charles, we need more info to tell you what to correct in your code.

What may be best is if you provide a sample spreadsheet with the code for us to take a look at. Also make sure the spreadsheet is accessible for us to review. Read access should be enough.

 Thanks,

  George

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/d20703f5-e02c-482e-a07a-4310c518cc6bn%40googlegroups.com.