Save correctly in PDF to Drive a Google Sheet

Question

I am developing a script under Sheet allowing:

• send an email with a PDF copy of the sheet attached
• save this sheet in PDF in my Drive (defined with the id of the folder)

I succeeded without any problem for the first part of the script. For the second part, I also succeeded in making this backup. However, the information present in this one does not suit me. Indeed, the images are not transferred in the file (I have "cellimage" which appears instead) and the data do not correspond to those present in my Sheet. Indeed, they are rounded to 3 digits after the comma in my sheet while in the backup, there is no rounding.

I would have liked to have the same file as the one that is attached to the email. I searched hard but I could not find a code to save my attachment in the drive.

Here is the link to the file.

Here is the function that allows you to send the mail:

function envoiMail() {
  // Déclaration des constantes
  const classeur = SpreadsheetApp.getActiveSpreadsheet();
  const feuille = classeur.getSheetByName('SYNTHESE');

  // Déclaration des variables
  var semaine = feuille.getRange('B5').getValue();
  var annee = feuille.getRange('B4').getValue();

  var auditeurMail = Session.getActiveUser().getEmail().split('@');
  var auditeurNomPrenom = auditeurMail[0].split('.');
  var auditeurPrenom = majString(auditeurNomPrenom[0]);
  var auditeurNom = majString(auditeurNomPrenom[1]);
  var signature = auditeurPrenom+' '+auditeurNom;

  var sujet = 'test ' + semaine + ' (' + annee +')';
  var corpsDuMail = '<p>Bonjour,</p>'
  + '<p> Veuillez trouver ci-joint le test de la semaine ' + semaine + '-'+annee+'.</b></p><br>'
  + '</p>Bonne réception,</p>'
  + signature;

 // Pièce jointe
 var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", classeur.getId());
 var url_ext = 'exportFormat=pdf&format=pdf' + '&size=A4' + '&portrait=true' + '&fitw=true&source=labnol' 
            + '&sheetnames=false&printtitle=false' + '&pagenumbers=false&gridlines=false' + '&fzr=false' + '&gid=';
 var token = ScriptApp.getOAuthToken();
 var pdfMarge = UrlFetchApp.fetch(url + url_ext + feuille.getSheetId(),{headers : {
              'Authorization' : 'Bearer ' + token }}).getBlob().setName("Marge_previsionnelle_S"+semaine+"_"+annee+".pdf");


 // Fonction permettant d'envoyer le mail
 MailApp.sendEmail("[email protected]", sujet, corpsDuMail, {
 htmlBody : corpsDuMail,
 attachments : [pdfMarge]
 });

 // Pop-up pour informer que l'envoi de mails s'est déroulé correctement
 SpreadsheetApp.getUi().alert("Le mail a bien été envoyé aux personnes concernées !");
}

// Fonction permettant de mettre en majuscule la première lettre de la chaîne de caractère
function majString(a){
   return (a+'').charAt(0).toUpperCase()+a.substr(1);
 }

And here is the function that allows me to archive my sheet in my drive:

function sauvegardeDrive() {
  // Déclaration des variables et constantes
  const classeur = SpreadsheetApp.getActiveSpreadsheet();
  const feuille = classeur.getSheetByName('SYNTHESE');
  var sheetName = "SYNTHESE";
  var folderID = "1sRkfgLO8C4ABcrzPBonGPJm3emzWD3Ct";
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID);
  var semaine = feuille.getRange('B5').getValue();
  var annee = feuille.getRange('B4').getValue();
  var pdfName = "test"+semaine+"_"+annee+".pdf";

  //Copie de l'ensemble du Sheet
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("copieFichier", folder));

 //Suppression des onglets inutiles
 var sheets = destSpreadsheet.getSheets();
 for (i = 0; i < sheets.length; i++) {
   if (sheets[i].getSheetName() != sheetName){
      destSpreadsheet.deleteSheet(sheets[i]);
   }
 }
 var destSheet = destSpreadsheet.getSheets()[0]; 
 var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
 var sourcevalues = sourceRange.getValues();
 var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
 destRange.setValues(sourcevalues);

 //Sauvegarde en pdf
 var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
 var newFile = folder.createFile(theBlob);

 //Suppression du Sheet temporaire
 DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

Answer 1

Comment out (or delete) these lines in sauvegardeDrive() function:

  // var destSheet = destSpreadsheet.getSheets()[0];
  // var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
  // var sourcevalues = sourceRange.getValues();
  // var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  // destRange.setValues(sourcevalues);

You don't need them. You already copied the sheet, you don't need to copy the sheet data again.

Update

If your sheet contains referenced date, you can convert it into static date if you add these two lines before the loop that removes another sheets (above the comment '//Suppression des onglets inutiles'):

var range = destSpreadsheet.getSheetByName('SYNTHESE').getDataRange(); 
range.setValues(range.getDisplayValues());

Update 2

If the mail script works fine for you, you can just use the same part of the code for saving the sheet. Try this hew function sauvegardeDrive():

function sauvegardeDrive() {
  // Déclaration des variables et constantes
  const classeur = SpreadsheetApp.getActiveSpreadsheet();
  const feuille = classeur.getSheetByName('SYNTHESE');
  var sheetName = "SYNTHESE";
  var folderID = "1gZEWeG4XzZDuEqDDWKaMoVolMmeLWDRI";
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID);
  var semaine = feuille.getRange('B5').getValue();
  var annee = feuille.getRange('B4').getValue();
  var pdfName = "test"+semaine+"_"+annee+".pdf";

  // Pièce jointe -- this part is stealed from envoiMail() function
  var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", classeur.getId());
  var url_ext = 'exportFormat=pdf&format=pdf' + '&size=A4' + '&portrait=true' + '&fitw=true&source=labnol' 
              + '&sheetnames=false&printtitle=false' + '&pagenumbers=false&gridlines=false' + '&fzr=false' + '&gid=';
  var token = ScriptApp.getOAuthToken();
  var pdfMarge = UrlFetchApp.fetch(url + url_ext + feuille.getSheetId(),{headers : {
                'Authorization' : 'Bearer ' + token }}).getBlob().setName(pdfName);

  folder.createFile(pdfMarge);
}