je m'aperçois que parfois la colonne est parfois en F parfois en H (et voilà pourquoi j'avais mis le -5)
function emailOnEdit(event) {
var feuille = event.source.getActiveSheet();
var cellule = event.source.getActiveRange();
if (cellule.getColumn() != 8 && cellule.getColumn() != 6) return;
if (cellule.getValue() == 'MANQUANT' || cellule.getValue() == 'DETERIOREE') {
//definir les variables
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1 = ss.getSheetByName('feuille 1');
var emailAddress = sheet1.getRange(2, 1).getValue();
var subject = sheet1.getRange(2, 2).getValue();
var message = sheet1.getRange(2, 3).getValue();
// envoyer le mail
MailApp.sendEmail({
to: emailAddress + ',autreadresse@gmail.com',
subject: subject,
htmlBody: message + '<b>' + tableHTMLplus(feuille.getRange(cellule.getRow(),1,1,feuille.getLastColumn()))
})
}
}
function tableHTMLplus(range){
var data = range.getDisplayValues()
var size = range.getFontSizes()
var style = range.getFontStyles()
var weight = range.getFontWeights()
var line = range.getFontLines()
var bg = range.getBackgrounds()
var color = range.getFontColors()
var font = range.getFontFamilies()
var textalign = range.getHorizontalAlignments()
var offsetx = range.getRow()
var offsety = range.getColumn()
var mergedRanges = range.getMergedRanges();
var flag = Array.from({ length: range.getNumRows() }, () => Array.from({ length: range.getNumColumns() }, () => true));
var layout = Array.from({ length: range.getNumRows() }, () => Array.from({ length: range.getNumColumns() }, () => ''));
mergedRanges.forEach((rng,i) => {
for (x=rng.getRow();x<rng.getRow()+rng.getNumRows();x++){
for (y=rng.getColumn();y<rng.getColumn()+rng.getNumColumns();y++){
try{flag[x-offsetx][y-offsety]=false}catch(e){}
}
}
flag[rng.getRow()-offsetx][rng.getColumn()-offsety]=true
layout[rng.getRow()-offsetx][rng.getColumn()-offsety]=' colspan="'+rng.getNumColumns()+'" rowspan="'+rng.getNumRows()+'" '
});
var tableformat = 'cellspacing="2" cellpadding="2" border="1" style="width:100%;border-collapse:collapse;border:1px solid #ccc"';
var htmltable = '<table ' + tableformat +' >';
for (row = 0; row<data.length; row++){
htmltable += '<tr>';
for (col = 0 ;col<data[row].length; col++){
if (flag[row][col]){
if (data[row][col] === '') {
htmltable += '<td '+layout[row][col]+' style="background-color:' + bg[row][col] + ';">' + ' ' + '</td>';
}
else
htmltable += '<td '+layout[row][col] +
' style="font-family:' + font[row][col] +
'; background-color:' + bg[row][col] +
'; color:' + color[row][col] +
'; font-style:' + style[row][col] +
'; font-weight:' + weight[row][col] +
'; text-decoration:' + line[row][col] +
'; font-size:' + size[row][col] + 'px' +
'; text-align:' + textalign[row][col] + ';">' +
data[row][col].replace(/(\r\n|\n|\r)/gm,"<br>") + '</td>';
}
}
htmltable += '</tr>';
}
htmltable += '</table>';
return htmltable
}