Save PDF file from a certain range of Spreadsheet

Question

My first question here, a little laborious, but it is that I can not solve at all.

Problem: I need to save a pdf file of a particular Spreadsheet in Google drive. This file (pdf) has to cover a certain area, because some columns of the spreadsheet cannot be shown. Lines that do not contain data should not be printed in pdf.

My idea: As some cells contain conditional formatting (vlookup) I chose to hide the columns before printing the pdf. The problem is that I can’t make a script or a condition to just copy the lines that contain pro pdf data.

I thought to make another spreadsheet and send the selected data to her and this in turn generate the pdf, but the cells containing the conditional format do not generate the data.

So far I have it and it works. I just wanted to not print the blank lines in the pdf. I cannot change the format of Spreadsheet, it has to adapt to the content. If someone can give me a light.

function onOpen() {

  SpreadsheetApp
  .getUi()
  .createMenu('ImprimePDF') 
  .addItem('ImprimePDF', 'printpdf')
  .addToUi();

}

//Imprime PDF
//Salva no Google Drive



 function printpdf() {

   //Apaga colunas que nao quero mostrar

   var source = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = source.getActiveSheet();
   sheet.hideColumns(1, 4);
   sheet.hideColumns(17, 2);


   //Gera o PDF e salva no Google drive

  var spreadsheet_id = ('id');
  var spreadsheetFile = DriveApp.getFileById(spreadsheet_id);
  var blob = spreadsheetFile.getAs('application/pdf'); 
  DriveApp.createFile(blob);

   //Mostra as colunas de novo que foram escondidas

   var source = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = source.getSheets()[0];
   sheet.showColumns(1, 4);
   sheet.showColumns(17, 2);

}

I understand a bit of javascript (use more for functions on sites associating with CSS), but using in Google Apps is the first time. I have already consulted several places, I have again taken an online Javascript course, I have already taken the course of Lynda’s Google Apps Script, but this does not enter my mind. I appreciate the help.

Editing the above post, I got a better script. However I still find it very heavy. It takes 12 seconds to finish the execution. But it seems that I managed to solve the problem of the data that I would not like to show in the pdf. If anyone has a better idea (sorry I don’t have an accent, and I use foreign keyboard at work).

   function onOpen() {

  SpreadsheetApp
  .getUi()
  .createMenu('ImprimePDF') 
  .addItem('ImprimePDF', 'printpdf')
  .addToUi();

}

//Imprime PDF
//Salva no Google Drive



 function printpdf() {

   //Apaga colunas que nao quero mostrar

   var source = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = source.getActiveSheet();
   sheet.hideColumns(1, 4);
   sheet.hideColumns(17, 2);

    //esconde as rows sem conteudo
    var sheet = SpreadsheetApp.getActiveSheet();
    var rows = sheet.getDataRange();
    var numRows = rows.getNumRows();
    var values = rows.getValues();

    for (var i=1; i <=numRows -1; i++){
      var row =values[i];var myValue = row[0];if (myValue == ""){
      sheet.hideRows(i+1);

  }

 }

    //esconde as sheets com dados usados na planilha que quero gerar o pdf
    var source = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = source.getSheets()[1];
    sheet.hideSheet();

   //Gera o PDF e salva no Google drive

   var spreadsheet_id = ('id');
   var spreadsheetFile = DriveApp.getFileById(spreadsheet_id);
   var blob = spreadsheetFile.getAs('application/pdf'); 
   DriveApp.createFile(blob);

   //Mostra as colunas de novo que foram escondidas

   var source = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = source.getSheets()[0];
   sheet.showColumns(1, 4);
   sheet.showColumns(17, 2);

}

If anyone has anything simpler, I’d really appreciate it.

Answer 1

The function I mentioned earlier get.LastRow() takes the last row of data, so if there are empty lines inside the spreadsheet, it will not resolve without full scan. But if the problem is hiding rows and columns after the data range, test this function:

function escondeVazio(aba){
  var aba = "comissao";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(aba);
  sheet.activate();
  var ultimalinhadados = sheet.getLastRow();
  var ultimacolunadados = sheet.getLastColumn();
  var ultimalinha = sheet.getMaxRows();
  var ultimacoluna = sheet.getMaxColumns();
  var numerolinhas = ultimalinha - ultimalinhadados;
  var numerocolunas = ultimacoluna - ultimacolunadados;
  if (numerolinhas > 0){
    sheet.hideRows(ultimalinhadados + 1,numerolinhas);
  }
  if (numerocolunas > 0){
    sheet.hideColumns(ultimacolunadados + 1,numerocolunas);
  }
}