Category: Uncategorized

This Google Apps Script lists all sheet names from the Google Sheet that you currently have open. It starts listing them in the currently selected cell, then moves down and lists one sheet per cell. It also links that cell with the corresponding sheet. -SM, 2024-08-15

/**
 * This function prints the names of all sheets.
 * -SpreadsheetsMade, 2024-08-15
 */
function printLinkedSheetNamesInAndBelowSelectedCell() {

  var ss;
  var sheets;

  var activeSheet;
  var printSheetNamesToThisColumn;
  var printSheetNamesToThisRow;
  var selectedCell;

  var currentSheet;
  var currentSheetName;
  var currentSheetURL;
  var richText;

  var i;



  ss = SpreadsheetApp.getActiveSpreadsheet();

  sheets = ss.getSheets();

  activeSheet = ss.getActiveSheet();

  selectedCell = activeSheet.getActiveCell();

  printSheetNamesToThisRow = selectedCell.getRow();

  printSheetNamesToThisColumn = selectedCell.getColumn();



  for (i = 0; i < sheets.length; i++) {

    currentSheet = sheets[i];

    currentSheetName = currentSheet.getSheetName();

    currentSheetURL = "#gid=" + currentSheet.getSheetId();

    // Create link to sheet
    richText = SpreadsheetApp.newRichTextValue()
      .setText(currentSheetName)
      .setLinkUrl(currentSheetURL)
      .build();

    // Print sheet names
    activeSheet.getRange(printSheetNamesToThisRow + i, printSheetNamesToThisColumn).setRichTextValue(richText);
  }
}

This is an Apps Script function that converts column letter to column number. It is to be used with Google Sheets and works up to column ZZZ. -SM, 2026-05-10

/**
 * Function converts column letter to column number
 * 
 * - @param columnLetter is required.
 * - @param startAt0or1 is optional.
 * 
 * - If @param startAt0or1 is 1,
 *   letter A is converted to number 1.
 * - If @param startAt0or1 is anything other than 1,
 *   letter A is converted to number 0.
 * 
 * - Google Sheets column limit is ZZZ
 *   (https://support.google.com/drive/answer/37603).
 */

function columnLetterToNumber(columnLetter, startAt0or1) {

  const alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
  const alphabetLength = alphabet.length; // 26
  const colLetLen = columnLetter.length;

  const rightLetter = columnLetter.slice(colLetLen - 1, colLetLen);

  // Convert right letter to number
  let columnNumber = alphabet.indexOf(rightLetter);

  // If column letter is AA or bigger
  if (colLetLen > 1) {
    const middleLetter = columnLetter.slice(colLetLen - 2, colLetLen - 1);

    // Convert middle letter to number, and add it to existing column number
    columnNumber = columnNumber
      + (alphabet.indexOf(middleLetter) + 1)
      * alphabetLength;


    // If column letter is AAA or bigger
    if (colLetLen > 2) {
      const leftLetter = columnLetter.slice(colLetLen - 3, colLetLen - 2);

      // Convert left letter to number, and add it to existing column number
      columnNumber = columnNumber
        + (alphabet.indexOf(leftLetter) + 1)
        * alphabetLength
        * alphabetLength;
    }
  }

  // Define if column numbers start at 0 or 1 
  if (startAt0or1 === 1) {
    columnNumber++;
  }

  // Return column number
  return columnNumber;
}

And here is a short version of this function, but it works only up to and including column Z.

function columnLetterToNumber(columnLetter) {
  return "ABCDEFGHIJKLMNOPQRSTUVWXYZ".indexOf(columnLetter);
}

Old version of this function. Am leaving it here just for reference. -SM, 2024-07-28

/**
 * Function converts column letter to column number
 * 
 * - @param columnLetter is required.
 * - @param startAt0or1 is optional.
 * 
 * - If @param startAt0or1 is 1,
 *   letter A is converted to number 1.
 * - If @param startAt0or1 is anything other than 1,
 *   letter A is converted to number 0.
 * 
 * - Google Sheets column limit is ZZZ
 *   (https://support.google.com/drive/answer/37603).
 */

function columnLetterToNumber(columnLetter, startAt0or1) {

  /**
   * Declare all variables
   */

  var alphabet;
  var alphabetLength;

  var columnNumber;

  var letterLength;
  var letterLeft;
  var letterMiddle;
  var letterRight;



  /**
   * Initialize main variables
   */

  alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

  alphabetLength = alphabet.length; /*26*/

  letterLength = columnLetter.length;



  /**
   * Calculate column number
   */

  /* Get rightmost letter */
  letterRight = columnLetter.slice(letterLength - 1, letterLength);

  /* Convert rightmost letter to number */
  columnNumber = alphabet.indexOf(letterRight);


  if (letterLength > 1) {

    /* Get middle letter */
    letterMiddle = columnLetter.slice(letterLength - 2, letterLength - 1);

    /* Convert middle letter to number, and add it to existing column number */
    columnNumber = columnNumber + (alphabet.indexOf(letterMiddle) + 1) * alphabetLength;


    if (letterLength > 2) {

      /* Get leftmost letter */
      letterLeft = columnLetter.slice(letterLength - 3, letterLength - 2);

      /* Convert leftmost letter to number, and add it to existing column number */
      columnNumber = columnNumber + (alphabet.indexOf(letterLeft) + 1) * alphabetLength * alphabetLength;
    }
  }



  /**
   * Define if column numbers start at 0 or 1 
   */

  if (startAt0or1 == 1) {

    columnNumber++;
  }



  /**
   * Return column number
   */

  return columnNumber;
}

How to capitalize first letter in Excel and Google Sheets. We will look at how to capitalize the first letter of every word (title case) and how to capitalize only the first letter of the first word (sentence case).

Capitalize every word

If you want title case where the first letter of every word is capitalized, and your text is in cell A1, use this formula:

=PROPER(A1)

Capitalize only the very first letter

If you want sentence case where only the first letter of the first word is capitalized, and your text is in cell A1, use this formula:

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN(A1)-1)))

This formula capitalizes only the very first letter and turns the rest into lowercase.

If you want to capitalize the very first letter without turning the rest into lowercase, use the following formula instead:

=CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))