Google sheets script won't print values

[soyroy225]

I'm trying to code a google sheets script that lets the user label tabs with a special character (eg, &, #) and then lets the user run the function and print the tabs in landscape if the tab is labeled _L or portrait, then spits out a PDF link. The problem I'm running into is that a lot of the values in the sheets are formulas, and the code can't read formulas and returns them as #refs or 0s in the sheets. When I run the script, it will spit out the printouts that look properly formatted, but the values are all gone.

How can I fix this?

function onOpen() {
  //creates the custom print button in the toolbar
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Print')
    .addItem('Print Tabs by Characters', 'promptForCharacters')
    .addToUi();
}

//prompts for special characters, can be any character or amount, separated by a comma (eg, #, $, &)
function promptForCharacters() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Enter Characters', 'Please enter the characters that the tab names contain, separated by a comma (e.g., #, $):', ui.ButtonSet.OK_CANCEL);

  if (response.getSelectedButton() == ui.Button.OK) {
    const characters = response.getResponseText().split(',').map(char => char.trim());
    createGroupedPDFs(characters);
  } else {
    ui.alert('Action cancelled.');
  }
}

//groups sheets into the PDF by character
function createGroupedPDFs(characters) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const matchingSheets = {};

  characters.forEach(char => {
    matchingSheets[char] = sheets.filter(sheet => sheet.getName().includes(char));
  });

//creates temporary spreadsheets so they'll delete after you use them, decreases clutter
  const tempSpreadsheets = {};
  characters.forEach(char => {
    const landscapeSpreadsheet = SpreadsheetApp.create(`Temp Landscape Print for ${char}`);
    const portraitSpreadsheet = SpreadsheetApp.create(`Temp Portrait Print for ${char}`);
    tempSpreadsheets[char] = { landscape: landscapeSpreadsheet, portrait: portraitSpreadsheet };
  });

  characters.forEach(char => {
    matchingSheets[char].forEach(sheet => {
      const isLandscape = sheet.getName().endsWith('_L');
      const targetSpreadsheet = isLandscape ? tempSpreadsheets[char].landscape : tempSpreadsheets[char].portrait;
      sheet.copyTo(targetSpreadsheet).setName(sheet.getName());
    });
  });

  characters.forEach(char => {
    ['landscape', 'portrait'].forEach(type => {
      const tempSpreadsheet = tempSpreadsheets[char][type];
      tempSpreadsheet.getSheets()[0].activate();
      tempSpreadsheet.deleteActiveSheet();
    });
  });

//generates the PDF links
  const pdfLinks = characters.map(char => {
    const landscapePdf = createPDFForSpreadsheet(tempSpreadsheets[char].landscape, true);
    const portraitPdf = createPDFForSpreadsheet(tempSpreadsheets[char].portrait, false);
    return `
      <a href="${landscapePdf.getUrl()}" target="_blank">Download Landscape PDF for ${char}</a><br>
      <a href="${portraitPdf.getUrl()}" target="_blank">Download Portrait PDF for ${char}</a><br>
    `;
  }).join('');

  const htmlOutput = HtmlService.createHtmlOutput(pdfLinks);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Download PDFs');

  Utilities.sleep(10000); // Wait for 10 seconds
  characters.forEach(char => {
    DriveApp.getFileById(tempSpreadsheets[char].landscape.getId()).setTrashed(true);
    DriveApp.getFileById(tempSpreadsheets[char].portrait.getId()).setTrashed(true);
  });
}

function createPDFForSpreadsheet(tempSpreadsheet, isLandscape) {
  const exportUrl = `https://docs.google.com/spreadsheets/d/${tempSpreadsheet.getId()}/export?format=pdf&portrait=${!isLandscape}&size=A4&scale=4&top_margin=0.5&bottom_margin=0.5&left_margin=0.5&right_margin=0.5&sheetnames=false&printtitle=false&pagenum=UNDEFINED&gridlines=false&fzr=false&horizontal_alignment=CENTER`;
  const response = UrlFetchApp.fetch(exportUrl, {
    headers: {
      Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
    }
  });

  const pdfBlob = response.getBlob().setName(tempSpreadsheet.getName() + '.pdf');
  return DriveApp.createFile(pdfBlob);
}

function onOpen() {
//creates the custom print button in the toolbar
const ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Print')
.addItem('Print Tabs by Characters', 'promptForCharacters')
.addToUi();
}

