Экспорт таблиц Google Sheets в PDF файл

В данной статье я расскажу, как можно экспортировать таблицу Google в PDF файл, в заданном формате, используя Google Script. И еще отправим PDF файл на Google диск и на почту.

UPD 14.03.2021: Скрипт обновлен. Добавлено:

указание пути при сохранении в Google Drive. Функция myExportPDFtoGDrive перед сохранением запрашивает путь. Пример пути: "New Folder/Next Folder", папка "New Folder" находится в корне диска
скачивание с сохранением на Google диск. При скачивании PDF файла есть сложности с изменением его названия, но при сохранении файла сначала на Google Driva и последующим скачиванием можно задать произвольное имя. Пример функция myExportPDFtoGDrive2
прикрепление к письму нескольких файлов. Функция myExportPDFtoEMail2 экспортирует все листы в отдельные PDF файлы и отправляет всё одним письмом
добавил определение параметров разрыва страниц. Для работы разрыва страниц в масштабе должна быть установлена шестерка (6 Выровнять по разрывам страниц)

В Google таблицах есть возможность экспорта в PDF формате. Для это нужно зайти в меню Файл->Скачать->Документ PDF. Чтобы понять, как Google делает экспорт, воспользуемся браузерными инструментами разработчика. Откроем нужную нам таблицу и нажмем сочетание клавиш Ctrl+Shift+I(браузер Оперы или Google Chrome), и перейдем на вкладку Ethernet как на картинке ниже

Теперь сделаем экспорт в PDF через меню Google таблиц и найдем ссылку, которая отвечает за это действие

Нажмем на ссылку и посмотрим параметры, которые передаются на сервер

На сервер передается идентификатор таблицы и три параметра: a, pc, gf. Для чего нужны a и gf я не знаю, а вот параметр pc – это все настройки выборки и форматирования для экспорта Google таблицы в PDF файл

[null,null,null,null,null,null,null,null,null,0,[["923744571"]],10000000,null,null,null,null,null,null,null,null,null,null,null,null,null,null,43745.64856909722,null,null,[0,null,1,0,0,0,0,0,1,1,1,1,null,null,1,1],["A4",1,2,0.26,[0.75,0.75,0.7,0.7]],null,0,null,0]

Немного пришлось по ломать голову над значением 43745.64856909722, но так как я давно работаю с Delphi ответ пришел сам собой. Опытным путем я определил все необходимые настройки и вот что получилось:

