Duplicates in Column A

function onOpen() {

  SpreadsheetApp.getUi()

    .createMenu('Duplicates')

    .addItem('Check Duplicates in Column A', 'checkDuplicatesInColumnA')

    .addToUi();

}

function checkDuplicatesInColumnA() {

  try {

    const sheet = SpreadsheetApp.getActiveSheet();

    const lastRow = sheet.getLastRow();

    const columnARange = sheet.getRange("A1:A" + lastRow);

    const values = columnARange.getValues();

   

    // Clear existing highlighting in column A

    columnARange.setBackground('white');

   

    // Track duplicates

    let valueCount = {};

    let duplicateFound = false;

   

    // First pass: count occurrences

    values.forEach((row, index) => {

      let value = row[0].toString().trim();

      if (value !== '') {

        if (!valueCount[value]) {

          valueCount[value] = {

            count: 1,

            positions: [index + 1]

          };

        } else {

          valueCount[value].count++;

          valueCount[value].positions.push(index + 1);

          duplicateFound = true;

        }

      }

    });

   

    // Second pass: highlight duplicates and prepare message

    let message = '';

    for (let value in valueCount) {

      if (valueCount[value].count > 1) {

        // Highlight all instances of this duplicate

        valueCount[value].positions.forEach(rowNum => {

          sheet.getRange(rowNum, 1).setBackground('#FFA500'); // Orange color

        });

       

        message += `\n"${value}" appears ${valueCount[value].count} times in rows: ${valueCount[value].positions.join(', ')}`;

      }

    }

   

    // Show results

    if (duplicateFound) {

      SpreadsheetApp.getUi().alert('Duplicates found in column A:' + message);

    } else {

      SpreadsheetApp.getUi().alert('No duplicates found in column A');

    }

   

  } catch (error) {

    SpreadsheetApp.getUi().alert('Error: ' + error.toString());

  }

}

getSheetNames()

 function getSheetNames() {

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  var sheetNames = sheets.map(sheet => sheet.getName());

  return sheetNames;

}

getModifiedSheetNames

function getModifiedSheetNames() {

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  var sheetNames = sheets.map(sheet => sheet.getName());

  var removeText = "_SIT";  // Change this to the text you want to remove

  var addText = "https://telus-iot-5g.atlassian.net/browse/";     // Change this to the text you want to add

  // Modify sheet names: Remove specific text and add new text

  var modifiedSheetNames = sheetNames.map(name =>

    addText+ name.replace(removeText, '').replace(/\s+/g, '')

  );

  return modifiedSheetNames;

}

Google sheet: Select default value from the list as per Change is dropdown

function onEdit(e) {

  var sheet = e.source.getActiveSheet();

  var range = e.range;

  var value = range.getValue();

  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var activeRange = SpreadsheetApp.getActiveSheet().getActiveRange();

  var rangeAddress = activeRange.getA1Notation();

  var column = rangeAddress.match(/[A-Z]+/)[0];

  var row = rangeAddress.match(/\d+/)[0];

  // Specify the cell where the dropdown is located (e.g., A1)

  var dp = 'C' + row;//Dropdown Location to update values

  var dropdownCell = sheet.getRange(dp);

  var range1 = sheet.getRange(dp);

  //Dropdown where we are making changes to updated second dropdown(e.g , In B Column we have a dropdown)

  if (column.startsWith('B')) {

    // Small, Caps and Int are the Name Ranges, Need to change it as the Value changes

    if (value == 'Small') {

      setDropdownUsingNamedRange(dp, value);

      setDefaultValue(dp, column + row);

    }

    if (value == 'Caps') {

      setDropdownUsingNamedRange(dp, value);

      setDefaultValue(dp, column + row);

    }

    if (value == 'Int') {

      setDropdownUsingNamedRange(dp, value);

      setDefaultValue(dp, column + row);

    }

  }

}

// funcation used to set name range for the dropdown

function setDropdownUsingNamedRange(range, nameRange) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var range = sheet.getRange(range); // Change this to the cell where you want the dropdown

  // Set the named range as the source for the dropdown

  var rule = SpreadsheetApp.newDataValidation()

    .requireValueInRange(SpreadsheetApp.getActiveSpreadsheet().getRangeByName(nameRange), true)

    .build();

  range.setDataValidation(rule);

}

// Setting the default values in the dropdown. 

function setDefaultValue(dp, dropdownValue) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var range = sheet.getRange(dropdownValue); // Specify the dropdown cell

  var value = range.getValue();

  var targetCell = sheet.getRange(dp); // Specify where to set the default value

  // Check the value of the dropdown

  if (value === 'Caps') {

    targetCell.setValue('A');

  } else if (value === 'Small') {

    targetCell.setValue('a');

  } else if (value === 'Int') {

    targetCell.setValue('1');

  }

}

Set a namespace in dropdown in google sheet

 function setDropdownUsingNamedRange() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var range = sheet.getRange("C3"); // Change this to the cell where you want the dropdown

 

  // Set the named range as the source for the dropdown

  var rule = SpreadsheetApp.newDataValidation()

              .requireValueInRange(SpreadsheetApp.getActiveSpreadsheet().getRangeByName("Caps"), true)

              .build();

             

  range.setDataValidation(rule);

}

Google Sheet Macro to Hide Unused rows

 /**

 * Delete the empty rows and columns outside of the DataRange()

 */

function deleteExternalEmptyRowsNColumns() {

  // get sheets and data

  const ss = SpreadsheetApp.getActiveSheet();

  const data = ss.getDataRange().getValues();

  //console.log(data);

  // determine last row and column

  const lastRow = data.length;

  const lastCol = data[0].length;

  // get maximum rows and columns sss

  const maxRows = ss.getMaxRows();

  const maxCols = ss.getMaxColumns();

  // only remove rows and columns if there are empty rows or columns beyond last row and columns

  if (maxRows > lastRow) {

    ss.deleteRows(lastRow + 1, maxRows - lastRow);

  }

  if (maxCols > lastCol) {

    ss.deleteColumns(lastCol + 1, maxCols - lastCol);

  }

}

Excel- Find Url from LInk

'Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next