0

I would like to be able to export a single specific sheet from a large workbook without having to hide the unrequired sheets. Is that actually possible with Google Scripts?

At the moment I am looping through a list of products, updating a query for each one and then exporting each result to an individual PDF. Basically creating a product "Printout" page for many products.

The code below works quite nicely but it starts by hiding all sheets other than my Printout page. That would be fine except some of the other sheets are protected and not all users that would be using my export functionality have the right to hide sheets.

I've considered adding an unprotect/protect function to my macro but it would be good to know if exporting a single sheet was an option before i went down this route?

The hiding sheets trick was from this post Export Single Sheet to PDF in Apps Script

function exportLoopedSheet(firstRow, lastRow) {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = 'Printout'; // update for print sheet name
  var productSheetName = 'ProductList'; // update for final product list
  var folderName = 'productPDFs';
  var main = ss.getSheetByName(sheetName);
  var sheets = ss.getSheets();
  var productList = ss.getSheetByName(productSheetName);
  var lastProductRow = lastRow;
  var firstProductRow = firstRow;   

  // Hide all sheets other than the Print Sheet
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() !== sheetName) {
      sheets[i].hideSheet();
    }
  }

  for (var prodNo = firstProductRow; prodNo < lastProductRow + 1; prodNo ++) {
    var currentProduct = productList.getRange('A'+ prodNo).getValue();
    main.getRange('B9').setValue(currentProduct);

    // Ensure all changes are updated
    SpreadsheetApp.flush();

    // call the export sheet function
    exportSheet();
  }

  // Unhide the sheets
  for (i = 0; i < sheets.length; i++) {
    sheets[i].showSheet();
  }
}

function exportSheet() {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = 'Printout';
  var main = ss.getSheetByName(sheetName);
  var sheets = ss.getSheets();

  //Hide All Empty Rows in the Print Sheet
  var maxRows = main.getMaxRows(); 
  var lastRow = main.getLastRow();
  if (maxRows-lastRow != 0){
    main.hideRows(lastRow+1, maxRows-lastRow);
  }

  // Save pdf version
  var folder = 'productPDF';
  var parentFolder = DriveApp.getFolderById('1234'); //add this line... 
  var folder, folders = DriveApp.getFoldersByName(folder);
   if (folders.hasNext()) {
     folder = folders.next();
   } else {
     folder = parentFolder.createFolder(folder);
   }
  var name = main.getRange("B8").getValue(); 
  folder.createFile(ss.getBlob().setName(name));

  // Unhide the rows again
  var fullSheetRange = main.getRange(1,1,main.getMaxRows(), main.getMaxColumns());
  main.unhideRow(fullSheetRange); 
}
tehhowch
  • 9,645
  • 4
  • 24
  • 42
whatapalaver
  • 865
  • 13
  • 25
  • You can use the export link method with the desired sheet id – tehhowch Oct 09 '18 at 11:06
  • Thanks for the comment which on research led me to this useful post https://stackoverflow.com/questions/49197358/generate-pdf-of-only-one-sheet-of-my-spreadsheet I've tried it out and it works for me which is great. It is using Google Sheets API and I would love to know if thats the only way forward - is it not possible to export a single sheet with Google Apps Script? – whatapalaver Oct 09 '18 at 13:10
  • That script is written in the language Google Apps Script, so I'm not sure what you mean. – tehhowch Oct 09 '18 at 13:36
  • By using the API I am now having 429 errors relating to number of calls made per second. This is not an issue when you use a non API script. – whatapalaver Oct 09 '18 at 14:06
  • 429s can be easily handled with standard url request error handling (exponential backoff). How many requests per second are you sending? – tehhowch Oct 09 '18 at 14:22
  • Well to be honest, not that many. I put in a 2000ms sleep as well but I am still getting the 429 errors after a while. I've ended up suggesting we use the API call for single product prints and the above method with the hiding of sheets for when multi-products need to be exported and restricting that to a few users only. Not ideal. – whatapalaver Oct 09 '18 at 14:44

0 Answers0