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("xxxxxxxxxx@gmail.com", "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": "xxxxxxxxxxxxxx@gmail.com"}]}],
"from": {"email": "xxxxxxxxxxx@gmail.com"},
"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()));
}