How to compress picture at google sheet to convert to PDF

Question

var changedFlag = false;
var TEMPLATESHEET='Boom-Report';

function emailSpreadsheetAsPDF() {


  DocumentApp.getActiveDocument();
  DriveApp.getFiles();

  // This is the link to my spreadsheet with the Form responses and the Invoice Template sheets
  // Add the link to your spreadsheet here 
  // or you can just replace the text in the link between "d/" and "/edit"
  // In my case is the text: 17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI
  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1NVJOdFLBAgNFqSHhnHJYybjUlSqhv4hKI_HXJyhJ88E/edit");

  // We are going to get the email address from the cell "B7" from the "Invoice" sheet
  // Change the reference of the cell or the name of the sheet if it is different
  const value = ss.getSheetByName("Source Email-Boom").getRange("X3").getValue();
  const email = value.toString();

  // Subject of the email message
  const subject = ss.getSheetByName("Source Email-Boom").getRange("B3").getValue();

    // Email Text. You can add HTML code here - see ctrlq.org/html-mail
  const body = "Boom Lifts Inspection Report - Sent via Auto Generate PDI Report from Glideapps";

  // Again, the URL to your spreadsheet but now with "/export" at the end
  // Change it to the link of your spreadsheet, but leave the "/export"
  const url = 'https://docs.google.com/spreadsheets/d/1NVJOdFLBAgNFqSHhnHJYybjUlSqhv4hKI_HXJyhJ88E/export?';

  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf
    '&size=A4' + // paper size letter / You can use A4 or legal
    '&portrait=true' + // orientation portal, use false for landscape
    '&fitw=true' + // fit to page width false, to get the actual size
    '&sheetnames=false&printtitle=false' + // hide optional headers and footers
    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
    '&fzr=false' + // do not repeat row headers (frozen rows) on each page
    '&gid=671631174'; // the sheet's Id. Change it to your sheet ID.
    // You can find the sheet ID in the link bar. 
  // Select the sheet that you want to print and check the link,
  // the gid number of the sheet is on the end of your link.
  
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  // Generate the PDF file
  var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
  
  // Send the PDF file as an attachement 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [{
            fileName: ss.getSheetByName("Source Email-Boom").getRange("B3").getValue().toString() +".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
        }]
    });

  // Save the PDF to Drive. (in the folder) The name of the PDF is going to be the name of the Company (cell B5)
  const nameFile = ss.getSheetByName("Source Email-Boom").getRange("B3").getValue().toString() +".pdf"
  const folderID = "1ZKWq9jWmeEQlxncuTPHssCFXC3Fidmxn";
  DriveApp.getFolderById(folderID).createFile(response).setName(nameFile);
}


function on_sheet_change(event) {
  var sheetname = event.source.getActiveSheet().getName();
  var sheet = event.source.getActiveSheet();
  
  if (sheetname == 'Boom-Report') {
    emailSpreadsheetAsPDF() ;
  } else return;
}

---

I have trigger for my coding to convert google sheet to PDF and send email straightaway. But the PDF turn up the image only half.

i believe it cause my the image size very huge, My google sheet looks like this.

But after appscript running the Pdf turn up the picture like this.

and the formula i've been using in the image cell is

=arrayformula(image('Source Email-Boom'!CX3,1))

I believe it cause the size of the image was very huge. I'm not sure how to compressed. you guys any ideas ?

Answer 1

The tl;dr version of the image issue:

The total amount of of pixels an image can have when uploaded via the Google App Script or the Google Sheets v4 API is 1,048,576 pixels.

--

The first user (Tanaike) that commented on your question has written much about this stuff. I stumbled on this gist that explains it better than my short version. Since the link might not work in the future or the content might get deleted, I will try to explain it here a bit:

You can upload any width x height combination you want in image size (measured in pixels), regardless of the file size (in bytes), as long as it's within the total amount of pixels mentioned above (1,048,576 px). This means you can have:

• 1024 x 1024 px
• 2048 x 512 px
• 4096 x 256 px

But for example you can't have:

• 1025 x 1025 px
• 1024 x 1025 px
• 1025 x 1024 px

--

Finally, for the code part, to resize (and compress a little bit) the image, you can use tanaikech's ImageApp library, at the doResize section. Follow this answer as a guide.

Hope this helps as much as it helped me!