Email with Google Script Editor

Question

I know there are two separate email functions in the code but the one I am trying to get to work is the sendsheettopdfwithA2mailaddress.

Every time I try to delete the other one I get an error message so any help with deleting that and everything still work would be greatly appreciated too. The error message is

Exception: Request failed for docs.google.com returned code 401. Truncated server response: <HTML><HEAD>

The main issue that I'm having is that I only want to email one sheet which is the main one or 0. When it sends the email it sends 12 different sheets and sheet 0 is very condensed to about a quarter of the sheet.

/** @OnlyCurrentDoc */
function onEdit(e) {
  var ss = SpreadsheetApp.getActive()          //gets the active spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet()  //gets the active sheet
  var cell = ss.getActiveRange()               //gets the active cell
  var cellContent = cell.getValue()            //gets the value of the active cell
  
  if(cellContent === 'BOXCHECKED') {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRange('B1:H4').activate();
    var currentCell = spreadsheet.getCurrentCell();
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
    currentCell.activateAsCurrentCell();
    currentCell = spreadsheet.getCurrentCell();
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
    currentCell.activateAsCurrentCell();
    spreadsheet.getRange('A1:H55').activate();
    spreadsheet.getRange('A1').activate();
    spreadsheet.getCurrentCell().setFormula('=H5');
    spreadsheet.getRange('H56').activate();
    spreadsheet.insertSheet(1);
    spreadsheet.getRange('\'Quote Sheet\'!A1:H55').copyTo(spreadsheet.getActiveRange(), 
                                                          SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    spreadsheet.getRange('\'Quote Sheet\'!A1:H55').copyTo(spreadsheet.getActiveRange(), 
                                                          SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.getActiveSheet().setHiddenGridlines(true);
    spreadsheet.getActiveSheet().hideSheet();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Data Sheets'), true);
    spreadsheet.getRange('2:35').activate();
    spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 34);
    spreadsheet.getActiveRange().offset(0, 0, 34, 
                                        spreadsheet.getActiveRange().getNumColumns()).activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Quote Sheet'), true);
    spreadsheet.getRange('B11').activate();
    currentCell = spreadsheet.getCurrentCell();
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
    currentCell.activateAsCurrentCell();
    currentCell = spreadsheet.getCurrentCell();
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
    currentCell.activateAsCurrentCell();
    currentCell = spreadsheet.getCurrentCell();
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
    currentCell.activateAsCurrentCell();
    currentCell = spreadsheet.getCurrentCell();
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
    currentCell.activateAsCurrentCell();
    currentCell = spreadsheet.getCurrentCell();
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
    currentCell.activateAsCurrentCell();
    currentCell = spreadsheet.getCurrentCell();
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.UP).activate();
    currentCell.activateAsCurrentCell();
    spreadsheet.getRange('A12:z44').activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Data Sheets'), true);
    spreadsheet.getRange('\'Quote Sheet\'!A12:z44').copyTo(spreadsheet.getActiveRange(), 
                                                           SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Quote Sheet'), true);
    spreadsheet.getRangeList(['C5:C10', 'H6:H10', 'B12:B44','D12:E44', 'H49:H50','B51:E55']).activate();
    spreadsheet.setCurrentCell(spreadsheet.getRange('H49'));
    spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
    spreadsheet.getRange('C5').activate();
    SpreadsheetApp.getActive();
    spreadsheet.getRange('G53').activate();
    spreadsheet.getCurrentCell().setValue('False')
    SpreadsheetApp.getActive();
    spreadsheet.getRange('A1').activate();
    spreadsheet.getCurrentCell().setValue('Quote Sheet')
    SpreadsheetApp.getActive().getSheets().forEach(function (sh) 
                                                   {
                                                     sh.setName(sh.getRange('a1').getValue())
                                                   })
  } 
};

function renameSheets() {
  SpreadsheetApp.getActive().getSheets().forEach(function (sh) {
    sh.setName(sh.getRange('A1').getValue())
  })
};

/* Email Google Spreadsheet as PDF */
function emailGoogleSpreadsheetAsPDF() {
  
  // Send the PDF of the spreadsheet to this email address
  var email = "t.mitchellbivens@gmail.com"; 
  
  // Get the currently active spreadsheet URL (link)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Subject of email message
  var subject = "PDF generated from spreadsheet " + ss.getName(); 
  
  // Email Body can  be HTML too 
  var body = "Install the <a href='http://www.labnol.org/email-sheet'>Email Spreadsheet add-on</a> for 
  one-click conversion.";
  
  var blob = DriveApp.getFileById(ss.getId()).getAs("application/pdf");
  
  blob.setName(ss.getName() + ".pdf");
  
  // If allowed to send emails, send the email with the PDF attachment
  if (MailApp.getRemainingDailyQuota() > 0) 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments:[blob]     
    }); 
};

function sendSheetToPdfwithA2MailAdress() { // this is the function to call
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[0]; // it will send sheet 0 which is the first sheet in the spreadsheet.
  // if you change the number, change it also in the parameters below
  var shName = sh.getName()
  sendSpreadsheetToPdf(0, shName, sh.getRange('A2').getValue()," Test Run ", "Let's Hope This Works");
}

function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId()  
  var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;  
  var url_base = spreadsheet.getUrl().replace(/edit$/,'');
  
  var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
  
  + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 
  // following parameters are optional...
  + '&size=A4'      // paper size
  + '&portrait=true'    // orientation, false for landscape
  + '&fitw=true'        // fit to width, false for actual size
  + '&fith=true'        // fit to height, false for actual size
  + '&sheetnames=true&printtitle=false&pagenumbers=true'  //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_base + url_ext, options);
  var blob = response.getBlob().setName(pdfName + '.pdf');
  if (email) {
    var mailOptions = {
      attachments:blob, htmlBody:htmlbody
    }
    MailApp.sendEmail(
      email, 
      subject+" (" + pdfName +")", 
      "html content only", 
      mailOptions);
  }
};

I would like for it to work under the onEdit function if possible. — Mitchell Bivens, Mar 05 '20 at 20:05
Does the code you have work? I don't understand why you would want to delete `sendSpreadsheetToPdf()` since you're calling it in `sendSheetToPdfwithA2MailAdress()` (thus it's required). So it makes sense that the code breaks if you delete the function. — Diego, Mar 06 '20 at 00:19
The macro works when i run it separately but when i place it in the onEdit function it won't work. — Mitchell Bivens, Mar 06 '20 at 00:23

Jeff Rush · Answer 1 · 2020-03-16T13:47:10.413

The macro works when i run it separately but when i place it in the onEdit function it won't work.

Take a look on Simple Triggers Restrictions

Quote:

They cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization, but a simple trigger can translate a phrase with the Language service, which is anonymous.

If you had used installable triggers instead, you would've had less restrictions (like being able to send emails)

score 0 · Answer 2 · answered Nov 09 '22 at 02:20

From OP's comment in the question, it's clear that an important part of the code comes from a macro created using the Google Sheets Macro Recorder. Using this tool and the mouse to jump from one cell or range to another, creates a lot of .activate() methods which are very slow and it's very likely that this is the causing problems due to the maximum execution limit for simple triggers.

The execution time could be reduced a lot either by recording again the macro but instead of using the mouse use the Go to range or the named range box to select the ranges to be modified or by refactoring the script. See my answer to Service Spreadsheet Time Out - Optimizing Macro script? for details.

Email with Google Script Editor

2 Answers2