PDF file from selection in Google Sheets-CodePudding

I am trying to save a selection of cells to a pdf. Everything is working except when I open the pdf it looks like html.

What am I doing wrong?

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var token = ScriptApp.getOAuthToken(); 
  sheet2.getRange('A1:N50').activate(); *// I realise this could be the problem but not sure how to correct it*

  var url =   "https://docs.google.com/spreadsheets/d/1roKWtpNPsfaQDxW82WZCfQzek77HEV4ihuC52radoXU/edit#gid=2092984379".replace("SS_ID", sheet2.getName());
  var reqFolder = '1sIxddxsPHKLKjuiArZdtrckCMxAePHR6'
  var folder = DriveApp.getFolderById(reqFolder);
  var paymentreq = sheet1.getRange("B7").getValue();
  var creditor = sheet2.getRange("D9").getValue();
  var pdfName = 'Req '   paymentreq   " "   creditor   " "   Utilities.formatDate(new Date(),"GMT 1","dd.MM.yyyy");

  var url_ext = 'exportFormat=pdf&format=pdf'   '&size=A4'   '&portrait=false'   'fitw=true';

  var response = UrlFetchApp.fetch(url   url_ext   sheet2.getSheetId()); 
    headers: {
  Authorisation:'Bearer'   token
}

  var blob = response.getBlob().setName(pdfName   '.pdf');
  folder.createFile(blob);
};

Thank you

I have tried removing the selection section but it still only gives me html. The file when I hover over it starts with HTML: "filename.pdf"

CodePudding user response：

Here is the code I currently have and it is always creating an HTML file.

var sourceSheet = ss.getSheetByName("Payment Req")

var url = "https://docs.google.com/spreadsheets/d/18JPPgqLe5W8pQ_oGP-8oC3Ar4DRfbqrXbUwDfrxy1pM/edit#gid=2092984379"   '/export?exportFormat=pdf&format=pdf'   '&size=A4'   '&portrait=false'   '&fitw=true'   '&sheetnames=true&printtitle=false'   '&pagenum=RIGHT&gridlines=false'   '&fzr=false'   '&horizontal_alignment=CENTER'   '&vertical_alignment=MIDDLE'   '&gid='   sourceSheet.getSheetId(); 
  var reqFolder = '1sIxddxsPHKLKjuiArZdtrckCMxAePHR6'
  var folder = DriveApp.getFolderById(reqFolder);
  var paymentreq = sheet1.getRange("B7").getValue();
  var creditor = sheet2.getRange("D9").getValue();
  var pdfName = 'Req '   paymentreq   " "   creditor   " "   Utilities.formatDate(new Date(),"GMT 1","dd.MM.yyyy");

  var response = UrlFetchApp.fetch(url, {headers: {"Authorisation":'Bearer '   ScriptApp.getOAuthToken()}});

  var theblob = response.getBlob().setName(pdfName   ".pdf");
  const pdfFile = folder.createFile(theblob);
  return pdfFile;

CodePudding user response：

Modification points:

In your script, ss, sheet1, and sheet2 are not declared.
headers: { Authorisation:'Bearer' token} is not included in UrlFetchApp.fetch
Authorisation is not correct. It's Authorization.
In your endpoint is required to be modified.
There are 2 gid in your query parameter.

I think that this might be the reason for the issue with your 2nd script.

When these points are reflected, it becomes as follows.

Modified script:

From:

var sourceSheet = ss.getSheetByName("Payment Req")

var url = "https://docs.google.com/spreadsheets/d/18JPPgqLe5W8pQ_oGP-8oC3Ar4DRfbqrXbUwDfrxy1pM/edit#gid=2092984379"   '/export?exportFormat=pdf&format=pdf'   '&size=A4'   '&portrait=false'   '&fitw=true'   '&sheetnames=true&printtitle=false'   '&pagenum=RIGHT&gridlines=false'   '&fzr=false'   '&horizontal_alignment=CENTER'   '&vertical_alignment=MIDDLE'   '&gid='   sourceSheet.getSheetId(); 
  var reqFolder = '1sIxddxsPHKLKjuiArZdtrckCMxAePHR6'
  var folder = DriveApp.getFolderById(reqFolder);
  var paymentreq = sheet1.getRange("B7").getValue();
  var creditor = sheet2.getRange("D9").getValue();
  var pdfName = 'Req '   paymentreq   " "   creditor   " "   Utilities.formatDate(new Date(),"GMT 1","dd.MM.yyyy");

  var response = UrlFetchApp.fetch(url, {headers: {"Authorisation":'Bearer '   ScriptApp.getOAuthToken()}});

  var theblob = response.getBlob().setName(pdfName   ".pdf");
  const pdfFile = folder.createFile(theblob);
  return pdfFile;

To:

var ss = SpreadsheetApp.getActiveSpreadsheet(); // Please set Spreadsheet object as ss.
var sheet1 = // Please set your sheet1.
var sheet2 = // Please set your sheet2.

var sourceSheet = ss.getSheetByName("Payment Req");
var url = ss.getUrl().replace(/\/edit/, "")   '/export?exportFormat=pdf&format=pdf'   '&size=A4'   '&portrait=false'   '&fitw=true'   '&sheetnames=true&printtitle=false'   '&pagenum=RIGHT&gridlines=false'   '&fzr=false'   '&horizontal_alignment=CENTER'   '&vertical_alignment=MIDDLE'   '&gid='   sourceSheet.getSheetId();
var reqFolder = '1sIxddxsPHKLKjuiArZdtrckCMxAePHR6';
var folder = DriveApp.getFolderById(reqFolder);
var paymentreq = sheet1.getRange("B7").getValue();
var creditor = sheet2.getRange("D9").getValue();
var pdfName = 'Req '   paymentreq   " "   creditor   " "   Utilities.formatDate(new Date(), "GMT 1", "dd.MM.yyyy");
var response = UrlFetchApp.fetch(url, { headers: { "Authorization": "Bearer "   ScriptApp.getOAuthToken() } });
var theblob = response.getBlob().setName(pdfName   ".pdf");
const pdfFile = folder.createFile(theblob);
return pdfFile;