[
  null,null,null,null,null,null,null,null,null,0,
	
  /* Источник данных. Передается в функцию экспорта. */
  [ /* массив выборок данных из листов книги. null - вся книга */
    [ /* параметры выборки */
      "1915571227", /* id листа. Если остальные параметры отсутствуют, 
                       то весь текущий лист */
      8, /* номер начальной границы по вертикали */
      13, /* номер конечной границы по вертикали */
      2, /* номер начальной границы по горизонтали */
      7 /* номер конечной границы по горизонтали */
    ]
  ],
	
  10000000,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
	
  /* текущая дата и время */
  43741.528910740744,  
	
  null,null,
	
  /* Опции. Передается в функцию экспорта. */
  [
    0, /* показать заметки
	        0 Нет
	        1 Да */
    null,
    1, /* показать линии сетки
	        0 Да
	        1 Нет */
    0, /* показать номера страниц
	        0 Нет
	        1 Да */
    0, /* показать название книги
	        0 Нет
	        1 Да */
    0, /* показать название листа
	        0 Нет
	        1 Да */
    0, /* показать текущую дату
	        0 Нет
	        1 Да */
    0, /* показать текущее время
	        0 Нет
	        1 Да */
    0, /* повторять закрепленные строки
	        0 Нет
	        1 Да */
    1, /* повторять закрепленные столбцы
	        0 Нет
	        1 Да */
    2, /* порядок страниц
            1 Вниз, затем вверх
            2 Вверх, затем вниз */
    1,
    null,
    null,
    2, /* горизонтальное выравниване
	        1 По левому краю
	        2 По правому краю
	        3 По центру */
    1 /* вертикальное выравнивание
	       1 По верхнему краю
	       2 По центру
	       3 По нижнему краю */
  ],
	
  /* Формат. Передается в функцию экспорта. */
  [
    "A4", /* название формата A4, A3 и тд.
	         или отношение сторон в формате "8.5x13", где 
	           8.5 - ширина(дюймы), 
	           13 - высота(дюймы) */
    0, /* ориентация страницы 
	        0 горизонтальная
	        1 вертикальная */
    2, /* масштаб 
	        1 Обычный (100%)
	        2 Выровнять по ширине
	        3 Выровнять по высоте
	        4 Выровнять по размеру страницы
	        5 Пользовательский масштаб
	        6 Выровнять по разрывам страниц */
    1, /* значение пользовательского масштаба
	      коэффициент %/100, две цыфры после запятой */
    [ /* поля */
      0.75, /* верхний отступ(дюймы)
	             Узкие: 0.75
	             Широкие: 1
                 Обычные: 0.75 */
      0.75, /* нижний отступ(дюймы)
	             Узкие: 0.75
	             Широкие: 1
	             Обычные: 0.75 */
      0.7, /* левый отступ(дюймы)
	            Узкие: 0.25
	            Широкие: 1
	            Обычные: 0.7 */
      0.7 /* правый отступ(дюймы)
	           Узкие: 0.25
	           Широкие: 1
	           Обычные: 0.7 */
    ]
  ],
  null,0,
  [ /* Разрывы страниц, если не используется ставим null
	   Для каждого листа отдельный массив
	   Для работы разрыва страниц в масштабе должна быть 
	     установлена шестерка (6 Выровнять по разрывам страниц) */
    [
	  "1915571227", /* id листа */ 
	  [ /* массив горизонтальных разрывово
	       далее следует список разрывов, где каждый разрыв 
	       представлен массивом из двух одинаковых значений,
	       где значение это номер столбца после которой расположен разрыв */
	    [ 11, 11 ],
	    [ 15, 15 ]
	  ],
	  [ /* массив вертикальных разрывово
	       далее следует список разрывов, где каждый разрыв 
	       представлен массивом из двух одинаковых значений,
	       где значение это номер строки после которой расположен разрыв,
	       где A - первый столбец, B - второй и тд. */
	    [ 4, 4 ],
	    [ 7, 7 ]
	  ]
	]   
	
  ]
  ,0
]

На основе полученных данных можно сделать скрипт для экспорта содержимого Google таблиц в нужном нам формате. Для запуска скрипта сделаем дополнительный пункт меню в триггере onOpen.

/*
  Add custom menu items
*/
function onOpen() {
  var submenu = [
    {name:"Export to local PDF", functionName:"myExportPDF"},
    {name:"Export PDF to Google Drive", functionName:"myExportPDFtoGDrive"},
    {name:"Export PDF to Google Drive and to local PDF file", functionName:"myExportPDFtoGDrive2"},
    {name:"Export single PDF to EMail", functionName:"myExportPDFtoEMail"},
    {name:"Export few PDF to EMail", functionName:"myExportPDFtoEMail2"}
  ];
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Мой экспорт', submenu);  
}

function encodeDate( yy, mm, dd, hh, ii, ss ){
  var days=[31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31];
  if(((yy % 4) == 0) && (((yy % 100) != 0) || ((yy % 400) == 0)))days[1]=29;
  for(var i=0; i<mm; i++) dd += days[ i ];
  yy--;
  return ((((yy * 365 + ((yy-(yy % 4)) / 4) - ((yy-(yy % 100)) / 100) + ((yy-(yy % 400)) / 400) + dd - 693594) * 24 + hh) * 60 + ii) * 60 + ss)/86400.0;
}

