I have exactly the same problem highlighted in this link but nothing I read seems to help.
I’m not a GAS expert and this is the code I developed inspired by similar codes on the internet
function testExportGoogleSheet() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheets()[0];
const fileName = sh.getName();
const fileType = 'pdf';
const file = exportGoogleSheet(fileName, fileType, sh);
MailApp.sendEmail('emailAddress', 'Test subject', 'Test body', file);
}
function exportGoogleSheet(fileName, fileType, sheets = null) {
// SaveAs multiple sheets ('sheets' object can be the reference to a single sheet, to an array of sheets or, if left off (the default value is applied), to the entire spreadsheet) in different formats.
// The file format is specified by the 'fileType' parameter. The file name is specified by the 'fileName' parameter.
//
// All requests must include id in the path and a format parameter
// https://docs.google.com/spreadsheets/d/{SpreadsheetId}/export?
// FORMATS WITH NO ADDITIONAL OPTIONS
// format=xlsx // excel
// format=xls // excel
// format=ods // Open Document Spreadsheet
// format=zip // html zipped
// CSV,TSV OPTIONS***********
// format=csv // comma seperated values
// tsv // tab seperated values
// gid=sheetId // the sheetID you want to export, The first sheet will be 0. others will have a uniqe ID
// PDF OPTIONS****************
// format=pdf
// size=0,1,2..10 paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B5
// fzr=true/false repeat row headers
// fzc=true/false repeat column headers
// portrait=true/false false = landscape
// fitw=true/false fit window or actual size
// gridlines=true/false
// printtitle=true/false
// pagenum=CENTER/UNDEFINED CENTER = show page numbers / UNDEFINED = do not show
// attachment = true/false dunno? Leave this as true
// gid=sheetId Sheet Id if you want a specific sheet. The first sheet will be 0. others will have a uniqe ID. Leave this off for all sheets.
// printnotes=false Set to false if you don't want to export the notes embedded in a sheet
// top_margin=[number] Margins - you need to put all four in order fir it to works, and they have to be to
// left_margin=[number] 2DP. So 0.00 for zero margin.
// right_margin=[number]
// bottom_margin=[number]
// horizontal_alignment=CENTER Horizontal Alignment: LEFT/CENTER/RIGHT
// vertical_alignment=TOP Vertical Alignment: TOP/MIDDLE/BOTTOM
// scale=1/2/3/4 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
// pageorder=1/2 1= Down, then over / 2= Over, then down
// sheetnames=true/false
// range=[NamedRange] Named ranges supported - see below
// EXPORT RANGE OPTIONS FOR PDF
// Use these options or ...
// gid=sheetId // must be included. The first sheet will be 0. others will have a uniqe ID
// ir=false // seems to be always false
// ic=false // same as ir
// r1=Start Row number - 1 // row 1 would be 0 , row 15 wold be 14
// c1=Start Column number - 1 // column 1 would be 0, column 8 would be 7
// r2=End Row number
// c2=End Column number
// ... just use A1 notation
// &range=Y1:AZ25&...
let sh;
let url, urlExt, gid;
let i;
// Base URL
url = 'https://docs.google.com/spreadsheets/d/SS_ID/export?'.replace('SS_ID', ss.getId());
if (sheets != null) {
if (Array.isArray(sheets)) {
sh = sheets[0];
gid = sh.getSheetId();
// In case of multiple sheets to merge, please do as follow --> sheet.getSheetId() + "%EE%B8%80"+sheet2.getSheetId() + "%EE%B8%80"+sheet3.getSheetId()
if (sheets.length > 1) {
for (i = 1; i < sheets.length; i++) {
sh = sheets[i];
gid += '%EE%B8%80' + sh.getSheetId();
}
}
gid = '&gid=' + gid;
} else {
sh = sheets;
gid = '&gid=' + sh.getSheetId();
}
} else {
gid = '';
}
switch (fileType) {
case 'xlsx':
case 'xls':
case 'csv':
case 'tsv':
case 'ods':
case 'zip':
urlExt = 'format=' + fileType;
url += urlExt + gid;
break;
case 'pdf':
/* Specify PDF export parameters
From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
*/
urlExt = 'format=' + fileType +
// The below parameters are optional...
'&size=a4' +
'&scale=2' +
'&portrait=false' +
'&fitw=true' +
'&sheetnames=false' +
'&printtitle=false' +
'&pagenumbers=false' +
'&gridlines=true' +
'&horizontal_alignment=CENTER' +
'&vertical_alignment=TOP' +
'&fzr=true' +
'&attachment=true';
url += urlExt + gid;
break;
}
const token = ScriptApp.getOAuthToken();
const params = { method: 'GET', headers: { 'authorization': 'Bearer ' + token } };
//'muteHttpExceptions': true,
//'oAuthServiceName': 'spreadsheets',
//'oAuthUseToken': 'always',
const blob = UrlFetchApp.fetch(url, params).getBlob().setName(fileName + '.' + fileType);
return blob;
}
The code manages exports in different formats and one or more sheets, but the problem is exclusively at the level of the call to the UrlFetchApp.fetch() function.
The code worked for a while, but then I must have changed something and I can’t figure out where the problem is anymore
I’ve been googling for days but I can’t solve the problem and I’m at a standstill.
Anyway, I’m also surprised that such a basic feature (exporting a google sheet in a different format) gives so many problems, considering the number of questions on the topic around the world!
Thanks in advance to anyone who wants to help me to solve the problem.