0

I am creating a Google Sheet to send mails using SendGrid.

Below is the code for sending mails with Google Apps Script, but I do not manage to send to multiple recipients. I adapted it from this SO question: Send emails using Sendgrid with google appscript

It works, but it sends an email only to the first element of the list of recipients.

function AutoSend2() {
    var SENDGRID_KEY ='MY_KEY';
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Feuille 1");
    var numRows = sheet.getLastRow() - 1;
    var sheet_ti = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TEMPLATE_ID");
    var data_ti = sheet_ti.getDataRange().getValues();
    var template_id = data_ti[0][0];
    var data = sheet.getDataRange().getValues();

    var headers = {
        "Authorization" : "Bearer "+ SENDGRID_KEY,

        "Content-Type": "application/json"
    }
    for (ind_to = 0; ind_to <= 15; ind_to++) {
       if (data[0][ind_to] == "EMAILS") {
          break;
       }
       if (ind_to == 15) {
          ind_to = -1;
          break;
       }
    }
    for (i = 1; i < data.length && i <= numRows; i++) {
        var pers = [{
            "to": [
              {
                "email": data[i][ind_to]
              }
             ]
        }]
        var body =
          {
            "template_id": template_id,

            "from": {
              "email": "newsletters@xxx.com",
              "name": "XXX"
            },
            "content": [
              {
                "type": "text/html",
                "value": "XXX"
              }
            ],

            "personalizations":
            pers
          }
        var options = 
          {
            'method': 'post',
            'headers': headers,
            'payload': JSON.stringify(body)
          }
        var response = UrlFetchApp.fetch("https://api.sendgrid.com/v3/mail/send", options)
    }
}

I get this error message :

https://api.sendgrid.com/v3/mail/send. Code renvoyé : 400. Réponse tronquée du serveur : {"errors":[{"message":"Invalid type. Expected: object, given: string.","field":"personalizations.0.to.0","help":"http://sendgrid.com/docs/API_Refer...

Any idea of how to fix this? Thanks.

Jules Zaccardi
  • 1
  • 2
  • In your script, ``ind_to`` is not declared. I think that because It's always used as ``0``, ``it sends an email only to the first element of the list of recipients.`` might occur. – Tanaike Sep 26 '18 at 22:05
  • I forgot to add the declaration, right. I edited the code now, but it does not seem to be the problem – Jules Zaccardi Sep 27 '18 at 08:09
  • You redefine the personalizations in every loop over data, and send the email every loop. You need to collect them in an array and then send the email after exiting the loop. – tehhowch Sep 27 '18 at 12:54
  • @tehhowch yes, because I have multiple rows with each time a list of emails and some values to send. For a given `i`, if I log the value of `data[i][ind_to]`, it would be `[{email1@example.com,email2@example.com}]` for example. Isn't this the needed array format ? – Jules Zaccardi Sep 27 '18 at 13:41
  • I based my code on this : https://stackoverflow.com/questions/37249209/send-emails-using-sendgrid-with-google-appscript, but it does not seem to solve my issue – Jules Zaccardi Sep 27 '18 at 13:44
  • A string is not an array, even if it has the same string representation as the array. You can try parsing the string as JSON, which would return an object-based interpretation of it, but will fail for non-JSON format. You could split the cell on given delimiters and then create your personalizations from that array. – tehhowch Sep 27 '18 at 14:21

0 Answers0