function openUrl( url ){
  //Browser.msgBox( '\\\"' );
  var html = HtmlService.createHtmlOutput( '<html><script>' +
    'window.close = function(){window.setTimeout(function(){google.script.host.close()},9)};' +
    'var a = document.createElement("a"); a.href="' + url + '"; a.target="_blank";' +
    'if(document.createEvent){' +
    '  var event=document.createEvent("MouseEvents");' +
    '  if(navigator.userAgent.toLowerCase().indexOf("firefox")>-1){window.document.body.append(a)}' +
    '  event.initEvent("click",true,true); a.dispatchEvent(event);' +
    '}else{ a.click() }' +
    'close();' +
    '</script>' +
    '<body style="word-break:break-word;font-family:sans-serif;">Failed to open automatically. <a href="' + url + '" target="_blank" onclick="window.close()">Click here to proceed</a>.</body>' +
    '<script>google.script.host.setHeight(40);google.script.host.setWidth(410)</script>' +
    '</html>').setWidth( 90 ).setHeight( 1 );
  SpreadsheetApp.getUi().showModalDialog( html, "Opening ..." );
}

function getESID(){
  return ( Math.round( Math.random() * 10000000 ) );
}

function exportPDF( ssID, source, options, format, breaks){
  var dt=new Date();
  var d=encodeDate( dt.getFullYear(), dt.getMonth(), dt.getDate(), dt.getHours(), dt.getMinutes(), dt.getSeconds() );
  var pc=[ null, null, null, null, null, null, null, null, null, 0,
          source,
          10000000, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
          d,
          null, null,
          options,
          format,
          null, 0, breaks, 0 ];
  openUrl( "https://docs.google.com/spreadsheets/d/" + ssID + "/pdf?" +
    "esid=" + getESID() + "&" +
    "id=" + ssID + "&" + 
    "a=true&" +
    "pc=" + encodeURIComponent( JSON.stringify( pc ) ) + "&" +
    "gf=[]&" +
    "lds=[]" );
}

function myExportPDF(){
  var as = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = as.getActiveSheet();
  exportPDF(as.getId(), // Идентификатор таблицы
    [ // source
      /* 
        Export full current page
        Экспортируем всю текущую страницу 
      */
      [ sheet.getSheetId().toString() ] // 
    ],
    [ // options
      0, /* Не показывать заметки (Hide notes) */
      null,
      1, /* Показывать линии сетки ( Show grid ) */
      0, /* Не показывать номера страниц ( Hide page number ) */
      0, /* Не показывать название книги ( Hide spreadsheet title ) */
      0, /* Не показывать название листа ( Hide sheet title ) */
      0, /* Не показывать текущую дату ( Hide current date ) */
      0, /* Не показывать текущее время ( Hide current time ) */
      1, /* Повторять закрепленные строки ( Repeat fixed rows ) */
      1, /* Повторять закрепленные столбцы ( Repeat fixed columns ) */
      1, /* Порядок страниц вниз, затем вверх  ( Page order down then up ) */
      1,
      null,
      null,
      1, /* Выравнивание по левому краю ( Left align ) */
      1 /* Выравнивание по верхнему краю (Top align ) */
    ],
    [ // format
      "A4", /* Фрмат листа A4 ( A4 sheet format ) */
      1, /* Ориентация страницы вертикальная ( Page orientation vertical ) */ 
      6, /* Выровнять по разрывам страниц ( Align to page breaks ) */
      1,
      [
        0.75, /* Отступ сверху 0.75 дюймов ( Top offset 0.75 inches ) */
        0.75, /* Отступ снизу 0.75 дюймов ( Bottom offset 0.75 inches ) */
        0.7, /* Отступ слева 0.7 дюйма ( Left offset 0.7 inches ) */
        0.7 /* Отступ справа 0.7 дюйма ( Right offset 0.7 inches ) */
      ]
    ],
    [ // breaks
      [
        sheet.getSheetId().toString(),
        [ [ 12, 12 ] ],
        [ [ 2, 2 ] ]
      ]

    ]
  );
}

