petems · July 22, 2025 02:29
diff --git a/README.md b/README.md
diff --git a/appscript.json b/appscript.json
 {
  "timeZone": "Europe/London",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
 }
diff --git a/code.gs b/code.gs
 /**
 * @file Google Sheets Hyperlink Shortener
 * @description This script provides a Google Sheets App Script extension
 * to create shorter hyperlinks from existing URLs in cells. It can process
 * an entire column, skipping non-URL cells gracefully.
 */

 /**
 * onOpen function runs automatically when the Google Sheet is opened.
 * It creates a custom menu to make the script easily accessible.
 */
 function onOpen() {
  // Get the active spreadsheet UI.
  const ui = SpreadsheetApp.getUi();

  // Create a custom menu named "Hyperlink Tools".
  ui.createMenu('Hyperlink Tools')
      // Add a menu item "Create Short Hyperlink (Column)" that calls the 'createShortHyperlinksInColumn' function.
      .addItem('Create Short Hyperlink (Column)', 'createShortHyperlinksInColumn')
      // Add the menu to the UI.
      .addToUi();
 }

 /**
 * createShortHyperlinksInColumn function processes all cells in the active column
 * to create new hyperlinks with shorter display text in the adjacent column.
 * It gracefully handles cells that are not valid URLs.
 */
 function createShortHyperlinksInColumn() {
  // Get the active spreadsheet.
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // Get the currently active sheet.
  const sheet = spreadsheet.getActiveSheet();
  // Get the currently selected cell to determine the active column.
  const activeCell = sheet.getActiveCell();

  // Get the index of the column where the active cell is located.
  const activeColumnIndex = activeCell.getColumn();
  // Determine the last row with content in the sheet.
  const lastRow = sheet.getLastRow();

  // If there are no rows with content, exit.
  if (lastRow === 0) {
    spreadsheet.toast('The sheet is empty.', 'Info', 3);
    return;
  }

  // Get the entire range of the active column from row 1 to the last row.
  // The range is (startRow, startColumn, numRows, numColumns).
  const columnRange = sheet.getRange(1, activeColumnIndex, lastRow, 1);

  // Get all formulas and display values from the selected column for efficient processing.
  const cellFormulas = columnRange.getFormulas();
  const cellValues = columnRange.getDisplayValues();

  // Initialize an array to hold the new formulas for the target column.
  // This will be a 2D array, where each inner array represents a row.
  const newFormulasForTargetColumn = [];

  let processedCount = 0; // Counter for successfully created hyperlinks.

  try {
    // Iterate through each row in the collected data.
    // 'i' represents the row index (0-based for arrays, 1-based for sheet rows).
    for (let i = 0; i < lastRow; i++) {
      const currentCellFormula = cellFormulas[i][0]; // [0] because getFormulas returns a 2D array for a single column.
      const currentCellValue = cellValues[i][0];     // [0] because getDisplayValues returns a 2D array for a single column.

      let url = '';
      const shortDisplayText = 'Go to Link'; // Define the short display text for the new hyperlink.

      // Check if the cell contains a HYPERLINK formula.
      if (currentCellFormula.startsWith('=HYPERLINK(')) {
        // Use a regular expression to extract the URL from the HYPERLINK formula.
        const urlMatch = currentCellFormula.match(/^=HYPERLINK\("([^"]*)",/);
        if (urlMatch && urlMatch[1]) {
          url = urlMatch[1];
        }
      } else if (currentCellValue.startsWith('http://') || currentCellValue.startsWith('https://')) {
        // If it's not a HYPERLINK formula, check if the cell value itself is a URL.
        url = currentCellValue;
      }

      // If a valid URL was extracted or found for the current cell.
      if (url) {
        // Construct the new HYPERLINK formula.
        const newHyperlinkFormula = `=HYPERLINK("${url}", "${shortDisplayText}")`;
        // Add the new formula to the array for this row.
        newFormulasForTargetColumn.push([newHyperlinkFormula]);
        processedCount++;
      } else {
        // If no valid URL was found, add an empty string to the array for this row.
        // This ensures the target column maintains its row count and doesn't shift.
        newFormulasForTargetColumn.push(['']);
      }
    }

    // Get the target range one column to the right of the active column.
    // The range is (startRow, startColumn, numRows, numColumns).
    const targetColumnRange = sheet.getRange(1, activeColumnIndex + 1, lastRow, 1);

    // Set all the new formulas to the target column in one go for efficiency.
    targetColumnRange.setFormulas(newFormulasForTargetColumn);

    // Provide feedback to the user.
    if (processedCount > 0) {
      spreadsheet.toast(`Created ${processedCount} short hyperlinks in column ${String.fromCharCode(64 + activeColumnIndex + 1)}.`, 'Success', 5);
    } else {
      spreadsheet.toast('No valid URLs found in the selected column to create hyperlinks.', 'Info', 5);
    }

  } catch (e) {
    // Catch any errors that occur during the process and show a toast message.
    spreadsheet.toast('An error occurred: ' + e.message, 'Error', 5);
  }
 }
