Vous utilisez les applications « Bulk ».Vous commencez à avoir plusieurs milliers de lignes.Nous avons créé un outil qui permet de fusionner les lignes de vos fichiers « Bulk » sans conflit et de vous indiquer les lignes avec des conflits pour les résoudre manuellement.
Pour le mettre en place, suivez les étapes ci-dessous :
[🎥 video à venir]
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Tools EdgeSEO')
.addItem(' Check doublons', 'showForm')
.addToUi();
}
function showForm() {
var htmlOutput = HtmlService.createHtmlOutputFromFile('form')
.setWidth(700)
.setHeight(500);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Tool check doublons');
}
function checkAndMergeDuplicatesStep2(sheetName, emailAddresses) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
if (!sheet) {
SpreadsheetApp.getUi().alert('Sheet not found: ' + sheetName);
return;
}
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var spreadsheetUrl = spreadsheet.getUrl();
var sheetId = sheet.getSheetId();
var sheetUrl = spreadsheetUrl + '#gid=' + sheetId;
var data = sheet.getDataRange().getValues();
var headers = data[0].map(function(header) { return header.replace(/<|>/g, ''); }); // Enlever les chevrons des noms de colonnes
var totalRows = data.length - 1; // Subtract 1 for header row
var urlColumnIndex = 0; // Column A
var duplicateUrls = {};
var mergedRows = [];
var originalIndices = {};
Logger.log('Total rows (excluding header): ' + totalRows);
// Identify duplicates and store original indices
for (var i = 1; i < data.length; i++) {
var url = data[i][urlColumnIndex];
if (url) {
if (duplicateUrls[url]) {
duplicateUrls[url].push(i);
} else {
duplicateUrls[url] = [i];
}
if (!originalIndices[url]) {
originalIndices[url] = [];
}
originalIndices[url].push(i + 1); // Store 1-based index
}
}
var duplicateCount = 0;
for (var url in duplicateUrls) {
if (duplicateUrls[url].length > 1) {
duplicateCount++;
}
}
Logger.log('Number of duplicate URLs: ' + duplicateCount);
var rowsToDelete = [];
var conflictRows = [];
var conflictLogs = [];
// Process each set of duplicates
for (var url in duplicateUrls) {
if (duplicateUrls[url].length > 1) {
var baseRowIdx = duplicateUrls[url][0];
var baseRow = data[baseRowIdx].slice(); // Copy the base row
var mergedLines = [originalIndices[url][0]]; // Use original index
for (var j = 1; j < duplicateUrls[url].length; j++) {
var currentRowIdx = duplicateUrls[url][j];
var currentRow = data[currentRowIdx];
var canMerge = true;
var conflictColumns = [];
// Check for conflicts in columns B to S (indices 1 to 18)
for (var col = 1; col <= 18; col++) {
if (baseRow[col] && currentRow[col] && baseRow[col] !== currentRow[col]) {
canMerge = false;
conflictColumns.push(headers[col]); // Store header name for column
}
}
// Merge if no conflict
if (canMerge) {
for (var col = 1; col <= 18; col++) {
if (!baseRow[col] && currentRow[col]) {
baseRow[col] = currentRow[col];
}
}
rowsToDelete.push(currentRowIdx);
mergedLines.push(originalIndices[url][j]); // Use original index
Logger.log('Merged row ' + originalIndices[url][j] + ' into row ' + originalIndices[url][0]);
} else {
conflictRows.push({url: url, lines: originalIndices[url][0] + ' / ' + originalIndices[url][j], conflicts: conflictColumns.join(' / ')});
var conflictLog = 'Conflit trouvé entre les lignes ' + originalIndices[url][j] + ' et ' + originalIndices[url][0] + ' pour les colonnes ' + conflictColumns.join(' / ');
conflictLogs.push(conflictLog);
Logger.log(conflictLog);
}
}
if (mergedLines.length > 1) {
mergedRows.push({url: url, lines: mergedLines.join(' / ')});
}
// Update the base row in the sheet
for (var col = 1; col <= 18; col++) {
sheet.getRange(baseRowIdx + 1, col + 1).setValue(baseRow[col]); // +1 for 1-based index, +1 to skip URL column
}
}
}
// Sort rows to delete in descending order
rowsToDelete.sort(function(a, b) { return b - a; });
// Delete rows
for (var k = 0; k < rowsToDelete.length; k++) {
sheet.deleteRow(rowsToDelete[k] + 1); // Adjust for header row
}
Logger.log('Rows deleted: ' + rowsToDelete.length);
// Refresh data after deletions
data = sheet.getDataRange().getValues();
// Create and send the report
var mergedRowsTable = '<table border="1" style="width: 100%; border-collapse: collapse;"><tr><th style="width: 50%; background-color: #34495e; color: white; text-align: left; padding: 8px;">URL</th><th style="width: 50%; background-color: #34495e; color: white; text-align: left; padding: 8px;">Lignes fusionnées</th></tr>';
for (var m = 0; m < mergedRows.length; m++) {
var rowColor = (m % 2 == 0) ? '#FFF' : '#ecf0f1';
mergedRowsTable += '<tr style="background-color: ' + rowColor + '; height: 30px;"><td style="padding: 8px;">' + mergedRows[m].url + '</td><td style="padding: 8px;">' + mergedRows[m].lines + '</td></tr>';
}
mergedRowsTable += '</table>';
var conflictRowsTable = '<table border="1" style="width: 100%; border-collapse: collapse;"><tr><th style="width: 50%; background-color: #34495e; color: white; text-align: left; padding: 8px;">URL</th><th style="width: 50%; background-color: #34495e; color: white; text-align: left; padding: 8px;">Ligne avec des conflits</th></tr>';
for (var c = 0; c < conflictRows.length; c++) {
var rowColor = (c % 2 == 0) ? '#FFF' : '#ecf0f1';
var updatedRowIndices = conflictRows[c].lines;
conflictRowsTable += '<tr style="background-color: ' + rowColor + '; height: 30px;"><td style="padding: 8px;">' + conflictRows[c].url + '</td><td style="padding: 8px;">L.' + updatedRowIndices + ' - ' + conflictRows[c].conflicts + '</td></tr>';
}
conflictRowsTable += '</table>';
var now = new Date();
var formattedDate = Utilities.formatDate(now, Session.getScriptTimeZone(), 'dd/MM/yy - HH:mm');
var emailSubject = 'EdgeSEO - ' + formattedDate + ' - Rapport Check Doublons';
var emailBody = '<p>Bonjour 👋,</p>';
emailBody += '<p>Voici le rapport du fichier : <a href="' + sheetUrl + '">' + spreadsheet.getName() + ' - ' + sheetName + '</a>.</p>';
emailBody += '<h2>Rapport des lignes fusionnées</h2><h3>Lignes fusionnées</h3>' + mergedRowsTable + '<h3>Ligne avec des conflits</h3>' + conflictRowsTable;
MailApp.sendEmail({
to: emailAddresses,
subject: emailSubject,
htmlBody: emailBody
});
}
function processForm(formObject) {
var sheetName = formObject.sheetName;
var emailAddresses = formObject.emailAddresses;
checkAndMergeDuplicatesStep2(sheetName, emailAddresses);
}
<!DOCTYPE >
<html>
<head>
<base target="_top">
<style>
body, p {
font-family: Arial, sans-serif;
margin: 0;
padding: 0;
background-color: #FFF;
}
p {
margin-bottom: 20px;
}
.container {
max-width: 700px;
margin: auto;
background: #fff;
padding: 20px;
}
label {
display: block;
margin-bottom: 8px;
color: #333;
font-weight: bold;
}
input[type="text"], select {
width: 100%;
padding: 10px;
margin-bottom: 20px;
border: 1px solid #ccc;
border-radius: 4px;
box-sizing: border-box;
}
input[type="button"] {
width: 100%;
background-color: #34495e;
color: white;
padding: 14px 20px;
margin: 8px 0;
border: none;
border-radius: 4px;
cursor: pointer;
}
input[type="button"]:hover {
background-color: #2c3e50;
}
.confirmation {
display: none;
text-align: center;
margin-top: 20px;
color: #333;
font-size: 16px;
}
.close-button {
width: auto;
background-color: #e74c3c;
color: white;
padding: 14px 20px;
margin: 8px 0;
border: none;
border-radius: 4px;
cursor: pointer;
}
.close-button:hover {
background-color: #c0392b;
}
</style>
</head>
<body>
<div class="container" id="formContainer">
<p id="infoText">L'outil permet de trouver les URLs en double dans la colonne url de l'app « Bulk SEO » et les fusionner s'il n'y a pas de conflit de données.</p>
<form id="mergeForm">
<label for="sheetName">Nom de la feuille à tester :</label>
<input type="text" id="sheetName" name="sheetName" placeholder="Exemple : Prod BulkSEO "><br>
<label for="emailAddresses">Votre e-mail :</label>
<input type="text" id="emailAddresses" name="emailAddresses" placeholder="Exemple : coucou@email.fr"><br>
<input type="button" value="Valider" onclick="submitForm()">
</form>
<div class="confirmation" id="confirmationMessage">
<strong>C'est fait 😄</strong><br /><br />
Vous recevrez un rapport avec les lignes qui ont été fusionnées et celles avec des conflits à résoudre manuellement.<br /><br />
<input type="button" class="close-button" value="Fermer" onclick="closeDialog()">
</div>
</div>
<script>
function submitForm() {
var formObject = {
sheetName: document.getElementById('sheetName').value,
emailAddresses: document.getElementById('emailAddresses').value
};
google.script.run.withSuccessHandler(showConfirmation).processForm(formObject);
}
function showConfirmation() {
document.getElementById('mergeForm').style.display = 'none';
document.getElementById('infoText').style.display = 'none';
document.getElementById('confirmationMessage').style.display = 'block';
}
function closeDialog() {
google.script.host.close();
}
</script>
</body>
</html>