Export multiple PDFs with Google Sheets Script

76 views Asked by At

I have a template sheet, of which I want to create several PDFs. I have a script that alters some of the cells of the sheet, then I export the sheet as a PDF, change some cells again, export again and so on.

Exporting the PDF works fine. The issue is with altering the cells: altering works, but the changes are only visible after the script finished. This in turn makes the export always create the same PDF of the sheet like it looked before the script started, not respecting the changes to the cells.

I used this tutorial to save the PDFs. Running the script apparently "commits" the changes only after the script is done, however the export uses the current state of the sheet. I use sheet.getRange(..).setValue(..); to set the cell values.

I am not sure how to approach this issue. I thought of creating several template sheets, but that would lead to the same issue. Is it possible to "commit" the changes to the sheet before exporting the PDF? Thanks!

Edit: here is a minimal example, the behaviour is the same. The script requires a folder in your drive named dummy_folder and one sheet of the spreadsheet needs to be named sheet1.

function save_pdfs()
{
  // Get spreadsheet and sheet object.
  //
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet       = spreadsheet.getSheetByName("sheet1");

  // Get spreadsheet and sheet IDs.
  //
  var ss_id = spreadsheet.getId();
  var s_id  = sheet.getSheetId();

  // Get the dummy folder.
  //
  var folders = DriveApp.getFoldersByName("dummy_folder");
  var folder  = folders.next();

  // Do three loops, change a single cell value and then export the PDF.
  //
  for (var i = 0; i < 3; ++i)
  {
    sheet.getRange(1,1,1,1).setValue(i);

    const url = "https://docs.google.com/spreadsheets/d/" + ss_id + "/export" +
    "?format=pdf&" +
    "size=a4&" +
    "scale=2&" +
    "fzr=true&" + 
    "portrait=true&" +
    "gridlines=false&" +
    "printtitle=false&" +
    "top_margin=0.2&" +
    "bottom_margin=0.2&" +
    "left_margin=0.2&" +
    "right_margin=0.2&" +
    "sheetnames=false&" +
    "pagenum=UNDEFINED&" +
    "attachment=true&" +
    "gid=" + s_id;

    const params = { method: "GET", headers: { "authorization": "Bearer " + ScriptApp.getOAuthToken() } };
    const blob = UrlFetchApp.fetch(url, params).getBlob().setName("dummy" + i + ".pdf");

    folder.createFile(blob);
  }
};
0

There are 0 answers