such a question: There is a code that sends an email with the data of the cell "var RANGE" Under the condition "var check" > 10 in the "if" function What needs to be done to:
The "var check" condition did not parse a single cell, but a range. (Just write Q123:Q126, it won't work, the script is not executed) and the condition should not be >10, but equal to =10 since the trigger runs the function every day and checks the table.
If the number 10 is in the "var check" range, then the entire line should be sent by email. If it doesn't work out, then at least do it like this>
"var RANGE" make it possible to selectively take cells (ex. B121:N129, R123:T129)
Can i get some advice?
var ID = "1sBtjJ9x4IKwb9GKu3x3DwoNWTxPej7HJWk5ze8aK"; //speadsheet id
var EMAIL = "Test@gmail.com"; //email
var RANGE = "Warranty_2021!B121:n129"; //data range to send
var check = "Warranty_2021!Q123:Q123"; //parametr
var text = "Servis"; //subject
function sendData() {
var spreadsheet = SpreadsheetApp.openById(ID);
var data = spreadsheet.getRangeByName(RANGE).getValues();
var message = {};
if (SpreadsheetApp.openById(ID).getRangeByName(check).getValues()>10){ //condition
message.subject = "[La Test] " + text;
message.to = EMAIL;
message.htmlBody = dataToHtmlTable_(data) +
"<br><br>[IT care you ;)]";
MailApp.sendEmail(message);
}
let quota = MailApp.getRemainingDailyQuota();
console.log(quota)
}
Array.prototype.datesToString = function(){
return this.map(function(row){
return row.map(function(cell){
return cell && cell.getTime ? Utilities.formatDate(cell, Session.getScriptTimeZone(), "yyyy-MM-dd ") : cell;
});
});
}
function dataToHtmlTable_(data){
return JSON.stringify(data, null, " ")
.replace(/^\[/g, "<table>")
.replace(/\]$/g, "</table>")
.replace(/^\s\s\[$/mg, "<tr>")
.replace(/^\s\s\],{0,1}$/mg, "</tr>")
.replace(/^\s{4}"{0,1}(.*?)"{0,1},{0,1}$/mg, "<td>$1</td>");
}Done it. @cooper help me just a little. Solution:
var ID = "1sBtjJ9x4IKwb9GKu3x3DwoNWTxPej7"; //speadsheet id
var EMAIL = "Test@gmail.com"; //email
var RANGE_LUT = "Warranty_2021!B10:Q20"; //data range to send
var text = "Time to ..."; //subject
function sendData() {
var spreadsheet = SpreadsheetApp.openById(ID);
var dataLut = spreadsheet.getRangeByName(RANGE_LUT).getValues();
var message = {};
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Warranty_2021");
var colValuesLut = sheet.getRange(11, 16, 10, 1).getValues();
for (var i = 0; i < colValuesLut.length; i++) {
if (colValuesLut[i][0] == -10) { //condition, value = -10
message.subject = "[La test] " + text;
message.to = EMAIL;
message.htmlBody = dataToHtmlTable_(dataLut) +
"<br><br>[IT care you]";
MailApp.sendEmail(message);
}
let quota = MailApp.getRemainingDailyQuota();
console.log(quota)
}
}