Error while merging 2 specific Google sheets tabs into one pdf using Google Apps Script

Question

I am trying to merge 2 specific sheets (both have different Data ranges, Sheet1->A2:Q28 , Sheet2->A1:I27) into one pdf. For that, I found this resource that explains about merging of sheets. Specifically, converting all sheets into one pdf (sheets can not be hidden). Here is the code:

function convertSpreadsheetToPdf(spreadsheetId, sheetName, pdfName) {

  var spreadsheet = spreadsheetId ? SpreadsheetApp.openById(spreadsheetId) : SpreadsheetApp.getActiveSpreadsheet();
  spreadsheetId = spreadsheetId ? spreadsheetId : spreadsheet.getId()  
  var sheetId = sheetName ? spreadsheet.getSheetByName(sheetName).getSheetId() : null;  
  var folder = DriveApp.getFolderById('1t6ns346GNX_gP-uGkwE43egGhTCaWgKO');
  var pdfName = pdfName ? pdfName : spreadsheet.getName();
  
  var url_base = spreadsheet.getUrl().replace(/edit$/,'');

  var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf

      // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided
      + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 
      // following parameters are optional...
      + '&size=letter'      // paper size
      + '&portrait=true'    // orientation, false for landscape
      + '&fitw=true'        // fit to width, false for actual size
      + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
      + '&gridlines=false'  // hide gridlines
      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page

  var options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }

  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(pdfName + '.pdf');
  folder.createFile(blob);
  
  folder.createFile(blob);

} 

If I run this function for one sheet like this:

function test(){
  var pdfName = "123"
  convertSpreadsheetToPdf("","Sheet1",pdfName);
}

Here is a sample sheet for which I want to run this function. It shows the following error:

Exception: Request failed for https://docs.google.com returned code 500. Truncated server response: <meta name="viewport" c... (use muteHttpExceptions option to examine full response)

I want to modify it to merge only 2 sheets into one pdf. Let's assume the names of the sheets are Sheet1 and Sheet2. Any guidance would be much appreciated.

Answer 1

Modification points:

• When I saw your provided Spreadsheet, I confirmed that Unicode characters like (Unicode Version:10.0), (Unicode Version:6.0), and (Unicode Version:6.0) are included in the Spreadsheet. In my experience, I'm worried that this might be the reason for your current issue. In the current stage, unfortunately, when such Unicode characters are included, it seems that an error occurs. Also, I could replicate the same error with you.

In order to avoid this error, it is required to remove or replace such Unicode characters. When this is reflected in your script, how about the following modification?

Modified script:

In this modification, your test() is modified.

function test(){
  const s = SpreadsheetApp.getActiveSpreadsheet();
  const replacement = [
    {"from": "", "to": "#1"},
    {"from": "", "to": "#2"},
    {"from": "", "to": "#3"},
  ];
  replacement.forEach(o => s.createTextFinder(o.from).useRegularExpression(true).replaceAllWith(o.to));
  SpreadsheetApp.flush();

  var pdfName = "123"
  convertSpreadsheetToPdf("","",pdfName);

  replacement.forEach(o => s.createTextFinder(o.to).useRegularExpression(true).replaceAllWith(o.from));
}

• In this modification, the Unicode characters are replaced. If you want to remove this, please modify the value of to to "".
• When I tested this modified script to your provided Spreadsheet, I confirmed that a PDF file including "Sheet1" and "Sheet2" was created.

Note:

Unfortunately, I cannot know your actual situation. But, if your actual Spreadsheet has more sheets including "Sheet1" and "Sheet2", and "Sheet1" and "Sheet2" are more large sheet, and you want to export the specific ranges from them, how about the following sample script? In this case, the ranges of Sheet1!A2:Q28 and Sheet2!A1:I27 are exported as a PDF file.

1. Install Javascript library.

In this sample script, in order to merge PDF data, a Javascript library of pdf-lib.min.js is used. So, please copy and paste the script from https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js to the script editor of your Google Apps Script and save the script.

The sample script for merging PDF data is from https://tanaikech.github.io/2023/01/10/merging-multiple-pdf-files-as-a-single-pdf-file-using-google-apps-script/ (Author: me).

2. Sample script

Please copy and paste the following script to the script editor and save the script.

async function myFunction() {
  // This is from your question.
  const output = [
    {sheetName: "Sheet1", range: "A2:Q28"},
    {sheetName: "Sheet2", range: "A1:I27"},
  ];
  const conv = [
    {"from": "", "to": "#1"},
    {"from": "", "to": "#2"},
    {"from": "", "to": "#3"},
  ];

  // Retrieve PDF data.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  conv.forEach(o => ss.createTextFinder(o.from).useRegularExpression(true).replaceAllWith(o.to));
  SpreadsheetApp.flush();
  const url_base = ss.getUrl().replace(/edit$/,'');
  const headers = {authorization: "Bearer " + ScriptApp.getOAuthToken()};
  const data = output.map(({sheetName, range}) => {
    const s = ss.getSheetByName(sheetName);
    const gid = s.getSheetId();
    const r = s.getRange(range);
    const r1 = r.getRow() - 1;
    const c1 = r.getColumn() - 1;
    const r2 = r1 + r.getNumRows();
    const c2 = c1 + r.getNumColumns();
    const url = `${url_base}export?exportFormat=pdf&format=pdf`
      + '&size=letter'
      + '&portrait=true'
      + '&fitw=true'
      + '&sheetnames=false&printtitle=false&pagenumbers=false'
      + '&gridlines=false'
      + '&fzr=false'
      + `&gid=${gid}&r1=${r1}&c1=${c1}&r2=${r2}&c2=${c2}`;
    const res = UrlFetchApp.fetch(url, {headers, muteHttpExceptions: true});
    if (res.getResponseCode() != 200) {
      console.log(res.getContentText())
    }
    return new Uint8Array(res.getContent());
  });
  conv.forEach(o => ss.createTextFinder(o.to).useRegularExpression(true).replaceAllWith(o.from));

  // Merge PDFs.
  const setTimeout = function(f, t) {
    Utilities.sleep(t);
    return f();
  }
  const pdfDoc = await PDFLib.PDFDocument.create();
  for (let i = 0; i < data.length; i++) {
    const pdfData = await PDFLib.PDFDocument.load(data[i]);
    const pages = await pdfDoc.copyPages(pdfData, [...Array(pdfData.getPageCount())].map((_, i) => i));
    pages.forEach(page => pdfDoc.addPage(page));
  }
  const bytes = await pdfDoc.save();

  // Create a PDF file.
  DriveApp.createFile(Utilities.newBlob([...new Int8Array(bytes)], MimeType.PDF, "sample1.pdf"));
}

• When this script is run, the ranges of Sheet1!A2:Q28 and Sheet2!A1:I27 are exported as a PDF file.
• If your Spreadsheet includes the values of #1, #2, #3, please modify conv.

References:

• createTextFinder(findText) of Class Spreadsheet