How to get email address from cell in Google Sheet / Google Script while sending Sheet as PDF

This script works for me but I need get email from value in exactly cell. My question is how to replace email from this script "example@mail.com" with email from specific Sheet cell, for example "Invoice!A2"

function emailPDF() {
    let token = ScriptApp.getOAuthToken();
    let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    spreadsheet.getSheetByName("Invoice");
    let spreadsheetId = spreadsheet.getId();
    let sheetId = spreadsheet.getSheetByName("Invoice").getSheetId();
    let sheetUrl = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?" + "format=xlsx" + "&gid=" + sheetId + "&portrait=true" + "&exportFormat=pdf";
    let request = UrlFetchApp.fetch(sheetUrl, {
        headers: {
            'Authorization': 'Bearer ' + token
        }
    });
    let content = request.getContent();
    let message = {
        to: "example@mail.com",
        subject: "Invoice",
        body: "Hello.",
        attachments: [{
            fileName: "Invoice.pdf",
            content: content,
            mimeType: "application/pdf"
        }]
    }
    MailApp.sendEmail(message);
    spreadsheet.getSheetByName("Invoice").activate();
}

>Solution :

I was able to get it done via below script :

Please find the reference here for further clarification

function onOpen() 
    {
      var ui = SpreadsheetApp.getUi();
      ui.createMenu('GetValues')
          .addItem('get current', 'getCurrentCellValue')
          .addItem('get by row col', 'getByRowAndColumn')
          .addItem('get by address a1', 'getByCellAddressA1Notation')
          .addToUi();
    }
    
    function getCurrentCellValue()
    {
      var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
      var a1 = cell.getA1Notation();
      var val = cell.getValue();
      
      SpreadsheetApp.getUi().alert("The active cell "+a1+" value is "+val);
    }
    
    function getByRowAndColumn()
    {
      var ui = SpreadsheetApp.getUi();
      
      var response = ui.prompt(
          'Cell address',
        'Please enter the cell address in this format: row,col for example, 5,2 means row 5 col 2',
          ui.ButtonSet.OK);
      
      var resp_text = response.getResponseText();
      
      var match = resp_text.match(/^(\d+)\,(\d+)/);
      
      
      var row = parseInt(match[1]);
      var col = parseInt(match[2]);
      var value = SpreadsheetApp.getActiveSheet().getRange(row,col).getValue();
    
      
      SpreadsheetApp.getUi().alert("The value is "+value);
    }
    
    function getByCellAddressA1Notation()
    {
      var ui = SpreadsheetApp.getUi();
      
      var response = ui.prompt(
          'Cell address',
          'Please enter the cell address (A1 notation)',
          ui.ButtonSet.OK);
      
      var a1 = response.getResponseText();
      
      var value = SpreadsheetApp.getActiveSheet().getRange(a1).getValue();
      
      ui.alert("cell value "+value);
    }

Leave a Reply