Google Sheet App Script - For Specific Sheet Hide Rows that are empty then export to PDF

Question

Updated with working script

I am having a little bit difficulty figuring how to hide ranges of rows. I also notice I am not using the updated api for ui messages.

UiApp API is deprecated.Expand
File: PrintOrder Line: 28
Anchor API is deprecated.Expand
File: PrintOrder Line: 31
UiInstance API is deprecated.Expand
File: PrintOrder Line: 28

I have a sheet("printOrder" that pulls data from sheet("salesOrder")

My plan is to link a script to button on salesOrder that prints or exports to PDF printOrder.

Now I would like to hide the empty rows that are not filled in between Row 41 to Row 76. Here is my mangled script so far:

function printOrder() {

    SpreadsheetApp.flush();
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("printOrder");
    var gid = sheet.getSheetId();

  //start number Row, start number Column, number of Rows , number of Columns 
    var range = sheet.getRange(41, 1, 36,10); 

  //get the values to those rows
    var values = range.getValues();
    Logger.log(values)

      for (var i=0; i<values.length; i++) {

         if(values[i][0] === ""){
             sheet.hideRow(sheet.getRange(i+41,1));1
         }     
       }

  // Getting 'name' for PDF from Cell G5 
    var name = sheet.getRange("G5").getValue()+".pdf";
  // setting output options  
    var pdfOpts = '&size=letter' +
                  '&fzr=true'+ 
                  '&portrait=true'+
                  '&fitw=true'+
                  '&gridlines=false'+
                  '&printtitle=false'+
                  '&sheetnames=false'+
                  '&pagenum=CENTER'+
                  '&attachment=false'+
                  '&gid=';

  // retrieving url to link for download of pdf
    var url = ss.getUrl().replace(/edit$/, '') + 
              'export?exportFormat=pdf&format=pdf' + 
               pdfOpts + 
               sheet.getSheetId();

  // simple ui popup to allow user to click-download pdf
    var app = UiApp.createApplication().setWidth(200).setHeight(50);
      app.setTitle('Print this sheet');

    var link = app.createAnchor('Download PDF', url).setTarget('_new');
      app.add(link);
      ss.show(app);

 }

Thank you for the guidance.

Cheers,

mars

Alan Wells Alan Wells · Accepted Answer · 2016-02-06T02:01:59

Initiate i at zero, then when you use the hideRows() method, you will need to add 41 to i to hide the correct row:

sheet.hideRows(i+41);

I think this line has an issue:

for (var i=41; i<values.length; i++) {

The values array has row 41 data in the array index zero. You can print the contents of the values array with a Logger.log('array: ' + array); statement. Add the Logger statement, run the code, and then in the VIEW menu, choose LOGS.

The range that the values are retrieved from starts in row 41, but to loop through the data, i needs to start at zero.

for (var i=0; i<values.length; i++) {

You can start the counter number at any number you want, but then you'd need to subtract that number to get to zero for the first loop.

var thisLoopValue;

for (var i=41; i<values.length; i++) {
  thisLoopValue = values[i-41]; //On the first iteration, index needs to be zero.  41 - 41 is zero

Google Sheet App Script - For Specific Sheet Hide Rows that are empty then export to PDF

1 Answers