Google Sheets: Save single sheet as PDF and save to a new drive folder

Question

I have been searching for hours and cant find a way to combine the following scripts.

first, I have this script which will create a folder and save programs into it:

  function printpdf() {   //busca un folder, si no lo hay lo crea y crea 2 sub carpetas un google document copiado en cada una de ellas

  var name='Programs';
  var carpeta = DriveApp.getRootFolder().searchFolders("title contains     '"+name+"'");
  if (carpeta.hasNext()===true) {    
      while (carpeta.hasNext()) {
      var folder = carpeta.next();
      Logger.log(folder.getName()+' '+folder.getId());
      }
  }else{
    var folder=DriveApp.getRootFolder().createFolder(name);

    var parent=DriveApp.getFolderById(folder.getId());   // get parent folder
    var folder2 =parent.createFolder('Subfolder');   // 1° way to create sub folder

    var doc = DocumentApp.create('Documento sta');
    var sheet = SpreadsheetApp.create('Spreadsheet sta');
    Utilities.sleep(300); // este retardo es para garantizar en el user-side la creacion del nuevo archivo
    var files = DriveApp.getFilesByName('Documento sta');// or Id var file = DriveApp.getFileById(doc.getId());
    while (files.hasNext()) {
      var file = files.next();
      Logger.log('ojo '+file.getName());
      file.makeCopy(folder2);
      var file2=file.makeCopy(folder);
      file2.setName('Acta individual del alumno')
      var blob = file2.getAs('application/pdf'); 
      var file2pdf = folder.createFile(blob);
      var file2pdf = DriveApp.getFileById(file2pdf.getId());
      var file2pdf=file2pdf.makeCopy(folder);
      Logger.log('se creó: '+file.getName()+' en la carpeta: '+folder.getName()+' el PDF es: '+file2pdf.getId());
      DriveApp.getFileById(file2pdf.getId()).setTrashed(true)
      DriveApp.getFileById(docid).setTrashed(true)
      }
  }
}

next, i have this script which will save a sheet as PDF and mail it:

function sendEmailWithPdf() {
    var 
   source   = SpreadsheetApp.getActiveSpreadsheet(),
    subject = source.getSheets()[1].getRange('b2').getValue(),
       body = source.getSheets()[1].getRange('b3').getValue(),
        pdfName = 'Training Workout',
  source = SpreadsheetApp.getActiveSpreadsheet(),
        sheetName = source.getActiveSheet().getName(),
        mailTo = source.getActiveSheet().getRange('g2')
            .getValue(),
        url,
        sheets = source.getSheets()
        sheets.forEach(function (s, i) {
            if (s.getName() !== sheetName) s.hideSheet();
        });
    url = Drive.Files.get(source.getId())
          .exportLinks['application/pdf'];
    url = url + '&size=letter' + //paper size
    '&portrait=false' + //orientation, false for landscape
    '&fitw=true' + //fit to width, false for actual size
    '&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional
    '&gridlines=false' + //false = hide gridlines
    '&fzr=false'; //do not repeat row headers (frozen rows) on each page
    var token = ScriptApp.getOAuthToken();
    var response = UrlFetchApp.fetch(url, {
        headers: {
            'Authorization': 'Bearer ' + token
        }
    });

    MailApp.sendEmail(mailTo, subject, body, {
        attachments: [response.getBlob().setName(pdfName)]
    });
    sheets.forEach(function (s) {
        s.showSheet();
    })

}

how do i combine the function of saving a single PDF from teh sheet based on cell value like the second script, into a new folder as designated by the first script? thanks a ton!

Gerneio Gerneio · Accepted Answer · 2016-04-18T15:50:12

Firstly I'd change the second script to return a blob of the pdf file, and then just delete all the email parts. Add parameters such as a spreadsheet and sheet name object.

Then change the first script to accept parameters such as a blob, folder name, and/or file name objects. And alter the code to use those parameters. Then in just a main function call those two functions, similar to this:

function mainMethod()
{
  var pdfBlob = getSheetAsPdf(SpreadsheetApp.getActiveSpreadsheet(), 'Sheet Name');

  saveBlobToDrive(pdfBlob, 'Folder Name', 'FileName.pdf');

  // then setup your email if you still need to do that
}

This should help give you a direction of how to proceed, if you need further help with code, add a comment or post an update with what you've tried and I'll see what I can do.

Google Sheets: Save single sheet as PDF and save to a new drive folder

1 Answers