function getFolder( path ){
  var folder = null;
  var parents = DriveApp.getFileById( SpreadsheetApp.getActiveSpreadsheet().getId() ).getParents();
  if( parents.hasNext() ) folder = parents.next();
  else folder = DriveApp.getRootFolder();

  if( path.charAt(0) == '/' ) path = path.substr( 1, path.length - 1 );
  if( path.charAt( path.length - 1 ) == '/' ) path = path.substr( 0, path.length - 1 );
  var folders = path.split( '/' );

  if(!folders)return folder;

  var fldrs = null, id;
  for( var i = 0; i < folders.length; i++ ){
    id = folder.getId();
    fldrs = folder.getFoldersByName( folders[ i ] );
    while( fldrs.hasNext() ){
      folder = fldrs.next();
      if( folder.getParents().next().getId() == id )break;
    }
    if( id == folder.getId() ) return null;
  }
  return folder;
}

function exportPDFtoGDrive( folder, ssID, filename, source, options, format, breaks ){
  var dt = new Date();
  var d = encodeDate( dt.getFullYear(), dt.getMonth(), dt.getDate(), dt.getHours(), dt.getMinutes(), dt.getSeconds() );
  var pc=[ null, null, null, null, null, null, null, null, null, 0,
          source,
          10000000, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
          d,
          null, null,
          options,
          format,
          null, 0, breaks ,0 ];

  var options = {
    'method': 'post',
    'payload': "a=true&pc=" + JSON.stringify(pc) + "&gf=[]&lds=[]",
    'headers': {Authorization: "Bearer " + ScriptApp.getOAuthToken()},
    'muteHttpExceptions': true
  };
  var theBlob = UrlFetchApp.fetch( "https://docs.google.com/spreadsheets/d/" + ssID + "/pdf?" +
    "esid=" + getESID() + "&" +
    "id=" + ssID, options ).getBlob();

  return folder.createFile( theBlob ).setName( filename + ".pdf" );
}

function myExportPDFtoGDrive(){
  var folderPath = Browser.inputBox( "Set Google Drive Path", Browser.Buttons.OK_CANCEL );
  var folder = getFolder( folderPath );
  var as = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = as.getActiveSheet();
  var filename = "TestPDFExport";
  var file = exportPDFtoGDrive(
    folder,
    as.getId(),  // Идентификатор таблицы
    filename,    // Имя файла
    [ [ sheet.getSheetId().toString() ] ],
    [ 0, null, 1, 0, 0, 0, 0, 0, 1, 1, 1, 1, null, null, 1, 1 ],
    [ "A4", 1, 2, 1, [ 0.75, 0.75, 0.7, 0.7 ] ],
    null
  );
}

function myExportPDFtoGDrive2(){
  var folderPath = Browser.inputBox( "Set Google Drive Path", Browser.Buttons.OK_CANCEL );
  var folder = getFolder( folderPath );
  var as = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = as.getActiveSheet();
  var filename = "TestPDFExport";
  var file = exportPDFtoGDrive(
    folder,
    as.getId(),  // Идентификатор таблицы
    filename,    // Имя файла
    [ [ sheet.getSheetId().toString() ] ],
    [ 0, null, 1, 0, 0, 0, 0, 0, 1, 1, 1, 1, null, null, 1, 1 ],
    [ "A4", 1, 2, 1, [ 0.75, 0.75, 0.7, 0.7 ] ],
    null
  );
  openUrl( file.getDownloadUrl() );
}

