I am new to Google App Scripts and have a spreadsheet with five columns: ISIN, Company, SecAccNo, Size and Type. I have named the range of my table A1:E20 Shares.

These columns are automatically populated by a formula, so sometimes I have this table populated with 5 rows of data and sometimes with 20 rows of data. Right now my script sends an email with 20 rows even if I only have 5 rows of data. So 15 rows of blank rows.

I would like to add a function "checkvalue" that checks if in the last column "Type" that is always "Entry" when the row is filled, so that an email is sent with only the rows that have "Entry" in the last column "Type", could you help me with this?

Here is my script:

function getEmailHtml(stockData) {
  var htmlTemplate = HtmlService.createTemplateFromFile("Template.html");
  htmlTemplate.stocks = stockData;
  var htmlBody = htmlTemplate.evaluate().getContent();
  return htmlBody;
}


function sendEmail() {
  var stockData = getData();
  var body = getEmailText(stockData);
  var htmlBody = getEmailHtml(stockData);
  if (stock.type === "Entry")

  MailApp.sendEmail({
    to: "[email protected]", //Enter your email address
    subject: "Manual Posad",
    body: body,
    htmlBody: htmlBody
  });
}
function getEmailText(stockData) {
  var text = "";
  stockData.forEach(function(stock) {
    text = text   stock.isin   "\n"   stock.company   "\n"   stock.secaccno   "\n"   stock.size   "\n"   stock.type   "\n-----------------------\n\n";
  });
  return text;
}
/**
 * @OnlyCurrentDoc
 */

function getData() {
  var values = SpreadsheetApp.getActive().getSheetByName("Data").getRange("Stocks").getValues();
  values.shift(); //remove headers
  var stocks = [];
  values.forEach(function(value) {
    var stock = {};
    //Logger.log("stocks:" stocks);
    stock.isin = value[0];
    stock.company = value[1];
    stock.secaccno = value[2];
    stock.size = value[3];
    stock.type = value[4];
    stocks.push(stock);

  })
  //Logger.log(JSON.stringify(stocks));
  return stocks;
}

CodePudding user response：

You can get an dynamic ending with another use of the

Sheet1: Range: A1:E20