How to instantly print marked columns in google sheets with script?

Question

I have file in google sheets(img 1).

i wrote script which is converting marked columns to word (i need to mark only 1st 4 columns becuase its going to be on sticker) (img 2)

https://i.sstatic.net/GnIXo.png [2]

this is the code:

  SpreadsheetApp.getUi()
      .createMenu('Drukowanie odpadów')
      .addItem('Drukuj zazaczone odpady - ZAZNACZ 4 pierwsze kolumny!', 'sprawdz')
      .addToUi();
/**
* @NotOnlyCurrentDoc
*/

function sprawdz() { //zbiera dane z zaznaczenia (4 kolumny) w arkuszu GoogleSheet, uzupełnia GoogleDoc danymi
  
// otworzenie dokumentu w google Doc, do którego będą przenoszone zaznaczone kolumny  
  var doc = DocumentApp.openByUrl("https://docs.google.com/document/d/WORD_ID/edit");
  var body = doc.getBody();
  
  //CZYSZCZENIE ORAZ USTAWIENIE WYMIARÓW ETYKIETY
  body.clear();
  body.setPageHeight(100);
  body.setPageWidth(260);
  body.setMarginLeft(0.1);
  body.setMarginRight(0.1);
  body.setMarginTop(0.01);
  body.setMarginBottom(0.01);
  
  
  
 
 
  
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var selection = sheet.getSelection();
  var data = selection.getActiveRange().getValues();
  

  
  //PIERWSZE UZUPEŁNIENIE DOKUMENTU
  body.getParagraphs()[0].appendText(' {nr_odpadu}                                          {dlugosc}').setFontSize(13);
  body.appendParagraph('   {kolor}').setAlignment(DocumentApp.HorizontalAlignment.LEFT).editAsText().setFontSize(10);
  body.appendParagraph('   {profil}').setAlignment(DocumentApp.HorizontalAlignment.LEFT).editAsText().setFontSize(10);
  body.appendParagraph(' ').setAlignment(DocumentApp.HorizontalAlignment.LEFT).editAsText().setFontSize(35);

 
  
  //PETLA KTORA ZBIERA DANE Z ARKUSZA I UMIESZCZA JE W ODPOWIEDNIM MIEJSCU W DOKUMENCIE
 for (var i = 0 ; i < data.length; i++) {
   
   body.replaceText('{nr_odpadu}', data[i][0]);
   body.replaceText('{profil}', data[i][1]);
   body.replaceText('{kolor}', data[i][2]);
   body.replaceText('{dlugosc}', data[i][3]+'mm');
  
   
   //jeśli dotrzemy do końca listy, to nie ma potrzeby dalszego drukowania
   if (i != data.length-1){
  body.appendParagraph(' {nr_odpadu}                                          {dlugosc}').setAlignment(DocumentApp.HorizontalAlignment.LEFT).editAsText().setFontSize(13); 
  body.appendParagraph('   {kolor}').setAlignment(DocumentApp.HorizontalAlignment.LEFT).editAsText().setFontSize(10);
  body.appendParagraph('   {profil}').setAlignment(DocumentApp.HorizontalAlignment.LEFT).editAsText().setFontSize(10);
  body.appendParagraph(' ').setAlignment(DocumentApp.HorizontalAlignment.LEFT).editAsText().setFontSize(35);
   }
 
 }
}

is there any option to insta print with printer the word document from google sheets script editor?

F.e i mark 4 columns i run the script and it print me the sticker. Im using printer "Zebra GK420t"

Im converting it to word document because when i was converting it to pdf i couldnt change page size to height 100 and weight 260 and i dontk now how to place the things like on screen 2

Or mby there is a way to insta print marked columns without creating word/pdf ?

Martí · Accepted Answer · 2020-12-18 09:24:32Z

The best approach I can think is to make an HTML page with CSS that is prepared to be printed as labels and using the browser for printing. This means making a template and using HtmlService.createTemplateFromFile (see docs) to generate a template, which then is evaluated into a web page. Then using Ui.showModelessDialog (see docs) you show a dialog that can call window.print (see MDN reference) so you can print the contents of the iframe that contains the generated labels.

function printLabels(labels) {
  const labelsPage = HtmlService.createTemplateFromFile('Labels');
  labelsPage.labels = labels;
  SpreadsheetApp.getUi().showModelessDialog(labelsPage.evaluate(), 'Labels');
}

Note that we are using the file Labels. Using another name won't work.

Now we have to make the template (templated HTML docs) of the labels using HTML and CSS which should be designed with printing in mind. Also we need to use JavaScript to trigger the browser's print dialog.

Since printing is hard to implement correctly and debug, and I was motivated enough, I took the time to make a version of something similar that what you have right now. Obviously you'll probably have to change it a bit. I took the sizes from your script but I don't have a label printer —and obviously not your printer— so you'll have to make some tests to ensure that everything works as it should.

<!DOCTYPE html>
<html>
  <head>
    <!-- Basic CSS -->
    <style type="text/css">
      :root {
        font-family: Helvetica, sans-serif;
      }
      
      :root, html, body {
        margin: 0;
        padding: 0;
      }


      article {
        padding: 5pt;
      }
      
      header {
        margin: 0;
        line-height: 1em;
        display: flex;
        justify-content: space-between;
        font-size: 13pt;
      }
      
      p {
        margin: 0.7em;
        padding: 0;
        font-size: 10pt;
      }
    </style>
    
    <!-- CSS to setup the printing -->
    <style type="text/css" media="print">
      @page {
        /* Define the size of the page (label) */
        size: 260pt 100pt;
        
        /* Remove the page information that the browser usually adds */
        margin: 0;
        marks: crop;
      }
      
      body > * {
        /* Make sure there is 1 article per page (label) */
        page-break-after: always;
      }
      
      button {
        /* Hide the print button when actually printing */
        display: none;
      }
    </style>

    <!-- Script that opens the print dialog after the page has completly loaded :) -->
    <script>
      // Show the printing dialog
      window.addEventListener('load', function() {
        window.print();
      })
      // Close after printing
      window.addEventListener('afterprint', function() {
        google.script.host.close();
      });
    </script>
  </head>
  <body>
    <button type="button" onclick="window.print()">Print</button>
    <? for (let label of labels) { ?>
    <article>
      <header>
        <div><?= label[0] ?></div>
        <div><?= label[1] ?>mm</div>
      </header>
      <p><?= label[2] ?></p>
      <p><?= label[3] ?></p>
    </article>
    <? } ?>
  </body>
</html>

Now you can call printLabels with an array of labels to print (in your case the data variable should work). Notice that this will print all the labels given, so if you'd like only print 4, you'should only send 4 to that function.

References and further reading

thanks a lot for your time! i will give it a try :)
– Kubuniunio
Commented Dec 18, 2020 at 11:37 — Kubuniunio, Commented Dec 18, 2020 at 11:37

Collectives™ on Stack Overflow

How to instantly print marked columns in google sheets with script?

1 Answer 1

References and further reading

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

References and further reading

Linked

Related