So thats the solution i was i able to find on the internet, with the help of Kelvin and try and error by myself. it works now on my behalf. Because it was so hard to find i wanted to post the final result for somebody who is looking for something like this.
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('Custom Menu')
.addItem('Send summary', 'menuItem1')
.addSeparator()
.addItem('Send summary&Week', 'menuItem2')
.addToUi();
}
function menuItem1() {
var source = SpreadsheetApp.getActiveSpreadsheet();
var subject = source.getSheets()[9].getRange('G1').getValue(); //cell for subject in sheet
var body = source.getSheets()[9].getRange('F1').getValue();
var sheetNumSummary = 9; // first sheet(tab) is zero, second sheet is 1, etc..
var source = SpreadsheetApp.getActiveSpreadsheet();
var thema = source.getSheets()[9].getRange('f3').getValue(); //
var mailTo = ('youremailaddress@here.com'); //source.getSheets() [0].getRange('D1').getValue(); // 'D1' cell which consists an emailaddress.
var name = source.getSheets()[9].getRange('G1').getValue(); // Name of Attachement
var sheets = source.getSheets();
sheets.forEach(function (s, i) {
if (i !== sheetNumSummary) s.hideSheet();
});
var url = Drive.Files.get(source.getId())
.exportLinks['application/pdf'];
url = url + '&size=letter' + //paper size
'&portrait=false' + //orientation, false for landscape
'&fitw=true' + //fit to width, false for actual size
'&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional
'&gridlines=false' + //false = hide gridlines
'&fzr=false'; //do not repeat row headers (frozen rows) on each page
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url, {
headers: {
'Authorization': 'Bearer ' + token
}
});
MailApp.sendEmail(mailTo, subject, body, {
attachments: [response.getBlob().setName(name)]
});
sheets.forEach(function (s) {
s.showSheet();
})
}
function menuItem2() {
var source = SpreadsheetApp.getActiveSpreadsheet();
var subject = source.getSheets()[9].getRange('G1').getValue();
var body = source.getSheets()[9].getRange('F1').getValue();
var sheetNumSummary = 9; // first sheet(tab) is zero, second sheet is 1, etc..
var source = SpreadsheetApp.getActiveSpreadsheet();
var thema = source.getSheets()[9].getRange('f3').getValue(); //
var mailTo = ('youremailaddress@here.com'); //source.getSheets() [0].getRange('D1').getValue(); // 'D1' cell which consists an emailaddress.
var name = source.getSheets()[9].getRange('G1').getValue(); // Name of Attachement
var sheets = source.getSheets();
sheets.forEach(function (s, i) {
if (i !== sheetNumSummary) s.hideSheet();
});
var url = Drive.Files.get(source.getId())
.exportLinks['application/pdf'];
url = url + '&size=letter' + //paper size
'&portrait=false' + //orientation, false for landscape
'&fitw=true' + //fit to width, false for actual size
'&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional
'&gridlines=false' + //false = hide gridlines
'&fzr=false'; //do not repeat row headers (frozen rows) on each page
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url, {
headers: {
'Authorization': 'Bearer ' + token
}
});
MailApp.sendEmail(mailTo, subject, body, {
attachments: [response.getBlob().setName(name)]
});
sheets.forEach(function (s) {
s.showSheet();
})
/*}
function menuItem2() {
*/
var source = SpreadsheetApp.getActiveSpreadsheet();
var subject = source.getSheets()[10].getRange('G1').getValue();
var body = source.getSheets()[10].getRange('F1').getValue(); //bleibt leer, kein Text notwendig
var sheetNumWeek = 10; // first sheet(tab) is zero, second sheet is 1, etc..
var source = SpreadsheetApp.getActiveSpreadsheet();
var thema = source.getSheets()[10].getRange('C1').getValue(); //
var mailTo = ('admin6142.c087880@m.evernote.com, Dwight@ndmarin.com'); //source.getSheets()[0].getRange('D1').getValue(); // 'D1' cell which consists an emailaddress.
var name = source.getSheets()[10].getRange('G1').getValue(); // Name of Attachement
var sheets = source.getSheets();
sheets.forEach(function (s, i) {
if (i !== sheetNumWeek) s.hideSheet();
});
var url = Drive.Files.get(source.getId())
.exportLinks['application/pdf'];
url = url + '&size=letter' + //paper size
'&portrait=false' + //orientation, false for landscape
'&fitw=true' + //fit to width, false for actual size
'&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional// was false
'&gridlines=false' + //false = hide gridlines
'&fzr=false'; //do not repeat row headers (frozen rows) on each page
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url, {
headers: {
'Authorization': 'Bearer ' + token
}
});
MailApp.sendEmail(mailTo, subject, body, {
attachments: [response.getBlob().setName(name)]
});
sheets.forEach(function (s) {
s.showSheet();
})
}
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…