Error in export to PDF script

260 views

Skip to first unread message

Pierre Rapin

unread,

Feb 15, 2023, 4:17:58 AM2/15/23

to Google Apps Script Community

Hello all

I'm trying to export automaticaly in PDF some sheets logged into a dedicated sheet called "To print" (in column B there is the list of sheets to print)

I tried with this code but i have an error:

Le code :

function onOpen() {

var ui = SpreadsheetApp.getUi();

var menu = ui.createMenu("GED");

menu.addItem("Enregistrer le PDF ↓ ","savePDF");

menu.addToUi();

}

function savePDF()

{

// SÉLECTIONNER LA FEUILLE A SAUVEGARDER

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getActiveSheet();

//URL de SS ?

var ssUrl = ss.getUrl();

var sheetId= sheet.getSheetId();

// Créer l'URL du document

var url = ssUrl.replace(/\/edit.*$/,'')

+ '/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="TEST"'

+ '&pagenum=false'

+ '&gridlines=false'

+ '&fzr=FALSE'

+ '&gid='+sheetId;

// RÉCOLTER LES INFORMATIONS DANS LE SHEET POUR COMPOSER LE NOM DU DOCUMENT

var docId = sheet.getRange('B2').getValue();

var clientName = sheet.getRange('B2').getValue();

var docDate = sheet.getRange('A7').getValue();

//mise en forme de la date : mois-annee

var docDateMMYY = (docDate.getMonth()+1)+"-"+docDate.getFullYear();

// Concaténons le tout

var docName = docId+"_DEVIS_10Zaine_"+clientName+"_"+docDateMMYY ;

// CRÉER LE PDF A PARTIR DE L'URL

var token = ScriptApp.getOAuthToken();

var docurl = UrlFetchApp.fetch(url, { headers: { 'Authorization': 'Bearer ' + token } });

var pdf = docurl.getAs('application/pdf').setName(docName);;

var file = DriveApp.createFile(pdf);

//COPIER LE PDF DANS LE DOSSIER

ATTENTION IL FAUT ADAPTER LE CODE AVEC L'ID DU FOLDER DE DESTINATION

var folder = DriveApp.getFolderById("1_ngDfUG2KHeI-97MD9qoZpXcNsXQrj5o");

var finalfile = file.moveTo(folder);

}

I've got this error in the first function:

Exception: Cannot call SpreadsheetApp.getUi() from this context.

onOpen @ onOpen.gs:2

ANd this error on the second one:

TypeError: docDate.getMonth is not a function

savePDF @ onOpen.gs:39

I ttried to seek for corrections in google but cannot find anything

Hopefully someone will be able to help me...

Have a nice day

Pierre

cwl...@gmail.com

unread,

Feb 17, 2023, 7:29:46 AM2/17/23

to Google Apps Script Community

Hello Pierre,
The second one is probably easier to fix than the first.
Try this:
var docDate = new Date(sheet.getRange('A7').getValue()); // that will make sure the value is converted to a date type instead of a string

For the first error, I want to make sure I understand more before assuming I know what you are doing. For example,

you are calling cell B2 twice for different variables:

var docId = sheet.getRange('B2').getValue();

var clientName = sheet.getRange('B2').getValue();

I'm guessing one of them is a typo, but neither should cause an error in your doc name. So I am guessing you are trying to open a second

Spreadsheet from the first? If that is the case, you will have to change "activeSheet" (which is the first [main] Spreadsheet) and open the second

Spreadsheet using SpreadsheetApp.openById("second_spreadsheet_id").

Note: I haven't tested this code!! But this is how I would try to do it (or similar):

So in the First (Main) Spreadsheet, you will have to get the list of secondary sheet Ids.
var sheet = SpreadsheetApp.getActiveSheet()

var sheetIds = sheet.getRange(2, 2, sheet.getLastRow() -1, 1).getValues(); //this will get all the values in column B

//Now iterate through the secondary sheetIds opening each one in-turn and saving as pdf.

sheetIds.forEach( (sheetId ) => {

var tempSheet = SpreadsheetApp.openById(sheetId)

var clientName = tempSheet.getRange('B2').getValue();

var docDate = tempSheet.getRange('A7').getValue();