0

I am developing this script in order to automate an email sending of a template. I have already selected the range that I want but I am obtaining an URL and I want an image in the email's body. I paste the code, maybe you can help me!

var PRINT_OPTIONS = {
  'size': 7,               // paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B
  'fzr': false,            // repeat row headers
  'portrait': true,        // false=landscape
  'fitw': true,            // fit window or actual size
  'gridlines': false,      // show gridlines
  'printtitle': false,
  'sheetnames': false,
  'pagenum': 'UNDEFINED',  // CENTER = show page numbers / UNDEFINED = do not show
  'attachment': false
}

var PDF_OPTS = objectToQueryString(PRINT_OPTIONS);

function onOpen(e) {
  SpreadsheetApp.getUi().createMenu('Print...').addItem('Print selected range', 'printSelectedRange').addToUi();
}

function printSelectedRange() {
  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(31,2,34,7);

  var gid = sheet.getSheetId();
  var printRange = objectToQueryString({
    'c1': range.getColumn() - 1,
    'r1': range.getRow() - 1,
    'c2': range.getColumn() + range.getWidth() - 1,
    'r2': range.getRow() + range.getHeight() - 1
  });
  
  var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' + PDF_OPTS + printRange + "&gid=" + gid;
  //var img = sheet.getCharts()[0].getBlob().getAs('image/png');

  var htmlTemplate = HtmlService.createTemplateFromFile('js');
  htmlTemplate.url = url;
  //htmlTemplate.url = img;
  SpreadsheetApp.getUi().showModalDialog(htmlTemplate.evaluate().setHeight(10).setWidth(100), 'Print range');

  var tienda = sheet.getRange('B29').getValue();
  var asunto = '¡Estos fueron los numeros de la semana pasada en tu tienda, ' + tienda + '!';
  var email = 'INSERT';
  var kam = sheet.getRange('J33').getValue();
  var mensaje = "<p>¡Hola, <em><strong>" + tienda +"</strong></em>!</p><p>&nbsp;</p><p>A continuacion, te adjuntamos un link donde podras ver los numeros de tu tienda en la ultima semana: </p><p>&nbsp;</p>" + "<a href=" + htmlTemplate.url + " >Haz click</a><p>&nbsp;</p><p>Ante cualquier duda o consulta, puedes contactarte con tu ejecutivo de cuentas a la siguiente direccion de correo: </p>" + kam + "<p>&nbsp;</p><p>¡Si tenes Rappi, tenes todo!&nbsp;</p><p>&nbsp;</p>"
  
  
  //'A continuación, te adjuntamos un link donde podrás ver los números de tu tienda en la última semana: ' + htmlTemplate.url //'<img  src = htmlTemplate.url />';
  


  GmailApp.sendEmail(email, asunto, mensaje,{htmlBody:mensaje})
}

function objectToQueryString(obj) {
  return Object.keys(obj).map(function(key) {
    return Utilities.formatString('&%s=%s', key, obj[key]);
  }).join('');
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
  • In your code some "SpreadsheetApp" is mentioned. Are you talking about an Excel-File and thus an "xlsx-file" instead of a "doc-file"? Or is it some sort of Google-Docs? – SebDieBln Nov 12 '21 at 15:47
  • Than you for your answer Sebastian! I am working on Google Scripts over a Google Sheet. The link that I get is something like this https://docs.google.com/spreadsheets/d/1tmeP2voo3NcIQXrbhrZ9YtylBxgdG-H3WoNIT9ixGQY/export?format=pdf&size=7&fzr=false&portrait=true&fitw=true&gridlines=false&printtitle=false&sheetnames=false&pagenum=UNDEFINED&attachment=false&c1=1&r1=30&c2=11&r2=89&gid=1537526106 – Federico Lazarte Nov 12 '21 at 16:00
  • 1
    https://stackoverflow.com/questions/58327686/get-range-of-google-spreadsheet-into-an-image-using-google-script – Cooper Nov 12 '21 at 17:28
  • Is this thread useful? https://stackoverflow.com/q/63471232/7108653 – Tanaike Nov 13 '21 at 00:29
  • @Tanaike that was useful! Thank you. Do you know now how can I send the created image through an email? I am using gmailApp but it doesnt work – Federico Lazarte Nov 15 '21 at 13:40

0 Answers0