function onOpen() { var ui = SpreadsheetApp.getUi(); // Or DocumentApp or FormApp. ui.createMenu('SVerB Menu') .addItem('Backup maken', 'manualBackup') .addItem('Maak PDFs', 'savePDFs') .addItem('Verstuur PDFs naar P.Eijs', 'emailSpreadsheetAsPDFPE') .addItem('Verstuur PDFs naar P.Franke', 'emailSpreadsheetAsPDFPF') .addItem('Verberg lege items [Duurt even]', 'verbergLeeg') .addItem('Toon lege regels', 'showAllRows') .addSeparator() .addSubMenu(ui.createMenu('Sorteren') .addItem('Sorteer Achternaam', 'menuItem2') .addItem('sorteer Postcode', 'menuItem3') .addItem('sorteer Plaats', 'menuItem4') .addItem('sorteer Nieuwsbrief', 'menuItem5') .addItem('sorteer Jarig', 'menuItem6')) .addToUi(); } function menuItem1() { SpreadsheetApp.getUi() // Or DocumentApp or FormApp. .alert('You clicked the first menu item!'); } function menuItem2() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var editedCell = sheet.getActiveCell(); var columnToSortBy = 4; var tableRange = "B2:V500"; // What to sort. { var range = sheet.getRange(tableRange); range.sort( { column : columnToSortBy, ascending: true } ); } } function menuItem3() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var editedCell = sheet.getActiveCell(); var columnToSortBy = 7; var tableRange = "B2:V500"; // What to sort. { var range = sheet.getRange(tableRange); range.sort( { column : columnToSortBy, ascending: true } ); } } function menuItem4(){ var sheet = SpreadsheetApp.getActiveSpreadsheet(); var editedCell = sheet.getActiveCell(); var columnToSortBy = 8; var tableRange = "B2:V500"; // What to sort. { var range = sheet.getRange(tableRange); range.sort( { column : columnToSortBy, ascending: true } ); } } function menuItem5(){ var sheet = SpreadsheetApp.getActiveSpreadsheet(); var editedCell = sheet.getActiveCell(); var columnToSortBy = 11; var tableRange = "B2:V500"; // What to sort. { var range = sheet.getRange(tableRange); range.sort( { column : columnToSortBy, ascending: true } ); } } function menuItem6(){ var sheet = SpreadsheetApp.getActiveSpreadsheet(); var editedCell = sheet.getActiveCell(); var columnToSortBy = 15; var tableRange = "B2:V500"; // What to sort. { var range = sheet.getRange(tableRange); range.sort( { column : columnToSortBy, ascending: true } ); } } // start - bron: http://stackoverflow.com/questions/30367547/convert-all-sheets-to-pdf-with-google-apps-script /** * Export one or all sheets in a spreadsheet as PDF files on user's Google Drive, * in same folder that contained original spreadsheet. * * Adapted from https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579#c25 * * @param {String} optSSId (optional) ID of spreadsheet to export. * If not provided, script assumes it is * sheet-bound and opens the active spreadsheet. * @param {String} optSheetId (optional) ID of single sheet to export. * If not provided, all sheets will export. */ function savePDFs( optSSId, optSheetId ) { // If a sheet ID was provided, open that sheet, otherwise assume script is // sheet-bound, and open the active spreadsheet. var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet(); // Get URL of spreadsheet, and remove the trailing 'edit' var url = ss.getUrl().replace(/edit$/,''); // Get folder containing spreadsheet, for later export var parents = DriveApp.getFileById(ss.getId()).getParents(); if (parents.hasNext()) { var folder = parents.next(); } else { folder = DriveApp.getRootFolder(); } // Get array of all sheets in spreadsheet var sheets = ss.getSheets(); // Loop through all sheets, generating PDF files. for (var i=0; i>"); var ss = SpreadsheetApp.getActiveSpreadsheet(); // Email Body can be HTML too with your logo image - see ctrlq.org/html-mail var body = "PDF generated using code at ctrlq.org from sheet " + ss.getName(); var url = ss.getUrl(); url = url.replace(/edit$/,''); /* Specify PDF export parameters // From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579 exportFormat = pdf / csv / xls / xlsx gridlines = true / false printtitle = true (1) / false (0) size = legal / letter/ A4 fzr (repeat frozen rows) = true / false portrait = true (1) / false (0) fitw (fit to page width) = true (1) / false (0) add gid if to export a particular sheet - 0, 1, 2,.. */ var url_ext = 'export?exportFormat=pdf&format=pdf' // export as pdf + '&size=letter' // paper size + '&portrait=false' // orientation, false for landscape + '&fitw=true&source=labnol' // fit to width, false for actual size + '&sheetnames=false&printtitle=false' // hide optional headers and footers + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines + '&fzr=false' // do not repeat row headers (frozen rows) on each page + '&gid='; // the sheet's Id var token = ScriptApp.getOAuthToken(); var sheets = ss.getSheets(); //make an empty array to hold your fetched blobs var blobs = []; for (var i=0; i 0) GmailApp.sendEmail(email, subject, body, {attachments:[zipBlob]}); } // e-mail PDFs naar Paula Eijs /* Send Spreadsheet in an email as PDF, automatically */ function emailSpreadsheetAsPDFPE() { // Send the PDF of the spreadsheet to this email address var email = "pceijs@hotmail.com"; // Subject of email message // The date time string can be formatted in your timezone using Utilities.formatDate method var subject = "PDF Reports - " + (new Date()).toString(); // Get the currently active spreadsheet URL (link) // Or use SpreadsheetApp.openByUrl("<>"); var ss = SpreadsheetApp.getActiveSpreadsheet(); // Email Body can be HTML too with your logo image - see ctrlq.org/html-mail var body = "PDF generated using code at ctrlq.org from sheet " + ss.getName(); var url = ss.getUrl(); url = url.replace(/edit$/,''); /* Specify PDF export parameters // From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579 exportFormat = pdf / csv / xls / xlsx gridlines = true / false printtitle = true (1) / false (0) size = legal / letter/ A4 fzr (repeat frozen rows) = true / false portrait = true (1) / false (0) fitw (fit to page width) = true (1) / false (0) add gid if to export a particular sheet - 0, 1, 2,.. */ var url_ext = 'export?exportFormat=pdf&format=pdf' // export as pdf + '&size=letter' // paper size + '&portrait=false' // orientation, false for landscape + '&fitw=true&source=labnol' // fit to width, false for actual size + '&sheetnames=false&printtitle=false' // hide optional headers and footers + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines + '&fzr=false' // do not repeat row headers (frozen rows) on each page + '&gid='; // the sheet's Id var token = ScriptApp.getOAuthToken(); var sheets = ss.getSheets(); //make an empty array to hold your fetched blobs var blobs = []; for (var i=0; i 0) GmailApp.sendEmail(email, subject, body, {attachments:[zipBlob]}); } var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var range = sheet.getRange(1, 1, sheet.getLastRow()); function verbergLeeg() { //add menu called Visibility onOpen //ss.addMenu("Visibility", [{ // name: "Show All Rows", functionName: "showAllRows" // }]); //get the values to those rows var values = range.getValues(); //go through every row for (var i=0; i