Trouble attaching exported Excel file to a sendGrid email in Google Apps Script-CodePudding

I'm trying to export a Google sheet as an excel file and then attach it to sendGrid email. The email comes through without the attachment.

I think it has to do with the base64encoding, but not sure where I'm going wrong.

Here is my code which exports the excel and tries to send it to the sendGrid function:

function getGoogleSpreadsheetAsExcel(){

 try {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var blankSS = 'xxxxxxxxxxxxxxxxxxxxxxx';
    var donations = ss.getSheetByName('Donations');
    var getrange = donations.getDataRange();
    var data = getrange.getValues();
    var header = data[0].slice(0, 13);
    var dataArray = [];

    dataArray.push(header);

    for(var i=0;i<data.length;i  ){
        const sent = data[i][14];
       if(sent){
        continue;
       }else{
        const row = data[i];
        row.splice(-2);
        dataArray.push(row);
        }
   }

    var temp = SpreadsheetApp.openById(blankSS).getActiveSheet();

    if(dataArray.length > 1){

    dataArray.forEach((row) => temp.appendRow(row));
    
    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key="   blankSS   "&exportFormat=xlsx";

    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer "   ScriptApp.getOAuthToken()}
    };

    var blob = UrlFetchApp.fetch(url, params).getBlob().getBytes();
    var doc = Utilities.base64EncodeWebSafe(blob);

    const subject = "Activity Report";
    const body = "Please see attached spreadsheet of recent purchases.";

    sendEmailWithSendGrid(subject,body,doc);
    
    donations.getRange(2, 15, donations.getLastRow()-1).setValue('sent');
    
    }

    temp.clear()
 } 

 catch (f) {
    MailApp.sendEmail("[email protected]", "Script Error", f);
  }

}

here is the sendGrid function:

function sendEmailWithSendGrid(subject,body,attachments){

const key = 'SG.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
const url = 'https://api.sendgrid.com/v3/mail/send';

  var payload = {
  "personalizations": [{"to": [{"email": "[email protected]"}]}],
  "from": {"email": "[email protected]"},
  "subject": subject,
  "content": [{ "type": "text/plain", "value": body }],
  "attachment": [{"content": attachments, "type": "text/plain" , "filename": "testExcel.xlsx"}]
  };
  
  var options = {
    "method" : "post",
    "payload" : JSON.stringify(payload)
  };
  
  options.headers = {    
    "Authorization" : "Bearer "   key,
    "Content-Type" : "application/json"
    
  };
  var res = UrlFetchApp.fetch(url, options);
  Logger.log(JSON.stringify(res.getResponseCode()));

}

CodePudding user response：

When I checked the official document of Request Body of Mail Send of SendGrid API, it seems that the key name is attachments and also content is the Base64 encoded content. So how about the following modification?

From:

"attachment": [{"content": attachments, "type": "text/plain" , "filename": "testExcel.xlsx"}]

To:

"attachments": [{"content": attachments, "type": "text/plain" , "filename": "testExcel.xlsx"}]

And, in your situation, var doc = Utilities.base64EncodeWebSafe(blob); might be var doc = Utilities.base64Encode(blob);. So also please test this.

Reference:

Mail Send of SendGrid API