0

I'm looking for a way to implement a custom menu option for all of my Google Sheets to create a PDF of the active sheet of that spreadsheet. I use Autocrat and create a lot of Google Sheets that I want to convert into PDFs. However, I don't want to have to "download as PDF" every time, then re-upload them into my drive.

I found a way to use Google Script to create the PDF on on this page, which pertained to Google Docs, not Sheets. So I changed it to fit Sheets. I also added a custom menu option to that at the top. The result was the following code.

    function onOpen(e) {
   SpreadsheetApp.getUi()
       .createMenu('PDF')
       .addItem('Save PDF', 'convertPDF')
       .addToUi();
 }


function convertPDF() {
  doc = SpreadsheetApp.getActiveSpreadsheet();
  var docId = doc.getId();
  var docFolder = DriveApp.getFileById(docId).getParents().next().getId();
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
      'Save As PDF?',
      'Save current document (Name:'+doc.getName()+'.pdf) as PDF',
      ui.ButtonSet.YES_NO);
  if (result == ui.Button.YES) {
    docblob = SpreadsheetApp.getActiveSpreadsheet().getAs('application/pdf');
    /* Add the PDF extension */
    docblob.setName(doc.getName() + ".pdf");
    var file = DriveApp.createFile(docblob);
    var fileId = file.getId();
    moveFileId(fileId, docFolder);
    ui.alert('Your PDF file is available at ' + file.getUrl());
  } else {
    ui.alert('Request has been cancelled.');
  }
}

function moveFileId(fileId, toFolderId) {
   var file = DriveApp.getFileById(fileId);
   var source_folder = DriveApp.getFileById(fileId).getParents().next();
   var folder = DriveApp.getFolderById(toFolderId)
   folder.addFile(file);
   source_folder.removeFile(file);
}

It works great. It creates a PDF of the spreadsheet and saves it to the folder containing the original spreadsheet.

However, that code is specific to that one Google Sheet. I was wondering if there is a way to apply this to all Sheets I open. I found this thread, but it's pretty old. Has it changed? Will I need to install this on each spreadsheet I want to do that with? Surely, there's an easier way to do that, right?

I'm sure it's clear, but I'm talking about using Google Drive through the web interface.

Jentz
  • 23
  • 1
  • 7
  • Publish as add-on. Then you only have to activate it in each document before it will work - you won't have to go and copy+paste the code, or authorize another (functionally identical but not truly identical) script project, as you will have authorized the add-on upon install. – tehhowch Jun 14 '18 at 12:06
  • Ok, thank you, @tehhowch! I appreciate your help. I'll see if I can figure out how to do that. I think it might be beyond my abilities, but I'll give it a shot. – Jentz Jun 18 '18 at 21:13

0 Answers0