javascript – Save a PDF file from a certain range of the spreadsheet

Question:

My first question here, a little laborious, but I can't solve it at all.

Problem: I need to save a pdf file from a certain spreadsheet to Google drive. This file (pdf) must cover a certain area as some columns of the spreadsheet cannot be displayed. Lines that do not contain data should not be printed in the 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 data to the pdf.

I thought about making another spreadsheet and sending the selected data to it and this in turn generates the pdf, but the cells that contain the conditional formatting do not generate the data.

So far I have it and it works. I just wanted not to print the blank lines in the pdf. I cannot change the format of the spreadsheet, it has to adapt to the content. If anyone 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 little bit of javascript (I use it more for functions on websites associating with CSS), but using it in Google Apps is the first time. I've already consulted several places, I've taken an online Javascript course again, I've already taken Lynda's Google Apps Script course, but that doesn't enter my mind. I appreciate the help.

Editing the above post, I got a better script. But I still think it's too heavy. It takes 12 seconds to finish running. 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 that I use a 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 something simpler I would be very grateful.

Answer:

The get.LastRow() function I mentioned earlier takes the last row of data, so if there are empty rows inside the spreadsheet, it won't resolve without full raster. But if the problem is hiding the 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);
  }
}
Scroll to Top