/**************************************************************
* Script by Faraz
**************************************************************/
/**************************************************************
* Script 1: tickCheckboxEnglish()
**************************************************************/
function tickCheckboxEnglish() {
Logger.log("Running tickCheckboxEnglish...");
var sheetName = "Implementation-EN⛔️";
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
Logger.log("Sheet '" + sheetName + "' not found.");
return;
}
var editedRange = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();
if (editedRange.getNumColumns() > 0 && editedRange.getNumRows() > 0) {
var editedRow = editedRange.getRow();
if (editedRow >= 3) {
var columnBValue = sheet.getRange(editedRow, 2).getValue();
var columnCValue = sheet.getRange(editedRow, 3).getValue();
var colorCellC = sheet.getRange(editedRow, 3).getBackground();
var colorCellE = sheet.getRange(editedRow, 5).getBackground();
var checkboxColumn = 8; // Column H
var checkboxValue = sheet.getRange(editedRow, checkboxColumn).getValue();
if (columnBValue !== "" && columnCValue !== "" && colorCellC === '#b7e1cd' && colorCellE === '#b7e1cd' && !checkboxValue) {
sheet.getRange(editedRow, checkboxColumn).setValue(true);
Logger.log("Checkbox set to true for row " + editedRow);
}
}
}
}
/**************************************************************
* Script 2: tickCheckboxArabic()
**************************************************************/
function tickCheckboxArabic() {
Logger.log("Running tickCheckboxArabic...");
var sheetName = "Implementation-AR⛔️";
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
Logger.log("Sheet '" + sheetName + "' not found.");
return;
}
var editedRange = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();
if (editedRange.getNumColumns() > 0 && editedRange.getNumRows() > 0) {
var editedRow = editedRange.getRow();
if (editedRow >= 3) {
var columnBValue = sheet.getRange(editedRow, 2).getValue();
var columnCValue = sheet.getRange(editedRow, 3).getValue();
var colorCellC = sheet.getRange(editedRow, 3).getBackground();
var colorCellE = sheet.getRange(editedRow, 5).getBackground();
var checkboxColumn = 8; // Column H
var checkboxValue = sheet.getRange(editedRow, checkboxColumn).getValue();
if (columnBValue !== "" && columnCValue !== "" && colorCellC === '#b7e1cd' && colorCellE === '#b7e1cd' && !checkboxValue) {
sheet.getRange(editedRow, checkboxColumn).setValue(true);
Logger.log("Checkbox set to true for row " + editedRow);
}
}
}
}
/**************************************************************
* Script 3: recordStaticValuesInCharts()
**************************************************************/
function recordStaticValuesInCharts() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet1 = ss.getSheetByName('Implementation-EN⛔️');
var sourceSheet2 = ss.getSheetByName('Implementation-AR⛔️');
var targetSheet = ss.getSheetByName('Charts');
var targetLastRow = targetSheet.getLastRow();
var targetValues = targetSheet.getRange('A22:A' + targetLastRow).getValues();
var today = new Date();
today.setHours(0, 0, 0, 0); // This is to avoid time mismatch
for (var i = 0; i < targetValues.length; i++) {
var currentDate = new Date(targetValues[i]);
currentDate.setHours(0, 0, 0, 0); // This is to avoid time mismatch
if (+currentDate == +today) { // The "+" operator is used to compare dates by their numeric representation
var sourceValues1 = sourceSheet1.getRange('I1').getValue();
var sourceValues2 = sourceSheet1.getRange('I2').getValue();
var sourceValues3 = sourceSheet2.getRange('I1').getValue();
var sourceValues4 = sourceSheet2.getRange('I2').getValue();
targetSheet.getRange('F' + (i + 22)).setValue(sourceValues1).setNumberFormat('0.00%');
targetSheet.getRange('G' + (i + 22)).setValue(sourceValues2).setNumberFormat('0.00%');
targetSheet.getRange('J' + (i + 22)).setValue(sourceValues3).setNumberFormat('0.00%');
targetSheet.getRange('K' + (i + 22)).setValue(sourceValues4).setNumberFormat('0.00%');
break;
}
}
}
/**************************************************************
* Time-Driven Triggers
**************************************************************/
function createTimeDrivenTriggers() {
ScriptApp.newTrigger('recordStaticValuesInCharts')
.timeBased()
.everyDays(1)
.atHour(5)
.create();
}
/**************************************************************
* Script 4: onEdit(e)
**************************************************************/
function onEdit(e) {
// Call checkbox functions after the edit
tickCheckboxEnglish();
tickCheckboxArabic();
// Pause for 1 minute after an edit
Utilities.sleep(60000); // 60000 milliseconds = 1 minute
}
/**************************************************************
* Script 5: HideAlreadyImplemented()
**************************************************************/
function HideAlreadyImplemented() {
var sheetNames = ["Implementation-EN⛔️", "Implementation-AR⛔️"];
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
sheetNames.forEach(function(sheetName) {
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
Logger.log("Sheet '" + sheetName + "' not found.");
return;
}
var lastRow = sheet.getLastRow();
var valuesH = sheet.getRange("H4:H" + lastRow).getValues();
var valuesI = sheet.getRange("I4:I" + lastRow).getValues();
for (var i = lastRow - 1; i >= 3; i--) { // we start at the end because hiding rows changes the index
if (valuesH[i - 3][0] === true && valuesI[i - 3][0] === true) { // if both H and I are true
sheet.hideRows(i + 1); // Hide the entire row
}
}
});
}
/**************************************************************
* Script 6: fetchMetaDataForSelectedRows()
**************************************************************/
function fetchMetaDataForSelectedRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var selection = sheet.getActiveRange();
var startRow = selection.getRow();
var numRows = selection.getNumRows();
var batchSize = 10; // Define the size of the batch
for (var batchStart = 0; batchStart < numRows; batchStart += batchSize) {
var batchEnd = Math.min(batchStart + batchSize, numRows);
var requests = [];
var rowsToUpdate = [];
for (var i = batchStart; i < batchEnd; i++) {
var row = startRow + i;
if (row >= 3) {
var url = sheet.getRange(row, 2).getValue(); // Fetching URL from Column B
if (url !== "") {
var encodedUrl = encodeURI(url);
requests.push({url: encodedUrl, muteHttpExceptions: true});
rowsToUpdate.push(row);
}
}
}
// Fetch all URLs in the batch simultaneously
var responses = UrlFetchApp.fetchAll(requests);
for (var i = 0; i < responses.length; i++) {
var content = responses[i].getContentText();
var title = "";
var description = "";
var titleMatch = /<title[^>]*>([^<]*)<\/title>/i.exec(content);
if (titleMatch && titleMatch.length >= 2) {
title = titleMatch[1].trim();
}
var metaMatch = content.match(/<meta[^>]+>/g);
if (metaMatch) {
metaMatch.forEach(function(tag) {
if (tag.toLowerCase().includes('name="description"')) {
var matches = tag.match(/content="([^"]+)"/i);
if (matches && matches.length > 1) {
description = matches[1];
}
}
});
}
var row = rowsToUpdate[i];
sheet.getRange(row, 4).setValue(title); // Title in Column D
sheet.getRange(row, 6).setValue(description); // Description in Column F
}
// Optional: Pause between batches to manage rate limits
Utilities.sleep(1000); // Pause for 1 second
}
// Optional: You can call other functions here, like tick checkboxes, if needed
}
function cleanAndDecodeCharacters() {
var sheetNames = ["Implementation-EN⛔️", "Implementation-AR⛔️"];
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
sheetNames.forEach(function(sheetName) {
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
Logger.log("Sheet '" + sheetName + "' not found.");
return;
}
var lastRow = sheet.getLastRow();
var columnDRange = sheet.getRange("D4:D" + lastRow);
var columnDValues = columnDRange.getValues();
var columnFRange = sheet.getRange("F4:F" + lastRow);
var columnFValues = columnFRange.getValues();
var cleanDecode = function(value) {
return value
.replace(/&/g, '&')
.replace(/</g, '<')
.replace(/>/g, '>')
.replace(/"/g, '"')
.replace(/'/g, "'")
.replace(/'/g, "'")
.replace(///g, '/')
.replace(/ /g, ' ')
.replace(/€/g, '€')
.replace(/©/g, '©')
.replace(/®/g, '®')
.replace(/™/g, '™')
.replace(/“/g, '“')
.replace(/”/g, '”')
.replace(/‘/g, '‘')
.replace(/’/g, '’')
.replace(/«/g, '«')
.replace(/»/g, '»')
.replace(/¢/g, '¢')
.replace(/£/g, '£')
.replace(/¥/g, '¥')
.replace(/§/g, '§')
.replace(/¨/g, '¨')
.replace(/ª/g, 'ª')
.replace(/­/g, '')
.replace(/¯/g, '¯')
.replace(/°/g, '°')
.replace(/±/g, '±')
.replace(/²/g, '²')
.replace(/³/g, '³')
.replace(/´/g, '´')
.replace(/µ/g, 'µ')
.replace(/·/g, '·')
.replace(/¸/g, '¸')
.replace(/¹/g, '¹')
.replace(/º/g, 'º')
.replace(/×/g, '×')
.replace(/÷/g, '÷')
.replace(/ø/g, 'ø')
.replace(/Ø/g, 'Ø')
.replace(/ß/g, 'ß')
.replace(/Æ/g, 'Æ')
.replace(/æ/g, 'æ');
};
columnDValues.forEach(function(row) {
if (row[0]) {
row[0] = cleanDecode(row[0]);
}
});
columnFValues.forEach(function(row) {
if (row[0]) {
row[0] = cleanDecode(row[0]);
}
});
columnDRange.setValues(columnDValues);
columnFRange.setValues(columnFValues);
});
}
/**************************************************************
* Script 7: onOpen(e)
**************************************************************/
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu("Implementations Tracker")
.addItem("1.Fetch Metadata for Selected Rows", "fetchMetaDataForSelectedRows")
.addItem("2.Snapshot Imp. Rates - English", "tickCheckboxEnglish")
.addItem("3.Snapshot Imp. Rates - Arabic", "tickCheckboxArabic")
.addItem("4.Hide already implemented values", "HideAlreadyImplemented")
.addItem("5.Clean the titles and Descriptions", "cleanAndDecodeCharacters")
.addToUi();
}