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

Excel- Create Sheet from List

Sub AddSheets()

'Updateby Extendoffice 20161215

    Dim xRg As Excel.Range

    Dim wSh As Excel.Worksheet

    Dim wBk As Excel.Workbook

    Set wSh = ActiveSheet

    Set wBk = ActiveWorkbook

    Application.ScreenUpdating = False

    For Each xRg In wSh.Range("A1:A30")

        With wBk

            .Sheets.Add after:=.Sheets(.Sheets.Count)

            On Error Resume Next

            ActiveSheet.Name = xRg.Value

            If Err.Number = 1004 Then

              Debug.Print xRg.Value & " already used as a sheet name"

            End If

            On Error GoTo 0

        End With

    Next xRg

    Application.ScreenUpdating = True

End Sub