Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
20 kB
2
Indexable
Never
/**************************************************************
 * 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) {
  // Get the range of the edited cell
  var range = e.range;
  var column = range.getColumn();
  var row = range.getRow();

  // Check if the edit occurred in columns C (3) or E (5) and the row is greater than or equal to 3
  if ((column === 3 || column === 5) && row >= 3) {
    // Call checkbox functions for the edited rows
    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 valuesG = sheet.getRange("G4:G" + lastRow).getValues();
    var valuesH = sheet.getRange("H4:H" + lastRow).getValues();
    var valuesI = sheet.getRange("I4:I" + lastRow).getValues();

    for (var i = lastRow - 1; i >= 3; i--) {
      // Check if column H is true and column G is empty
      if (valuesH[i - 3][0] === true && !valuesG[i - 3][0]) {
        sheet.getRange(i + 1, 9).setValue(true); // Mark column I as true
        valuesI[i - 3][0] = true; // Update the in-memory array
      }
      
      // If both H and I are true, hide the row
      if (valuesH[i - 3][0] === true && valuesI[i - 3][0] === true) {
        sheet.hideRows(i + 1);
      }
    }
  });
}



/**************************************************************
 * 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) {
  // Get the range of the edited cell
  var range = e.range;
  var column = range.getColumn();
  var row = range.getRow();

  // Check if the edit occurred in columns C (3) or E (5) and the row is greater than or equal to 3
  if ((column === 3 || column === 5) && row >= 3) {
    // Call checkbox functions for the edited rows
    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 valuesG = sheet.getRange("G4:G" + lastRow).getValues();
    var valuesH = sheet.getRange("H4:H" + lastRow).getValues();
    var valuesI = sheet.getRange("I4:I" + lastRow).getValues();

    for (var i = lastRow - 1; i >= 3; i--) {
      // Check if column H is true and column G is empty
      if (valuesH[i - 3][0] === true && !valuesG[i - 3][0]) {
        sheet.getRange(i + 1, 9).setValue(true); // Mark column I as true
        valuesI[i - 3][0] = true; // Update the in-memory array
      }
      
      // If both H and I are true, hide the row
      if (valuesH[i - 3][0] === true && valuesI[i - 3][0] === true) {
        sheet.hideRows(i + 1);
      }
    }
  });
}



/**************************************************************
 * 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
  var urlPattern = new RegExp('^(https?:\\/\\/)?' + // Protocol
                              '((([a-z\\d]([a-z\\d-]*[a-z\\d])*)\\.?)+[a-z]{2,}|' + // Domain name
                              '((\\d{1,3}\\.){3}\\d{1,3}))' + // OR IP
                              '(\\:\\d+)?(\\/[-a-z\\d%_.~+]*)*' + // Port and path
                              '(\\?[;&a-z\\d%_.~+=-]*)?' + // Query string
                              '(\\#[-a-z\\d_]*)?$','i'); // Fragment locator

  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

        // Skip if URL is not valid
        if (url === "" || !urlPattern.test(url)) {
          sheet.getRange(row, 4).setValue("Error: Invalid URL"); // Title in Column D
          sheet.getRange(row, 6).setValue("Error: Invalid URL"); // Description in Column F
          continue;
        }

        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++) {
      if (responses[i].getResponseCode() != 200) {
        // Handle invalid URL case
        var row = rowsToUpdate[i];
        sheet.getRange(row, 4).setValue("Error: Invalid URL"); // Title in Column D
        sheet.getRange(row, 6).setValue("Error: Invalid URL"); // Description in Column F
        continue;
      }

      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];
            }
          }
        });
      }

      title = cleanDecode(title);
      description = cleanDecode(description);

      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 cleanDecode(value) {
  return value
    .replace(/&amp;/g, '&')
    .replace(/&lt;/g, '<')
    .replace(/&gt;/g, '>')
    .replace(/&quot;/g, '"')
    .replace(/&#x27;/g, "'")
    .replace(/&#039;/g, "'") // Added line to replace &#039;
    .replace(/&#39;/g, "'")
    .replace(/&#x2F;/g, '/')
    .replace(/&nbsp;/g, ' ')
    .replace(/&ndash;/g, '–') // Added line to replace &ndash;
    .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, 'æ');
}

function cleanDecodeSelectedRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var selection = sheet.getActiveRange();
  var startRow = selection.getRow();
  var numRows = selection.getNumRows();

  for (var i = 0; i < numRows; i++) {
    var row = startRow + i;
    if (row >= 3) { // Assuming the data starts from row 3
      var title = sheet.getRange(row, 4).getValue(); // Title in Column D
      var description = sheet.getRange(row, 6).getValue(); // Description in Column F

      title = cleanDecode(title);
      description = cleanDecode(description);

      sheet.getRange(row, 4).setValue(title);
      sheet.getRange(row, 6).setValue(description);
    }
  }
}

/**************************************************************
 * 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.Clean and Decode Selected Rows", "cleanDecodeSelectedRows") // New menu item
    .addItem("3.Hide already implemented values", "HideAlreadyImplemented")
    .addItem("4.Snapshot Imp. Rates - English", "tickCheckboxEnglish")
    .addItem("5.Snapshot Imp. Rates - Arabic", "tickCheckboxArabic")
    .addToUi();
}