0

I'm using this scrip to save to pdf but I need to make it Size A5 and Landscape Orientated I have found some parameters but I don't know how to combine

function checkSheet() {
var sheetName = "OS";
var folderID = "FOLER ID"; // Folder id to save in a folder.
var sourceSpreadsheet = SpreadsheetApp.getActive();
var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
var folder = DriveApp.getFolderById(folderID);
var docID = sourceSpreadsheet.getSheetByName(sheetName).getRange('B6').getValue();
var pdfName = "OS "+docID+".pdf";

//Copy whole spreadsheet
var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

//delete redundant sheets
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];
//repace cell values with text (to avoid broken references) 
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);

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

//Delete the temporary sheet
DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

source: Exporting Google SpreadSheet to PDF (filename referring to a cell D7)

That is the code for paper size:

var theurl = 'https://docs.google.com/a/mydomain.org/spreadsheets/d/'
      + 'XXXXXXXXXXXXXXXXXXXXXXXXXXXX'  //the file ID
      + '/export?exportFormat=pdf&format=pdf'
      + '&size=LETTER'
      + '&portrait=true'
      + '&fitw=true'       
      + '&top_margin=0.50'              
      + '&bottom_margin=0.50'          
      + '&left_margin=0.50'             
      + '&right_margin=0.50'           
      + '&sheetnames=false&printtitle=false'
      + '&pagenum=false'
      + '&gridlines=false'
      + '&fzr=FALSE'      
      + '&gid='
      + 'XXXXXXXXXXXXXXXXXXXXXX';       //the sheet's Id

7
  • 1
    Welcome to Web Applications. The code "for the paper size" shown at the end of the question doesn't have a place in the previous code at the beginning as it's is intended to be used with UrlFetchApp but the beginnings code is using DriveApp. Where do you found that code?
    – Rubén
    Dec 15, 2021 at 23:00
  • Related: 1, 2
    – Rubén
    Dec 15, 2021 at 23:07
  • Ruben, that was the source, indeed. is there a way to make paper size work on the other code or to modify the main code to integrate the paper size? Dec 15, 2021 at 23:41
  • 2
    Please edit the question to add proper attribution of the source and show what you tried or describe where do you get stuck to adapt the script to your needs.
    – Rubén
    Dec 16, 2021 at 0:19
  • 1
    Hi and welcome to Webapps. Your code is the same as referenced in Webapps (Sept 2018): Exporting Google SpreadSheet to PDF (filename referring to a cell D7) and also "Docs Editor Help" (Nov 2020): How to save G-Sheet or Doc as PDF within Drive. This script was explicitly designed without regard for paper size. You need to modify your script and base it on one of the examples referenced by @Rubén. The key is the use of UrlFetchApp.fetch.
    – Tedinoz
    Dec 17, 2021 at 0:08

1 Answer 1

0

You want to add page size and orientation parameters to a script to save a sheet as PDF. To do this, your script must use the UrlFetchApp.fetch() method (Doc ref)

The following is an adaptation of your script with the relevant elements of the WebApps question/answer Batch convert Google sheets in a specific directory to PDF and email.


function savesheetAsPDF() {
  var sheetName = "OS";
  var folderID = "177xwnrZRS8LUIpfqgcV0TGxcOTNvJSxn"; // Folder id to save in a folder.
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID);
  var docID = sourceSpreadsheet.getSheetByName(sheetName).getRange('B6').getValue();
  var pdfName = "OS "+docID+".pdf";

  //Copy whole spreadsheet
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

  //delete redundant sheets
  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 url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", destSpreadsheet.getId());



  /* Specify PDF export parameters
  From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
  */

  var url_ext = 'exportFormat=pdf&format=pdf'        // export as pdf / csv / xls / xlsx
  + '&size=A5'                           // paper size legal / letter / A4
  + '&portrait=false'                    // orientation, false for landscape
  + '&gid=';                             // the sheet's Id

  
  var token = ScriptApp.getOAuthToken();

  // Convert the sheet to PDF
  var response = UrlFetchApp.fetch(url + url_ext + destSheet.getSheetId(), {
  headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

  //convert the response to a blob and store in our array
  var theBlob = response.getBlob().setName(pdfName)
  var newFile = folder.createFile(theBlob)

  //Delete the temporary sheet
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.