gmail - Google Script copy to clipboard and mailto [Questions] -
i have excel spreadsheets writing schedules , sending them crews. involves vba hides columns , saves pdf.
in order me use google-sheets printing pdf, , opening individual emails in gmail seems less efficient. discovered can copy (ctrl+c) range (ex: "a1:e10"), , paste straight gmail (ctrl+v) , looks good.
what press button run script that:
- activates specific range ( did )
- copies clipboard ( can not figure 1 out
- & activates mailto url ( didn't figure out, i'm using =hyperlink(url,name) ).
or
- directly emails sheet formating , range-values
or
- a script either run print dialogue, or save pdf specific google-drive folder.
see here (my public version of 'sheet') 
i new google scripts, familiar vba (and object oriented programming in general exception scripting languages xd)
any or sources, or alternative solutions accomplish same thing helpful.
since google sheets not application running on computer, script capabilities different vba in excel. no access pc's clipboard, one. no triggering of print dialog, another. can things in browser while using sheets, not script.
the straight-forward approach given capabilities of google apps script, though, be:
- change script button call function will...
- build email embedded schedule, and
- send message.
there no need hide or unhide columns way, embedded schedule can built of interesting columns.
sendemail()
you've asked preserve formatting , range-values, here's approach that. sendmail() function operates on active spreadsheet, , reads schedule fixed range on sheet, builds email, , sends email address found on sheet.
for up-to-date code, refer 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 email first. // want schedule within borders, // these 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(); // debugging, send self gmailapp.sendemail(recipient, subject, "requires html", {htmlbody:body}) } gethtmltable()
the sendemail() function relies on gethtmltable(), beginning of general utility render spreadsheet range html table. see github latest version.
caveats:
- it produces way style info presently, result reasonably faithful copy of spreadsheet.
- the general table style, including borders, set in
tableformatvariable. since there no way determine borders in place on spreadsheet, isn't possible transfer them. - numeric formatting can read spreadsheet, not directly adaptable in javascript, numbers aren't rendered appear in spreadsheet.
- dates, likewise. in support of specific question, dates identified , formatted shown in question. beware.
code:
/** * return string containing html table representation * of 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(); // css style attributes 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(); // column widths in pixels var colwidths = []; (var col=startcol; col<=lastcol; col++) { colwidths.push(sheet.getcolumnwidth(col)); } // row heights in pixels var rowheights = []; (var row=startrow; row<=lastrow; row++) { rowheights.push(sheet.getrowheight(row)); } // future consideration... var numberformats = range.getnumberformats(); // build html table, inline styling 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 (col=0;col<colwidths.length;col++) { html.push('<col width="'+colwidths[col]+'">') } // populate rows (row=0;row<data.length;row++) { html.push('<tr height="'+rowheights[row]+'">'); (col=0;col<data[row].length;col++) { // 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: colored grid firefox oddity, think. looks fine in chrome, , html specify black. 
Comments
Post a Comment