diff --git a/HOSTING.MD b/HOSTING.MD
	{
	"timeZone": "Europe/London",
	"dependencies": {},
	"exceptionLogging": "STACKDRIVER",
	"runtimeVersion": "V8"
	}
	/**
	* @file Google Sheets Hyperlink Shortener
	* @description This script provides a Google Sheets App Script extension
	* to create shorter hyperlinks from existing URLs in cells. It can process
	* an entire column, skipping non-URL cells gracefully.
	*/

	/**
	* onOpen function runs automatically when the Google Sheet is opened.
	* It creates a custom menu to make the script easily accessible.
	*/
	function onOpen() {
	// Get the active spreadsheet UI.
	const ui = SpreadsheetApp.getUi();

	// Create a custom menu named "Hyperlink Tools".
	ui.createMenu('Hyperlink Tools')
	// Add a menu item "Create Short Hyperlink (Column)" that calls the 'createShortHyperlinksInColumn' function.
	.addItem('Create Short Hyperlink (Column)', 'createShortHyperlinksInColumn')
	// Add the menu to the UI.
	.addToUi();
	}

	/**
	* createShortHyperlinksInColumn function processes all cells in the active column
	* to create new hyperlinks with shorter display text in the adjacent column.
	* It gracefully handles cells that are not valid URLs.
	*/
	function createShortHyperlinksInColumn() {
	// Get the active spreadsheet.
	const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
	// Get the currently active sheet.
	const sheet = spreadsheet.getActiveSheet();
	// Get the currently selected cell to determine the active column.
	const activeCell = sheet.getActiveCell();

	// Get the index of the column where the active cell is located.
	const activeColumnIndex = activeCell.getColumn();
	// Determine the last row with content in the sheet.
	const lastRow = sheet.getLastRow();

	// If there are no rows with content, exit.
	if (lastRow === 0) {
	spreadsheet.toast('The sheet is empty.', 'Info', 3);
	return;
	}

	// Get the entire range of the active column from row 1 to the last row.
	// The range is (startRow, startColumn, numRows, numColumns).
	const columnRange = sheet.getRange(1, activeColumnIndex, lastRow, 1);

	// Get all formulas and display values from the selected column for efficient processing.
	const cellFormulas = columnRange.getFormulas();
	const cellValues = columnRange.getDisplayValues();

	// Initialize an array to hold the new formulas for the target column.
	// This will be a 2D array, where each inner array represents a row.
	const newFormulasForTargetColumn = [];

	let processedCount = 0; // Counter for successfully created hyperlinks.

	try {
	// Iterate through each row in the collected data.
	// 'i' represents the row index (0-based for arrays, 1-based for sheet rows).
	for (let i = 0; i < lastRow; i++) {
	const currentCellFormula = cellFormulas[i][0]; // [0] because getFormulas returns a 2D array for a single column.
	const currentCellValue = cellValues[i][0]; // [0] because getDisplayValues returns a 2D array for a single column.

	let url = '';
	const shortDisplayText = 'Go to Link'; // Define the short display text for the new hyperlink.

	// Check if the cell contains a HYPERLINK formula.
	if (currentCellFormula.startsWith('=HYPERLINK(')) {
	// Use a regular expression to extract the URL from the HYPERLINK formula.
	const urlMatch = currentCellFormula.match(/^=HYPERLINK\("([^"]*)",/);
	if (urlMatch && urlMatch[1]) {
	url = urlMatch[1];
	}
	} else if (currentCellValue.startsWith('http://') \|\| currentCellValue.startsWith('https://')) {
	// If it's not a HYPERLINK formula, check if the cell value itself is a URL.
	url = currentCellValue;
	}

	// If a valid URL was extracted or found for the current cell.
	if (url) {
	// Construct the new HYPERLINK formula.
	const newHyperlinkFormula = `=HYPERLINK("${url}", "${shortDisplayText}")`;
	// Add the new formula to the array for this row.
	newFormulasForTargetColumn.push([newHyperlinkFormula]);
	processedCount++;
	} else {
	// If no valid URL was found, add an empty string to the array for this row.
	// This ensures the target column maintains its row count and doesn't shift.
	newFormulasForTargetColumn.push(['']);
	}
	}

	// Get the target range one column to the right of the active column.
	// The range is (startRow, startColumn, numRows, numColumns).
	const targetColumnRange = sheet.getRange(1, activeColumnIndex + 1, lastRow, 1);

	// Set all the new formulas to the target column in one go for efficiency.
	targetColumnRange.setFormulas(newFormulasForTargetColumn);

	// Provide feedback to the user.
	if (processedCount > 0) {
	spreadsheet.toast(`Created ${processedCount} short hyperlinks in column ${String.fromCharCode(64 + activeColumnIndex + 1)}.`, 'Success', 5);
	} else {
	spreadsheet.toast('No valid URLs found in the selected column to create hyperlinks.', 'Info', 5);
	}

	} catch (e) {
	// Catch any errors that occur during the process and show a toast message.
	spreadsheet.toast('An error occurred: ' + e.message, 'Error', 5);
	}
	}