function exportPDFtoEMail( ssID, email, files, subject, message, sources, options, formats, breaks){
  var dt=new Date();
  var d=encodeDate(dt.getFullYear(),dt.getMonth(),dt.getDate(),dt.getHours(),dt.getMinutes(),dt.getSeconds());
  if( !Array.isArray( files ) ){
    files = [ files ];
    sources = [ sources ];
    options = [ options ];
    formats = [ formats ];
    breaks = [ breaks ];
  }
  var blobs = [];
  for( var i = 0; i < files.length; i++){

    var pc=[ null, null, null, null, null, null, null, null, null, 0,
          sources[ i ],
          10000000, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
          d,
          null, null,
          options[ i ],
          formats[ i ],
          null, 0, breaks[ i ], 0 ];

    var options = {
      'method': 'post',
      'payload': "a=true&pc=" + JSON.stringify(pc) + "&gf=[]&lds=[]",
      'headers': { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
      'muteHttpExceptions': true
    };
    var theBlob = UrlFetchApp.fetch( "https://docs.google.com/spreadsheets/d/" + ssID + "/pdf?" + 
      "esid=" + getESID() + "&" +
      "id=" + ssID, options).getBlob();
    theBlob.setName( files[ i ] + ".pdf" );
    blobs.push( theBlob );
  }
  if( blobs.length == 1 ) blobs = blobs[ 0 ];
  MailApp.sendEmail( email, subject, message, { 'attachments': blobs } );
}

function myExportPDFtoEMail(){
  var email = Browser.inputBox( "Input E-mail address", Browser.Buttons.OK_CANCEL );
  if( email == 'cancel' ) return false;
  var as = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = as.getActiveSheet();
  var filename = as.getName()+" "+sheet.getName();
  exportPDFtoEMail(
    as.getId(), 
    email,      
    filename,   
    'Mail subject',
    'Mail content',
    [ [ sheet.getSheetId().toString() ] ],
    [ 0, null, 1, 0, 0, 0, 0, 0, 1, 1, 1, 1, null, null, 1, 1 ],
    [ "A4", 1, 2, 1, [ 0.75, 0.75, 0.7, 0.7 ] ],
    null
  );
}

function myExportPDFtoEMail2(){
  var email = Browser.inputBox( "Input E-mail address", Browser.Buttons.OK_CANCEL );
  if( email == 'cancel' ) return false;
  var as = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = [], files = [], options = [], formats = [], breaks = [];
  for( var i = 0; i < as.getNumSheets(); i++ ){
    sheets.push( [ [ as.getSheets()[ i ].getSheetId().toString() ] ] );
    files.push( as.getName() + " " + as.getSheets()[ i ].getName() );
    options.push( [ 0, null, 1, 0, 0, 0, 0, 0, 1, 1, 1, 1, null, null, 1, 1 ] );
    formats.push( [ "A4", 1, 2, 1, [ 0.75, 0.75, 0.7, 0.7 ] ] );
    breaks.push( null );
  }
  exportPDFtoEMail(
    as.getId(), 
    email,      
    files,   
    'Mail subject',
    'Mail content',
    sheets,
    options,
    formats,
    breaks
  );
}

Функции:

exportPDF – скачивание PDF файла
myExportPDF – пример использование exportPDF, с использованием разрыва
exportPDFtoGDrive – отправка файла в Google Drive c указанием пути сохранения
myExportPDFtoGDrive – пример использование exportPDFtoGDrive
myExportPDFtoGDrive2 – пример использование exportPDFtoGDrive, с последующим скачиванием файла
exportPDFtoEMail – отправка файла на электронную почту
myExportPDFtoEMail – пример использование exportPDFtoEMail
myExportPDFtoEMail2 – пример использование exportPDFtoEMail, экспортирует все листы в отдельные PDF файлы и отправляет их одним письмом

Вход

Комментировать

Вначале новые

Всего комментариев:

Andrey Tolstunov

29.03.2021 09:46

Здравствуйте Руслан!
Спасибо что поделились наработками, много времени сэкономит.
Подскажите пожалуйста, как сделать так, чтобы письма, автоматически отправляемые по расписанию (раз в неделю) на почте шли одной веткой, а не по отдельности. Пробовал указывать в теме письма "Re: ", но это не работает.

Ну и чтобы поддержать сообщество прикладываю решение для конвертации документа одновременно в пдф и excel и отправка двух файлов одним письмом по расписанию. Название файлов берётся в соответствии с содержимым ячейки.

function exportXLSXtoEMail( ssID, email, files, subject, message, sources, options, formats, breaks){
  var dt=new Date();
  var d=encodeDate(dt.getFullYear(),dt.getMonth(),dt.getDate(),dt.getHours(),dt.getMinutes(),dt.getSeconds());
  if( !Array.isArray( files ) ){
    files = [ files ];
    sources = [ sources ];
    options = [ options ];
    formats = [ formats ];
    breaks = [ breaks ];
  }
  var as = SpreadsheetApp.getActiveSpreadsheet();
//значение из ячейки для переименования файла
  var cellvalue = as.getSheetByName("sheet3").getRange(1, 8).getValue();
  var blobs = [];
  for( var i = 0; i < files.length; i++){

    var pc=[ null, null, null, null, null, null, null, null, null, 0,
          sources[ i ],
          10000000, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
          d,
          null, null,
          options[ i ],
          formats[ i ],
          null, 0, breaks[ i ], 0 ];

    var options = {
      'method': 'post',
      'payload': "a=true&pc=" + JSON.stringify(pc) + "&gf=[]&lds=[]",
      'headers': { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
      'muteHttpExceptions': true
    };
    var theBlob = UrlFetchApp.fetch( "https://docs.google.com/spreadsheets/d/" + ssID + "/pdf?" + 
      "esid=" + getESID() + "&" +
      "id=" + ssID, options).getBlob();
//переименование файла на основании значения из ячейки
    theBlob.setName( /*files[ i ]*/ "Week 21" + cellvalue + " report" + ".pdf" );
    blobs.push( theBlob );
  }

  for( var i = 0; i < files.length; i++){
    var options = {
      'method': 'post',
      'payload': "a=true&pc=" + JSON.stringify(pc) + "&gf=[]&lds=[]",
      'headers': { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
      'muteHttpExceptions': true
    };
    var theBlob2 = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/"+ssID+"/export?format=xlsx&id="+ssID, options).getBlob();
//переименование файла на основании значения из ячейки
    theBlob2.setName( /*files[ i ]*/ "Week 21" + cellvalue + " report" + ".xlsx" );
    blobs.push( theBlob2 );
  }
  if( blobs.length == 1 ) blobs = blobs[ 0 ];
  MailApp.sendEmail( email, subject, message, { 'attachments': blobs} );
}

function exportPDFandXLSXtoEMail(){
  var as = SpreadsheetApp.getActiveSpreadsheet();
//листы для экспорта
  var sheet1 = as.getSheetByName("sheet1");
  var sheet2 = as.getSheetByName("sheet2");
  var sheet3 = as.getSheetByName("sheet3");
//список ящиков для рассылки
  var emails = 't****а@mail.ru' + "," + 'm****a@mail.ru' + "," + 'y****n@mail.ru' + "," + 'm****n@mail.ru' + "," + 'k****n@mail.ru';
//автоматически открыть скрытые столбцы на основании значения из ячейки
  var columnstoshow = 15 + as.getSheetByName("sheet3").getRange(1, 8).getValue(); 
  sheet1.showColumns(1, columnstoshow);
  exportXLSXtoEMail(
    as.getId(), 
    emails,
    as.getName(),
    'Weekly report',
    'Weekly report in attachment. This report is generated automatically.\nВо вложении еженедельный отчёт по ремонтам. Отчёт формируется автоматически.',
    [ [sheet1.getSheetId().toString()],[sheet2.getSheetId().toString()],[sheet3.getSheetId().toString()]  ],
    [0,null,1,0,0,0,0,0,1,1,2,1,null,null,2,2],
    ["A4",0,4,1,[0.75,0.75,0.25,0.25]],
    null
  );
}

Показать полностью

Поиск по сайту

Экспорт таблиц Google Sheets в PDF файл

Новости сайта

Популярные статьи

Популярные загрузки