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<sheets.length; i++) {
    var sheet = sheets[i];

    // If provided a optSheetId, only save it.
    if (optSheetId && optSheetId !== sheet.getSheetId()) continue; 

    //additional parameters for exporting the sheet as a pdf
    var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
        + '&gid=' + sheet.getSheetId()   //the sheet's Id
        // following parameters are optional...
        + '&size=letter'      // paper size
        + '&portrait=true'    // orientation, false for landscape
        + '&fitw=true'        // fit to width, false for actual size
        + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
        + '&gridlines=false'  // hide gridlines
        + '&fzr=false';       // do not repeat row headers (frozen rows) on each page

    var options = {
      headers: {
        'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()
      }
    }

    var response = UrlFetchApp.fetch(url + url_ext, options);

    var blob = response.getBlob().setName(ss.getName() + ' - ' + sheet.getName() + '.pdf');

    //from here you should be able to use and manipulate the blob to send and email or create a file per usual.
    //In this example, I save the pdf to drive
    folder.createFile(blob);
  }
}

// drive folder en file id nog aanpassen als deze verhuisd naar ander account met LINK Ophalen
function manualBackup() {
  var folder = DriveApp.getFolderById("0B2mWIIplnNWjUmFVNDBsQUphNlU");
  var file = DriveApp.getFileById("1WL10d4cnfZc64UpS539WNC3xm_7qJ8ZZdfcbop1dgls");
  var name = file.getName() + "_Backup_Copy";
  file.makeCopy(name ,folder);
}

// e-mail PDFs in een ZIP file naar PeterFranke
// https://productforums.google.com/forum/#!topic/docs/kMRj02mJwEU //
/* Send Spreadsheet in an email as PDF, automatically */
function emailSpreadsheetAsPDFPF() {
      
  // Send the PDF of the spreadsheet to this email address
  var email = "peter@peterfranke.nl"; 
 
  // 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("<<SPREADSHEET URL>>");
  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<sheets.length; i++) {
    
    // Convert individual worksheets to PDF
    var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });
 
    //convert the response to a blob and store in our array
    blobs[i] = response.getBlob().setName(sheets[i].getName() + '.pdf');
 
  }
  
  //create new blob that is a zip file containing our blob array
  var zipBlob = Utilities.zip(blobs).setName(ss.getName() + '.zip'); 
  
  //optional: save the file to the root folder of Google Drive
  DriveApp.createFile(zipBlob);
  
  // Define the scope
  Logger.log("Storage Space used: " + DriveApp.getStorageUsed());
 
  // If allowed to send emails, send the email with the PDF attachment
  if (MailApp.getRemainingDailyQuota() > 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("<<SPREADSHEET URL>>");
  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<sheets.length; i++) {
    
    // Convert individual worksheets to PDF
    var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });
 
    //convert the response to a blob and store in our array
    blobs[i] = response.getBlob().setName(sheets[i].getName() + '.pdf');
 
  }
  
  //create new blob that is a zip file containing our blob array
  var zipBlob = Utilities.zip(blobs).setName(ss.getName() + '.zip'); 
  
  //optional: save the file to the root folder of Google Drive
  DriveApp.createFile(zipBlob);
  
  // Define the scope
  Logger.log("Storage Space used: " + DriveApp.getStorageUsed());
 
  // If allowed to send emails, send the email with the PDF attachment
  if (MailApp.getRemainingDailyQuota() > 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<values.length; i++){

        //if row value is equal to empty  
        if(values[i][0] === ""){

        //hide that row
        sheet.hideRows(i+1);   
        }   
    }
}

function showAllRows(){
    sheet.showRows(1,sheet.getLastRow());
}