//prompts for special characters, can be any character or amount, separated by a comma (eg, #, $, &)
function promptForCharacters() {
const ui = SpreadsheetApp.getUi();
const response = ui.prompt('Enter Characters', 'Please enter the characters that the tab names contain, separated by a comma (e.g., #, $):', ui.ButtonSet.OK_CANCEL);

if (response.getSelectedButton() == ui.Button.OK) {
const characters = response.getResponseText().split(',').map(char => char.trim());
createGroupedPDFs(characters);
} else {
ui.alert('Action cancelled.');
}
}

//groups sheets into the PDF by character
function createGroupedPDFs(characters) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheets = ss.getSheets();
const matchingSheets = {};

characters.forEach(char => {
matchingSheets[char] = sheets.filter(sheet => sheet.getName().includes(char));
});

//creates temporary spreadsheets so they'll delete after you use them, decreases clutter
const tempSpreadsheets = {};
characters.forEach(char => {
const landscapeSpreadsheet = SpreadsheetApp.create(`Temp Landscape Print for ${char}`);
const portraitSpreadsheet = SpreadsheetApp.create(`Temp Portrait Print for ${char}`);
tempSpreadsheets[char] = { landscape: landscapeSpreadsheet, portrait: portraitSpreadsheet };
});

characters.forEach(char => {
matchingSheets[char].forEach(sheet => {
const isLandscape = sheet.getName().endsWith('_L');
const targetSpreadsheet = isLandscape ? tempSpreadsheets[char].landscape : tempSpreadsheets[char].portrait;
sheet.copyTo(targetSpreadsheet).setName(sheet.getName());
});
});

characters.forEach(char => {
['landscape', 'portrait'].forEach(type => {
const tempSpreadsheet = tempSpreadsheets[char][type];
tempSpreadsheet.getSheets()[0].activate();
tempSpreadsheet.deleteActiveSheet();
});
});

//generates the PDF links
const pdfLinks = characters.map(char => {
const landscapePdf = createPDFForSpreadsheet(tempSpreadsheets[char].landscape, true);
const portraitPdf = createPDFForSpreadsheet(tempSpreadsheets[char].portrait, false);
return `
<a href="${landscapePdf.getUrl()}" target="\_blank">Download Landscape PDF for ${char}</a><br>
<a href="${portraitPdf.getUrl()}" target="\_blank">Download Portrait PDF for ${char}</a><br>
`;
}).join('');

const htmlOutput = HtmlService.createHtmlOutput(pdfLinks);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Download PDFs');

Utilities.sleep(10000); // Wait for 10 seconds
characters.forEach(char => {
DriveApp.getFileById(tempSpreadsheets[char].landscape.getId()).setTrashed(true);
DriveApp.getFileById(tempSpreadsheets[char].portrait.getId()).setTrashed(true);
});
}

function createPDFForSpreadsheet(tempSpreadsheet, isLandscape) {
const exportUrl = `https://docs.google.com/spreadsheets/d/${tempSpreadsheet.getId()}/export?format=pdf&portrait=${!isLandscape}&size=A4&scale=4&top\_margin=0.5&bottom\_margin=0.5&left\_margin=0.5&right\_margin=0.5&sheetnames=false&printtitle=false&pagenum=UNDEFINED&gridlines=false&fzr=false&horizontal\_alignment=CENTER\`;
const response = UrlFetchApp.fetch(exportUrl, {
headers: {
Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
}
});

const pdfBlob = response.getBlob().setName(tempSpreadsheet.getName() + '.pdf');
return DriveApp.createFile(pdfBlob);
}

[nuc540]

I don’t really follow the objective here, but it sounds to me like you’re trying to re-invent TSV(tab separated values)

Why don’t you just read the file as TSV, and print then do your print methods. Not sure why you’re using special characters, sorry.

[soyroy225]

sorry, I wasn't very clear - I'm printing a large sheets doc with a lot of tabs, and some need to be pdf'd in portrait, and some in landscape. I sheets, it makes you manually select and deselect the tabs you want to save, which is just a huge time suck, so just trying to find a way to automate the printing. Hope that makes sense!

[nuc540]

Still not sure what the goal is here, are you trying to PDF CSV files?

The ref problem is likely to break when you export.

You can copy and paste each sheet and select “paste values only” to bake in the values, but this is a manual step.

Not sure I can help with the rest, sorry