Google Script copy to clipboard and mailto [Questions]

Question

I have Excel spreadsheets for writing schedules and sending them to crews. This involves vba which hides columns and saves as a PDF.

In order for me to use Google-Sheets printing to PDF, and opening individual emails in Gmail seems less efficient. I discovered you can copy (ctrl+c) a range (ex: "A1:E10"), and paste straight into Gmail (ctrl+v) and it looks just as good.

What I would like to do is press a button to run a script that:

1. Activates a specific range ( I already did this )
2. Copies it to clipboard ( I can not figure this one out
3. & activates a mailto URL ( I didn't figure that out, but I'm using =hyperlink(url,name) ).

or

• Directly emails from sheet with the formating and range-values

or

• A script to either run the print dialogue, or save as a PDF to a specific Google-Drive folder.

See here (my public version of this 'sheet')

I am new to Google Scripts, but familiar with VBA (and object oriented programming in general with exception to scripting languages XD)

Any help or sources, or alternative solutions to accomplish the same thing would be very helpful.

Answer 1

Since Google Sheets is not an application running on your computer, its script capabilities are very different from VBA in Excel. No access to your PC's clipboard, for one. No triggering of a print dialog, for another. You can do those things in a browser while using Sheets, but not from a script.

The most straight-forward approach given the capabilities of Google Apps Script, though, will be:

• Change your script button to call a function that will...
• Build email with the embedded schedule, and
• Send the message.

There is no need to hide or unhide columns this way, as the embedded schedule can be built of only the interesting columns.

sendEmail()

You've asked to preserve formatting and range-values, so here's an approach that will do that. The sendMail() function operates on the active spreadsheet, and reads the schedule from a fixed range on that sheet, builds an email, and sends it to the email address found on that sheet.

For the most up-to-date code, refer to this library in Github.

function sendEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var recipient = sheet.getRange("I4").getValue(); // "TO" email address
  var subject = Utilities.formatDate(
                  sheet.getRange("E2").getValue(),
                  ss.getSpreadsheetTimeZone(),
                  "MMM d EEE");
  var schedRange = sheet.getRange("B5:G26");

  // Put Name & Date into email first.
  // We only want the schedule within borders, so
  // these are handled separately.
  var body = '<div style="text-align:center;display: inline-block;font-family: arial,sans,sans-serif">'
  body += '<H1>'+ sheet.getRange("E1").getValue() +'</H1>';
  body += '<H2>'
        + Utilities.formatDate(
            sheet.getRange("E2").getValue(),
            ss.getSpreadsheetTimeZone(),
            "EEEEE, MMMMM d, yyyy")
        + '</H2>';
  body += getHtmlTable(schedRange);
  body += '</div>';
  debugger;

  recipient = Session.getActiveUser().getEmail();  // For debugging, send only to self
  GmailApp.sendEmail(recipient, subject, "Requires HTML", {htmlBody:body})
}

getHtmlTable()

The sendEmail() function relies on getHtmlTable(), which is the beginning of a general utility to render a spreadsheet range as an HTML table. See github for the latest version.

Caveats:

• It produces WAY too much style info presently, but the result is a reasonably faithful copy of the spreadsheet.
• The general table style, including borders, is set in the tableFormat variable. Since there is no way to determine what borders are in place on a spreadsheet, it isn't possible to transfer them.
• Numeric formatting can be read from a spreadsheet, but is not directly adaptable in Javascript, so numbers aren't rendered as they appear in the spreadsheet.
• Dates, likewise. In support of this specific question, dates will be identified and formatted as shown in the question. Beware.

Code:

/**
 * Return a string containing an HTML table representation
 * of the given range, preserving style settings.
 */
function getHtmlTable(range){
  var ss = range.getSheet().getParent();
  var sheet = range.getSheet();
  startRow = range.getRow();
  startCol = range.getColumn();
  lastRow = range.getLastRow();
  lastCol = range.getLastColumn();

  // Read table contents
  var data = range.getValues();

  // Get css style attributes from range
  var fontColors = range.getFontColors();
  var backgrounds = range.getBackgrounds();
  var fontFamilies = range.getFontFamilies();
  var fontSizes = range.getFontSizes();
  var fontLines = range.getFontLines();
  var fontWeights = range.getFontWeights();
  var horizontalAlignments = range.getHorizontalAlignments();
  var verticalAlignments = range.getVerticalAlignments();

  // Get column widths in pixels
  var colWidths = [];
  for (var col=startCol; col<=lastCol; col++) { 
    colWidths.push(sheet.getColumnWidth(col));
  }
  // Get Row heights in pixels
  var rowHeights = [];
  for (var row=startRow; row<=lastRow; row++) { 
    rowHeights.push(sheet.getRowHeight(row));
  }

  // Future consideration...
  var numberFormats = range.getNumberFormats();

  // Build HTML Table, with inline styling for each cell
  var tableFormat = 'style="border:1px solid black;border-collapse:collapse;text-align:center" border = 1 cellpadding = 5';
  var html = ['<table '+tableFormat+'>'];
  // Column widths appear outside of table rows
  for (col=0;col<colWidths.length;col++) {
    html.push('<col width="'+colWidths[col]+'">')
  }
  // Populate rows
  for (row=0;row<data.length;row++) {
    html.push('<tr height="'+rowHeights[row]+'">');
    for (col=0;col<data[row].length;col++) {
      // Get formatted data
      var cellText = data[row][col];
      if (cellText instanceof Date) {
        cellText = Utilities.formatDate(
                     cellText,
                     ss.getSpreadsheetTimeZone(),
                     'MMM/d EEE');
      }
      var style = 'style="'
                + 'color: ' + fontColors[row][col]+'; '
                + 'font-family: ' + fontFamilies[row][col]+'; '
                + 'font-size: ' + fontSizes[row][col]+'; '
                + 'font-weight: ' + fontWeights[row][col]+'; '
                + 'background-color: ' + backgrounds[row][col]+'; '
                + 'text-align: ' + horizontalAlignments[row][col]+'; '
                + 'vertical-align: ' + verticalAlignments[row][col]+'; '
                +'"';
      html.push('<td ' + style + '>'
                +cellText
                +'</td>');
    }
    html.push('</tr>');
  }
  html.push('</table>');

  return html.join('');
}

Email Example

^{_{PS: The colored grid is a firefox oddity, I think. Looks fine in Chrome, and the HTML does specify black.}}