Save PDF file of a given spreadsheet range

Question

Save PDF file of a given spreadsheet range

Navigation

#1 by (-1 votes)

3

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

Problem: I need to save a pdf file of a given spreadsheet in Google Drive. This file (pdf) must cover a certain area, because some columns of the worksheet can not be displayed. Lines that do not contain data should not be printed in pdf.

My idea: Since some cells contain conditional formatting (vlookup) I opted to hide the columns before starting the pdf. The problem is that I can not make a script or a condition to just copy the lines that contain data to pdf.

I thought of making another spreadsheet and sending the selected data to it and in turn generating the pdf, but the cells containing the conditional formatting do not generate the data.

So far I have this and it works. I just wanted to not print the blank lines in the pdf. I can not 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 bit of javascript (I use more for functions in websites associating with CSS), but using it in Google Apps is the first time. I already consulted several places, I did an online Javascript course again, I already did the Google Apps Script course of Lynda, but that does not enter my head. Thanks for the help.

Editing the above post, I got a better script. But still I find it very heavy. It takes 12 seconds to finish executing. But it seems that I was able to solve the data problem that I would not like to display in the pdf. If anyone has a better idea (sorry to have no 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 someone has something simpler thank you very much.

javascript script google

asked by anonymous 10.06.2015 / 04:44

1 answer

Problem when applying Pop up Jquery on e-commerce platform Looping on an array to always choose the next value

score -1 · Answer 1

The function I mentioned previously get.LastRow() takes the last row of data, so if there are empty rows inside the worksheet, it will not resolve without full scan. But if the problem is to hide the rows and columns after the data range, try 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);
  }
}