google-sheetsgoogle-apps-scriptgoogle-drive-apipdf-generation

Update PDF from Sheets and Keep Share Link and Permissions

I have an Apps Script that prints a Google Spreadsheet to a PDF and saves it on my drive. The script is working great for that. The challenge is that I want to replace the existing file on the drive, keeping sharing links in tact, and keeping permissions so anyone with the link can access the updated file. Below is my code. How can I adjust this so that it will update the file in place rather than deleting the old file and making a new file?

function savePDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('sheet_name'); // Specifically target the sheet_name sheet
  var folderId = 'my_google_drive_folder_id';
  var pdfName = 'filename.pdf';

  // Determine the last non-empty cell in each column and row
  var dataRange = sheet.getDataRange();
  var lastRow = dataRange.getLastRow();
  var lastColumn = dataRange.getLastColumn();

  // Adjust print settings to fit the data range
  var range = sheet.getRange(1, 1, lastRow, lastColumn);
  sheet.setActiveRange(range);
  spreadsheet.setActiveSheet(sheet, true);

  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFilesByName(pdfName);

  // Delete the existing PDF in the folder
  while (files.hasNext()) {
    var file = files.next();
    folder.removeFile(file);
  }

  // Define PDF export URL with adjusted print range
  var url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet.getId() + '/export?exportFormat=pdf&format=pdf';
  var options = '&size=letter' +
                '&portrait=true' +
                '&scale=2' +
                '&top_margin=0.25' + '&bottom_margin=0.75' + '&left_margin=0.25' + '&right_margin=0.25' +
                '&sheetnames=false&printtitle=false&pagenumbers=true&gridlines=false&fzr=true' +
                '&gid=' + sheet.getSheetId(); // Using the specific Sheet ID for exporting

  var token = ScriptApp.getOAuthToken();

  // Fetch the PDF
  var response = UrlFetchApp.fetch(url + options, {
    headers: {
      'Authorization': 'Bearer ' + token
    }
  });

  // Convert to a blob and create new file in the drive folder
  var blob = response.getBlob().setName(pdfName);
  folder.createFile(blob);
  
  // Log a success message
  Logger.log('PDF updated and view permissions set in Google Drive folder!');
}

I also tried the code below, but the code below produces corrupt PDFs.

function updatePDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('sheet_name'); // Target specific sheet
  var folderId = 'my_google_drive_folder_id';
  var pdfName = 'filename.pdf';

  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFilesByName(pdfName);
  var existingFile = files.hasNext() ? files.next() : null;

  var url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet.getId() + '/export?exportFormat=pdf&format=pdf';
  var options = '&size=letter' +
                '&portrait=true' +
                '&scale=2' +
                '&top_margin=0.25' + '&bottom_margin=0.75' + '&left_margin=0.25' + '&right_margin=0.25' +
                '&sheetnames=false&printtitle=false&pagenumbers=true&gridlines=false&fzr=true' +
                '&gid=' + sheet.getSheetId(); // Using the specific Sheet ID for exporting

  var token = ScriptApp.getOAuthToken();
  var headers = {
    'Authorization': 'Bearer ' + token,
    'muteHttpExceptions': true
  };
  var response = UrlFetchApp.fetch(url + options, { headers: headers });

  if (response.getResponseCode() == 200) {
    var blob = response.getBlob().setName(pdfName);

    // Create a new file and capture its ID
    var newFile = folder.createFile(blob);
    newFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);

    // Delete the old file if it exists
    if (existingFile) {
      existingFile.setTrashed(true);
    }

    Logger.log('PDF updated successfully and permissions are set.');
  } else {
    Logger.log('Failed to fetch the PDF: ' + response.getContentText());
  }
}
Solution

  • I believe your goal is as follows.

    In this case, how about using Drive API? When this is reflected in your bottom script, it becomes as follows.

    Modified script:

    Before you use this script, please enable Drive API v3 at Advanced Google services.

    function updatePDF() {
  var folderId = '###';
  var pdfName = 'filename.pdf';

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('sheetName');
  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFilesByName(pdfName);
  var existingFileId = files.hasNext() ? files.next().getId() : Drive.Files.create({ name: pdfName, mimeType: MimeType.PDF, parents: [folderId] }).id;
  var url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet.getId() + '/export?exportFormat=pdf&format=pdf';
  var options = '&size=letter' +
    '&portrait=true' +
    '&scale=2' +
    '&top_margin=0.25' + '&bottom_margin=0.75' + '&left_margin=0.25' + '&right_margin=0.25' +
    '&sheetnames=false&printtitle=false&pagenumbers=true&gridlines=false&fzr=true' +
    '&gid=' + sheet.getSheetId(); // Using the specific Sheet ID for exporting
  var token = ScriptApp.getOAuthToken();
  var headers = {
    'Authorization': 'Bearer ' + token,
    'muteHttpExceptions': true
  };
  var response = UrlFetchApp.fetch(url + options, { headers: headers });
  if (response.getResponseCode() == 200) {
    var blob = response.getBlob().setName(pdfName);
    Drive.Files.update({}, existingFileId, blob);
    DriveApp.getFileById(existingFileId).setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    Logger.log('PDF updated successfully and permissions are set.');
  } else {
    Logger.log('Failed to fetch the PDF: ' + response.getContentText());
  }
}

    Reference: