Change Sheet 's color after printing

Question

I want to have a script/ print button that, after i print the page, it will change the sheet color from red to green/ or else.

Because, my team create many bills, but we don't know which bill is printed to send. Is there any way to know which bills is printed out.

Answer 1

Implementing custom printing functions for Google Sheets is quite a tricky topic. There is no way of setting a trigger to run when the print function is executed.

Adapted from this Stackoverflow answer on how to print a Range in a Sheet, you could implement the code below:

var PRINT_OPTIONS = {
  'size': 7,               // paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B
  'fzr': false,            // repeat row headers
  'portrait': true,        // false=landscape
  'fitw': true,            // fit window or actual size
  'gridlines': false,      // show gridlines
  'printtitle': false,
  'sheetnames': false,
  'pagenum': 'UNDEFINED',  // CENTER = show page numbers / UNDEFINED = do not show
  'attachment': false
}

var PDF_OPTS = objectToQueryString(PRINT_OPTIONS);

function onOpen(e) {
  SpreadsheetApp.getUi().createMenu('Print...').addItem('Print sheet', 'printSheet').addToUi();
}

function printSheet() {
  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();

  var gid = sheet.getSheetId();
  var printRange = objectToQueryString({
    'c1': range.getColumn() - 1,
    'r1': range.getRow() - 1,
    'c2': range.getColumn() + range.getWidth() - 1,
    'r2': range.getRow() + range.getHeight() - 1
  });
  var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' + PDF_OPTS + printRange + "&gid=" + gid;

  var htmlTemplate = HtmlService.createTemplateFromFile('js');
  htmlTemplate.url = url;
  SpreadsheetApp.getUi().showModalDialog(htmlTemplate.evaluate().setHeight(10).setWidth(100), 'Print range');

  sheet.setTabColor("ff0000"); // change tab color to red after sheet has been printed
}

function objectToQueryString(obj) {
  return Object.keys(obj).map(function(key) {
    return Utilities.formatString('&%s=%s', key, obj[key]);
  }).join('');
}

You will also need to create an HTML file in your project. You can do that by clicking on File>New>HTML File within the editor, setting its name to js, and pasting the following content into it:

<script>
  window.open('<?=url?>', '_blank', 'width=800, height=600');
  google.script.host.close();
</script>

All this together will create a menu item on top of your Sheets UI, that when you click on it, will bring you to a PDF-export of your Sheet from which you will be able to print your Sheet. Furthermore, it will set the tab color to red, to represent that the Sheet has already been printed.