Untitled

/**************************************************************
 * 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(/&amp;/g, '&')
        .replace(/&lt;/g, '<')
        .replace(/&gt;/g, '>')
        .replace(/&quot;/g, '"')
        .replace(/&#x27;/g, "'")
        .replace(/&#39;/g, "'")
        .replace(/&#x2F;/g, '/')
        .replace(/&nbsp;/g, ' ')
        .replace(/&euro;/g, '€')
        .replace(/&copy;/g, '©')
        .replace(/&reg;/g, '®')
        .replace(/&trade;/g, '™')
        .replace(/&ldquo;/g, '“')
        .replace(/&rdquo;/g, '”')
        .replace(/&lsquo;/g, '‘')
        .replace(/&rsquo;/g, '’')
        .replace(/&laquo;/g, '«')
        .replace(/&raquo;/g, '»')
        .replace(/&cent;/g, '¢')
        .replace(/&pound;/g, '£')
        .replace(/&yen;/g, '¥')
        .replace(/&sect;/g, '§')
        .replace(/&uml;/g, '¨')
        .replace(/&ordf;/g, 'ª')
        .replace(/&shy;/g, '­')
        .replace(/&macr;/g, '¯')
        .replace(/&deg;/g, '°')
        .replace(/&plusmn;/g, '±')
        .replace(/&sup2;/g, '²')
        .replace(/&sup3;/g, '³')
        .replace(/&acute;/g, '´')
        .replace(/&micro;/g, 'µ')
        .replace(/&middot;/g, '·')
        .replace(/&cedil;/g, '¸')
        .replace(/&sup1;/g, '¹')
        .replace(/&ordm;/g, 'º')
        .replace(/&times;/g, '×')
        .replace(/&divide;/g, '÷')
        .replace(/&oslash;/g, 'ø')
        .replace(/&Oslash;/g, 'Ø')
        .replace(/&szlig;/g, 'ß')
        .replace(/&AElig;/g, 'Æ')
        .replace(/&aelig;/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();
}